A 500 GB Postgres table that takes hours to vacuum and forever to query is a candidate for partitioning. Done right, partitioning turns “this query times out” into “this query is fast.” This post is the working guide.

When partitioning helps

  • Table > 50 GB, growing fast.
  • Access pattern strongly correlates with one column (typically time).
  • You delete old data regularly (drop partitions; instant).
  • Maintenance (VACUUM, REINDEX) takes too long on the whole table.

When it doesn’t:

  • Tables under 50 GB. Indexes alone are fine.
  • No natural partition key.
  • Workload spans all partitions equally.

Range partitioning (the common case)

CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT,
    created_at TIMESTAMPTZ NOT NULL,
    payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_q2 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

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

CREATE INDEX events_2026_q2_user ON events_2026_q2 (user_id);
CREATE INDEX events_2026_q3_user ON events_2026_q3 (user_id);

Postgres routes inserts to the right partition. Queries with WHERE created_at BETWEEN ... only scan matching partitions (partition pruning).

List partitioning

CREATE TABLE orders_by_region (
    id BIGINT,
    region TEXT NOT NULL,
    ...
) PARTITION BY LIST (region);

CREATE TABLE orders_in PARTITION OF orders_by_region FOR VALUES IN ('IN');
CREATE TABLE orders_us PARTITION OF orders_by_region FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_eu PARTITION OF orders_by_region FOR VALUES IN ('GB', 'FR', 'DE');

For explicit, finite categorical keys.

Hash partitioning

CREATE TABLE messages (
    id BIGINT,
    user_id BIGINT NOT NULL,
    ...
) PARTITION BY HASH (user_id);

CREATE TABLE messages_p0 PARTITION OF messages FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE messages_p1 PARTITION OF messages FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ... 4 partitions total

Even distribution. Good for write-heavy uniformly-distributed workloads. Pruning works only when the query has WHERE user_id = X.

Partition pruning

EXPLAIN ANALYZE
SELECT * FROM events WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01';

The plan should scan only events_2026_q2. If it scans all partitions, partition pruning failed — usually because:

  • Query uses functions that hide the constant (WHERE date_trunc('month', created_at) = ...).
  • Query joins on the partition key.

Rewrite to use plain comparisons.

Drop old partitions instantly

DROP TABLE events_2025_q1;

Instant. No DELETE, no VACUUM, no bloat. The single biggest operational benefit of partitioning.

For incremental archival, DETACH first, then move:

ALTER TABLE events DETACH PARTITION events_2025_q1;
COPY events_2025_q1 TO 's3://bucket/...';
DROP TABLE events_2025_q1;

Auto-creating future partitions

A common pattern: cron job creates the next month’s partition before it’s needed.

CREATE OR REPLACE FUNCTION ensure_partition(date_arg date) RETURNS void AS $$
DECLARE
    partition_name TEXT := 'events_' || to_char(date_arg, 'YYYY_MM');
    start_date DATE := date_trunc('month', date_arg);
    end_date DATE := start_date + interval '1 month';
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = partition_name) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
                       partition_name, start_date, end_date);
        EXECUTE format('CREATE INDEX ON %I (user_id)', partition_name);
    END IF;
END;
$$ LANGUAGE plpgsql;

Run via pg_cron or app-side scheduler.

Default partition

Catches rows that don’t match any partition:

CREATE TABLE events_default PARTITION OF events DEFAULT;

Useful as a safety net. But if it grows, you’ve forgotten to create a real partition.

Indexes per partition

Indexes are per-partition (no global index in Postgres). Two implications:

  • Index by partition key NOT needed; partition pruning handles it.
  • Other indexes (e.g., user_id) must be created on each partition (or with ON ONLY parent + per-partition).
CREATE INDEX events_user_id ON events (user_id);   -- creates on all current + future

Pitfalls

1. Bad partition key

Picking user_id for a 50/50 read-write workload that’s sometimes time-scoped means time-range queries scan everything. Choose by query pattern.

2. Too many partitions

Hundreds of thousands of partitions slow planning. Stick to ~tens to a few hundred.

3. UPDATE that moves rows across partitions

By default Postgres allows UPDATE that changes a partition-key value, moving the row. Surprising and slow. Set ROW MOVEMENT explicitly if you depend on this.

4. Foreign keys

Partitioned-to-partitioned FKs work in 11+. FKs from non-partitioned to partitioned require care. Plan.

5. Forgetting to add indexes on new partitions

Auto-creation should include all the indexes. Or use CREATE INDEX ON parent (Postgres 11+ inherits to children).

Read this next

If you want my time-series partitioning template + auto-create cron, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .