1,250,964 rows that recorded nothing, and why deleting them gave back no disk at all

Munchable's Postgres is on the Supabase Free plan: 0.5 GB, which is a real constraint rather than a rounding error. In September one table in it was 418 MB on its own, and 1,250,964 of its 1,302,848 rows recorded something that had not happened.

This is the shape of that mistake, the one-off script that undid it, and the Postgres behaviour that makes "delete the rows" the least important part of the job.

An audit row for a non-event

The table is a ledger. A background job works through a backlog of label words the app does not recognise yet, proposes what should happen to each one, and the ledger is the record of what it decided and why, so that a decision can be explained or reversed later.

It also recorded the case where the job looked at a word and passed: the row that says, in effect, "I have nothing to say about this yet."

That row is worthless in a way that is worth being precise about. It writes nothing elsewhere, it closes nothing, there is nothing to reverse, and nothing in the request path ever reads it. The one fact it carries, that the word was last passed over at some backlog count, was already stored on the backlog row itself, which is the row the job actually reads next time.

So it was an audit trail of inaction, and it would have been a harmless few thousand rows except for the second half of the story. The job ran every fifteen minutes for four days. Before it had a marker to remember which words it had already examined, every run re-examined the same roughly 7,000 unreadable words and wrote a fresh pass row for each one. That is about 180 rows per word, 1.25 million rows in total, for words that never got a decision at all.

The 51,884 rows that record a real decision are the entire value of the table, and they were 4 percent of it.

Fix the writer before you touch the data

The first commit changed one line in the job:

// 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.
// The backlog row already remembers it (`proposed`, with the count it was
// skipped at). Ledgering skips is what filled catalog.taxonomy_ledger with
// 1.25M "unreadable" rows in four days (180 per slug, one per run of the
// deterministic pass) and pushed the database past the Free plan quota.
if (d.action !== 'skip') ledger.push(ledgerRowFor(d, bySlug.get(d.slug), opts.model));

The comment is longer than the code, and deliberately so. A line that stops writing a row looks like dead weight to the next person reading it, and the numbers are the only thing that explains why the condition is there.

If you only remember one thing from this post: an audit log is for things that changed. A log entry for "considered, no change" multiplies by however often your scheduler fires, which is a number nobody thinks about when they add the write.

The script, and the two things it will not do by default

Cleaning up the old rows is a one-off, run by hand from a laptop against production. Two rules for scripts like that: the default mode must be harmless, and they must print the numbers that justify them.

pnpm exec tsx scripts/db-prune-ledger.mts            # dry run: counts + sizes only
pnpm exec tsx scripts/db-prune-ledger.mts --apply    # delete skips + VACUUM FULL

The size read is the whole dry run:

select pg_size_pretty(pg_database_size(current_database())) as database,
       pg_size_pretty(pg_total_relation_size('catalog.taxonomy_ledger')) as ledger,
       count(*)::int as rows,
       (count(*) filter (where decision = 'skip'))::int as "skipRows"
  from catalog.taxonomy_ledger

pg_total_relation_size rather than pg_relation_size, because the indexes are the difference between the 302 MB of tuples and the 418 MB the quota was actually counting. A table you are about to prune is usually carrying an index per foreign key and a timestamp index nobody remembers adding, and that is the part that pushes you over.

The script prints before, prints what it is about to delete, and exits unless you asked for --apply. It has no interactive confirmation prompt, because a prompt trains you to press y.

The delete is batched, and the batches are the point

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;
}

One statement deleting 1.25 million rows would hold a single transaction open for the duration and pile up the whole thing as WAL on a disk that is nearly full, which is exactly the resource that is already in trouble. Thirteen statements, each its own commit, keep the peak small and let the job be interrupted and resumed without losing what it has done.

delete ... where id in (select id ... limit n) rather than a plain delete ... limit n, because Postgres has no LIMIT on DELETE. The subselect is the standard workaround and it reads better than a CTE here.

Then the delete gives back nothing

This is the part worth internalising if you only ever use Postgres from an ORM.

A DELETE does not shrink a table. It marks row versions dead. A plain VACUUM makes that space available for the same table to reuse, which is wonderful for a table that will refill and useless for one you have just permanently emptied. The file on disk stays 418 MB, and the quota that is about to stop your app from writing is measured on the file.

VACUUM FULL is what actually returns it. It rewrites the table with only its live rows, swaps the new copy in, and drops the old one.

Which raises the obvious objection: rewriting a table needs room for a second copy, and we are doing this precisely because there is no room. The reason it works is that the copy is the size of the result, not the size of the input. 51,884 rows is about 20 MB. So the peak requirement was 418 MB of existing file plus 20 MB of new one, and that fit where "free up 400 MB first" had nothing to work with.

It takes an ACCESS EXCLUSIVE lock for the few seconds that takes. That was acceptable here only because of a fact about this specific table: nothing in the request path reads it, and the only writer is an operator-run job that is not running while I am running this. For a table your API touches, those few seconds are an outage, and the answer is pg_repack or a rewrite into a new table.

The script also expects to lose:

try {
  await sql.unsafe(`vacuum full catalog.taxonomy_ledger`);
} catch (e) {
  console.log('VACUUM FULL failed:', (e as Error).message);
  console.log('Run it from the Supabase SQL editor instead:  vacuum full catalog.taxonomy_ledger;');
}

A pooled connection with a statement timeout is a bad place to run a table rewrite, and the useful failure message is not the stack trace, it is the exact sentence to paste somewhere else. A one-off script that fails should tell you how to finish the job by hand.

What it cost and what it bought

418 MB to about 20 MB, on a 0.5 GB budget, from deleting rows that described nothing. No feature changed, no query got faster, nothing in the app noticed. The only outcome is that the database has room for the data the app is actually for, which on a free plan is the whole game.

Two generalisations I would stand behind:

1. Before you buy a bigger plan, find out which table is big and whether its biggest contributor is a non-event. Ours was 96 percent padding.
2. Every bytes-on-disk problem has a writer behind it. Fix that first, then clean up, or you will be writing this script again in four days.

Look at it yourself

• munchable.app/about is the page that commits us to maintaining the ingredient database ourselves rather than deferring to somebody else's, which is why there is a curation job with a ledger at all. The housekeeping in this post is the unglamorous half of that promise.
• munchable.app/privacy is the counterpart: the short version at the top says the conditions you pick, your sensitivities and your scan history stay on your device. Nothing in the 1.25 million rows above was about a person, which is the only reason a founder could prune the table from a laptop at all.
• munchable.app/answers is what the curated vocabulary is eventually for: 373 pages, one per ingredient and condition pair the app has something to say about. That is the data worth keeping inside a 0.5 GB budget.