Declarative Table Partitioning in PostgreSQL: Managing Billions of Rows

1. Why Monolithic Tables Degrade Over Time

A single table accumulating billions of rows degrades on several independent axes at once. Index size grows roughly linearly with row count, so a B-tree on a hundred-million-row events table can easily exceed available shared_buffers, forcing every index traversal to touch disk-backed pages rather than cached ones — the exact opposite of the fast, mostly-in-memory lookups a healthy index provides. Vacuum has to scan and process the entire table on every pass (autovacuum's cost-based throttling notwithstanding), and a full VACUUM or REINDEX on a monolithic multi-terabyte table can run for hours, during which it competes for I/O bandwidth with live traffic.

Data retention becomes an operational liability rather than a policy decision: deleting old rows with `DELETE FROM events WHERE created_at < $1` on a huge table generates a proportional volume of dead tuples that must then be vacuumed, produces a large amount of WAL, can hold row locks for an extended duration, and does nothing to shrink the table's on-disk footprint — the space is only reusable by future inserts, not actually returned to the OS, without a subsequent VACUUM FULL that itself takes an ACCESS EXCLUSIVE lock for its duration.

Query planning also suffers in subtler ways: statistics for a monolithic table represent an average across its entire history, and default_statistics_target's fixed sample size means the planner's view of a table's value distribution gets proportionally coarser as the table grows, increasing the odds of the kind of estimate/actual row-count mismatch discussed in the EXPLAIN ANALYZE article of this series, which cascades into bad join-order and join-algorithm decisions on nearly every query touching that table.

None of these problems are solved by throwing more indexes or a bigger instance at the table — they are structural consequences of one physical relation growing without bound, and the fix is structural too: partitioning the logical table into multiple, independently-manageable physical tables.

2. Declarative Range Partitioning by Month

PostgreSQL's declarative partitioning (since version 10) lets you define a parent table with `PARTITION BY RANGE (column)` and attach child tables, each owning a disjoint slice of the key range, that the planner and executor treat as a single logical table for querying while physically storing and maintaining them as separate relations with their own indexes, statistics, and vacuum cycles. Range partitioning by a timestamp column, one partition per month (or week/day for extremely high-volume tables), is the standard pattern for time-series and event data.

Each partition is a real, independently-indexable table — you create indexes on the parent using `CREATE INDEX ON events (...)`, which PostgreSQL propagates to every existing and future partition automatically, but you can also add partition-specific indexes when access patterns for a given range genuinely differ. Constraints, including primary keys, must generally include the partition key as part of their definition, which is a real schema-design constraint to plan around, not an afterthought.