PostgreSQL Deep Dive
by Sergey Okinchuk
Configuration BaselineWhen a Table Gets Too Big

Chapter Eleven · Big Tables and Partitioning

Big Tables and Partitioning

delivery_events is 1.2 billion rows in 700 GB and takes on 4 million more a day. Deleting a year of it ran for eleven hours, left 200 GB of dead space and never finished vacuuming. Five topics turn that table into a set of monthly partitions where retention is a detach that takes milliseconds.

5 topics

The previous chapter gave pg-primary a configuration that matches its hardware and a pooler that turned 400 client connections into 40 server ones. Every number in it improved. One table did not care. delivery_events is append-only, it grows by 120 million rows a month, and no setting in postgresql.conf changes the fact that a single relation of 700 GB has to be vacuumed, indexed and pruned of old data as one unit.

The symptom that forced the issue was retention. Nadia ran a DELETE for everything older than twelve months, 340 million rows, on a Sunday evening. It finished at eleven hours, well into Monday's traffic, and the space it freed went nowhere: 200 GB of dead tuples and index entries that autovacuum was still working through days later, on a table that had meanwhile taken on another 12 million rows. The data was gone and the disk was fuller than before it started.

Partitioning is the fix, and it is a maintenance strategy that happens to help some queries rather than a performance feature that happens to help maintenance. These five topics decide whether Cartwheel needs it, declare the partitioned table and migrate into it, make pruning visible in a plan instead of assumed, build the monthly attach-detach-archive process that replaces the eleven-hour delete, and then step back to ask the question one server eventually raises, where the answer for Cartwheel turns out to be that it is nowhere near needing to shard.

The same 1.2 billion rows, and what a year of retention costs in each shape
One table delivery_events today
700 GB in a single relation, vacuumed, indexed and pruned as one unit. Retention was a DELETE of 340 million rows: eleven hours, nothing returned to the operating system, and 200 GB of dead space autovacuum was still working through days later.
Monthly partitions the same rows, twelve relations
Maintenance runs a month at a time and retention is a detach followed by a drop: a catalogue change, then one unlink per file. No row versions created, no vacuum work generated, and the disk space comes back immediately.

Topics in This Chapter

Topic 56
When a Table Gets Too Big
Query latency is not what breaks first — vacuum, index builds, rewrites and mass deletes are. What partitioning actually buys, what it costs permanently, and the two cheaper answers to try before adding structural complexity.
Scaling Limits
Topic 57
Declarative Partitioning
Range, list and hash; bounds that are inclusive at the start and exclusive at the end; indexes that cascade from the parent and unique constraints that must contain the partition key. Plus the migration path, because there is no in-place conversion.
Table Design
Topic 58
Partition Pruning
The entire performance argument for partitioning, and the one thing to verify rather than assume. Plan-time pruning read off the partition count, execution-time pruning read off Subplans Removed, and the four predicates that quietly defeat both.
Query Planning
Topic 59
Living With Partitions — Attach, Detach, Retention
A partitioned table needs a process, not just a schema. Creating ahead of the calendar, making ATTACH skip its validation scan, DETACH CONCURRENTLY as the retention primitive, and the autovacuum rule that surprises everyone about the parent.
Retention
Topic 60
When One Server Isn't Enough
How far a single primary really goes, the five cheaper levers to exhaust first, and what sharding costs permanently once you take it. Citus and its distribution column, described honestly — including why Cartwheel does not need any of it.
Scaling Out