Munchable's database lives on a hosted Free plan with a 0.5 GB quota. That is a constraint I chose, and it has been one of the more educational parts of the project, because a quota turns storage into a thing you have to design rather than a thing you have.

It has been exceeded twice. The two incidents taught completely different lessons, and the second one is the one I would want somebody else to read before they ship an audit table.

The first time was boring

A large table was large. The fix was to decide how much of it the product actually needed, cut it to that, and move on. Dull, and the kind of problem that announces itself: you look at the biggest table, it is the biggest table, done.

The important thing it left behind was a habit of checking sizes at all, which is what made the second incident survivable.

The second time, the big table was innocent

Sixteen days later, the quota alert fired again. The dashboard said 720 MB. pg_database_size said 674 MB. (They disagree, they always disagree, and when both numbers are over your quota the difference is not the interesting part.)

I opened the products table expecting to see it bloated again. It was 192 MB, which is inside its budget and nowhere near the problem.

The problem was an append-only audit trail: 418 MB, 302 MB of it tuples and the rest indexes. It held 1,302,848 rows. 1,250,964 of those recorded that nothing had happened.

How you write 1.25 million rows about nothing

The table exists for a good reason. A background job works through a backlog of items and makes a decision about each one, and every decision it applies gets an immutable row: what it decided, what it was about, what evidence it had. That is what makes the result explainable later, and reversible. I would build that again.

The bug was in the word "decision". The job has an outcome that is not a decision: I cannot resolve this one yet. That outcome was being written to the ledger too, and three facts turned it into a disaster:

  1. The job was scheduled every 15 minutes.
  2. It had no memory of which items it had already failed to resolve, so every run re-examined the same backlog of roughly seven thousand unresolvable items.
  3. Each run wrote a fresh row for every one of them.

Four days of that. 1.25 million rows describing seven thousand items that never got an answer, at an average of a couple of hundred rows per item, every one of them saying the same thing as the last.

The 51,884 rows that record real decisions, the entire reason the table exists, were 4 per cent of it.

The fix was deleting a line, not adding a feature

What makes this a nice bug rather than a depressing one is that the data was never needed. The non-decision was already recorded in the right place: the backlog row itself remembers the state it was last parked at, which is exactly what the job reads on the next pass. The ledger rows were a second copy of a fact that already had an owner.

So the job stopped writing them, and the rule got written into the schema next to the table:

 * catalog.taxonomy_ledger: an append-only audit trail of every decision that * changed something. Skips are NOT ledgered: a skip writes no row elsewhere and * closes nothing, and the backlog row already remembers it. (They used to be, * and one row per item per run grew this table to 418 MB in four days.) 

A schema comment that names the incident, the size and the duration is the cheapest documentation in this codebase. Nobody reads a postmortem. Everybody reads the file they are about to edit.

The general version, which I now apply to every table that is written by a scheduled job:

A table written per run must be bounded by design. Record only outcomes that changed something. Never record that nothing happened.

The reason is that a row per run per item is quadratic growth wearing a disguise. It scales with items times runs, items grow, and runs grow with time, which is the one input you cannot cap. "It is only a small row" is true and irrelevant.

Measuring, before touching anything

The query that actually settled it is four lines, and I keep it in the prune script so the answer is always a run away rather than a recollection:

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; 

Two details worth copying. pg_total_relation_size rather than pg_relation_size, because indexes are a third of the problem here and the first function does not count them. And the count and the filtered count in one pass, so the output is not "this table is big" but "this table is big and 96 per cent of it is one value in one column", which is a different sentence entirely: the first one invites you to buy more disk, the second one tells you what to delete.

DELETE does not give the disk back

This is the part that catches people who have only ever run Postgres with room to spare. A DELETE marks the space reusable by that same table. It does not return it to the filesystem, and therefore not to a quota. VACUUM alone does not either.

VACUUM FULL does: it rewrites the table containing only its live rows and drops the old copy. The arithmetic of that is the opposite of what you would fear:

 * VACUUM FULL rewrites the table with its live rows only, so it needs free * disk for the COMPACTED size (about 20 MB here), not the current 418 MB, * which is why it fits on the Free plan's nearly full disk. 

It needs room for the survivors, not for the patient. So the worse the bloat, the better VACUUM FULL works, and the case where it fails is the uncomfortable mirror image: a huge table whose rows are mostly real, on a disk with no space, where it dies with "No space left on device" and leaves you exactly where you started. That case wants TRUNCATE, which frees space instantly because it unlinks the files, and which you can only use if you are prepared to lose everything in the table.

The other thing VACUUM FULL needs is a lock. It holds an exclusive lock on the table for as long as the rewrite takes, which was a few seconds here. That was acceptable for exactly one reason, written down in the script:

 * It takes an exclusive lock on catalog.taxonomy_ledger for a few seconds; * only the operator-run job writes to that table. 

Nothing in the request path reads or writes it. If you cannot say that sentence about a table with certainty, you do not know whether you can VACUUM FULL it, and the answer is probably not during the day.

The script, and why the dry run is the default

Both of the clean-up scripts in this repo have the same shape:

 * Usage: * pnpm exec tsx scripts/db-prune-ledger.mts # dry run: counts + sizes only * pnpm exec tsx scripts/db-prune-ledger.mts --apply # delete + VACUUM FULL */ const APPLY = process.argv.slice(2).includes('--apply'); 

Dry run by default, destruction behind a flag, sizes printed before and after, and the connection string read from a local env file so the script cannot be run accidentally by anything that is not a person on a laptop.

This is not a moral position, it is a practical one. The dry run is the thing you run five times while you are still deciding whether your WHERE clause means what you think it means, and if the safe mode is not the default you will eventually run the other one by muscle memory.

I also keep these as scripts rather than migrations. A migration runs on deploy, in whatever state the database happens to be in, with no one watching. A clean-up is a decision with a timestamp and a person attached.

The part the product never noticed

Here is the detail that makes the whole story a bit funny. This quota drama is invisible to anyone using the product, and completely invisible to the public site, because none of those pages touch the database at all. The condition guides, the 461 answer pages, the recipe library and the hidden gluten reference are all prerendered from data committed in the repo. 516 URLs that cannot be affected by a vacuum.

The database serves the phone app, and the app's own experience of all this was that it carried on working while 418 MB of rows describing nothing in particular were deleted underneath it.

Three things to take

  1. When a quota alert fires, measure every table. The one you expect is not the one.
  2. Never write an audit row for an outcome that changed nothing. Audit the changes, let the state own the non-changes.
  3. Know, in writing, which of your tables the request path touches. That is the list that decides what you are allowed to lock.