Database Engineering
313 Million Dead Tuples: What Partitioning a 1 TB PostgreSQL Table Actually Taught Us
16 September 2026 · 30 min read
PostgreSQL performs remarkably well even when tables grow into hundreds of millions of rows.
That can be deceptive.
A query can still return in milliseconds while the table underneath it is becoming steadily harder to maintain.
We ran into exactly that. What looked at first like a query-performance problem turned into a much broader investigation: dead tuples, autovacuum, stale statistics, storage I/O, indexing, and eventually partitioning.
At one point PostgreSQL reported:
Live tuples: 14 million
Dead tuples: 313 million
More than 22 dead tuples for every live one, on a table that had reached about 1.16 TB.
Those numbers changed how we thought about the problem.
Where it ended up: we moved 788 million rows onto partitioned tables across six deployments. Each cutover itself took seconds. The preparation took two weeks. Along the way we lost five rows and got them back, found queries taking more than a thousand relation locks apiece, hit a planner estimate wrong by five orders of magnitude, and learned that the 12,000 IOPS on our storage configuration was not a number the instance could sustain.
The story has two arcs. The first is the diagnosis — what we measured, what we misread, and why VACUUM turned out to be the wrong tool for the problem we actually had. The second is the migration that followed: preparing for it, cutting over, verifying it, and everything that went wrong along the way.
The second half is the part I couldn't find written down anywhere when I needed it.
Does this apply to your table?
The specifics below come from one system, but the problem is a shape, not a domain. If your table looks like this, everything here should transfer:
-
rows arrive continuously and are rarely modified long after they land
-
every row carries a timestamp that queries naturally filter on
-
old data eventually expires, and you delete it in large batches
-
there is a composite business key, not just a surrogate
id -
the table is expected to grow indefinitely
To keep the examples readable, I'll use a generic version of our schema throughout:
CREATE TABLE readings (
id bigserial,
site_id bigint NOT NULL, -- parent entity
device_id bigint NOT NULL,
metric_id bigint NOT NULL, -- what is being measured
"timestamp" timestamptz NOT NULL,
value numeric,
-- …plus fifteen other columns
UNIQUE (site_id, device_id, "timestamp", metric_id)
);
Your column names will differ. The shape is what matters: a wide fact table, a natural time axis, and a business key that is not the primary key.
The environment was PostgreSQL 14 on AWS RDS. One of the instances involved:
PostgreSQL: 14.22
Instance class: db.m6g.xlarge
vCPU: 4
Memory: 16 GB
Storage: 1,500 GiB gp3
Provisioned IOPS: 12,000
Make a note of that last line. It was a real number — it just wasn't the number this instance could sustain, and I'll come back to why.
Part I — The diagnosis
The numbers that started it
pg_stat_all_tables reported:
n_live_tup: 14,004,569
n_dead_tup: 313,135,079
A ratio of about 22 dead tuples to every live one. Alongside it:
n_mod_since_analyze ≈ 481 million
Which raised the obvious questions. Was the table genuinely carrying hundreds of millions of dead tuples? Was autovacuum failing? Were the statistics stale? Was maintenance eating the instance's I/O budget?
And underneath all of them: was VACUUM even the right thing to be reaching for?
Why dead tuples exist
PostgreSQL uses Multi-Version Concurrency Control.
An UPDATE does not generally modify a row in place. It writes a new version and leaves the old one behind until no transaction can still see it. A DELETE doesn't physically remove anything either — the row simply becomes invisible.
Either way, you get a dead tuple. VACUUM decides later whether the space can be reused.
INSERT → live tuple → UPDATE / DELETE → dead tuple → VACUUM → space reusable
This is part of why PostgreSQL's transactional behaviour is so good. It also means that any workload with heavy updates or deletes accumulates dead tuples as a matter of course.
The subtlety that bites at scale: a normal VACUUM does not return space to the operating system. It makes the space reusable inside the table. On a 20 GB table nobody notices. On a terabyte, the difference is the whole problem.
Why VACUUM FULL wasn't the answer
The instinctive response to a bloated table:
VACUUM FULL table_name;
But VACUUM FULL rewrites the entire relation under an exclusive lock. On a table approaching a terabyte that means budgeting for the rewrite time, temporary disk space equal to the table, an ACCESS EXCLUSIVE lock that makes the table unavailable to the application throughout, rebuilt indexes, blocked transactions, replication lag, and a sustained I/O spike.
None of that was something we wanted to treat casually, so the practical first step was to understand ordinary VACUUM behaviour instead.
Autovacuum wasn't broken. It was losing a race.
The table carried custom settings:
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.10
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = 20 ms
A 5% scale factor sounds aggressive. On a large table it isn't, because the trigger is proportional:
vacuum threshold + (scale factor × table rows)
At 200 million rows, 5% is ten million changed tuples before autovacuum even starts. And while it works through those, the application keeps producing more.
So autovacuum wasn't failing. It was simply being outrun.
There's a related ceiling in PostgreSQL 14 that took us a while to find. A single vacuum pass tracks dead tuple IDs in an array that is hard-capped at 1 GB — roughly 179 million of them. Raising maintenance_work_mem beyond that buys you nothing at all. Exceed the cap and vacuum starts another complete pass over every index on the table.
With eight indexes and 300 million dead tuples, that isn't a detail. That's the runtime.
VACUUM is an I/O workload, not housekeeping
VACUUM gets discussed as though it runs harmlessly in the background. At scale it does not.
It reads table pages, rewrites visibility information, cleans every index, and generates its own write traffic. On a busy system it competes directly with application reads and writes, batch jobs, analytics, ETL, and backups.
We could watch this happen on the instance graphs: when vacuum activity dropped, I/O pressure dropped with it. Which ruled out the simple answer of just vacuum harder and turned it into a balancing problem.
One rule from this phase cost us a fully drained burst-credit balance:
A client-side timeout does not cancel a running query.
We once believed we'd killed a heavy query after two minutes. It ran on the server for thirty-nine. Set statement_timeout server-side — your driver's timeout only stops your client from waiting for an answer.
Maintenance windows and throttling
Our users were concentrated in a single timezone several hours from our own, so we had a genuine quiet period during our working morning. That let us do controlled manual maintenance rather than relying entirely on background autovacuum:
VACUUM (VERBOSE, ANALYZE) readings;
More useful than the manual run itself is that vacuum cost parameters are adjustable at runtime. VACUUM can be throttled. Spare I/O capacity means you can push harder; rising user traffic means you back off. That's a far safer posture than treating maintenance as all-or-nothing.
Then ANALYZE changed the picture
Two days after the first snapshot, the statistics looked like this:
before after
n_live_tup: 14.0M 204.4M
n_dead_tup: 313.1M 0.7M
At a glance, a spectacular cleanup: 313 million dead tuples down to 705 thousand.
But look at the other column. The live count went from 14 million to 204 million — and no, we hadn't inserted 190 million rows in two days.
n_live_tup and n_dead_tup are estimates. They are not SELECT COUNT(*). They're derived from sampling, and they drift badly when a table has taken hundreds of millions of modifications since its last ANALYZE. Ours had taken 481 million.
So part of what we had been staring at was a statistics artifact. The bloat was real — but not 22:1 real.
Never read
n_live_tupandn_dead_tupas row counts.
They remain excellent diagnostic signals. They just have to be read alongside n_mod_since_analyze and the vacuum and analyze timestamps:
SELECT schemaname, relname,
n_live_tup, n_dead_tup, n_mod_since_analyze,
last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
vacuum_count, autovacuum_count, analyze_count, autoanalyze_count
FROM pg_stat_all_tables
WHERE relname = 'readings';
The question that matters isn't whether autovacuum has ever run. It's whether it runs often enough to keep pace with the workload. Those are very different things.
ANALYZE, incidentally, took seconds. When a table's statistics look implausible, it's a much safer first move than an aggressive vacuum.
Meanwhile, the queries were fine
The thing that kept the investigation honest: these tables weren't slow.
A second table in the same system was 207 GB of heap and 125 GB of indexes, around 390 million rows, with an estimated 226 million dead. A real business query against it — one parent entity, a date range, and about 74 device names — came back in:
COUNT: ~127 ms
SUM: ~166 ms
on parallel index-only scans.
On the main table, a composite index:
CREATE UNIQUE INDEX ...
ON readings (site_id, device_id, "timestamp", metric_id);
answered a latest-record lookup in 17 ms.
So we had a table whose maintenance profile was alarming and whose query latency was excellent. Both were true at once.
A very large table is not automatically a slow table — which is precisely why query latency is a poor proxy for table health.
Three problems that look like one
It's easy to file everything under "PostgreSQL is slow." We got much further by splitting it in three.
Query performance. Is the planner choosing the right index? Are index columns ordered correctly? Can this be an index-only scan? A planning problem.
Table maintenance. Is autovacuum keeping pace? How fast are dead tuples accumulating? Are statistics current? What is maintenance costing in I/O? An operational problem.
Data lifecycle. How long is data retained, how is it removed, and what does keeping history cost? An architecture problem.
We had the third one. And you cannot solve an architecture problem by iterating on the first.
Retention deletes are the real cost
Say you keep five years of data and the business only needs recent history. The natural approach:
DELETE FROM readings WHERE "timestamp" < '2025-01-01';
Logically correct. Operationally brutal.
PostgreSQL has to locate every one of those rows, mark the heap tuples deleted, generate the WAL for all of it, and leave a dead row version behind for each one. The corresponding index entries stay put too — removing those is VACUUM's job, not the DELETE's. So a large retention delete does expensive work immediately and then leaves a second round of cleanup behind it.
Deleting old data creates work for the thing already struggling. And it recurs:
large table → delete millions of rows → millions of dead tuples → VACUUM → repeat next month
Under monthly partitioning, that whole cycle collapses into:
DROP TABLE readings_2024_12;
No row-by-row deletes, no dead tuple per expired row, and none of the index churn that follows. PostgreSQL removes an entire relation instead.
Partitioning is usually pitched as a query optimization via partition pruning, and pruning is genuinely useful. But the reason we did it was operational: it makes the unit of maintenance small. You vacuum a month instead of five years. You rebuild one partition's index instead of a 125 GB one. Retention becomes metadata.
So we committed to the migration. Here is what it actually cost.
Part II — What the migration taught us
The approach was a shadow table: build a partitioned copy beside the live table, backfill it, verify it, then swap the two with a rename inside one transaction.
Six deployments, roughly two weeks.
788,000,000 rows migrated
5 rows lost, and recovered
The ratio between those two weeks and the few seconds each swap actually took is the thing I had not anticipated. The cutover is one transaction. Everything else is preparation — and that is where all of the following went wrong, ordered roughly by how much time each one cost us.
1. Partitioning can make unbounded queries dramatically worse
If I could send one paragraph back in time, this would be it.
Partition pruning is not a benefit you receive on completion. It is a precondition you have to satisfy first — and the query that cannot satisfy it is the one most likely to regress on the day you partition.
The guarantee runs one way only: without a usable constraint on the partition key, PostgreSQL cannot eliminate partitions. What it does instead depends on the query — an Append, a parallel plan, sequential scans. Here is what it did to ours.
The ingestion pipeline looked up its resume point once per device, every ten minutes:
Reading.objects
.filter(site_id=…, device_id=…, metric_id=…)
.order_by("-timestamp")
.values_list("timestamp", flat=True)
.first()
No timestamp bound anywhere. Measured against the partitioned table:
| Predicate | Partitions scanned |
|---|---|
| Unbounded | 121 |
| Lower bound only | 18 |
| Both bounds | 1 |
One index access became a MergeAppend over all 121.
The same query drives lock consumption, which is where it gets genuinely uncomfortable. max_locks_per_transaction is not a per-query ceiling — it sizes a lock table shared across every session, so a query touching hundreds of partitions applies pressure to a structure the whole database depends on, not just to its own transaction. We measured one unpruned query holding 1,019 relation locks. The same query, pruned, held 11 — a 99% cut in its relation-lock footprint, on something running once per device every ten minutes.
Audit for unbounded queries before you cut over. Each one is a candidate to regress, and cutover day is a bad time to find out which.
There's a pleasant corollary. Adding an explicit timestamp predicate helped even before partitioning existed, because it let the planner intersect two indexes:
| Run | Plan | Time | Pages from disk |
|---|---|---|---|
| Cold, no predicate | Parallel Index Scan | 20,887 ms | 1,186,470 |
| Cold, with predicate | BitmapAnd | 4,221 ms | 232,548 |
Five times faster, five times fewer reads. But warm, the numbers invert:
| Run | Time |
|---|---|
| Warm, no predicate | 212 ms |
| Warm, with predicate | 1,625 ms |
With zero disk reads there's no I/O for the BitmapAnd to save, so its CPU cost is pure overhead.
Which regime applies to you is a question to answer with BUFFERS, not with arithmetic on relation size against shared_buffers — the working set for a given query can be far smaller than the table, and the OS page cache sits underneath both. Our production numbers settled it: the cold plan was reading over a million pages from disk per execution, so this workload was overwhelmingly I/O-sensitive and the cold case was the one that mattered. A small hot window that genuinely stays cached will be slower under the new plan, and it's worth knowing which of yours those are.
This also makes naive A/B timing useless: whichever variant runs second reads a buffer pool the first one warmed. Compare shared read counts, not elapsed time.
2. The planner's estimate was wrong by five orders of magnitude
Chasing that same slow query produced the strangest finding of the project.
The index it had chosen was on timestamp_tz. The query ordered by timestamp — a different column. So the index couldn't supply the ordering, and PostgreSQL top-N sorted every matching row: 34,479 blocks and 42 seconds per call, executed once per device per ten minutes.
The real question was why the planner picked that index at all. It estimated the filter would return 1 row. The true answer was around 106,000.
PostgreSQL multiplies the selectivity of site_id, device_id and metric_id as though they're independent. They aren't — in our data device_id implies metric_id with a functional dependency of 1.0. Three near-redundant predicates multiplied together estimate essentially nothing.
The fix was one statistics object and an ANALYZE. Three seconds, no meaningful storage cost, fully reversible:
CREATE STATISTICS stat_site_device_metric (ndistinct, dependencies)
ON site_id, device_id, metric_id
FROM readings;
ANALYZE readings;
The estimate went from 1 to 106,280 and the plan flipped from Sort + Index Scan to Index Only Scan Backward on the business-key index.
Across the instance:
| | Before | After |
|---|---|---|
| Block reads on this table | 4,922/s (63%) | 1,209/s (34%) |
| Four-table total | ~7,487/s | 3,574/s |
| That scan's share of backend time | 86% | 0 of 2,606 samples |
| DiskQueueDepth | 41 | 0.7 |
| ReadLatency | 5.2 ms | 0.6 ms |
Extended statistics are underused, and correlated columns in a composite key are exactly where they pay. If you have a multi-column business key whose columns describe a hierarchy, you probably have this problem.
Two things caught us out when we went to confirm it worked. The improvement didn't appear until connections recycled on the next deploy, and for a few hours we thought the fix had failed. PostgreSQL does replan prepared statements when planner statistics change, so whatever was holding the old plan lived somewhere in our own stack — check how your driver, pooler and prepared-statement settings cache plans before assuming new statistics take effect immediately. And don't read per-table I/O rates as independent: one of our other tables went up, 181 to 1,163 blk/s, not because it regressed but because it had been starved behind this query and could finally run. Judge by the total.
3. Provisioned storage IOPS are not sustained instance IOPS
The rollout stalled for a full day on something with no apparent connection to partitioning.
The instance had been pinned at ~5,967 IOPS continuously for over a week. Instance burst balance at zero, queue depth between 40 and 161, read latency up to 45 ms.
The volume was provisioned for 12,000.
Both numbers are real, and the gap between them is the whole lesson. gp3 storage delivers its provisioned IOPS consistently — that layer has no burst credits to run out of. But the instance sits in front of the volume with its own EBS-optimized limits, and those come as a baseline and a maximum:
| Instance type | Baseline IOPS | Maximum IOPS | Baseline MB/s |
|---|---|---|---|
| m6g.large | 3,600 | 20,000 | 84.9 |
| m6g.xlarge | 6,000 | 20,000 | 148.5 |
| m6g.2xlarge | 12,000 | 20,000 | 296.9 |
| m6g.4xlarge | 20,000 | 20,000 | 593.8 |
So an m6g.xlarge genuinely can serve 12,000 IOPS — for as long as it has burst capacity. Spend that and it settles back to 6,000, which is precisely what ours had been doing for a week.
This is why a migration is different from ordinary traffic. A backfill is not a burst. It is hours of sustained sequential work, and it will spend nearly all of that time at the baseline number, not the provisioned one.
Across our fleet:
| Provisioned storage IOPS | Sustained instance baseline | Shortfall once burst is spent |
|---|---|---|
| 12,000 | 6,000 | 50% |
| 12,000 | 3,600 | 70% |
| 24,000 | 20,000 | 17% |
| 3,000 | 2,000 | 33% |
The third row differs in kind from the others. On m6g.4xlarge the baseline and the maximum are both 20,000, so there is no burst headroom to borrow against and that 17% is a hard ceiling. AWS publishes no burst-balance metric for instances in that position, which had a second consequence for us: our I/O pacing tool read that metric to decide when to throttle, found nothing, and quietly disabled itself on the largest table in the fleet.
Check yours before planning anything:
aws ec2 describe-instance-types --instance-types m6g.xlarge \
--query 'InstanceTypes[0].EbsOptimizedInfo'
Two diagnostics saved us time afterwards. EBSIOBalance% is the instance's burst credit, not the volume's — when it reads zero you are at the instance baseline regardless of what the storage can do. And if EBSByteBalance% sits at 92–99% while EBSIOBalance% is at 0, you are IOPS-bound rather than throughput-bound, so buying storage throughput will change nothing.
RDS gp3 provides 12,000 IOPS as baseline storage performance at 400 GiB and above, so on volumes this size the figure isn't something anyone opted into and overpaid for. It's an architecture observation rather than a billing one. What matters for a migration is narrower:
Plan the backfill against the sustained instance baseline, never the provisioned storage number.
And more generally: fix the workload before resizing the hardware. Handing 6,000 extra IOPS to a query that should read 4 blocks instead of 34,479 just re-pins the ceiling at a higher number.
I/O headroom, not table size, is what gates when a backfill can run.
4. The index that made the backfill 83× faster
A backfill copies in time slices:
WHERE "timestamp" >= $1 AND "timestamp" < $2
If no index leads on "timestamp", PostgreSQL will reach for the business-key index — (site_id, device_id, "timestamp", metric_id) — and apply your bound as a non-leading index condition.
Which means traversing the entire index on every slice, regardless of how little data that slice contains.
Nothing errors. Nothing warns. The slices just crawl.
One deployment, 5.2 GB index against 1.9 GB of shared_buffers, identical 7-day slice, identical 22,350 rows:
| | Buffers from disk | Cache hit | Time |
|---|---|---|---|
| Before | 660,473 (~5 GB) | 0.2% | 34,748 ms |
| After | 3,080 | 86% | 419 ms |
214× fewer reads, 83× faster. Over that deployment's ~356 slices, a 390 MB index that took three minutes to build removed about 3.4 hours of pure traversal overhead.
Whether you need it depends entirely on cache. Where the business-key index fit comfortably in shared_buffers, adding a standalone timestamp index changed nothing at all. Where it didn't, it was the difference between hours and days.
Don't try to reason this out from free memory, either. That deployment had 4.3 GB free against a 5.2 GB index and still managed a 0.2% hit rate, because heap reads and write traffic evict the index continuously. Run EXPLAIN (ANALYZE, BUFFERS) on a single slice and look at shared read. Hundreds of thousands means you have it.
Slice width behaves backwards here too. Before the index, a 1-day slice and a 7-day slice both read ~630,000 buffers, because the cost is the fixed traversal rather than the data. Widening slices doesn't make each cheaper — it reduces how many traversals you pay for. Afterwards, cost becomes proportional to rows and slice width mostly stops mattering.
One sharp edge if you build it on a live table: never run CREATE INDEX CONCURRENTLY under a short client timeout. A terminated concurrent build leaves an index with indisvalid = false, which PostgreSQL refuses to use but continues to maintain on every insert. Worst of both, and it needs a manual DROP INDEX. We ran one under a 120-second client timeout and got away with it only because a long CREATE INDEX never writes to the socket and so never notices the client is gone.
5. Drop the secondary indexes before you backfill
Loading 200 million rows into a table with eight index trees means maintaining eight index trees, 200 million times.
So we didn't. The partitioned table was created with only the primary key and the business key — both required for the ON CONFLICT upsert — and the other six were built afterwards, per partition.
| | Rate | I/O credits per million rows |
|---|---|---|
| Backfill, 2 indexes | 9,700 rows/s | ~1.4 |
| Backfill, 8 indexes | 5,062 rows/s | ~4.8 |
| Deferred build afterwards | 60,000 rows/s | — |
| Full run | Wall clock |
|---|---|
| Indexes deferred | ~7 hours |
| All indexes maintained (projected) | ~28 hours |
Building per partition pays twice over: the heap is read once and stays cached for the remaining five indexes, and each per-partition sort (~350 MB) fits in maintenance_work_mem where a whole-table sort would not.
Before moving on, check the result. An index left indisvalid = false is ignored by the planner in silence:
SELECT indexrelid::regclass, indisvalid
FROM pg_index WHERE indrelid = 'readings'::regclass;
6. What the numbers looked like across six deployments
All PostgreSQL 14 on RDS except one, which was PostgreSQL 15 on Cloud SQL:
| | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| Rows migrated | 201 M | 10.5 M | 5.4 M | 58.4 M | 201.2 M | 311.7 M |
| Months reconciled | all | 22/22 | 79/79 | 82/82 | 96/96 | 81/81 |
| Backfill wall-clock | ~7 h | ~50 min | ~65 min | ~2.5 h | ~7 h | ~24 h |
| Observed rate | 9,700/s | ~10,000/s | ~10,000/s | 10–11,000/s | 4,500–10,000/s | ~8,000/s |
| Deferred index build | 42 min | 6 min | 12.8 min | 17.4 min | 41.2 min | ~70 min |
| Total size before → after | 327→194 GB | 36→5.2 GB | 5.5→4.6 GB | 59→45 GB | 430→182 GB | 481→283 GB |
| Cutover attempts | 3 | 1 | 1 | 1 | 1 | 1 |
| Rows lost | 5 (recovered) | 0 | 0 | 0 | 0 | 0 |
A single deployment would not have taught us any of the following.
Copy rate converges on roughly 10,000 rows/s once a leading timestamp index exists. Deviations from that are index traversal, not copy throughput.
Wall-clock doesn't track row count. Deployment C took longer than B for half the rows, because on sparse-but-long histories the cost follows slice count rather than data volume — C spent about 48 seconds per month on slice overhead while its ~44,000 rows copied in under 5. Scale slice width to data density, not to table size.
The size reduction is reclaimed bloat, not partitioning, and it ranged from 7× down to 1.2×. Don't forecast one deployment from another: B's 7× briefly convinced us that F's storage problem would dissolve on its own, and C's 1.2× settled the argument the other way.
And every single deployment differed — table owner, index set, month range, PostgreSQL version, credential source, I/O profile. Building the tooling to derive all of that from the catalog, rather than carrying facts forward from the last environment, was the best engineering decision in the project.
Part III — The cutover
The transaction has four statements, and two of them get forgotten
1. rename the live table → readings_old
2. rename the shadow table → readings
3. CREATE OR REPLACE VIEW ← forgotten
4. ALTER SEQUENCE … OWNED BY ← forgotten
A view binds to a relation OID, not to a name. Rename the table underneath it and the view carries on reading the frozen old table. In our case every reporting path went through a view, so the failure mode would have been entirely plausible numbers that quietly stopped matching reality. Nothing errors. Nothing breaks. It's just wrong, indefinitely.
The shadow table shares the original's sequence, which is still owned by the old table's column. Skip the ALTER SEQUENCE … OWNED BY and dropping readings_old weeks later takes the sequence with it, breaking id defaults on a table that has been happily serving traffic since — with no obvious connection back to the cutover.
Wrap all four in SET LOCAL lock_timeout = '30s' so the transaction fails fast rather than queueing.
The catch-up needs two passes
We didn't want to stop ingestion to do this. So the transaction takes its ACCESS EXCLUSIVE locks and then, while nothing on the system can possibly write, re-copies recent data with ON CONFLICT … DO UPDATE before performing the renames.
It's cheap: 3,277 rows over a 3-hour window, a sub-second insert.
Our first version bounded that copy by timestamp alone, and it cost us the only rows we lost in the entire project:
| | |
|---|---|
| Bulk copy finished | 10:41 UTC |
| Cutover committed | 11:08 UTC |
| Catch-up covered | timestamp >= 08:08 UTC |
| Rows lost | 5 |
Those five rows carried a timestamp of 00:05 UTC — eleven hours outside the window — but arrived seconds before the swap. Our pipeline re-uploads historical windows, so a row can be freshly arrived and ancient at the same time.
There are two independent ways a row can be stale, and neither pass catches the other's case:
By arrival — id > <the shadow's current max id>. This catches late arrivals at any timestamp. It works because id comes from a sequence shared with the source table and the backfill copies it verbatim, so id order is arrival order.
By timestamp — >= now() - <interval>. This catches rows the pipeline updated in place. The DO UPDATE path keeps the existing id, so an updated row never rises above the watermark and the first pass can't see it.
Keep them as two separate statements rather than one with OR. An OR across two columns invites a bitmap-or or a sequential scan of a 246 GB heap — while every reader and writer on the system is blocked behind you. Each statement should be driven by exactly one index.
One more trap in the same code path: resolve the watermark to a literal value first.
-- read this into a variable
SELECT max(id) FROM readings_old;
-- then use it as a literal
SELECT count(*) FROM readings WHERE id > 615978549;
id > (SELECT max(id) FROM readings_old) is not usable as an index bound and degrades to scanning every partition. We measured it: sub-second with a literal, still running after two minutes with the subquery — inside a transaction holding an exclusive lock on the whole table.
Two gaps this still doesn't close, which we chose to accept: a row updated in place and older than the timestamp window, and a row deleted from the source after the bulk copy. An INSERT cannot observe an absence.
Two failures a dry run cannot find
Both hit the real cutover. Neither is reachable by printing statements without executing them, because no lock is ever requested and no trigger event is ever queued.
LockNotAvailable — could not acquire ACCESS EXCLUSIVE within 30 seconds. Preflight had passed moments earlier. But the table has continuous readers, and some reporting queries legitimately run for minutes. Any query already holding AccessShareLock when the swap asks for its lock blocks it for that query's full duration.
The fix is retries, not a longer lock_timeout — and that's worth sitting with, because the instinct runs the other way. A queued ACCESS EXCLUSIVE request blocks every new reader behind it. Waiting longer converts a harmless failed attempt into a partial outage. Fail fast, let the queue drain, try again in twenty seconds. A failed attempt changes nothing.
ObjectInUse: cannot ALTER TABLE … because it has pending trigger events. Our foreign keys are DEFERRABLE INITIALLY DEFERRED, reproduced faithfully from the source table. So the catch-up INSERT queues its FK checks as pending trigger events, and PostgreSQL then refuses ALTER TABLE on that relation — aborting the whole transaction two statements later.
SET CONSTRAINTS ALL IMMEDIATE; between the catch-up and the renames fires the deferred checks and clears the queue.
Rehearse the cutover somewhere you can afford to be wrong. Both of these were found on a sandbox, on the third attempt, which is exactly where you want to find them.
Row counts are not verification
Once the swap lands, the live table legitimately runs ahead of the old one, because ingestion now writes to it. A count comparison therefore can't distinguish "ahead" from "ahead, and also missing something."
The only query that answers the question is a business-key anti-join:
SELECT count(*) FROM readings_old o
WHERE o."timestamp" >= $1
AND o."timestamp" < $1::timestamptz + interval '1 day'
AND NOT EXISTS (
SELECT 1 FROM readings n
WHERE n.site_id = o.site_id
AND n.device_id = o.device_id
AND n."timestamp" = o."timestamp"
AND n.metric_id = o.metric_id);
Business key, not primary key — a re-emitted row carries a fresh id.
This is the query that found our five missing rows. The automated verification suite had already passed, twice, before anyone ran it. A green verification run is not evidence that no data is missing unless the suite contains this query.
Slice it by day, or it will outlive your shell. Ours cost ~105 seconds for a 37,000-row day and ~352 seconds for a 260,000-row day.
Recovery, while the old table still exists, is trivial and idempotent:
INSERT INTO readings (<columns>)
SELECT <columns> FROM readings_old
WHERE "timestamp" >= $1 AND "timestamp" < $1::timestamptz + interval '1 day'
ON CONFLICT (site_id, device_id, "timestamp", metric_id) DO NOTHING;
Which is the entire argument for not dropping the old table quickly.
Confirm writes by arrival, not by max(timestamp)
How do you prove the application is now writing to the new table?
We compared max("timestamp") across both tables, got identical values, and concluded ingestion hadn't resumed. It had — 13,935 rows had already landed.
Because the pipeline upserts existing windows, max("timestamp") only advances when genuinely newer data arrives. A busy writer can leave it static for hours while writing continuously.
The id watermark answers it directly:
SELECT max(id) FROM readings_old; -- e.g. 615978549
SELECT count(*) FROM readings WHERE id > 615978549;
Anything above the old table's final id was written after the swap.
A verification that cannot fail is not a verification
I want to end this section on the mistake we made three separate times, in three different tools, before recognising it as a pattern.
One comparison tool's first run reported:
2026-06 site=1 IDENTICAL rows=0 devices=0
...
PASS -- the partitioned table returns identical numbers
Every pair was empty. It had picked test subjects with SELECT id FROM site ORDER BY id LIMIT 2, and those two rows happen to have no readings at all.
It compared zero to zero, four times, and called it success.
There are two defects in that, and they're the same defect in different clothes: choosing test subjects by convenience rather than by content, and treating equal as verified. Two empty sets are equal.
The tool now probes each candidate for rows first, reports empty pairs as HOLLOW — 0 rows on both sides, proves nothing, excludes them from the pass count, and exits non-zero when every pair was hollow.
Before trusting a green run, confirm it processed a non-zero amount of data — and make the tool refuse to report success when it didn't. "Nothing was compared" has to be a distinct outcome from "what was compared, matched."
We later found the same shape in an API parity harness and in a check that compared two equal maxima. Three times in one project isn't bad luck, it's a design smell.
Part IV — After the cutover
The old table is not a rollback option
This is the intuitive framing and it's wrong, so it's worth being blunt about.
The old table froze at the moment of cutover. Ingestion has been writing to the new one ever since — 2.06 million rows within days on one deployment. Renaming the old table back would silently discard every row written since the swap.
Which means the old table is not a rollback target. You could in principle go back — but only by first reconciling every row written since the swap out of the live table and into the frozen one, and that is a data migration, not a rollback. Nobody is doing that under incident pressure. Its remaining value is as a reference copy — what did the old data say — and that does not require keeping 327 to 481 GB of heap around.
So keep the evidence instead of the table. Before dropping, capture per-month row counts and per-entity monthly checksums. That's kilobytes standing in for hundreds of gigabytes, and it preserves the only thing the old table was still good for.
Which reframes the timing question. It isn't how long until rollback stops being needed, because rollback was never available. It's what latent problem would more time actually reveal?
The answer is rarely-exercised read paths: monthly and quarterly aggregations, exports, regulatory reports, scheduled notifications. Those run on schedules, so the forcing function is a reporting cycle, not elapsed days.
Wait for one month-end cycle to complete, then drop — staggered across deployments, so the others survive anything that surfaces.
One expectation to set early, because it came up the moment finance saw the numbers: dropping the old table does not reduce your RDS bill. Allocated gp3 storage can't be decreased. The space is reclaimed inside the volume, not removed from it. What you gain is headroom, and lower vacuum and backup overhead.
Two configuration bugs worth stealing
Both are shaped like code you have already written.
A truthy default silently overriding derived configuration. Several entry points declared --region with a default of "us-west-1". The derivation logic beneath it was correct and already present:
region = region_name or cfg["region"]
But "us-west-1" is truthy, so region_name or … never reaches the derived value. Every deployment looked up its correct parameter names in the wrong region.
When a parameter exists so configuration can be overridden, its default must be
None— not the most common value. A non-Nonedefault turns "override if given" into "always override".
A hardcoded role that existed on two deployments out of six. A scheduled maintenance job did set_config('role', 'app_owner', true). That role owns the table on two deployments and doesn't exist on the other four, where the job failed silently every night for weeks.
Both are the same error: a value that happened to be correct in the environment where it was written. In a fleet, derive from the catalog.
What I'd monitor now
-- Where is the bloat, and are statistics current?
SELECT schemaname, relname, n_live_tup, n_dead_tup, n_mod_since_analyze,
last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC;
-- Heap versus indexes
SELECT pg_size_pretty(pg_relation_size('schema.table')) AS table_size,
pg_size_pretty(pg_indexes_size('schema.table')) AS indexes_size,
pg_size_pretty(pg_total_relation_size('schema.table')) AS total_size;
-- Is a vacuum running right now, and how far has it got?
SELECT pid, relid::regclass, phase, heap_blks_total,
heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count
FROM pg_stat_progress_vacuum;
And one that works on any platform, including managed services that expose no infrastructure metrics at all. Take it twice, about 25 seconds apart, and difference it:
SELECT blks_read, blks_hit, xact_commit
FROM pg_stat_database WHERE datname = current_database();
SELECT count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE wait_event_type = 'IO') AS waiting_on_io
FROM pg_stat_activity WHERE backend_type = 'client backend';
Read blks_read carefully: it counts blocks PostgreSQL had to fetch because they were not in shared_buffers, and blks_hit counts only shared_buffers hits. Neither knows anything about the OS page cache, so blks_read/s × 8192 is bytes entering PostgreSQL's buffers rather than bytes leaving a physical device — it will overstate real disk traffic wherever the filesystem cache is absorbing reads. As a workload indicator it is still excellent, and it is not interchangeable with your provider's disk-bytes metric. The trend, the hit ratio, and the count waiting on I/O are the signals that matter. We came to prefer this over cloud provider metrics, partly because two of our deployments couldn't supply those at all.
No single number here means much alone. The picture comes from combining them.
What I'd do differently
Partition earlier. Hundreds of millions of rows is not itself a problem. But when data is time-based, arrives continuously, eventually expires, gets deleted in large batches, and is expected to grow without bound — partitioning belongs in the first architecture conversation. Waiting until the table is hundreds of gigabytes turns a design decision into a migration project.
Audit unbounded queries first, not last. That blocked day was the single most expensive item in the project, and the audit that would have prevented it takes an afternoon.
Ship the application changes before the cutover, not with it. Our pruning predicates were backward compatible — a redundant WHERE clause on an unpartitioned table, and measurably helpful even there. Shipping them a week early meant that on cutover day, any application surprise surfaced while the old table was still serving traffic and rollback was still trivial.
Derive everything from the catalog. Owner, index set, month range, grants, sequence ownership, credentials. All of it differed across six deployments. Anything hardcoded is a bug waiting for the environment where it's wrong.
The conclusion I didn't expect
PostgreSQL was never failing. It was doing exactly what we asked.
Given the right index it answered real business queries against a 390-million-row table in roughly a hundred milliseconds, and a latest-record lookup in 17. Autovacuum ran. VACUUM worked. ANALYZE worked.
What had actually happened is that the data lifecycle outgrew the table architecture — and a database can stay fast for a long time while that debt accumulates underneath. The warning signs aren't slow queries. They look like this:
ever-growing table size
+ large retention DELETEs
+ increasing vacuum work
+ large indexes
+ stale statistics
+ maintenance I/O
When those appear together, another index isn't the answer.
Partitioning didn't make our queries dramatically faster, and it was never going to. What it did was make the table operable: maintenance bounded by a month rather than five years, retention as a DROP rather than a DELETE, and indexes small enough to rebuild without scheduling an outage.
The path for us ran:
query tuning → index optimization → VACUUM investigation
→ statistics investigation → I/O management → partitioning
Two things to take away, if nothing else.
A fast query does not mean you have a healthy table. Query performance, table maintenance and data lifecycle are three different problems, and at scale you have to design for all three.
Partition pruning is a precondition, not an automatic benefit. Audit the queries that don't constrain the partition key before you cut over — they are the ones most likely to regress afterwards, and cutover day is a bad time to find out which.