Skip to content

Postgres Table Partitioning

Declarative partitioning, available since Postgres 10, splits a large table into smaller physical child tables while presenting a single logical table to queries. Done correctly, it enables partition pruning — the planner skips irrelevant partitions entirely — dramatically reducing I/O for time-range and category queries. Done incorrectly, it multiplies maintenance complexity with no performance benefit.

When partitioning helps

Partitioning solves two problems. First, it enables partition pruning: if your query always filters on the partition key (e.g., WHERE created_at > '2024-01-01'), the planner only scans the matching partitions. Second, it enables partition-wise maintenance: VACUUM, REINDEX, and data archival operate on one partition at a time, not the full table.

Partitioning does not help if:

  • Queries don't filter on the partition key — all partitions get scanned.
  • The table is under ~10–50 GB — the planner overhead outweighs the benefit.
  • Partition keys aren't known at insert time (requires application changes).

Range partitioning

Most common pattern: partition by time. An events table partitioned by month:

CREATE TABLE events (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  event_type  text        NOT NULL,
  payload     jsonb
) PARTITION BY RANGE (created_at);

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');

The FROM/TO bounds are inclusive/exclusive respectively. An insert with created_at = '2024-01-31' lands in events_2024_01. An insert outside all defined ranges fails unless you add a default partition:

CREATE TABLE events_default PARTITION OF events DEFAULT;

The default partition catches anything that doesn't match a defined range, preventing insert failures during new-partition creation lag.

List and hash partitioning

List partitioning maps discrete values to partitions — useful for tenant isolation or region sharding:

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

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-central');

Hash partitioning distributes rows evenly across a fixed number of partitions based on a hash of the partition key. Use it when there's no natural range or list grouping but you want to bound individual partition size:

CREATE TABLE users (
  id    bigserial,
  email text NOT NULL
) PARTITION BY HASH (id);

CREATE TABLE users_0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition pruning

Pruning only works when the partition key appears in the WHERE clause with a constant or stable expression. Verify pruning with EXPLAIN:

EXPLAIN SELECT * FROM events WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

Look for Partitions selected: 1 (out of N) in the output. If all partitions appear, pruning failed. Common causes:

  • Partition key is cast or transformed in the filter (WHERE date_trunc('month', created_at) = '2024-03-01' — use a direct range comparison instead).
  • Filter uses a volatile function — the planner can't evaluate it at plan time.
  • enable_partition_pruning = off — check GUC.

Indexes on partitioned tables

A CREATE INDEX on the parent table automatically creates matching indexes on all existing and future partitions:

CREATE INDEX ON events (event_type, created_at);

Each partition gets its own physical index. The parent-level index is virtual — queries use the partition indexes directly. This means index sizes are bounded by partition size, which keeps B-tree depth shallow.

Primary keys and unique constraints must include the partition key. This is a hard constraint: Postgres cannot enforce uniqueness across partitions without the partition key in the constraint.

ALTER TABLE events ADD PRIMARY KEY (id, created_at);

Operational pitfalls

Detaching old partitions. The preferred archival pattern is to detach old partitions and move them to cold storage or drop them:

ALTER TABLE events DETACH PARTITION events_2023_01 CONCURRENTLY;
-- verify, then:
DROP TABLE events_2023_01;

DETACH ... CONCURRENTLY (Postgres 14+) avoids a long lock. Plain DETACH takes a ShareUpdateExclusiveLock on the parent.

Cross-partition queries are slow. Aggregations and joins that span all partitions force the planner to append results from every partition. Monitor with EXPLAIN ANALYZE — Append nodes with many children are a red flag.

Foreign keys into partitioned tables. A FK referencing a partitioned table works, but FK references from a partitioned table to another table are only allowed if they include the partition key.

Autovacuum runs per partition. On a table with 120 monthly partitions, autovacuum has 120 independent targets. The default autovacuum_max_workers = 3 can serialize. Consider raising workers on heavily-partitioned instances.

Quick checklist

  • Partition key must appear in all time-range or category query filters — verify pruning with EXPLAIN.
  • Add a default partition to catch out-of-range inserts.
  • Create indexes on the parent; they propagate to all partitions automatically.
  • Primary keys and unique constraints must include the partition key.
  • Use DETACH ... CONCURRENTLY (PG14+) for zero-downtime archival.
  • Raise autovacuum_max_workers proportionally to partition count.

Related: Postgres MVCC · Autovacuum Tuning

Postgres Table Partitioning | TuneStack