Rebuilding a 1.2 TB Live Table Without a Maintenance Window

One of our customer's PostgreSQL tables had reached 1,273 GB and about 392 million rows. An ETL pipeline wrote to it continuously, and live monitoring dashboards depended on it. We needed to rebuild the table while both stayed running.

Autovacuum was struggling to keep up with the updates. The pipeline regularly re-uploaded historical windows, and its upsert created a new row version even when the values hadn't changed. Over one month, we measured 245 million updates against 8.5 million inserts: roughly 29 updates for every new row.

The accumulated bloat was expensive. The heap used 3.2 KB per row; a clean copy needed about 0.9 KB. Queries were reading far more pages than the data warranted. Disk queue depth stayed above 100 for hours, and the pipeline's own inserts were taking fourteen minutes each.

We chose monthly partitioning so autovacuum could work on smaller tables. A 14 GB partition could be vacuumed in minutes. The larger tables were taking longer than the workload allowed.

A dump and restore would have required hours of downtime. Rebuilding the table in place under an exclusive lock wasn't practical either. We decided to create a replacement alongside the original, copy the data gradually, and switch over once it was ready.

We eventually used this approach across ten deployments, moving roughly 1.5 billion rows. Four were production deployments. All four production cutovers completed on their first attempt, with no missing rows found by our checks. Getting there took a few failed attempts elsewhere, including one cutover that missed five rows.

Building the replacement

The basic approach was straightforward: create a partitioned shadow table, copy into it while the application continued using the original, then swap the names in a transaction.

The application didn't read the shadow table, so we could spread the copy over several days if necessary. It still competed for the same disk and CPU, though. Most of the backfill work went into keeping that competition manageable.

Once the data was ready, the swap needed four DDL statements:

1. Rename the source table to ..._old.
2. Rename the shadow table to the original name.
3. Recreate the dependent view with CREATE OR REPLACE VIEW.
4. Transfer ownership of the ID sequence with ALTER SEQUENCE ... OWNED BY.

The last two were easy to overlook because a successful rename wouldn't expose either problem.

A PostgreSQL view refers to the underlying relation by OID. Renaming a table doesn't redirect the view to whichever table later takes its name. Without recreating the view, our dashboards would have continued reading the old table. They would still return plausible results, but those results would stop changing. Every dashboard path in this application went through that view.

The sequence needed similar attention. The shadow table used the original sequence, but the sequence was still owned by the old table's column. Leaving that dependency in place would make the eventual cleanup of the old table affect a sequence the new table still needed.

We set SET LOCAL lock_timeout = '30s' for the swap transaction. If it couldn't acquire the lock, it would fail and leave the original table in place. Later attempts showed why we needed that limit.

Keeping the copy under control

The backfill used INSERT INTO new SELECT FROM old, working through the data in daily slices. A progress table recorded completion by month so an interrupted run could resume. That was enough for the copy itself; index maintenance and IO capacity needed more work.

We first reduced the number of indexes maintained during insertion. The finished table had eight indexes, but we kept only the primary key and business key during the backfill because we needed them for conflict handling. The remaining six were built afterwards.

That reduced IO credits consumed per row by a factor of 3.3 and made the copy 1.9 times faster. We built the secondary indexes one partition at a time, which helped keep each partition's heap in cache for successive builds. On the largest deployment, the process created 1,176 indexes in about two hours.

The source table also needed an index with the partition timestamp as its leading column. Without one, PostgreSQL was traversing the business-key index for each timestamp-bounded slice. Adding the timestamp index reduced disk reads per slice from 888,895 to 55,110 on one deployment. On a smaller deployment, the same operation went from 11,683 ms to 20 ms.

Building that index had its own operational detail. All four production databases had a default statement_timeout of 120 seconds. An interrupted CREATE INDEX CONCURRENTLY can leave an invalid index behind: unavailable to queries, but still adding write overhead. We disabled the statement timeout for the build session and ran the build as a background job.

We also had to check the instance's EBS limits, not just the provisioned IOPS on its volume. One development database had a volume provisioned for 12,000 IOPS, while its instance class had a baseline of 6,000. We spent a week seeing the database sit at roughly 5,967 IOPS, with no room for the backfill, before checking the instance limit. Provisioning more IOPS on the volume hadn't given us that much sustained capacity at the instance.

On instances with burst credits, the backfill watched EBSIOBalance%. It paused below 30% and resumed above 50%. This let the copy back off as credits ran down.

That guard wasn't available everywhere. Larger instance classes had an EBS baseline equal to their maximum and didn't publish a burst-credit balance. Two of our four production instances, including the busiest, fell into that group. The script warned when the metric was absent and continued without that guard.

For those deployments, we used shorter slices, sleeps between slices, and quieter periods identified from each instance's daily load. Another guard stopped the job after three consecutive slow slices. It aborted the backfill twice on the busiest deployment when application traffic spiked.

Database statistics were useful across all the deployments, including two where we couldn't get CloudWatch data. We sampled these counters about 25 seconds apart:

SELECT blks_read, blks_hit
FROM pg_stat_database
WHERE datname = current_database();

