Two quota alerts on the same 0.5 GB database, and the second one was not the table I expected

Munchable's database holds a curated catalogue of packaged foods and the ingredient knowledge that turns a label into a verdict for a gut condition. You can see the output at munchable.app/conditions and a public slice of the ingredient data at munchable.app/answers.

It lives on a Postgres instance with a 0.5 GB quota. We have gone over that quota twice, and the interesting part is that the two incidents had nothing in common except the number on the dashboard.

This post is the capacity budget that came out of it, written as a set of rules rather than a war story, because the rules are the reusable part.

Alert one: the obvious table

First time over, the database was 866 MB and the products table was essentially all of it. That is the boring case: we had loaded more product rows than the plan allows. The fix was to decide which rows were worth keeping, which turns out to be a product question rather than a database one.

The ordering we landed on is written into the prune script:

 * Deletes HALF of the IMPORTED rows, least valuable first. User captures are
 * never touched. Order:
 *   1. products never seen scanned (unique_scans_n = 0),
 *      non-UK/IE before UK/IE, so the launch market keeps everything it can;
 *   2. then the least-scanned of the rest, if half is more than that.

Two rules in there that I would keep in any similar situation.

Never delete what a user contributed. The catalogue's imported rows were a one-time seed and are re-obtainable in principle. A product somebody photographed the label of to fill a gap is not. Those rows are the ones the whole product is built on, and they are excluded from every prune by construction, not by a flag somebody remembers to pass.

Sort by demand, not by age. A row nobody has ever scanned costs the same disk as one scanned ten thousand times and is worth considerably less. The number of distinct users who have scanned a product is the sharpest signal we have, and the prune runs down that list from the bottom. When it was measured, roughly two thirds of the imported rows had never been scanned by anyone, which meant deleting half of them never touched a single product a user had actually looked at.

The script is a dry run by default and prints counts and sizes. --apply is a separate, deliberate act, and the header says in capitals that it is irreversible, because it now is: the local artifact those rows came from is gone.

Alert two: 45% of a column was derivable

Second time over quota, the products table was 192 MB and completely innocent. The dashboard number was similar; the cause was not.

Two separate things had grown.

The first was inside the products table's own data rather than its row count. Each product carries a list of ingredient tags, and the seed had pre-expanded the taxonomy's ancestors into that list: en:vegetable stored right after en:carrot, en:dairy after en:cheese. The rules engine expands ancestors itself at read time. Every one of those stored ancestors was therefore derivable, and it was 45% of the stored tags: 38 MB of the 148 MB that rows with ingredients occupied.

Worse than the disk, and the reason it got fixed rather than tolerated:

 * and every one of them rides in every lookup payload the phone downloads and
 * caches.

A redundant column is a storage cost on the server and a bandwidth cost on every device, every scan, forever.

Deleting data that a verdict depends on is a nervous business, so the rewrite is verified row by row rather than argued for in the abstract. Every candidate row is run through the engine twice, once with the old tag list and once with the new, with every condition, allergen and preference switched on, and a row whose output differs in any byte is left exactly as it was and reported. Proving equivalence per row beats proving it by reasoning, especially when the reasoning has to be right 250,000 times.

The second growth, which was a design bug

The other half of alert two was a ledger table at 418 MB: 1.25 million rows, none of which recorded anything happening.

The curation pass that assigns meaning to unrecognised ingredient words ledgered every decision, including its non-decision: a skip, meaning "I cannot place this word yet". It ran on a schedule every fifteen minutes, and before a marker existed to remember what it had already examined, each run wrote a fresh skip row for the same several thousand unplaceable words. Four days of that produced 1.25 million rows and 418 MB. The rows that recorded a real decision numbered about 52,000, and they are all still there.

The rule that came out of this one is the most transferable thing in this post:

Any table written per job run must be bounded by design. Write only decisions that changed something, never "nothing happened" rows.

The fix had two halves: stop ledgering skips at all, and a delete-only script for the rows already written. The information was not even lost, because the thing the job actually reads is a counter on the backlog row, not the ledger.

And a diagnostic habit, which is the reason the second alert took longer to understand than the first: check pg_total_relation_size per table before assuming you know which table. I spent the first half hour of alert two looking at the products table, because that was the culprit last time. It was fine. The dashboard tells you the total, and the total is an unhelpful number when four tables can produce it.

VACUUM FULL, and the trap inside it

All three scripts end the same way, and this is the part that surprises people who have not had a full disk before.

A DELETE does not give space back. Neither does an UPDATE: it leaves the old row version behind. A plain VACUUM marks that space reusable by the same table, which is exactly what you want in steady state and completely useless when you are trying to get under a quota. Only VACUUM FULL rewrites the table with its live rows and returns the difference.

The trap is that VACUUM FULL needs free disk to build that copy, and you are attempting it because you have no free disk. Which way it goes depends on a detail worth internalising:

 * 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.

Free space is needed for the survivors, not for the current size. So:

• Deleting most of a big table: VACUUM FULL works, because the copy is small. That is the ledger case, 418 MB down to about 20 MB.
• Deleting a little of a big table: it fails with "No space left on device", because the copy is nearly as large as the original.

When the survivors are large and the disk is full, TRUNCATE is the tool that frees space instantly, which is why "truncate and re-load with a filter" is the practical way to shrink a large table rather than a series of careful deletes. That is what alert one actually became.

Lock duration follows the same shape: rewriting a table with 20 MB of survivors takes an exclusive lock for a few seconds, and rewriting the products table takes about a minute. Worth knowing which one you are about to do.

The budget, as it now stands

• Total database: comfortably under 500 MB, never "just under".
• Products table: 300 to 400 MB, and it is the only table allowed to be large.
• Everything else: bounded by design, with a size check rather than a hope.
• Bulk loads run with a size filter. An unfiltered load of the original extraction would be several gigabytes and would put us straight back at alert one.
• Every destructive script is dry run by default, prints sizes before and after, and refuses to be interesting.

That last point is the one I would argue for hardest. All three of these scripts do nothing unless passed --apply, and all three print what they would delete in enough detail to be checked. A tool that silently does the right thing is indistinguishable from a tool that silently does the wrong thing, right up until it matters.

Nothing here required upgrading the plan, and nothing here cost the product a feature: the search, the scan path and the recipe pages at munchable.app/recipes all run on the same database, with the same latency, after all three cleanups.