Scaling a Blockchain Database From a Single Partition to 4.6 TB

In 2021, SupraScan's transaction tables lived in a single partition each. That was the right call at the time: low volume, simple queries, nothing to gain from splitting data you could scan end to end in milliseconds anyway. Five years later, that same table shape would have been an operational disaster. The interesting part of this story isn't that partitioning strategy changed. It's that it changed five separate times, each change a direct response to a problem the previous grain created, and the last of those changes caused a real production outage before it was fully safe.

Stage one: one partition, then six months, then a monster

The grain tightened in stages as ingestion grew. From 2021 through mid-2024, everything sat in one partition per table. From mid-2024 through the start of 2025, tables were split into six-month partitions, still coarse, but enough to keep individual partitions from growing unbounded.

Then came the full-year partition covering all of 2025, created when the six-month grain was tightened again. That single partition, covering an entire year of transaction events, became the largest structural liability in the entire database. By the time a dedicated storage audit looked at it in April 2026, the prod SupraScan database sat at 10 TB on a single Postgres instance, and that one year-long event partition alone accounted for 4.9 TB of that, roughly half the entire database, driven by three JSONB columns storing content, event data, and identifiers. A separate transaction-detail table was carrying 71 to 86 percent index overhead, meaning its indexes were two to three times larger than the data they indexed. Ingestion at that point was running around 500,000 transactions and 11 million events per day, all still landing in yearly buckets.

The audit, and the decision not to rebuild everything at once

Faced with a 10 TB database and a monster partition eating half of it, the natural instinct is to reach for a bigger architectural change. Three tiering options were seriously evaluated, and it's worth being honest about how each one would actually have played out, not just what it promised on paper. Moving cold data into ClickHouse for the older window would have delivered the best compression and the fastest historical queries of the three, at the real cost of operating a second database engine indefinitely: a new sync pipeline, a second system to monitor, and a second place a query result could quietly drift from the source of truth. Archiving cold data to object storage as Parquet was the cheapest option by a wide margin, but it pushed historical query latency out to 2 to 10 seconds, a real, felt tradeoff for anyone or anything querying older data, not a rounding error. Staying entirely inside Postgres with TimescaleDB's columnar compression was the lowest-effort option precisely because it didn't introduce a second engine at all, at the cost of a compression ceiling lower than a purpose-built columnar store would give.

None of those decisions were made in isolation from a more basic fact: regardless of which tiering approach eventually gets picked, a set of quick wins didn't depend on the choice at all. Extracting frequently-queried JSONB fields into typed columns, auditing and dropping unused indexes on that same transaction-detail table, splitting the year-long monster partition into monthly chunks, and adding Redis caching for hot-path queries were all identified as work worth doing regardless of the bigger architectural direction. That's the pattern worth naming here: the team didn't wait for a strategic decision to start paying down the parts of the debt that were unambiguous.

Tightening the grain again, and the outage that came with it

The partition grain kept tightening after the initial audit: monthly partitions from early 2026, then daily partitions starting in April 2026, auto-created seven days ahead of when the data would land, managed by a dedicated partition-management service running on UTC midnight. The reasoning for going all the way to daily wasn't purely about the production database. QA and testnet environments only need to retain seven days of data, and dropping a daily partition is instant, where deleting the equivalent rows row-by-row was generating real database load on environments that didn't need the data kept at all. Production mainnet runs in create-only mode, keeping every partition forever; QA and the testnet tiers run with cleanup enabled, dropping partitions older than the retention window automatically.

This is also where the evolution produced a real incident. On April 27 and 28, 2026, the automated partition-creation cron silently failed to create the upcoming daily partitions for exactly three tables: the sender, receiver, and fee-payer tables, the only partitioned parents in the schema carrying AFTER DELETE row-level triggers. The reason was subtle: creating a partition on a table with active triggers loses a lock race against the live indexer process writing to that same table, and the create-only rollout hadn't built in a pause for that window. The failures were caught, but only logged at warning level per table, so the automated job looked healthy from the outside while it was quietly failing on exactly the tables that mattered most. The outage hit at 03:57 UTC on April 29, when the indexer tried to insert into a partition that had never been created. It was fixed manually with a defensive CREATE TABLE IF NOT EXISTS ... PARTITION OF statement, and two concrete follow-ups came out of it: pausing the indexer during partition creation, and extending the creation lookahead from two days to seven, so a single missed run has more runway before it becomes a live outage.

Where the numbers landed

A live measurement taken shortly after this work, on May 4, 2026, put the production mainnet database at 4,667 GB, roughly 4.6 TB, down from the 10 TB the April audit had measured on the same instance. It's worth being honest about what does and doesn't explain that drop: the individually-quantified levers, 229 GB from the index audit and roughly 10 MB a day from the first JSONB removals, don't add up to 5.4 TB on their own. The rest is best attributed to the broader restructuring work described here, not to a single measured fix, and that gap is worth naming rather than implying away. The old monster partition's damage was still visible in the numbers even after the paydown: that same year-long transaction-detail partition alone held 452.9 million rows across 1,641 GB, 86 percent of which was index, not data. The new daily partitions for 2026 were running at a much more modest 4.5 to 5 GB per day.

The same audit pass surfaced a second, entirely separate lever: a systematic scan for indexes that had never been scanned, ever, in production. It found 229 GB across 296 zero-scan indexes on prod mainnet alone, with individual offenders as large as a 57 GB primary key index on an automation-fees table with zero recorded scans. That number, sitting right next to the partition work, is the honest reminder that storage debt in a system like this rarely has one cause. Five years of grain changes, a JSONB-heavy schema, and indexes created for a query pattern that never materialized all compound in the same database at the same time, and the fix for each one looks nothing like the fix for the others.

The grain history is still visible directly in the schema today, not just in a report from the time it happened: the earliest partition on the core transaction tables still covers that original 2021-through-2024 span, followed by the six-month partition marking the next grain change, the full 2025 partition still carrying the size of the monster year it covers, three separate monthly partitions for the first quarter of 2026, and daily partitions from spring 2026 onward, currently running several days ahead of the present date exactly as a seven-day creation lookahead would produce. Fifteen separate tables carry this same partitioning strategy today. The evolution this piece describes isn't a closed chapter being reconstructed from memory, it's sitting in the live schema as a literal, dated record of every decision described above.

What the progression actually shows

None of the five grain changes were wrong at the time they were made. Yearly partitions were a reasonable tightening of six-month partitions, until ingestion volume and JSONB column size turned that year into a 4.9 TB single object. What makes this a case study rather than a postmortem is that each stage's cost only became visible under the load the previous stage couldn't have anticipated, and the team's response each time was to tighten the grain further rather than declare the whole model broken and start over. The daily-partition outage is part of that same story, not a separate failure. It's what happens when a mature, working pattern gets pushed one more notch tighter and meets a database-level detail, trigger locking during DDL, that the earlier, coarser grains had never been fast-moving enough to expose.