Schema Evolution in a JSON Column Without Breaking Old Rows
A client's product catalog stored variant attributes — size, color, material, and dozens of category-specific fields — in a single JSONB column, which was the right call originally: a rigid relational schema for attributes that differ wildly by product category (a shoe has sizes, a lamp has wattage) would have meant an unworkable number of nullable columns or a painful EAV model. JSONB let the schema stay flexible while still living in Postgres alongside the rest of the relational data.
The problem surfaced two years later. The 'dimensions' field had been stored as a string ('10x5x3in') in 2022, changed to a nested object ({length, width, height, unit}) in 2023 after a redesign, and a third variant appeared briefly during a botched migration that only updated new rows. Application code reading this field had accumulated defensive branching to handle all three shapes, and nobody was fully sure which rows had which shape without querying to check.
We didn't do a big-bang migration, since with roughly 60,000 products live and actively being edited by customers, a long-running lock or an all-or-nothing script felt too risky. Instead we wrote a normalizing read path first — a function that accepts any of the three known shapes and returns the current canonical form — and deployed that everywhere the field was read, so behavior was correct immediately regardless of the underlying row's shape.
Then we ran a backfill migration in batches of 1,000 rows with a short sleep between batches to avoid saturating the primary, rewriting every row to the canonical shape, verified by comparing the normalizing function's output before and after. This took about six hours to run across the full table but never blocked live traffic. Once the backfill finished and we'd confirmed zero rows in the old shapes, we removed the defensive branching from the read path.
The change we made going forward: every JSONB field now has a documented schema version, either as an explicit 'schema_version' key inside the JSON itself or tracked in a lightweight internal doc, and any change to a field's shape requires a backfill plan before the new shape ships, not after. JSONB's flexibility is genuinely valuable for attributes like these, but it removes the schema enforcement that would have caught this drift automatically in a normal typed column.
