The migration on read#
Profiles are jsonb documents with a version field. Version 2 replaces a single phone with a phones list. The new release upgrades a version 1 document whenever it loads one, and writes it back:
def load_profile(profile_id):
doc = db.execute("SELECT doc FROM profiles_doc WHERE id = %s", (profile_id,)).fetchone()[0]
if doc["v"] == 1:
doc = {"v": 2, "name": doc["name"], "phones": [doc["phone"]]}
db.execute("UPDATE profiles_doc SET doc = %s WHERE id = %s AND (doc->>'v')::int = 1",
(Jsonb(doc), profile_id))
return doc
The AND (doc->>'v')::int = 1 keeps two pods that read the same profile from both upgrading it, and keeps a pod from overwriting a version 2 document with its own upgrade.
How far traffic gets#
We loaded 100,000 version 1 profiles into PostgreSQL 16.15 and simulated 50,000 reads a day, with a few profiles read far more than the rest, as real traffic is. After each checkpoint we applied the upgrades and counted:
SELECT doc->>'v' AS v, count(*) FROM profiles_doc GROUP BY 1 ORDER BY 1;
| After | Version 1 | Version 2 |
|---|---|---|
| 1 day | 85,154 | 14,846 |
| 7 days | 47,872 | 52,128 |
| 30 days | 10,429 | 89,571 |
Progress slows as it goes, because each new day's reads mostly land on profiles that were already upgraded. The last 10% are profiles nobody opened in a month: dormant accounts, but also the ones a support agent or a data export will open next year.
What stays until the end#
While any version 1 document exists, the new release needs its upgrade code. And while any release that reads only version 1 can run, upgraded documents break it:
doc["phone"] # 2026.11.3 on a version 2 document: KeyError: 'phone'
So migration on read needs the same order as a column change. The release that understands version 2 ships first and becomes the rollback target, and the upgrade-on-read starts after that. The old release deletes JSON fields it does not know covers the opposite direction, an old release saving a new document.
Finish with a backfill#
Run the same upgrade for the remaining documents in batches, keyed by id, with the same version guard:
UPDATE profiles_doc
SET doc = jsonb_build_object('v', 2, 'name', doc->'name', 'phones', jsonb_build_array(doc->'phone'))
WHERE id > $1 AND id <= $1 + 1000 AND (doc->>'v')::int = 1;
Batch a backfill by key, not by OFFSET explains the batching. Pass when the count by version shows no version 1 document. Then the upgrade code, and the rollback target that reads version 1, can go.