I rewrote 1.2M Postgres rows a run to change nothing

I went looking for why one Supabase project kept draining its Disk IO Budget, and the answer was a Python script rewriting 1,206,313 rows to change nothing.

The product is GoodStanding, a free EIN diagnostic I'm building under Kynth. You type in a nonprofit's EIN and it tells you whether that org is on the IRS Auto-Revocation List or on California's "May Not Operate or Solicit" list. The table behind it, gs_orgs, is fed by ops/src/ingest-registries.py, which pulls two files: the IRS bulk revocation zip, and the CA AG CSV that refreshes on the 1st and 3rd Wednesday.

Both feeds republish their entire corpus every cycle. A few thousand rows actually move. The ingest didn't know that, so every run it upserted all 1.2 million.

Measured against the stats reset on May 22, that one script was the largest consumer of disk IO on the whole project: 24,721 upsert statements, 10.2 GB read from disk, 3h17m of database time.

Fingerprint the row, not the feed

The obvious fix is to diff against what's already in the table, but reading 1.2M rows back to decide whether to write 1.2M rows is the same IO with extra steps. What made a cheaper option available is a property of this particular table: ingest-registries.py is the only writer to gs_orgs. Nothing else touches it. So a local record of what the script last wrote is authoritative, and no read is needed at all.

def fingerprint(row):
    """Stable 16-hex digest of the columns we actually write."""
    blob = '\x1f'.join('' if row.get(c) is None else str(row[c]) for c in COLUMNS)
    return hashlib.blake2b(blob.encode(), digest_size=8).hexdigest()

Hashing COLUMNS and not the parsed source record matters. The feeds carry fields the table doesn't store, and a whitespace change in one of those would otherwise flag a row as changed and buy back the write I'm trying to avoid.

The selection is then four lines:

fresh = {ein: fingerprint(r) for ein, r in merged.items()}
if args.full:
    rows = list(merged.values())
else:
    seen = load_fingerprints()
    rows = [r for ein, r in merged.items() if seen.get(ein) != fresh[ein]]

Two things about the cache file are deliberate. Missing it isn't an error — load_fingerprints() swallows FileNotFoundError and returns {}, every row mismatches, and you get a full rewrite. The degraded state is the old behaviour, which is slow and correct. And it's written once, after the last batch lands:

# Written only after every batch landed: a crash mid-run leaves the cache
# untouched so the next run retries the same rows.
save_fingerprints(fresh)

Saving per batch would have been the natural thing to write, and it's the version where a timeout halfway through convinces the next run that rows it never wrote are already durable.

Run against that day's corpus: 0 of 1,206,313 rows flagged as changed.

The indexes nobody had ever used

While I was in the stats I checked which indexes on gs_orgs had been scanned. Three had a scan count of zero: gs_orgs_name_trgm, gs_orgs_posting_idx, gs_orgs_ca_status_idx. Never once, and maintained on every one of those upserts.

gs_orgs_name_trgm is the one worth sitting with. Despite the name it wasn't a trigram index at all — it was a btree on lower(name) text_pattern_ops. The name search the app actually issues is in src/app/api/lookup/route.ts:65:

rows = await sb(`gs_orgs?name=ilike.*${encodeURIComponent(safe)}*&...&limit=8`);

Leading wildcard. A text_pattern_ops btree can serve a prefix match and nothing else, so that query had been sequential-scanning the whole table since the day it shipped, next to an index named as though it were handling it. Dropping all three took the table's index footprint from 198 MB to 72 MB.

The write was the smaller problem

The next day I made the bigger call, and it went the other way from where I started. The project had been moved onto its own Supabase instance for blast-radius isolation, at $10/month. For one product that isn't a trade worth making — so instead of paying to keep 1.2M rows somewhere safe, I asked what the product can actually do with them.

An org revoked in 2013 is looking at a full 1023 re-application. GoodStanding sells a reinstatement Cure Pack; that org isn't a buyer, and its row is heap, index and WAL on a database the rest of the estate shares. California is different — AB 488 blocks a listed org's donation pages regardless of how old the listing is.

def worth_keeping(r):
    if r.get('ca_reg_no') or r.get('ca_registry_status'):
        return True                      # CA-listed: AB 488 buyer at any age
    if r.get('reinstatement_date'):
        return False                     # back in good standing; "clear" is correct
    return (r.get('revocation_date') or '') >= keep_since \
        or (r.get('revocation_posting_date') or '') >= keep_since

331,377 rows of 1,206,313. 261 MB down to 81 MB, back on the shared database, and the outreach delta came through unchanged: 716 IRS postings in the last 45 days, 8,219 CA delinquent or suspended.

There's a trap in that, and it's the reason the comment block above worth_keeping is longer than the function. /api/lookup reads "no row" as clear — the table only ever holds orgs that are on a list, so absence is the good answer. Drop a still-revoked 2013 org and the tool tells that nonprofit it's fine.

So the cutoff is load-bearing on a claim in the UI, and the two are now written down together. GS_KEEP_REVOKED_SINCE defaults to 2020-01-01, the route file carries a retention warning at the top, and the clear-state copy in EinLookup.tsx says "No IRS auto-revocation posted since 2020" rather than the unqualified all-clear it used to say. Widening the cutoff means widening that sentence.

What I'd been treating as a performance problem had a correctness statement buried in it. The rows I was paying to rewrite twice a month were also the rows that decided what the product was allowed to promise, and I only found that by going looking for the disk IO.