Munchable has a curation pipeline. Label words arrive from product packs, the ones nothing in the taxonomy can place go into a backlog, and a pass works through that backlog deciding what each word is: an alias of something already known, a genuinely new entry, or noise. Every decision that changed something is written to an append-only ledger, so any curated row can be explained later and reverted.
Then there is the fourth outcome. Sometimes the pass looks at a word and cannot place it at all. That is a skip: it writes nothing to the taxonomy, closes no backlog row and changes no behaviour. For four days in September, every one of those was written to the ledger too.
The result: 1,250,964 rows, 302 MB of tuples, 418 MB with indexes, on a database with a 0.5 GB quota. Set against 51,884 rows that record an actual decision. Twenty-four audit rows for every one that audits anything.
The curated data this pipeline produces is public, if you want to see what the ledger is an audit trail of. Every page under munchable.app/answers is generated by running the real checker over one ingredient, and the E number pages such as does E260 cause reflux only exist because a curation pass decided what E260 is. The ledger is the paperwork behind those pages.
How 7,000 words became 1.25 million rows
The deterministic pass ran every 15 minutes. At that point it had no marker recording which version of itself had last examined a word, so each run picked up the same roughly 7,000 unreadable slugs, failed to place them again, and wrote 7,000 fresh skip rows. One row per slug per run, 96 runs a day, four days. About 180 rows per word, every one of them saying the same thing: still cannot read this.
Nothing was broken. Each individual row was correct. The pass was correctly recording that it had considered a word, and the table was correctly storing what it was told to store. The bug was a category error: a skip is an attempt, not a transition, and an audit log of attempts grows with your schedule rather than with your data.
The one line fix, and the 1.25 million row cleanup
Stopping the growth was an if:
// A skip is not a decision about the word: it writes no overlay row and
// closes nothing, so there is nothing for the ledger to explain or revert.
if (d.action !== 'skip') ledger.push(ledgerRowFor(d, bySlug.get(d.slug), opts.model));
The backlog row already remembers the skip. It carries the count the word was last skipped at, which is the only thing the next run actually reads. The ledger was storing a second, worse copy of a fact that already had a home.
Deleting the existing rows needed an argument that they are not evidence of anything. Three parts: a skip row records no state change, so nothing can be reverted from it; the backlog already holds the count; and nothing in the request path reads the ledger at all. The product lookup the phone calls never touches that table, so the rows are not even a cache.
A DELETE does not give you your disk back
This is the part worth internalising if you run anything on a small plan. DELETE marks the row versions dead. Plain VACUUM makes the space reusable by future inserts into the same table. Neither returns a byte to the filesystem, so neither moves the number your quota is measured against.
VACUUM FULL does, by rewriting the table with only its live rows. The usual warning about it is that it needs room for a second copy of the table, which sounds like exactly the thing you cannot afford on a disk that is already nearly full.
The arithmetic is kinder than that, and it is kinder in precisely the case you need it to be. VACUUM FULL needs free space for the compacted size, not the current one. Here that was the 51,884 rows worth keeping, about 20 MB, rather than the 418 MB the table occupied. Deleting first and then rewriting is what made the operation fit. It took an exclusive lock on the table for a few seconds, which is acceptable for a table only an operator run job writes to.
Deleting 1.25 million rows on a nearly full disk
The delete is batched rather than one statement:
const BATCH = 100_000;
for (;;) {
const r = await sql.unsafe(
`delete from catalog.taxonomy_ledger where id in (
select id from catalog.taxonomy_ledger where decision = 'skip' limit $1)`,
[BATCH],
);
deleted += r.count;
if (r.count < BATCH) break;
}
Each batch is its own commit, so no single transaction stays open for minutes and no single statement piles up a large chunk of WAL on a disk that is short of room. One delete from ... where decision = 'skip' would have been one line shorter and would have held every one of those row versions in one transaction.
The script also runs as a dry run by default. Without --apply it prints the counts and the sizes and exits, because the first thing you want from a script that deletes a million rows is the confidence that it has found the right million. And if VACUUM FULL fails, it prints the statement to run in the Supabase SQL editor instead rather than leaving you to work out what half happened: a pooled connection with a statement timeout is a bad place to run a table rewrite.
The rule I took from it
An audit log should record state transitions, not attempts. If a row explains nothing and reverts nothing, it is telemetry rather than audit, and telemetry belongs somewhere you can expire it: a log with a retention window, a counter, a column on the row it is about.
The giveaway is the schedule. If a table's size depends on how often a job runs rather than on how much data exists, it is recording the job, not the data. This one grew by 7,000 rows every fifteen minutes while the thing it was describing, the backlog, did not change at all.
Related
I wrote earlier about the two quota alerts that led here, and about deleting 45% of our stored ingredient tags, which was the other half of the same week. The app itself, which never reads any of this, is at munchable.app.
Top comments (0)