Rewriting a million rows when the only acceptable diff is zero bytes

Munchable's catalogue is a bit over a million product rows in a Postgres on a 0.5 GB quota. Space is the constraint that shapes almost everything about it, which is how I ended up measuring what was actually in the ingredients_tags array on each row:

45% of stored tags were ancestors the engine re-derives anyway
38 MB of the 148 MB that rows with ingredients occupy

The tags are taxonomy ids. A row for a carrot soup stores en:carrot, and it also stores en:vegetable, because the import pre-expanded the parents. The engine walks the taxonomy graph itself on every scan, so it derives en:vegetable from en:carrot regardless. Every one of those redundant tags costs disk, and rides along in every lookup payload a phone downloads and caches.

I wrote up the measurement itself in 45% of our stored ingredient tags were derivable, so we deleted them. This post is the other half: how you actually run that rewrite across a million rows when a single wrong verdict is a person eating something they were avoiding.

Do not prove it, verify it

The tempting approach is a careful argument: the engine expands ancestors, therefore dropping ancestors is safe, therefore rewrite everything. The argument is even correct, roughly, and "roughly" is the problem. Every clause in it is a claim about code that changes weekly.

So the script does not rely on the argument. For every row it considers, it runs everything the engine can say, twice, and compares:

const PROFILE: Profile = {
  conditions: ALL_CONDITIONS,          // all seven
  allergens: [...ALLERGEN_IDS],        // all fourteen
  healthy: [...HEALTH_PREFERENCE_IDS], // every preference switched on
};

function everything(row: CatalogProduct): string {
  const product = shapeProductRow(row);
  const normalized = normalizeProduct(product);
  return JSON.stringify({
    fit: fitCheck(product, PROFILE),
    allergens: checkAllergens(normalized, PROFILE.allergens ?? []),
    healthy: checkHealthy(normalized, PROFILE.healthy ?? []),
  });
}

Then, per row:

if (everything(row) !== everything({ ...row, ingredientsTags: next })) {
  differs++;
  if (differing.length < 20) differing.push(row.barcode);
  continue;                 // left exactly as it was, and reported
}

The maximal profile is the point. A verdict that only changes for somebody with reflux and a sesame allergy and the sweeteners filter on is still a changed verdict, and switching everything on at once is the cheapest way to catch it. The output is JSON-stringified and compared as a string, so a reordered array or a changed reason line counts as a difference, not just a flipped verdict.

Rows that differ are not fixed, not forced, not investigated later. They keep their old tags and their barcodes get printed. Out of a million rows you would rather leave a few hundred fat than think hard about them under time pressure.

Three carve-outs, and each one is a bug that did not happen

A tag goes only if it is an ancestor of an earlier tag in the same row. Not "an ancestor of anything in the row". The normalizer walks the list in label order, so by the time it reaches en:vegetable after en:carrot it has already added it as derived and skips it harmlessly. But a row where the ancestor comes first is a different animal: the engine treats it as printed on the pack, gives it its own label rank, and may report it as unscored. Dropping it would change the output. Order is semantics here, not presentation.

function compact(tags: readonly string[]): string[] {
  const implied = new Set<string>();
  const out: string[] = [];
  for (const t of tags) {
    if (PROTECTED.has(t) || !implied.has(t)) out.push(t);
    for (const a of ancestors(t)) implied.add(a);
  }
  return out;
}

Any tag an allergen notice can key on stays, whatever the graph says. That is the PROTECTED set above, built from every allergen source tag. Allergen checks read the printed tags, and a generic word like en:nut counts only when the pack actually printed it. Derivability is irrelevant: if removing a tag could change an allergen warning, it does not get removed. Allergen output is byte-identical by construction, before the verification even runs, because allergens are the one axis where a false green is not a bug report, it is an ambulance.

The walk uses the seed and hand-curated graph only, never the AI-curated overlay. This is the one I would have got wrong. The overlay grows nightly and reaches phones separately from app releases, so a device may be holding an older or a newer copy than the laptop running the script. Anything derived from the overlay is therefore not guaranteed to be re-derivable on the device that has to re-derive it. Seed and hand entries win every merge in the engine, so what this walk derives, every engine build derives, including one on a phone that has not synced this week.

That is the general shape of the rule: a migration may only rely on the parts of your data model that every consumer is guaranteed to agree with you about.

The mechanics, which are boring on purpose

Keyset pagination, not OFFSET. Pages of 2,000 ordered by the primary key, each page asking for barcode > $last. A million-row scan with OFFSET gets quadratically slower and, worse, can skip or repeat rows if anything is written while it runs.

One UPDATE per page, with an optimistic guard. The batch goes in as a single jsonb parameter and is unpacked server-side:

update catalog.products as p
   set ingredients_tags = v.tags
  from jsonb_to_recordset($1::jsonb) as v(barcode text, old text[], tags text[])
 where p.barcode = v.barcode and p.source = 'seed' and p.ingredients_tags = v.old

p.ingredients_tags = v.old is the whole concurrency story. A row that changed between being read and being written, because a contributor's label capture landed on it, fails the predicate and is skipped rather than overwritten with a value computed from stale input. No locks, no transaction spanning the scan, no repeatable-read snapshot held open for an hour.

One driver gotcha worth the sentence: pass the array itself, not JSON.stringify(batch). The driver serialises a jsonb parameter for you, so a pre-stringified value arrives as a JSON string containing an array rather than an array.

updated_at is deliberately not bumped. This changes how a row is stored, not what the product is. Every consumer that reads a modification time to mean "this product changed" would be told a lie by a million rows at once, and cache invalidation at that scale is not free.

Dry run is the default. Without --apply the script does the entire walk, the entire verification, prints the counts and writes nothing. That is not a courtesy flag, it is how you find out that 300 rows differ before you have written any of the other 999,700.

The part that actually returns the disk

An UPDATE in Postgres writes a new row version and leaves the old one as dead weight. Run the whole rewrite and the table is bigger than when you started. A plain VACUUM makes that space reusable by the table but does not hand it back to the filesystem, which is exactly what a quota measures.

So the last step is VACUUM FULL catalog.products, which rewrites the table and returns the space, while holding an exclusive lock for about a minute. On a shopping app, a minute where no product can be looked up is a real outage, so it is an explicit, operator-run step at a chosen moment, with a fallback printed for when a connection pooler kills the statement:

VACUUM FULL failed: <message>
Run it from the Supabase SQL editor instead:  vacuum full catalog.products;

Three lines of summary at the end, and the one that matters is not the megabytes:

console.log({ examined, unchanged, verified, differs, tagsBefore, tagsAfter, ... });

differs is the number I actually read. Zero would be suspicious. A handful is the script telling me it did the check.

The result is a smaller payload on every lookup, which you can feel rather than read about: app.munchable.app is the app in a browser, and a scan there fetches the same product shape the phone caches. The condition guides are the other end of the same data, if you would rather see what the tags are for than how they are stored.