Partitioning and Managing Big Tables

At some point a table stops being a table and becomes a write amplifier: indexes span millions of rows, vacuum takes hours, queries scan what they can't prune. Partitioning splits it into smaller tables behind one name — Postgres handles the routing.

Declarative partitioning

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

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Queries that filter on created_at get partition pruning — the planner skips partitions that can't match. Inserts route automatically. It's the same table to your app.

When partitioning pays

• Time-series data — logs, events, metrics: drop a month in DROP TABLE events_2025_01 (instant) instead of DELETE (hours of dead tuples).
• Maintenance windows — vacuum runs per-partition; new partitions are tiny by definition.
• Index size — each partition gets its own index, kept small by design.

When it doesn't

• Small tables (<10M rows, single-digit GB) — partitioning adds complexity for nothing.
• Queries that don't include the partition key — a WHERE user_id = X without created_at probes every partition.
• Join-heavy OLTP — partition pruning doesn't help joins on non-key columns.

Field notes

• Unique constraints on a partitioned table must include the partition key (UNIQUE (id) can't work — the uniqueness is per-partition).
• pg_partman automates creating/dropping partitions — managing them by hand is a cron job waiting to fail.
• FOR VALUES FROM boundaries are inclusive/exclusive per partition; overlap is a constraint error at creation.