Database Table Partitioning: Range, List, Hash, Pruning, and Retention

Database Table Partitioning: Range, List, Hash, Pruning, and Retention

Table partitioning splits one logical table into smaller physical pieces inside the database. Done well, it can make very large tables easier to query, maintain, retain, and archive. Done badly, it adds planning overhead and operational complexity without fixing the actual bottleneck.

Partitioning is often confused with sharding. They are related ideas, but they solve different problems:

• partitioning usually divides a table inside one database system
• sharding distributes data across independent database nodes or write owners

This guide focuses on table partitioning itself: choosing the key, pruning, indexes, retention, operational workflows, and failure modes.

What partitioning changes

Without partitioning, one logical table is stored as one table object:

events

With monthly range partitioning, the application can still query events, while storage is divided into child partitions:

events_2026_07
events_2026_08
events_2026_09

The database routes rows to the correct partition and can skip irrelevant partitions when a query predicate proves they cannot contain matching rows.

That skipping behavior is called partition pruning.

Partitioning is not automatically a performance feature

A smaller physical table can be easier to manage, but partitioning helps only when the workload aligns with the partition boundaries.

Good reasons include:

• very large time-series/event tables
• retention by date
• large tenant datasets with clear tenant locality
• bulk archival/removal
• reducing the amount of data scanned for common predicates
• isolating operational maintenance to subsets of data

Weak reasons include:

• “the table has a million rows, so it must be partitioned”
• “partitioning sounds scalable”
• hoping partitioning will repair missing indexes or inefficient queries

PostgreSQL's current documentation explicitly notes that poor partitioning design can hurt planning and execution. The partition key should usually align with the columns commonly used to restrict the data.

Range partitioning

Range partitioning divides values into non-overlapping ranges.

Time is the classic example:

