PostgreSQL Table Partitioning in Practice

I. Partitioning Relieves the Pain of Large Tables

After a business has been running for two or three years, its events, logs, or orders table may quietly grow to hundreds of millions of rows. Then everything starts slowing down:

• Slow queries: Even with indexes, random I/O through a BTree on a table with hundreds of millions of rows can take several seconds; range scans are even worse.
• Large indexes: The indexes themselves can reach hundreds of GB. Less and less of them fit in shared_buffers, sending the cache hit rate downward.
• Slow VACUUM: PostgreSQL implements MVCC through multiple row versions. DELETE/UPDATE leaves dead tuples behind, and VACUUM must scan the entire table to reclaim them. The larger the table, the longer the maintenance window and the worse the bloat.
• Slow deletion of old data: DELETE FROM events WHERE created_at < '2024-01-01' looks harmless, but it actually scans storage, writes WAL, creates enormous numbers of dead tuples, and triggers another lengthy VACUUM. Deleting one month of data can take hours and hold locks long enough to make you nervous.

The idea behind partitioning is simple: instead of treating one large table as a single unit, split it into several smaller physical tables called partitions while keeping it logically as one table. This directly addresses all four problems above:

• When a query includes the partition key, it scans only the relevant small partitions through partition pruning, reducing I/O by orders of magnitude.
• Each partition has its own indexes, so the entire index for a hot partition can fit in memory.
• VACUUM runs per partition, making each job smaller and faster while keeping bloat under control.
• To remove old data, simply DROP TABLE old_partition. It finishes almost instantly and creates zero dead tuples. This is one of partitioning's biggest advantages.

In short: partitioning is not primarily about making a single query much faster; it is about making day-to-day operations on large tables manageable again.

II. Three Partitioning Types: Choosing the Right One Comes First

PostgreSQL 10 introduced native declarative partitioning with three types:

RANGE Partitioning: By Range, and the Most Common

Data is divided by ranges of a field, most commonly time. Monthly, weekly, or daily partitions are the first choice for logs, events, and orders.

CREATE TABLE events (
    id          BIGSERIAL,
    created_at  TIMESTAMPTZ NOT NULL,
    user_id     BIGINT,
    payload     JSONB,
    PRIMARY KEY (id, created_at)   -- The partition key must be in the primary key; see below
) PARTITION BY RANGE (created_at);

LIST Partitioning: By Enumerated Value

Data is divided by discrete values of a field, typically region, tenant, or category. Partitioning a multi-tenant SaaS application by tenant_id is common.

