An engineer proposes partitioning a 60-million-row table because queries feel slow. How do you decide whether a table is actually big enough or shaped right to be worth partitioning, and what does partitioning cost when it is not warranted?

## The wrong test and the right ones "How many rows before I partition?" is the question everyone asks and it has no useful answer, because row count does not determine whether partitioning helps. A 500-million-row table serving only point lookups by primary key is perfectly happy unpartitioned: a B-tree adds roughly one level per hundredfold increase in rows, so the lookup costs a handful of page reads either way. Meanwhile a 30-million-row table that must delete 20 million rows every night can be an excellent partitioning candidate. The right tests are about **operations and access shape**, not size. ## Trigger 1: data expires in bulk This is the strongest and most common justification. A large `DELETE` is expensive in every relational engine: it must find the rows, write undo or dead tuples, rewrite index entries, generate WAL proportional to the work, and then leave space that needs reclaiming. Doing that nightly on a large table produces bloat, long-running transactions, replication lag and index degradation. With range partitioning aligned to the retention boundary, expiry becomes detaching or dropping one child table: near-instant, minimal WAL, no bloat, no vacuum debt. If your retention policy is by time and your volumes are large, this benefit alone usually decides it. The same logic applies to bulk *loading*: staging a new period's data into a standalone table and attaching it beats inserting into a live table with all its indexes. ## Trigger 2: maintenance operations no longer fit in a window Everything that must process a whole table gets easier when the table is many tables: rebuilding a bloated index, refreshing statistics, running a rewrite, taking a logical backup of one slice. When `REINDEX` on the single table takes six hours and you have a two-hour window, partitioning turns one impossible operation into forty possible ones you can run over successive nights. This is a manageability argument, and it is the one experienced engineers lead with. ## Trigger 3: hot and cold data are mixed In a time-series table, essentially all reads and writes touch the last few days, but the indexes span years. Every insert updates an index whose upper levels compete for cache with cold history. Partitioning by time gives the hot partition its own small indexes that stay resident, so inserts touch fewer pages and index maintenance is cheap. The cold partitions are rarely read and can sit on cheaper storage. ## Trigger 4: a real scan pattern that pruning can cut If a common query genuinely reads a large fraction of the table but a *small* fraction of the key range — a monthly report over one month out of thirty-six — pruning turns a full scan into one partition's scan. Note the precondition: the query must be scan-shaped and constrained on the partition key. If it is already served by an index, pruning adds little. ## What it costs when you do it anyway - **Planning overhead.** The planner must consider the partition set on every query. Pruning helps, but the per-relation cost is not zero, and on short OLTP queries with many partitions planning can rival execution. - **Multiplied objects.** Every index exists once per partition. Statistics, autovacuum work, bloat monitoring, storage parameters — all multiply. Operational surface grows with partition count. - **Constraint restrictions.** Every unique constraint and primary key must include the partition key, which sometimes forces an unnatural key or gives up a uniqueness guarantee you wanted table-wide. Foreign keys referencing a partitioned table have engine-version-dependent limitations. - **Queries without the key get worse.** Any query that does not constrain the partition key now touches N relations instead of one, with N index probes instead of one. - **Irreversibility.** The key and strategy are baked in; changing them means rewriting the table. ## The 60-million-row case Apply the checklist. Does data expire in bulk? If not, one trigger gone. Do maintenance operations fit? At 60 million rows, almost certainly yes. Is the working set separable? Possibly, if it is event data. Are the slow queries scan-shaped on a key you would partition by? Most often, the diagnosis is a missing or badly-ordered composite index, statistics that are stale, a predicate wrapped in a function so no index applies, or a query pulling far more rows than the application needs. Fixing that is hours of work and reversible; partitioning is weeks and is not. So: read the plan first, and reach for partitioning when the *operational* triggers are present, not merely when a dashboard is red. ## When you do proceed Decide the key and granularity from retention and query predicates, target tens to low hundreds of partitions, plan the migration (create partitioned parent, backfill in batches, cut over), and pre-create future partitions with automation so a missing partition never rejects an insert. Measure before and after on the queries you claimed would improve — a partitioning project that cannot show which query got faster or which maintenance job got possible was not justified. ## How to answer Reject row count as the criterion, give the operational triggers (bulk expiry, maintenance windows, hot/cold separation, prunable scans), state plainly that a point lookup does not get faster, and enumerate the concrete costs of partitioning a table that does not need it.