CREATE TABLE events (
    id bigint,
    occurred_at timestamptz NOT NULL,
    payload jsonb
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_09
PARTITION OF events
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Range partitioning works well for:

• events by day/month/year
• orders by creation date
• logs by timestamp
• numeric ID ranges

Its biggest operational advantage is often retention. Old data can be detached or dropped by partition instead of deleting rows one by one.

List partitioning

List partitioning assigns explicit values to partitions.

Example:

CREATE TABLE invoices (
    id bigint,
    region text NOT NULL,
    amount numeric
) PARTITION BY LIST (region);

You might create partitions for regions such as:

IN
US
EU

List partitioning is useful when there is a small, stable set of categories with clear operational meaning.

Be careful when the value set changes frequently. A scheme that requires constant DDL just to accept new values may become awkward.

Hash partitioning

Hash partitioning distributes rows according to a hash of the partition key.

It is useful when:

• no natural ranges exist
• you want a more even distribution
• retention is not tied to the partition boundary
• the key has enough cardinality to distribute well

A conceptual split might be:

hash(customer_id) mod 4 = 0
hash(customer_id) mod 4 = 1
hash(customer_id) mod 4 = 2
hash(customer_id) mod 4 = 3

Hash partitioning is good for distribution inside one database, but it does not inherently add multiple independent database servers.

Choose the partition key from the access pattern

The partition key is the most important design decision.

Suppose a huge events table is partitioned by created_at, and almost every query looks like:

WHERE created_at >= now() - interval '7 days'

That is a strong match: the database can prune old partitions.

Now suppose the common query is:

WHERE user_id = $1

and it includes no time predicate. If data is partitioned only by date, the database may need to consider many partitions to find that user's rows.

Partition by the field that matches the dominant operational/query boundary, not merely the field that is easiest to explain.

Partition pruning is where much of the query benefit comes from

When the optimizer can prove a partition cannot contain matching rows, it can exclude that partition from the plan.

For example:

SELECT *
FROM events
WHERE occurred_at >= '2026-09-01'
  AND occurred_at <  '2026-09-08';

If the table is partitioned by month, the database may only need to access the September partition.

PostgreSQL performs pruning from partition bounds, and current versions can also prune during execution when parameter values become known later.

You can verify pruning with EXPLAIN / EXPLAIN ANALYZE rather than assuming it happened.

Partitioning does not replace indexes

Partition pruning determines which partitions should be considered.

Indexes determine how rows inside a chosen partition are found.

If a query selects a tiny subset of one large partition, an index may still be essential.

For example:

WHERE occurred_at >= $1
  AND occurred_at < $2
  AND user_id = $3

Time partitioning can remove irrelevant months, while an index involving user_id may make the scan inside the remaining partition efficient.

Partitioning and indexing solve different layers of the query plan.

Smaller indexes can improve locality

One benefit of partitioning is that each partition can have its own smaller index structures.

That can help when:

• queries touch only recent partitions
• recent indexes stay hot in memory
• old partitions are queried rarely

But smaller indexes are not free: every partition adds catalog objects, planning work, maintenance, and operational state.

Too many tiny partitions can be worse than a manageable unpartitioned table.

Do not create thousands of partitions casually

Partition count has a cost.

Very fine-grained partitioning can increase:

• planning time
• catalog size
• DDL operations
• monitoring noise
• maintenance complexity
• memory used for metadata/locks

If you partition a high-volume table by minute for years of retention, the result may be operationally absurd.

Choose a granularity that matches both query patterns and lifecycle operations.

Typical choices might be:

• daily partitions for very high-volume events
• monthly partitions for business records
• yearly partitions for slower-growing historical tables

The correct granularity depends on the workload, not a fixed row-count rule.

Retention is one of partitioning's strongest use cases

Consider a log table that keeps 90 days of data.

Without partitioning, cleanup may involve:

DELETE FROM logs
WHERE created_at < cutoff;

A massive delete can generate significant WAL/redo, dead tuples, index work, and vacuum/cleanup overhead depending on the database.

With time partitioning, retention can become:

detach/drop oldest partition

PostgreSQL documentation specifically highlights dropping or detaching partitions as much faster than bulk deletion and notes that it avoids the vacuum overhead caused by large DELETE operations.

For append-heavy time-series workloads, this operational benefit can justify partitioning even when query speed was acceptable before.

Pre-create future partitions

A range-partitioned table needs somewhere to route new rows.

If the next month's partition does not exist, inserts can fail unless a suitable default partition or equivalent strategy is configured.

Treat partition creation as normal infrastructure automation:

create future partitions ahead of time
monitor that upcoming range exists
alert before the current partition expires

Do not discover missing partitions from production insert errors at midnight on the first day of a month.

A default partition can help—but can hide mistakes

Some databases support a default partition that receives rows that do not match an explicit partition.

This can protect availability, but it can also hide broken partition automation.

If a default partition exists, monitor it. Unexpected growth means rows are not being routed where the design intended.

Partition key updates can move rows

If a row's partition key changes so that it belongs to another partition, the database may need to move that row between partitions.

That can be significantly more expensive than updating a normal non-partition key.

Choose partition keys that are naturally stable when possible.

Time-of-creation and immutable tenant ownership are often easier to manage than frequently edited categories.

Unique constraints become more complicated

Global uniqueness across partitions can have restrictions depending on the database.

In PostgreSQL, a unique or primary-key constraint on a partitioned table generally needs to include all partition-key columns so uniqueness can be enforced using the per-partition indexes.

That can influence schema design.

For example, if you partition by tenant_id, a natural key might become:

(tenant_id, id)

instead of assuming that id alone can always be enforced globally in the same way.

Review your database's exact constraint rules before partitioning an existing table.

Foreign keys and cross-partition operations deserve testing

Declarative partitioning has become much more capable over modern database versions, but operations involving:

• foreign keys
• unique constraints
• ON CONFLICT
• partition-key updates
• bulk attach/detach
• schema migrations

should be tested against the exact database version you run.

Do not design from old blog posts: partitioning capabilities and limitations have changed substantially over time in systems such as PostgreSQL and MySQL.

Partition maintenance is part of the architecture

A partitioned table introduces lifecycle work:

• create upcoming partitions
• validate bounds
• build indexes
• analyze statistics
• archive or detach old partitions
• monitor skew
• vacuum/maintain partitions as required
• migrate schema changes across the partition hierarchy

If no one owns that automation, the design is not production-ready.

Partitioning by tenant

Multi-tenant systems often consider list or hash partitioning by tenant_id.

Potential advantages:

• tenant-local queries can prune aggressively
• large tenants can be isolated operationally
• tenant data may be easier to archive/migrate

Potential problems:

• many tenants can mean far too many partitions
• tenant sizes may be extremely uneven
• one huge tenant can dominate a partition
• cross-tenant reporting may touch many partitions

Creating one partition per tenant is usually unsuitable when tenant count can grow very large.

Hashing tenants across a bounded number of partitions can be more manageable when the goal is even distribution rather than per-tenant lifecycle control.

Partitioning by time

Time-based range partitioning is one of the cleanest use cases because query and retention patterns often align naturally.

Good fits include:

• logs
• audit events
• metrics
• transactions with long retention
• activity feeds

It works best when common queries include time predicates and old data is removed/archived by time window.

If nearly every query needs a user's full history across ten years, time partitioning may not reduce the number of partitions touched for those queries.

Partitioning vs sharding

Partitioning

Typically:

one logical database
one logical table
multiple physical partitions

Primary goals:

• pruning
• maintenance
• retention
• manageable large tables

Sharding

Typically:

multiple independent database nodes
routing determines which node owns the data

Primary goals:

• horizontal storage scale
• horizontal write capacity
• failure/isolation boundaries

Partitioning can be a useful precursor to sharding because it forces the team to define data boundaries, but one does not automatically imply the other.

When partitioning is the wrong fix

Do not partition a table just because queries are slow.

First check:

• missing or weak indexes
• poor query predicates
• stale statistics
• unnecessary sequential scans
• bad joins
• application N+1 queries
• connection/storage pressure

A well-indexed normal table can outperform an unnecessarily partitioned design while being far simpler to operate.

What to monitor

For a partitioned table, observe:

• rows and bytes per partition
• partition skew
• number of partitions scanned per important query
• query planning time
• partition-pruning effectiveness
• insert-routing failures
• default-partition growth
• future partition availability
• retention jobs
• index size per partition

Use EXPLAIN to confirm the optimizer is actually pruning partitions for your critical queries.

A practical decision framework

Partitioning is a strong candidate when:

• the table is genuinely large
• common queries naturally restrict by a partition key
• retention can drop entire ranges
• maintenance on subsets is valuable
• index/data locality improves for hot ranges

Avoid it when:

• the table is still comfortably manageable
• queries do not filter on a useful partition key
• partition count would explode
• the team has no lifecycle automation
• the real problem is a missing index or inefficient query

The best partition design makes the database touch less data for common work and less data for lifecycle maintenance. If it merely creates more tables without eliminating work, it has not earned the complexity.

References

• PostgreSQL — Table Partitioning
• PostgreSQL — EXPLAIN
• MySQL — Partitioning