The running example#
An orders service stores an order's status in a lowercase text column, status. The team wants a new column, state, with a stricter set of uppercase values, and eventually wants status gone. Three releases take part:
| Release | Reads | Writes |
|---|---|---|
2026.11.3 | status | status |
2026.11.4 | state | status and state |
2026.11.5 | state | state |
During each rollout two neighboring releases run at once, and the previous one stays available as a rollback target. We ran every lab on PostgreSQL 16.15 with Python 3.11 and psycopg 3.2. Timings come from a laptop-sized machine and only compare one method with another on the same data.
Know who still writes the old shape#
The move cannot finish while something still writes status without state. That writer is not always the previous release of the service: batch jobs, exports and other services connect too.
- Tag database connections with the release that opened them shows how
application_nameand a small trigger attribute every write to the old column. - Why a data step in a schema migration misses rows shows the old release writing rows after the migration's
UPDATEhas finished.
Backfill the rows that already exist#
A backfill copies existing rows into the new shape while both releases keep writing. Each of these labs shows a backfill that completes without an error and leaves rows wrong.
| Question | Lab | What went wrong without it |
|---|---|---|
| How large should each batch be? | Backfill in batches so the application never waits on row locks | One UPDATE made an application write wait 3,664 ms |
| How do I walk the table? | Batch a backfill by key, not by OFFSET | OFFSET over unfilled rows left 500,000 of 1,000,000 unfilled |
| Where should the new value be computed? | Compute the backfilled value in SQL, not in the job | A cancelled order was written back as PAID |
| Can a copy resume from the highest ID it saw? | Why a watermark copy misses a late commit | Row 3 never reached the new table |
| Can the backfill run in one transaction? | Commit each batch so vacuum keeps up | 20,000 dead rows in another table could not be removed |
| What else fires when a row is backfilled? | Keep a backfill from firing update triggers | 100,000 order.updated events and a new updated_at on every row |
What the backfill costs the database#
| Question | Lab |
|---|---|
| Should the new column's index exist before the backfill? | Create the new column's index after the backfill |
| Why did the table double in size? | Vacuum between backfill batches to keep the table from doubling |
| Why did the replica stop showing new rows? | Backfill replica lag comes from replay conflicts too |
Keep both shapes in step while both releases write#
Rows the old release writes after the backfill starts need a path into the new shape too, and a rollback needs a path back.
| Question | Lab |
|---|---|
| Application dual writes or a trigger? | Use a trigger when the old release must keep filling the new column |
| Can the new column start as a generated column? | Use a generated column as the first step of a column change |
| How does the old column stay current for a rollback? | Sync two columns both ways while the old and new releases write |
| How do I move a counter that keeps changing? | Move a counter to a new table without losing increments |
| Can the backfill skip triggers with the replication role? | A backfill with session_replication_role = replica skips foreign keys |
| Does a new version column see the old release's writes? | Add an optimistic lock version that the old release cannot bypass |
| How do I keep a Redis copy in step with PostgreSQL? | Write to PostgreSQL and Redis without letting them drift |
Move the readers#
- Why COALESCE(new, old) shows a value the user removed shows the fallback read bringing back a cleared value, and a marker column that avoids it.
- Compare old and new reads on live traffic shows what shadow reads find and what they miss.
- Move reads to the new data source by user, not by request keeps each user on one source while the share grows.
- Introduce soft delete while the old release still reads the table shows a new column that changes what every old query means.
Values whose meaning or precision changes#
Some conversions succeed on every row and still change what a value means. Each of these labs shows one.
| Change | Lab | What went wrong without it |
|---|---|---|
| One column split into two | Test a column split for rollback and for meaning separately | Nine of nine names round-tripped; seven needed review |
| Integer cents to a decimal amount | Convert prices between cents and decimals without losing a cent | Truncation lost a cent on 65,624 of a million prices |
timestamp to timestamptz | Move from timestamp to timestamptz while old writers send local times | Two pods stored one booking an hour apart |
| Timestamps read by JavaScript | A JavaScript release that writes a timestamp back drops its microseconds | An optimistic lock check matched no row |
| IDs above 2⁵³ | 64-bit IDs that a JavaScript consumer rounds to another row | An update changed the neighboring event |
Documents, caches and messages#
The same two releases share more than tables.
- The old release deletes JSON fields it does not know shows a whole-document save erasing a new field, and the one-level limit of
||. - Migrate JSON documents on read, and finish with a backfill shows 10% of documents still on the old version after 30 days of reads.
- Change a cached object's format during a rolling deploy and keep old and new cache entries consistent when both releases write cover Redis keys during a rollout.
- Change a queue message so old and new consumers both handle it and new-format messages still in the queue after a rollback cover messages that outlive the release that sent them.
Move a table to another database#
- Set sequences before cutting over to a logically replicated database shows the first insert after a cutover failing on a sequence that was never replicated.
- A column added during logical replication stops the subscriber shows the order for schema changes while a move is in progress.
- Freeze writes before a cutover, including connections already open shows why
default_transaction_read_onlyis not a freeze.
Prove the shapes converged and stop writing the old one#
- Find every backfill mismatch with IS DISTINCT FROM counts the rows that differ, including the ones a
<>comparison misses because a side is null, and separates rows still being written from rows that stayed wrong. - Compare two tables in chunks with a checksum narrows a million-row comparison to the two ranges that differ.
- Find every query that still reads the old column finds readers in the statement statistics and makes hidden ones fail in staging.
- What a rollback loses after one value became many counts the rows the old shape cannot hold.
- Run the previous release's tests against the new schema catches a migration that ships a release too early.
- How long the old column has to stay turns rollout, rollback, job, queue and cache clocks into a date.
- Decide when to stop writing the old column lists the evidence to collect first and shows what a rollback reads once the old column stops receiving writes.
Related guides#
The changes to the schema itself have their own labs. Which PostgreSQL migration needs a rehearsal covers lock queues, table rewrites and constraints. How to test a PostgreSQL migration before rolling back the app shows the read side of a column rename. The migration review checklist gives the expand and contract sequence that this collection fills in.