We also checked how many client backends were active or waiting on IO:

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';

The change in blks_read gave us the rate of block reads into PostgreSQL's buffer cache. With 8 KB blocks, multiplying the per-second rate by 8,192 gives the corresponding byte rate, although some reads may be served by the operating system's cache. We watched that alongside the cache hit ratio and the number of backends waiting on IO.

With enough headroom, the backfill sustained about 10,000 rows per second. Maintaining all eight indexes during the copy had given us roughly 5,000.

The cutover that missed five rows

The production runs benefited from mistakes made during the earlier cutovers. The most serious was an incomplete catch-up.

Our backfill left the current month out of the historical copy because the pipeline was still changing it. The cutover included a final catch-up under lock, before the tables were renamed. Once the lock was held, concurrent writes could no longer change the source while we copied.

Our first catch-up query selected rows whose timestamps fell within the previous three hours. It missed five rows.

The pipeline can upload old data at any time. A measurement timestamped 00:05 might arrive at 11:00. Those five rows had arrived just before the cutover, but their timestamps were eleven hours outside the catch-up window. The query never selected them.

We changed the catch-up to use two passes. The first selected IDs above the shadow table's highest ID to pick up newly inserted rows, including late arrivals with old timestamps. The second selected the recent timestamp window to refresh rows that had been updated while retaining their existing IDs.

We kept these as separate statements. Combining the predicates with OR risked a plan that scanned a large part of the heap while readers and writers were blocked. Separate statements let each pass use its own index. Both were idempotent, so selecting a row twice was acceptable.

The following nine cutovers had no missing rows found by verification. The check that found the original five was a business-key anti-join; the structural checks had already passed twice.

Two failed swap attempts

One attempt failed with LockNotAvailable after waiting 30 seconds for the exclusive lock. A preflight check moments earlier had shown only a query that was 16 seconds old.

Increasing the timeout would have been a poor fix. A waiting exclusive-lock request can cause new readers to queue behind it. The longer it waits, the longer those queries may be blocked, even though the swap hasn't started doing useful work.

We used retries instead: up to five attempts, twenty seconds apart. A failed attempt rolled back without changing the tables. With continuous readers and some long-running analytical queries, the preflight check couldn't promise a clear moment for the swap.

The next failure was less obvious:

cannot ALTER TABLE because it has pending trigger events

The source table had four foreign keys declared DEFERRABLE INITIALLY DEFERRED, and we had copied those definitions to the shadow table. Their checks normally waited until commit.

That mattered because the catch-up inserts and table renames happened in the same transaction. The inserts queued foreign-key checks. Before the transaction reached its commit, the rename tried to alter a table that still had pending trigger events, which PostgreSQL refused to do.

We added this between the catch-up and the renames:

SET CONSTRAINTS ALL IMMEDIATE;

It ran the queued checks before the rename, allowing the transaction to continue.

Neither failure appeared in our dry run, which printed the statements without executing them. It never requested a lock or inserted a row, so it couldn't encounter either problem. The earlier deployments gave us the rehearsal the dry run couldn't provide.

Checking what actually moved

After the swap, the new table immediately started receiving writes. The old table stayed frozen. That made a simple row-count comparison inconclusive: the new table could have more rows overall and still be missing some from the old one.

We checked for missing business keys instead, one day at a time:

SELECT count(*)
FROM measurements_old o
WHERE o."timestamp" >= %(day)s
AND o."timestamp" < %(day)s + interval '1 day'
AND NOT EXISTS (
SELECT 1
FROM measurements n
WHERE n.site_id = o.site_id
AND n.sensor_id = o.sensor_id
AND n."timestamp" = o."timestamp"
AND n.method_id = o.method_id
);

The business key was the right identity for this comparison because a row re-emitted by the pipeline could have a new primary-key ID. This query found the five rows the first cutover had missed.

We used several checks because each covered a different part of the migration:

Even some apparently simple checks needed correction. To see whether the pipeline had resumed, we initially compared the tables' maximum timestamps. On one deployment, that suggested no progress even though the new table had received 13,935 rows. The pipeline was writing historical windows, so the maximum timestamp hadn't changed.

We switched to checking for IDs above the old table's highest ID. Resolving that watermark to a literal value before running the query also gave us a better plan in our tests. The subquery form, id > (SELECT max(id) FROM ..._old), scanned across the partitions in the plan we observed.

Our first three HTTP capture attempts were empty for another reason: the endpoint resolved sensors by name, and we had supplied numeric IDs. It returned HTTP 200 with an empty payload. We had to read the resolver to distinguish that from a valid request for a window with no data.

On the final deployment, three endpoints reported differing results even though our data checks agreed. Counts, sums, and the highest ID matched, and subsequent writes affected a different day and site.

The remaining difference came from floating-point addition. The backfill had inserted rows in timestamp order. The original table held them in the order they had arrived over six years. A different aggregation order could change the last digits of the result.

For a total of roughly 21,000, the difference was about 0.0000000001, or around six parts in a quadrillion. We didn't dismiss it just because it was small. We checked the underlying data and per-sensor counts before treating it as rounding noise. Matching counts alone wouldn't have proved that the values were unchanged.

