CTAS-and-swap: rewriting a large Postgres table without the MVCC tax

When it comes to data engineering, scale matters a lot. A single-row update on a small SQL table of ten to a thousand rows doesn't raise any concerns. But once you start moving into tens of millions of rows and having to update all of them, the simple SQL UPDATE command becomes inefficient. It turns minutes into hours.

The first step to optimizing at scale is understanding your bottleneck, which in this case is time. What makes updating every row in a table of tens of millions scale exponentially? The cause is Postgres's MVCC design.

What UPDATE actually does under MVCC

Postgres never modifies a row in place. Every UPDATE writes a new version of the row and marks the old version as dead. That's the price of MVCC: readers get consistent snapshots without blocking writers, at the cost of leaving old versions behind until a VACUUM cleans them up.

For a single UPDATE, this is invisible. For millions of UPDATEs against the same table, it compounds. Every UPDATE adds a dead tuple to the table's pages, and every index on the table gets a new entry pointing to the new row version while the old entry stays until VACUUM.

As dead tuples accumulate:

• Each table page holds less live data per unit of page. Postgres has to read and write more pages to touch the same amount of live data.
• Indexes bloat with dead entries. B-tree traversal walks over more pages for the same lookup.
• Each subsequent UPDATE does more work than the last, not because your query changed, but because the table it's running against is fatter.

The slowdown is baked in the moment you choose UPDATE for a full-table rewrite.

And it compounds further when the transformation runs on a regular schedule. Each successive run pays a higher bloat tax than the one before, so the pipeline that finished in 3 hours this month can take 4 next month for reasons that have nothing to do with the code you wrote. VACUUM isn't a way out either: it scans the whole table to find and mark dead space, so its own cost scales linearly with table size. On a 20-million-row table, running VACUUM is measured in minutes to hours, and autovacuum tends to lag on tables being heavily updated.

The fix: don't UPDATE, INSERT into a fresh table

The pattern is called CTAS-and-swap, short for "Create Table As Select and swap." Instead of updating rows in place, create a new empty table, INSERT the transformed rows into it, then swap the new table into the position of the old one.

-- 1. Create the new table with the same shape
CREATE TABLE records_new (LIKE records INCLUDING ALL);

-- 2. Insert the transformed rows (in parallel, batched, however you like)
INSERT INTO records_new
SELECT record_id, col_a, transform(col_b), col_c, ...
FROM records;

-- 3. Atomically swap
BEGIN;
ALTER TABLE records RENAME TO records_old;
ALTER TABLE records_new RENAME TO records;
COMMIT;

-- 4. Drop the old
DROP TABLE records_old;

Every INSERT hits a fresh, empty table. No dead tuples, no page bloat, no index bloat, and no work that gets harder as the run progresses. Write speed is flat from row 1 to row 20 million.

The swap is a metadata operation, not a data movement. It takes a moment of table-level lock but finishes in milliseconds regardless of table size.

What you give up

CTAS-and-swap assumes the source table isn't being modified concurrently during the transformation. Given that, two constraints worth naming:

• Disk headroom. For a brief window (while the new table is being built and before the old one is dropped), both tables sit on disk. If you're already close to a disk limit, you need to plan for this.
• Resume needs bookkeeping. If your INSERT phase is a single transactional INSERT ... SELECT, a crash rolls back everything and you restart from scratch. If it's driven by a script committing batches as it goes (like the parallel-worker setup from Part 2), committed batches survive the crash, but the new table has no natural marker for "how far I got," so resume means tracking which ranges finished before the crash.

The numbers

The pipeline that led here was reprocessing a 20-million-row table with a Python transform, using 8 parallel workers each holding a range of primary keys. Under the UPDATE-based approach, wall-clock time was over 3 hours per run, and the biweekly cadence meant bloat compounded across runs on top of the within-run slowdown.

Switching to CTAS-and-swap on the same hardware, same 8 workers, same 20 million rows: 12 minutes. About a 14x improvement, coming entirely from the write shape (the read side and enrichment cache, covered in Part 2 and Part 1 of this series, didn't change).

If you're doing full-table transformations at 10M+ row scale, this pattern belongs in your default toolkit. UPDATE has its place, just not batch reprocessing at that scale.