Learning center

Moving data while two application releases share one database

During a rolling deploy, the release you are replacing and the release you are shipping read and write the same tables. After a rollback, the older one does so again. A schema change can be made compatible with both in one migration. Moving data from one shape to another takes several releases, because every row has to reach the new shape while the old release keeps writing the old one. This collection follows one such move from start to finish and shows, at each step, the check that tells you the data converged and the limit on rolling back.

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:

ReleaseReadsWrites
2026.11.3statusstatus
2026.11.4statestatus and state
2026.11.5statestate

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.

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.

QuestionLabWhat went wrong without it
How large should each batch be?Backfill in batches so the application never waits on row locksOne UPDATE made an application write wait 3,664 ms
How do I walk the table?Batch a backfill by key, not by OFFSETOFFSET 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 jobA cancelled order was written back as PAID
Can a copy resume from the highest ID it saw?Why a watermark copy misses a late commitRow 3 never reached the new table
Can the backfill run in one transaction?Commit each batch so vacuum keeps up20,000 dead rows in another table could not be removed
What else fires when a row is backfilled?Keep a backfill from firing update triggers100,000 order.updated events and a new updated_at on every row

What the backfill costs the database#

QuestionLab
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.

QuestionLab
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#

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.

ChangeLabWhat went wrong without it
One column split into twoTest a column split for rollback and for meaning separatelyNine of nine names round-tripped; seven needed review
Integer cents to a decimal amountConvert prices between cents and decimals without losing a centTruncation lost a cent on 65,624 of a million prices
timestamp to timestamptzMove from timestamp to timestamptz while old writers send local timesTwo pods stored one booking an hour apart
Timestamps read by JavaScriptA JavaScript release that writes a timestamp back drops its microsecondsAn optimistic lock check matched no row
IDs above 2⁵³64-bit IDs that a JavaScript consumer rounds to another rowAn update changed the neighboring event

Documents, caches and messages#

The same two releases share more than tables.

Move a table to another database#

Prove the shapes converged and stop writing the old one#

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.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading