PostgreSQL partitioning: taming very large tables

When to partition, which strategy to pick, and a working example with automated partition management.

Postgres partitioning splits one logical table into many physical ones. Below a few hundred million rows, you rarely need it. Above that, it can turn "the reporting query kills prod" into "the reporting query completes in 200 ms".

When to partition

Tables with 100M+ rows growing steadily.
Queries and deletes that are naturally scoped by a range (time-series, tenant_id, region).
You need to drop old data efficiently — a DROP of a partition is instant vs DELETE which bloats the table.

Strategy — usually RANGE by date

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

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

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

Indexes on partitioned tables

Create indexes on the parent table — Postgres creates them on each partition automatically:

CREATE INDEX ON events (user_id, created_at DESC);
CREATE INDEX ON events (event_type);

Automate partition creation with pg_partman

CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'created_at',
  p_type         => 'range',
  p_interval     => 'monthly',
  p_premake      => 3
);

Then schedule partman.run_maintenance() nightly (pg_cron or an external scheduler).

Query performance — verify partition pruning

EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-08'
  AND user_id = 42;

You want to see "Seq Scans" only on the partitions actually touched, not on every partition.

Common mistakes

Partition key not in the WHERE clause → Postgres scans every partition.
Too many partitions (thousands) → planner overhead. Aim for hundreds, not thousands.
Foreign keys to partitioned tables were limited before PG 12; on modern versions they work.