Backfilling Historic Events After a Schema Change Safely

A schema change is easy in a table and awkward in a pile of JSON events. You can add a new property tomorrow. The ten million events from before it are still in the old shape, and anything that wants to treat them the same, a dashboard, a model or a warehouse load, needs either a reader that understands both or a rewrite of history.

This article is about the rewrite. I changed a property in a million and a half events three ways, and compared how long they took and what each left behind.

What changes

Before: {"amount": 42.5, "currency": "usd", "items": 2}. After: {"amount_cents": 4250, "currency": "usd", "items": 2}. The old key goes, the new one appears, and the value is converted. It is the usual shape of a backfill: a rename plus a conversion on a subset of rows.

The expression is the same for every method:

(props - 'amount') || jsonb_build_object('amount_cents', round((props->>'amount')::numeric * 100)::bigint)

and the condition selects only the rows that need it:

where name = 'purchase' and jsonb_typeof(props->'amount') = 'number'

The guard on the type matters. Without it the first string or object amount makes the whole statement fail (handling properties that change type shows how much of a real column that is). The guard also defines which rows are left for a human to decide about.

Method A: one UPDATE

update bf_a set props = (props - 'amount') || jsonb_build_object(...)
where name = 'purchase' and jsonb_typeof(props->'amount') = 'number';

1.37 s for 186,767 rows. It is the simplest and the one most people reach for, and it has costs that the timing hides.

WAL written        265 MB
dead tuples        186,767
table size         213 MB -> 239 MB

An update writes a new version of each row and leaves the old one for vacuum. For a row whose JSON value is large (this one is a few hundred bytes), the new version is a full copy, so 186,767 changed rows produced 265 MB of write-ahead log, more than the data they contain. On a replica or a backup stream that is traffic, and on a busy primary it is I/O that competes with real work. The table grew by 26 MB until autovacuum reclaims the space for reuse (the file does not shrink).

The other cost is the transaction. One statement means one transaction, locks held on every changed row for its whole duration, and no way to stop halfway without losing everything done so far. For a million rows that is acceptable, for a billion it is not.

Method B: batches

Walk the table in ranges of the primary key and commit each:

do $$ declare lo bigint := 0; hi bigint; mx bigint; n int := 0;
begin
  select max(id) into mx from bf_b;
  while lo < mx loop
    hi := lo + 20000;
    update bf_b set props = (props - 'amount') || jsonb_build_object(...)
      where id > lo and id <= hi and name = 'purchase' and jsonb_typeof(props->'amount') = 'number';
    commit; lo := hi; n := n + 1;
  end loop;
  raise notice 'batches: %', n;
end $$;

75 batches of 20,000 ids took 1.78 s, which is 30 percent slower than the single update, and left 186,764 dead tuples (three fewer than the single update) and the same 239 MB. What it buys is operational. Each transaction is short (about 24 ms), so locks are brief, autovacuum can run during the job, and replicas see a steady stream instead of a burst. You can stop it with a signal and resume from the last lo. The batch size is a tunable: smaller batches reduce lock time, and each batch costs a round of overhead.

Use the primary key for the ranges, not LIMIT over an unindexed filter, which scans the table again for each batch.

Method C: rewrite into a new table

If most of a table changes, or you can afford a brief swap, build the new version beside the old one:

create table bf_c2 as
select id, ts, name, user_id,
       case when name = 'purchase' and jsonb_typeof(props->'amount') = 'number'
            then (props - 'amount') || jsonb_build_object('amount_cents', round((props->>'amount')::numeric*100)::bigint)
            else props end as props
from bf_c;
alter table bf_c2 add primary key (id);
begin; alter table bf_c rename to bf_c_old; alter table bf_c2 rename to bf_c; commit;

The copy took 1.21 s and the primary key another 0.44 s. The result is a compact table with no dead rows: 215 MB. The rename inside a transaction is quick, and the old table stays around as a rollback until you drop it.

What it does not do is handle writes during the copy. New events inserted into the old table after the create table as are missing from the new one. Either stop writes briefly, or capture the id high-water mark, copy, then copy the rows above it before the swap, or write through a trigger. Indexes, constraints and grants also have to be recreated by hand, and anything that refers to the table by oid (views, foreign keys) must be handled. Method C is the cleanest result and the most work to do safely on a live system.

They agree

sum of amount_cents:   A 909,045,150   B 909,045,150   C 909,045,150

All three methods produced the same total: 909,045,150 cents, or 9,090,451.50 in currency units. Compare an aggregate before and after, on a snapshot, before you drop anything. That check catches the mistakes that matter most: a condition that selected the wrong rows, a rounding choice, a conversion applied twice.

The leftovers

After the backfill, 596 events in the first table still had an amount key. These were the purchases the guard excluded: strings (including "n/a"), objects and nulls. The guard did its job. A backfill that converts everything it can and leaves a count of what it did not convert is more honest than one that coerces everything. Query the leftovers by type and decide what to do with each group, and write the decision into the next pass: "4,5" becomes 450 cents, "n/a" becomes a missing key, a wrapped object is unwrapped. Rerun the same script and the remaining count drops to zero or to a known residue.

This only works if the backfill is idempotent: its WHERE selects rows that still have the old shape. After it runs, no row matches, and running it again changes nothing. A condition that selects rows by anything else (a date range) can convert the same row twice, which turns 42.5 into 4250 and then into 425,000.

Order of operations in a live system

The sequence that avoids a gap:

1. Write both. Deploy producers that write the new property (and, if readers still need it, the old one).

2. Read both. Deploy readers that accept either shape, with the new one preferred.

3. Backfill. Rewrite old rows in batches. The writers and readers already cope, so the job can run for as long as it takes.

4. Verify. Zero rows with the old shape, aggregates unchanged.

5. Stop writing the old shape, then remove the reader fallback. Drop the compatibility code at the end, not the beginning.

Skipping steps 1 and 2 means readers fail on whichever shape they do not expect for as long as the backfill runs. Versioning JSON payloads covers the version marker that makes step 2 simple, and extracting properties into typed columns every night is the cheaper alternative when the real need is a typed column and not a rewrite of the source.

Reference: the PostgreSQL manual on routine vacuuming explains the dead tuples, and the WAL configuration chapter the log volume. To check a single event before and after, use the JSON diff tool.

When not to rewrite history

The old shape is already readable. If a view or a function normalizes both shapes (as amount_of does), the rewrite buys nothing but risk. Make the reader tolerant instead.

The events are an audit record. Rewriting a log changes the evidence. Keep the originals, and put the normalized form in a derived table.

The table is huge and cold. Rewriting a petabyte of old events for a convenience is a project. Normalize on read, or convert partitions as they are touched.

You cannot verify the result. A backfill without an aggregate to compare against is a guess. If there is nothing to check against, build that first.