When a Table Gets Too Big

Postgres has no row limit that delivery_events is creeping up on. At 1.2 billion rows in 700 GB it still answers WHERE order_id = 8812337 in four page reads, and it would do the same at ten billion, because a B-tree's depth grows with the logarithm of the row count and nothing else about a point lookup changes. Query latency is not the thing that degrades.

What degrades is every operation that has to treat the relation as one unit. Vacuum reads the whole heap and every index. An index build runs for the better part of a day. A type change rewrites 700 GB and wants 700 GB of free space while it does. And a year's worth of deletes creates dead rows faster than autovacuum can retire them. Partitioning answers that list and nothing above it, so the case for it is built out of maintenance windows rather than out of a row count.

What Actually Breaks First

Take the four maintenance operations in order of how often they bite. VACUUM has to scan the heap and then every index on it, so its cost is set by the size of the relation and not by how much of it changed; the visibility map spares it the pages that are already all-visible, which on an append-only table is most of them, but the index passes are not spared. CREATE INDEX reads the whole table and sorts it — an hour of maintenance_work_mem-bound work on a table this size, and the CONCURRENTLY variant that lets writes continue costs two passes instead of one. Most forms of ALTER TABLE … ALTER COLUMN … TYPE rewrite every row and every index under ACCESS EXCLUSIVE, which Chapter 3 priced at 40 million rows and which here means a maintenance window measured in half-days.

SELECT pg_size_pretty(pg_table_size('delivery_events'))            AS heap,
       pg_size_pretty(pg_indexes_size('delivery_events'))          AS idx,
       pg_size_pretty(pg_total_relation_size('delivery_events'))  AS total;

  heap   |  idx  | total
---------+-------+--------
 640 GB  | 58 GB | 698 GB   -- 1.2 billion rows, ~530 bytes each
                            -- of the 58 GB, occurred_at alone is 40 GB

Most of the damage sits in two of those numbers. The heap at 640 GB is what every full scan and every rewrite has to move. The 40 GB B-tree on occurred_at is what every vacuum pass has to walk and what every insert has to maintain, and its only job is to support queries that ask for a time range, which is precisely the access pattern a partition boundary answers for free. Chapter 8 already made that argument and sized the replacement: a BRIN index on the same column, about 90 MB, viable because events arrive in occurred_at order and the physical correlation is near perfect.

Deletion Is the Real Driver

Retention is where the pain concentrates, and it concentrates for a reason that has nothing to do with how the DELETE is written. Postgres does not remove a row; it marks the existing version dead and leaves it in place, which means a delete is a write. Each of the 340 million rows Nadia removed produced a WAL record, a modified heap page, and dead index entries in all three indexes. Eleven hours later the statement committed, having freed exactly zero bytes to the operating system and having handed autovacuum roughly 200 GB of debris to work through — about 180 GB of dead heap and 16 GB of dead index entries. Chapter 7 covers why that reclamation is slow and what to do when it falls behind; the point here is that the whole cost was self-inflicted by the shape of the table.

-- on one big table: 11 hours, 340M dead tuples, ~200 GB to reclaim
DELETE FROM delivery_events WHERE occurred_at < '2025-09-01';

-- on a partitioned table: catalogue change, then one unlink per file
ALTER TABLE delivery_events DETACH PARTITION delivery_events_2025_08 CONCURRENTLY;
DROP TABLE delivery_events_2025_08;

The second form does not delete any rows. It removes a partition from the parent's definition, which is a catalogue update, and then drops a table, which unlinks its files. No row versions are created, no index entries are invalidated, no vacuum work is generated, and the disk space comes back immediately instead of being held as reusable free space inside a relation that is only going to grow. Eleven hours against a few milliseconds of lock is the strongest contrast this chapter has, and it is available only where the retention boundary lines up with a partition boundary.

The Alternatives, Honestly

Partitioning is permanent structural complexity, so it should buy something you can name. Three cheaper answers deserve a hearing first. The first is archiving the data out of the operational database entirely: if nothing in the Cartwheel API ever reads an event older than ninety days, then months four onward belong in object storage or the warehouse, and the OLTP table shrinks by an order of magnitude with no schema machinery at all. Chapter 4 already built the mechanism for moving them, a data-modifying CTE that deletes a bounded batch and inserts it into delivery_events_archive in one atomic statement.

The second is to check whether the complaint is really about size. A 40 GB index replaced by a 90 MB BRIN, a per-table autovacuum_vacuum_scale_factor that stops waiting for 20% of 1.2 billion rows to go dead, and a maintenance_work_mem raised to 2 GB together fix a surprising share of "the big table is a problem" reports without touching the schema. The third is to ask whether the query pattern is genuinely time-bounded. Partitioning by month helps only queries that say something about the month; a dashboard that scans by event_type across all history gains nothing and pays planning overhead on every execution.

What Partitioning Gives

It gives four things, worth stating separately because only one of them is about speed. Maintenance becomes per partition: vacuum, analyze, index builds, statistics targets and reindexes all operate on one month at a time, so the hot current partition can be vacuumed aggressively while the closed months are never touched again. Retention becomes a detach. Queries that filter on occurred_at touch only the partitions whose bounds can contain matching rows, which is the pruning that Topic 58 is entirely about. And a closed month can be moved to a slower, cheaper tablespace, since the partition is a real relation with its own file and its own placement.

What partitioning does not give is a free speedup. A query with no predicate on the partition key scans every partition, and does it slightly slower than the unpartitioned table would have, because the planner had to consider each one and the executor has to run an Append over all of them. Splitting a table does not make a sequential scan cheaper: the same 640 GB is read either way, in twelve pieces instead of one.

Choosing the Key and the Granularity

The key is pinned down by two constraints before taste enters into it. It must appear in the WHERE clauses you want pruned, and it must be a column of every unique constraint on the table, because Postgres enforces uniqueness inside each partition's own index and has no global index spanning them. For delivery_events both point at occurred_at: the API reads a delivery's recent history by order and time, the analytics dashboard reads by month, and the retention policy is expressed in months. The existing primary key on id has to become a composite of (id, occurred_at), which is a real change to the data model and the reason this decision belongs at design time.

Granularity follows from the retention window and the planner's tolerance. Monthly gives twelve relations a year, so Cartwheel's year of history is twelve partitions and even an eight-year policy stays around a hundred, comfortably inside what the documentation describes as handled well. Daily granularity would turn three years of the same data into 1,096 relations, at which point planning time on short OLTP queries becomes measurable, and every session that touches them loads per-partition metadata into its own memory. The current month at 4 million rows a day is a 120-million-row, 58 GB partition, which is a size vacuum and index builds finish with time to spare.

The Costs You Sign Up For

There are four, and they are permanent. Unique constraints must include the partition key, so application-level identifiers stop being globally unique unless you design them that way. Foreign keys work in both directions but are implemented per partition, and the referencing side still needs its own index. Planning cost grows with the number of partitions the planner has to consider, which is a ceiling on how fine the granularity can go. And something has to create next month's partition before the first row for it arrives, which means the schema now depends on a scheduled job, and that dependency announces itself at midnight on the first, when an insert fails because there is nowhere to route it.

None of those is a reason to avoid partitioning a table like this one. They are a reason to be sure the table is like this one. An append-only event stream with a time-based retention policy and time-bounded queries is the case partitioning was built for, and delivery_events matches it on all three counts. A large but static table with no retention policy and no natural range key matches on none.