Learning center

Migrate JSON documents on read, and finish with a backfill

Upgrading each document when the application next reads it spreads the work over normal traffic. It never finishes on its own. In our simulation, 10% of 100,000 profiles were still on the old version after 30 days of reads, because most documents are read rarely. Count documents by version, and run a backfill for the rest before you remove the code that reads the old version.

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;
AfterVersion 1Version 2
1 day85,15414,846
7 days47,87252,128
30 days10,42989,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.

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