A Disk Alert, a 392 GB Table, and Indexes Bigger Than the Data

Our Postgres disk crossed its usage threshold again last week. The boring fix is to bump the disk size and move on. I did that part. Then I spent twenty minutes looking at where the bytes actually went, and found more than 150 GB of storage I could stop paying for: indexes bigger than the data they sat on, output that nothing reads back, and the same values copied across hundreds of millions of rows.

A disk alert is not an emergency

When the alert fires, the answer is almost always the same: customers are growing, the data is real, add disk. That was true this time too, and I resized to clear the alert like always.

But I have a habit: before I move on, I check whether the growth is real customers or just us being sloppy. Most of the time it’s customers. Sometimes it isn’t, and the only way to tell them apart is to look. A resize buys time, not understanding.

This time I sorted tables by size, and one of them dwarfed everything else. github_check_run sat at roughly 392 GB. Mergify caches a lot of GitHub data so we can evaluate merge conditions without hammering the API, and check-runs are some of the highest-volume objects GitHub produces. A big table there is not surprising on its own. What was surprising was the shape of it once I broke it down by column.

pie showData
    title github_check_run on prod, 392 GB by component
    "Indexes" : 111
    "Output blob" : 98
    "app JSONB (~200M rows)" : 43
    "Everything else (real columns)" : 140

Each of those numbers turned out to be a different kind of waste. None of them was “customers grew.”

The output we store for everyone and read for almost no one

GitHub check-runs carry an output object: a title, a summary, some body text, an annotations URL. We stored all of it, for every check-run, from every GitHub app that posts to a repository we watch. CI providers, linters, security scanners, anything wired into GitHub Checks.

We only ever read that output back for our own checks. Every reader in the codebase funnels through one function, to_check_run_light, and that function already strips the output for foreign apps before anyone sees it. Our own app is 5.6% of the rows in this table. The other 94.4% are foreign apps whose output no code path ever reads back, so almost all of that 98 GB was dead weight.

Keeping output was the original miss. It’s a big nested blob that, for foreign apps, no reader ever touches: cheap to keep when the table was small, quietly expensive at 200 million rows.

The fix is small. There’s a single choke point where check-runs get written to Postgres, so the guard goes there:

if validated_data["output"] is not None:
    if validated_data["app"]["id"] != settings.GITHUB_APP_ID:
        # We only read output back for our own checks; for foreign
        # apps it's never loaded (every reader strips it anyway).
        validated_data["output"]["text"] = None
        validated_data["output"]["summary"] = ""
        validated_data["output"]["annotations_url"] = ""

We keep title and the annotation count, which to_check_run_light does surface. Everything else for foreign apps stops at the door. There’s no backfill, so for a while the table is half-migrated: old foreign rows still carry their output, new ones don’t. That’s safe to read, because every reader strips foreign output anyway, and since this is a cache, the old rows expire and get deleted on their own. The column shrinks over time without anyone touching it.

The JSONB column that was the same value 200 million times

The next column down was app, stored as JSONB on every row: the GitHub App’s id, name, slug, and an owner object with its own id, login, type, and avatar URL. About 43 GB of it.

The distinct set of GitHub apps that post checks to our customers’ repos is small. A few hundred, maybe. We were repeating that same handful of payloads across 200 million rows, as text, in a JSONB column.

Why store it denormalized at all? Because almost everywhere else, that’s the right call. Our GitHub cache flattens the few keys we need to query on into real columns and keeps the rest of each payload in a JSONB column as-is. It’s a cache, not a source of truth. The real data lives on GitHub, the webhooks that maintain the cache arrive out of order, we sometimes fill gaps with API calls, and every resource has its own lifecycle. So we deliberately don’t build foreign keys between resources on our side. A little duplicated JSON per row buys that independence, and on a normal table it’s a fine trade.

github_check_run is the table where it stops being fine. One engineer opening ten PRs a day, each PR running fifty CI checks that post a status, every push re-running all of them, produces check-runs at a volume nothing else in the cache approaches. The same few hundred app payloads, copied across 200 million rows, is 43 GB. This is the one place the default pattern is worth breaking.

So the plan is a github_app lookup table keyed by the app id, plus an app_id bigint column on the check-run rows, reconstructing the full app object on read with a join. Latest write wins when an app’s metadata changes, which matches how the rest of our GitHub cache already behaves.

It’s more involved than the output strip, because the old app JSONB is load-bearing in places that aren’t obvious. The app slug, for example, builds the @{app_slug}/{check_name} string that powers check-success and check-failure condition matching. Get that wrong and you break merge conditions for every customer relying on a third-party check. So the rollout is staged:

flowchart LR
    A[Add github_app table<br/>+ dual-write] --> B[Backfill in batches]
    B --> C[Switch reads to a join]
    C --> D[Drop the app JSONB column]
    A:::done
    classDef done fill:#1CB893,stroke:#0e7a5f,color:#fff

The first box, the dual-write, already shipped; everything downstream of it is still ahead.

It also isn’t free on read: reconstructing app now means a join on a hot query. The lookup table is only a few hundred rows, so it should stay in cache, but I’m watching the query plan once reads flip over. The upside is the functional indexes that currently cast app->>'id' to bigint get rebuilt to key on the real app_id column, which makes them both smaller and cheaper.

None of this is the quick win. The index drop was. Normalizing app reclaims around 40 GB, but it’s a staged migration whose payoff arrives slowly. It’s the long game.

The biggest win was the cheapest one

Look at that breakdown again. The indexes were larger than either data column: 111 GB. I pulled the per-index stats from pg_stat_user_indexes to see which ones earned their size. (An LLM did the grunt work, querying the stats and running the comparisons; the calls on what to drop were mine.)

The biggest one I dropped was almost a duplicate of another index. Exactly one query used it, and dropping it just pushes that query onto the near-identical sibling. EXPLAIN ANALYZE confirmed the fallback plan: slower, yes, but by an amount that’s noise next to the tens of gigabytes the index cost to keep. The speedup it bought was never worth its size.

Dropping about 64 GB of indexes on that reasoning was instant. No table rewrite, no migration dance, no read-path changes to verify. DROP INDEX CONCURRENTLY and the space is gone. Compare that to the column work, where the payoff only arrives after the data is physically rewritten.

So I did the indexes first, and you should too.

I didn’t bother reclaiming the disk

One detail that trips people up: in Postgres, dropping a column or pruning data doesn’t hand the space back to the operating system. The pages stay allocated to the table until you VACUUM FULL or pg_repack, both of which mean either downtime or a careful online rewrite.

I skipped all of it. We upgrade Postgres major versions periodically by doing a live migration between two instances with logical replication. That migration rewrites every table into a fresh instance from scratch, which reclaims the freed space as a side effect, with no separate maintenance window. The column changes were never about winning back disk today. They stop the table from growing faster than it has to, so each customer costs us less storage going forward, and the reclaim rides along for free on the next upgrade.

What I’d tell you to copy

The most expensive thing in my biggest table wasn’t the data, it was the indexes on top of it. If your storage is creeping up, sort your indexes by size before you redesign anything. The dead weight may come off in an afternoon at zero risk while the clever normalization is still in code review.

The rest is a habit worth copying: treat the disk alert as a recurring audit, not a chore. Resizing takes one click and teaches you nothing. The twenty minutes of looking is where you find the blob you’ve been writing for years and never reading. It pays off more the more you cache, and we cache a lot of GitHub data, so the waste compounds fast.