Query performance after the swap

Keeping the original table gave us a useful comparison. We could run the same query against the frozen, unpartitioned copy and the new table, rather than compare measurements from different days or environments.

We tested a representative dashboard query: one site, one month, aggregated by sensor and day. The tests covered three query variants on two production deployments. We ran each twice and reversed the order to account for the first run warming the buffer pool. The before-and-after measurements below compare the unpartitioned table with the partitioned table using explicit bounds on the partition key.

Deployment B's recorded disk reads fell from 180,685 pages to seven in the cold-cache comparison. Across the two deployments, the query ran 18–25 times faster with a warm cache and about 7–12 times faster with a cold one.

Getting those results required query changes as well as the new table. The plan against the original table referenced two relations. The partitioned query with bounds on the partition key referenced five. Without those bounds, it referenced 112.

Our table had two timestamp columns. One stored UTC; the other represented the same measurement in the site's local time. Operators used local time, so most dashboard queries filtered on the local column. We had partitioned on UTC because it was unambiguous.

PostgreSQL couldn't infer the relationship between the two columns. A condition on local time didn't tell it which UTC partitions it could skip, so those queries could still visit every partition.

We added a UTC predicate derived from the requested local-time range, extending the range by 24 hours at both ends. The existing local-time filter continued to determine which rows belonged in the result. The wider UTC filter allowed partition pruning.

For the timezone offsets we were accounting for, from UTC−12 to UTC+14, that margin covered the difference between the two columns. It could include extra candidate rows, which the original filter would remove. With monthly partitions, the extra day on either side meant visiting at most two additional partitions instead of all 110.

We audited the query sites in the codebase and added the predicate to fifteen of them. The helper logged a warning and omitted the extra predicate when it couldn't parse a date. A failed optimization shouldn't break an otherwise valid request.

Three query sites remained unpruned and were documented. One asked whether a site had any data at all; adding a date bound would have changed the question.

Storage and cutover results

These were the recorded sizes for the four production deployments:

The largest deployment recovered about 929 GB. Across the four, the saving was roughly 1.1 TB, mostly from removing accumulated bloat.

The source of that saving varied. On three deployments, the heap changed little and most of the reduction came from rebuilding the indexes. One replacement had eight indexes occupying 12 GB, while the source had four occupying 30 GB. On the largest deployment, the heap itself fell from 1,168 GB to 280 GB.

There was still a brief interruption during each swap. The transaction required an ACCESS EXCLUSIVE lock: existing queries had to release their locks, and new queries waited. On our deployments, the work performed while holding the lock included two catch-up passes and four DDL statements. The catch-up moved between 290 and 11,137 rows, and the work under lock finished in under a second.

We didn't schedule a maintenance window or stop the pipeline. We did briefly block queries. Systems with stricter latency requirements would need to measure that interval against their own limits, including any wait to acquire the lock.

What we'd do differently next time

The measurement I wish we'd taken earlier was how many updates actually changed anything.

After the migration, we found that 92.5% of the rows rewritten by the pipeline had identical values before and after the update. Most of the dead rows autovacuum was struggling with came from work the pipeline didn't need to do. Adding a WHERE ... IS DISTINCT FROM condition to the upsert could avoid the bulk of those writes.

Partitioning still helped, and the rebuild recovered a substantial amount of space. But we should have examined the update workload before committing so much effort to handling it.

We'd also keep the deployment-by-deployment inspection. Across ten environments, the details varied enough that reusing assumptions would have caused problems: table owners, index definitions, PostgreSQL versions, available IO, date ranges, permissions, and monitoring metrics. One database contained three stray rows at the Unix epoch. Using its minimum timestamp without checking would have created 680 monthly partitions instead of 146. Each deployment needed a fresh read-only inventory.

The verification scripts needed just as much scrutiny as the migration scripts. An early version reported success before the swap had happened because it checked the view's table name without establishing which relation that name referred to. We changed it to refuse to report until the old table existed. Empty API responses also stopped counting as successful comparisons; they told us nothing about the data we had changed.

Several of the problems we checked for wouldn't produce an obvious application error. A view could keep reading the old table. An invalid index could add write overhead without helping a query. A sequence dependency could remain unnoticed until cleanup. The migration needed explicit checks for those states, as well as the anti-join that checked for missing business keys.

Resumability was worth the effort from the start. The backfill recorded progress by month, and the index builder read the system catalogue to see what remained. We had two load-related aborts and several dropped connections. Recovering from them meant restarting the job and continuing from the recorded state.

We eventually moved the long-running jobs onto an instance in the same VPC as the database. Before that, VPN disconnections had interrupted a backfill, a row count, and a comparison run. The database did the copying; the client mostly needed a connection that would stay up for hours.

Building a replacement alongside the original gave us time to copy, inspect, and test before the swap. The earlier deployments exposed problems the dry run couldn't, and the retained tables let us check both correctness and performance afterwards. By the time we reached production, we had rehearsed the cutover and knew what we needed to verify.

We help with live database migrations, performance investigations, and the checks needed to establish that a migration worked. If your table has outgrown its maintenance window, get in touch.