An events table that had been growing for years finally crossed a size where adding another index was no longer the answer to slow queries. We were well past four hundred million rows, and every new index we added to compensate for slow queries was making writes measurably slower without meaningfully improving the reads that mattered most. We partitioned it by month instead, which meant rethinking a fair amount of application-level query logic that had quietly assumed the table would always behave like a single, simple structure.
Why range partitioning fit our access pattern
Range partitioning by timestamp matched how the application actually queries the data, mostly recent-window lookups, almost perfectly, which made the migration worth the effort rather than a purely theoretical improvement. The overwhelming majority of our queries filtered on a date range within the last thirty to ninety days, and under the old unpartitioned table, Postgres still had to consult an index covering the entire multi-year history to satisfy even the narrowest recent-window query. With monthly partitions, a query scoped to the last thirty days only touches one or two partitions, and Postgres's query planner prunes the rest automatically once it can see the date filter maps cleanly onto partition boundaries.
We considered partitioning by a different key, customer ID was the other realistic candidate, but our query patterns skewed so heavily toward recency rather than a specific customer's full history that time-based partitioning was the clear fit. A table with a different access pattern, one dominated by per-customer lookups spanning all of history, would have wanted a different partitioning strategy entirely, and we'd caution against copying our approach without checking that the underlying access pattern actually matches.
The migration itself
The migration itself needed a careful, staged approach to avoid locking the table for an unacceptable window, since it was already serving production traffic throughout. Postgres doesn't offer a built-in way to convert an existing table into a partitioned one in place; the practical approach is to create a new partitioned table, backfill historical data into the appropriate partitions in batches, and then cut over writes once the backfill catches up to the present.
- Range partitioning by timestamp matched how the application actually queries the data, mostly recent-window lookups, almost perfectly, which made the migration worth the effort.
- Backfilling four years of history in batches of roughly a million rows kept each batch's lock duration short enough not to visibly impact production query latency.
- Dual-writing to both the old and new tables during the transition window caught a handful of edge cases in our batching logic before they reached production data.
We backfilled roughly four years of history in batches of about a million rows each, monitored for lock contention and replication lag after every batch, and paused the process entirely twice when a batch coincided with a period of unusually high production load. The full backfill took about four days of wall-clock time, running mostly overnight and during low-traffic windows, though the actual migration project including planning, testing on a staging copy, and the cutover itself spanned closer to three weeks.
Dual-writing to both the old and new tables during the transition window, rather than committing to a single cutover moment, gave us a safety net and caught a handful of edge cases in our batching logic, a few rows near month boundaries were landing in the wrong partition due to a timezone handling bug, before they reached production data permanently. We're glad we didn't skip that step in the interest of moving faster.
Query logic changes and unexpected wins
A handful of queries that had implicitly relied on scanning the whole table, an admin dashboard's rarely-used all-time export feature, most notably, needed rewriting once partition pruning meant the query planner behaved differently depending on whether a date filter was present. We hadn't fully catalogued every code path that touched this table before starting, and finding the ones the migration broke took longer than we'd planned for, closer to a week of follow-up fixes spread across two sprints after the main migration was already live.
An unexpected win: dropping an entire month of very old data, something compliance now requires for a subset of event types after a retention period, went from a slow, lock-heavy delete operation to an near-instant partition drop. That alone has made a previously dreaded quarterly maintenance task trivial.
What we'd do differently
Partitioning isn't something to reach for early, but once a table's growth pattern and query pattern both clearly favor it, the performance and maintenance wins are substantial. If we were doing this again, we'd audit every code path touching the table for hidden full-table-scan assumptions before starting the migration rather than discovering them afterward, since that follow-up work ended up being a meaningful fraction of the total project time. We'd also set up automated partition creation further in advance; we currently create the next few months of partitions via a scheduled job, but we initially cut that job's lead time too close and had one tense afternoon when a deploy delay nearly left us without a partition for the upcoming month.
Measuring the actual query improvement
We benchmarked a representative sample of production queries before and after the migration, rather than relying on intuition about how much partition pruning should help. A typical thirty-day lookback query, one of our most common query shapes, dropped from a median of a little over half a second to well under fifty milliseconds, since Postgres was no longer traversing an index spanning years of largely irrelevant history to satisfy a narrow, recent-window request. Write latency improved too, though less dramatically, since inserts into a much smaller, actively-written partition need to touch a proportionally smaller index than an insert into the single sprawling table did before.
Not every query improved. A handful of reporting queries that genuinely need to aggregate across the entire history, a multi-year churn analysis, most notably, got slightly slower under partitioning, since the query planner now needs to combine results across many partitions rather than scanning one large structure directly. That was an acceptable tradeoff given how rarely those queries run compared to the recent-window lookups that dominate our actual traffic, but it's worth checking your own query mix before assuming partitioning is a universal win.
Index strategy within each partition
Each partition needed its own set of indexes, since Postgres doesn't automatically share index structures across partitions the way you might intuitively expect coming from a single-table mental model. We initially copied the exact index set from the old monolithic table onto every new partition, which turned out to be more indexes than any individual partition actually needed, since some of those indexes existed specifically to compensate for the lack of partition pruning in the first place and were now redundant. Trimming the per-partition index set down, informed by actual query plans against the new structure rather than assumptions carried over from the old table, meaningfully reduced both storage overhead and the write-side cost of maintaining indexes on every insert.
Vacuum and maintenance behavior changed too
Autovacuum behavior improved meaningfully once the table was partitioned, though it wasn't something we'd specifically budgeted time to investigate going in. Under the old monolithic table, a single autovacuum pass on a table with hundreds of millions of rows could run for hours and compete for I/O with production queries during that window, occasionally showing up as a period of elevated query latency that took us a while to correctly attribute back to vacuum activity rather than application load. With monthly partitions, autovacuum runs against individual partitions independently, and a vacuum pass against last month's now-mostly-static partition finishes in a small fraction of the time the old full-table vacuum needed, with older, no-longer-written partitions barely needing vacuum attention at all.
A subtlety with foreign keys we hadn't anticipated
Postgres's support for foreign keys referencing a partitioned table has real limitations that we didn't fully appreciate until we hit one directly during testing on staging. A foreign key from an unrelated table into our events table's primary key worked fine, but a couple of application-level assumptions about referential integrity that had never been enforced by an actual database constraint in the first place, just assumed correct by convention, surfaced as a genuine gap once we looked closely at what partitioning would and wouldn't enforce for us. We ended up adding a lightweight application-level check for the one case that mattered, rather than trying to force a foreign key constraint that Postgres's partitioning support couldn't cleanly express.
Monitoring partition health going forward
Partitioning isn't a one-time migration that you can consider finished at cutover; it needs ongoing monitoring to stay healthy. We added a dashboard tracking row counts and size per partition, alerting if the scheduled job that creates future partitions falls behind its lead time, and a periodic check confirming that query plans against common query shapes are actually pruning partitions as expected rather than silently falling back to scanning every partition due to a query that doesn't filter on the partition key in a way the planner can use. That last check has caught two cases since launch where a new query, added by an engineer unfamiliar with the partitioning scheme, accidentally bypassed pruning entirely.
Coordinating the cutover with other teams
Because this table backs several downstream reporting jobs owned by other teams, we treated the cutover itself as a cross-team coordination exercise rather than a purely database-team migration. We gave downstream teams a two-week notice window along with a staging environment running the new partitioned schema, so they could validate their own queries against it before the production cutover happened. That surfaced one downstream job with a hardcoded assumption about the table's physical row ordering, an assumption that had happened to hold under the old table but was never guaranteed and broke under partitioning, in time to fix it before it caused a silent data quality issue in a nightly report.