Start With One Table

A well-indexed table is fine for a long time, even at hundreds of millions of rows. While its indexes fit in memory and you have no retention job to speed up, partitioning mostly adds planning overhead. It earns its place when you have a clear access pattern, usually time, and a real problem removing old data.

The nightly job that deletes old events used to take minutes. Now it takes five hours, overlaps with morning traffic, and leaves the table bloated. Nothing is broken exactly; the table has simply grown past the point where treating it as one thing works.

Partitioning splits a large table into pieces, usually by time, so queries touch only the pieces they need and old data can be dropped a whole partition at a time. Postgres’s declarative partitioning, with a tool such as pg_partman to create partitions ahead of time, makes this routine. Below is when to partition, how to pick the key, and what it costs in operational work.

Signs a table needs partitioning
  • Your events table is 800 GB and every query takes 30 seconds
  • You don't know whether to partition by created_at or tenant_id
  • You partitioned and somehow queries got slower
  • You have one Cosmos partition that's on fire while the rest are idle