CREATE TABLE users (
    id        BIGSERIAL,
    region    TEXT NOT NULL,
    name      TEXT,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE users_cn PARTITION OF users FOR VALUES IN ('CN');
CREATE TABLE users_us PARTITION OF users FOR VALUES IN ('US', 'CA');
CREATE TABLE users_other PARTITION OF users DEFAULT;  -- Catch-all partition

HASH Partitioning: Even Hash Distribution

When there is no clear time or category dimension but the table is simply too large and you want to distribute load and storage, use HASH. PG hashes the partition key, applies a modulus, and routes each row to a fixed partition.

CREATE TABLE sessions (
    id            UUID PRIMARY KEY,        -- The partition key is id, so it is already in the primary key
    user_id       BIGINT,
    last_seen_at  TIMESTAMPTZ
) PARTITION BY HASH (id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ... Continue through sessions_7

How should you choose? If the data has a time dimension, prefer RANGE; it covers 90% of use cases. If it has a stable category dimension with a relatively even distribution, use LIST. If no such dimension exists but the table still must be split, use HASH as a fallback. Do not partition merely for the sake of partitioning. HASH partitioning offers limited benefit for an individual point lookup; its main value is distributing writes and VACUUM pressure.

III. Creating Partitions: Parent Table + Child Partitions

Declarative partitioning presents two views: the application sees one logical parent table, while storage consists of multiple physical child partitions. The parent stores only metadata; all data lives in the child partitions.

-- 1. Parent table: declare the partitioning strategy
CREATE TABLE events (
    id          BIGSERIAL,
    created_at  TIMESTAMPTZ NOT NULL,
    user_id     BIGINT,
    payload     JSONB,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 2. Create child partitions and bind their ranges
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 3. A child partition can itself be partitioned (multilevel partitioning)
CREATE TABLE events_2024_03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01')
    PARTITION BY RANGE (user_id);

Ranges are inclusive at the lower bound and exclusive at the upper bound, [from, to), so adjacent partition boundaries fit together without leaving gaps. On insertion, PG automatically routes the row to the corresponding child partition based on the partition key. If no matching partition exists, it immediately reports an error (no partition of relation "events" found for row) unless you created a DEFAULT partition as a fallback.

Create indexes on the parent table, and they automatically propagate to all child partitions, including those created later:

CREATE INDEX idx_events_user ON events (user_id);
CREATE INDEX idx_events_payload ON events USING GIN (payload);

IV. Partition Pruning: The Core of Partitioning

Partitioning pays off when the SQL WHERE clause lets the query planner derive the partition key's value. The planner then scans only the relevant partitions and skips every other partition. This is called partition pruning.

EXPLAIN SELECT * FROM events
WHERE created_at >= '2024-02-10' AND created_at < '2024-02-15';

In a good execution plan, only the events_2024_02 partition should be scanned. Partitions for every other month should not appear at all. There are two kinds of pruning:

• Static pruning: The condition is a constant, so pruning happens during planning.
• Runtime pruning: The condition is a parameter, such as one from PREPARE or a partition key in a JOIN, so pruning cannot happen until execution. PG 13+ substantially improved support for pruning on partition keys used in a JOIN.

Common pitfall: A cross-partition query does not automatically scan only one partition. If you write SELECT * FROM events WHERE user_id = 123 without including created_at, PG scans every partition. The partitioned table may even be slower than a regular table because of the added partition traversal overhead. Make it a habit to include the partition key in queries against partitioned tables.

EXPLAIN is your only reliable ally. Before deploying any critical query, inspect its execution plan and confirm that pruning works. This is more dependable than any tuning rule of thumb.

V. Maintenance: Automatically Create New Partitions and Directly Remove Old Ones

Once a partitioned table is in production, two operational tasks must be automated.

1. Continuously create future partitions. Do not wait for an insertion error to discover that next month's partition does not exist. There are two approaches:

• pg_partman: The community's most mature partition management extension. It automatically maintains partitions, supports range and list partitioning, and lets you configure how many future partitions to create in advance. It is recommended for production.
• Scheduled jobs: Use cron or pg_cron to run a stored procedure that executes CREATE TABLE ... PARTITION OF ... every day and prepares partitions for the next N days.

-- pg_cron: create partitions for the next 7 days at 03:00 every day
SELECT cron.schedule('create_events_partitions', '0 3 * * *', $$SELECT partman.create_parent(...)$$);

2. Remove old data cleanly and decisively. This is partitioning's biggest operational benefit:

-- Old approach: DELETE tens of millions of rows, followed by a lengthy VACUUM
DELETE FROM events WHERE created_at < '2024-01-01';

-- Partitioned-table approach: DROP one partition in milliseconds, with zero dead tuples
DROP TABLE events_2024_01;
-- Or detach it from the parent, retain the data for observation, and DROP it after verification
ALTER TABLE events DETACH PARTITION events_2024_01;

DETACH is the safer approach: first detach the partition from its parent so queries stop scanning it, retain the data, and then DROP it after confirming that everything is correct. Concurrency and lock overhead are also manageable, and PG 14+ supports ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY.

VI. When to Partition: Large Tables + A Clear Dimension

Not every table should be partitioned. I have seen HASH partitioning forced onto configuration tables with only a few hundred thousand rows, making queries slower because the fixed cost of traversing partitions completely outweighed any benefit on a small table.

Signals that a table should be partitioned (preferably at least two should apply):

• The table has tens of millions of rows or more and occupies at least tens of GB.
• It has a clear query dimension, such as time, tenant, or region, and most queries naturally include that dimension.
• Old data must be deleted regularly, as with logs, events, or analytics events.
• The VACUUM window has become an operational burden and bloat is severe.

Cases where you should not partition:

• Small tables with no more than a few hundred thousand rows. Indexes and conventional optimization are enough.
• There is no stable query dimension, and queries use many unrelated fields. Nothing can be pruned, so partitioning provides no benefit.
• Write pressure is extremely high but every partition must still bear the load evenly. Partitioning is not a silver bullet for write performance, and HASH partitioning adds overhead to each insert.

Rule of thumb: A table with hundreds of millions of rows plus a time dimension is almost an automatic candidate for RANGE partitioning. Below ten million rows, optimize indexes, queries, and connection pool settings before rushing to partition.

VII. Predictable Pitfalls

• The partition key must be included in the primary key or unique constraint. PG requires every uniqueness constraint to include all partition key columns; otherwise, it cannot guarantee uniqueness across partitions. That is why the earlier primary key was PRIMARY KEY (id, created_at) rather than PRIMARY KEY (id). Need a globally unique id? Use a UUID or generate it in the application instead of relying on a single-column unique constraint.
• Cross-partition queries are not pruned. This bears repeating: a query without the partition key scans every partition. Ignore this rule, and a partitioned table can be slower than a regular table.
• A DEFAULT partition is a double-edged sword. It is a convenient fallback, but once data lands in DEFAULT, PG may be unable to prune precisely for later range queries, and extracting that data into separate partitions is cumbersome. Prefer creating future partitions in advance instead of relying on DEFAULT.
• A FOREIGN KEY pointing to a partitioned table has limitations. PG did not support this before version 12. It does now, but cross-partition constraints still impose performance overhead, so account for that in the design.
• Do not create too many partitions. Thousands of partitions significantly increase planning time. PG 14 improved this considerably, but the cost still exists. If monthly partitions can cover several years, do not partition by day.

VIII. Partitioning vs. Sharding: Do Not Confuse Them

One common misconception needs clarification. Partitioning splits data within a single database. All partitions share one PostgreSQL instance, one WAL stream, and one transaction model. It solves the problem of an oversized table and remains simple, transparent, and invisible to the application.

Sharding distributes data across multiple independent database instances, each of which is self-contained. It solves the problem of a single database reaching its limits in write throughput, connection count, or storage capacity. The cost is high: cross-shard transactions, distributed queries, globally unique IDs, application routing, and much more. Complexity rises sharply, so sharding is a last resort.

In practice: first split vertically by placing business domains in separate databases → then partition horizontally with PG partitioned tables → only then, if necessary, shard horizontally across databases. Most systems can stop at the second step. Far fewer workloads genuinely require sharding than online articles suggest.

Summary

• A partitioned table is logically one table but physically many tables. Its core benefits are scanning only relevant partitions + replacing DELETE with DROP for old partitions + faster VACUUM on smaller partitions.
• The three types are RANGE for time and the most common use case, LIST for enumerations or tenants, and HASH as a fallback when no suitable dimension exists.
• Use PARTITION BY to create the parent and CREATE TABLE ... PARTITION OF to create child partitions. Indexes created on the parent are automatically propagated.
• Partition pruning is the key: queries must include the partition key to be pruned, and EXPLAIN is the only way to verify it.
• Automate two operational tasks: continuously create future partitions with pg_partman or cron, and remove old data by using DROP or DETACH on old partitions.
• The partition key must be part of the primary key or unique constraint. Queries without the partition key are not pruned, and the partition count should not reach the thousands.
• The threshold is a large table with tens or hundreds of millions of rows plus a clear query dimension. Do not partition small tables.
• Partitioning is not sharding. The former splits data within one database and is simple; the latter spans multiple databases, is complex, and should be a last resort.

Key takeaway: Partitioned tables are not about running faster; they are about keeping large tables running longer. They bring routine queries and operations on hundreds of millions of rows back to a manageable scale. That is the real value of partitioning.