Paying Down Three Years of JSONB Debt
JSONB earns its place in a blockchain indexer's schema honestly. Move-based chains produce resources and event payloads with genuinely variable shape, and modeling every one of those shapes as its own narrow table would mean either a schema with hundreds of tables or an unmaintainable entity-attribute-value design. JSONB is the pragmatic answer to that problem. The debt shows up later, once you've been writing JSONB columns for three years and nobody's gone back to ask which of them still earn their keep.
Twelve columns, and the ones nobody was reading
A systematic pass through the schema classified twelve JSONB fields across the indexer's tables into removal phases, ranked by how safe and valuable removal would be. The first tier, already shipped, targeted the clearest cases: a column storing execution statistics on the block table that turned out to be fully derivable from two typed columns already sitting right next to it. A metadata column on the fungible-asset table that nothing in the backend ever actually read, replaced by fetching the same data from the chain's RPC layer on demand instead of storing it at all. A column on an automation gas-assessment table that had been storing an entire event payload when only one derived number from it was ever used anywhere, that number now extracted in memory instead. A signature column on the transaction detail table, dropped the same way, because nothing downstream depended on it being persisted. The rollout for each of these went through an intermediate step before the column disappeared entirely, writes were stopped first, reducing the column to an empty placeholder while everything downstream adjusted, and only then was the column itself dropped. Checking the live schema today confirms all four have completed that full arc: none of them, execution statistics, the fungible-asset metadata field, the gas-assessment payload, or the transaction signature column, exist anywhere in the current tables anymore, not even as an unused placeholder.
None of these were large individually. The observed savings on a lower-volume environment were around 10 megabytes a day, which sounds unremarkable until you scale it by production's substantially higher write volume and multiply by however many years the column would otherwise have kept accumulating. The backend service that reads from these same underlying tables had its own, independent version of this cleanup running in parallel, removing the same category of unused JSONB columns from its side of the codebase, because each service has its own read and write paths into the same tables and each needed its own audit.
The three columns nobody gets to unilaterally drop
Five more fields were still being classified into later removal phases at the time of this writing, with a further idea under evaluation: for the columns that genuinely need to stay, converting the storage format itself to something more compact than raw JSONB, to shrink the cost of keeping them without removing them outright.
Three specific columns sit in a different category entirely, and they're also the largest storage contributors in the entire schema: a payload field on the transaction detail table, an authenticator field on the core transaction table, and a content field on the event table. These three are read directly by teams outside SupraScan's own codebase, which means dropping or reshaping them isn't a decision this team gets to make alone, regardless of how much storage they cost. The options being weighed for these three are all about reducing their footprint without breaking that external contract: compressing the data at write time into a binary column instead of raw JSONB text, moving them into a separate, colder table linked by a foreign key so the frequently-queried hot tables stay lean, or working directly with the teams that depend on them to see whether an API could replace the need for a direct database read at all. None of those are simple swaps, and none of them are happening unilaterally.
The rule that exists now because this happened
The lasting output of this work isn't any single column removal. It's a standing rule, written down rather than left as tribal knowledge, that gets applied before any new JSONB column is added to this schema: can this be typed columns instead, can this be fetched from the chain's RPC layer on demand rather than stored at all, and is this something that's only ever written and never actually read, in which case it shouldn't be stored in the first place. Three years of JSONB columns accumulated because each individual one seemed like a reasonable, low-friction choice at the time it was added. The rule exists specifically so the next three years don't repeat the same accumulation for the same reasons.
What this demonstrates
The interesting tension in this story isn't "JSONB is bad." It's that a tool chosen correctly for a real problem, modeling genuinely variable-shaped blockchain data, still accumulates debt if nobody revisits whether each individual use of it is still earning its cost. Some of these columns had already stopped being read years before anyone checked. Others are permanently protected not because they're well designed, but because an external dependency makes them expensive to change regardless of their internal cost. Paying this down wasn't one migration. It was a classification exercise, a standing rule to prevent recurrence, and an honest acknowledgment that some of the debt isn't fully payable without a conversation that doesn't belong to this team alone.