Partition tables that outgrow memory

How to do it

The PostgreSQL docs give this size as a rule of thumb, and add that the exact point depends on the app. Choose a key that most queries filter on, such as created_at, so the planner skips the other partitions. Keep to a few thousand partitions at most, since each one costs planning time and memory. Dropping a partition is far faster than a bulk delete and avoids the vacuum work. Create the next partition ahead of time with a scheduled job. Track size as Watch size and growth says.

Examples

SQL example

CREATE TABLE order_events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  order_id bigint NOT NULL,
  created_at timestamptz NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE order_events_2026_11 PARTITION OF order_events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- Remove a month without deleting rows one by one
ALTER TABLE order_events DETACH PARTITION order_events_2024_10 CONCURRENTLY;
DROP TABLE order_events_2024_10;

How to check

Every table larger than the server's memory is partitioned or has a written reason. EXPLAIN on the 5 most frequent queries of each partitioned table scans only the matching partitions. Next month's partition exists by the 20th.

Sources

• PostgreSQL table partitioning

Related rules

• Watch size and growth in Scaling