The TOAST Tax: How Unbounded JSONB Arrays Cost My Blockchain Indexer 5TB

How PostgreSQL TOAST turned unbounded JSONB arrays into a 5TB storage problem, and how we moved the hot path out without taking the indexer offline.

Our blockchain indexer's biggest PostgreSQL table looked healthy. Row counts made sense, the queries were simple and the indexes were in place. Then we looked at disk usage, and the numbers didn't add up. The table was taking terabytes more than its rows could explain.

The space wasn't in the table's own pages at all. It was in a second table we had never created, never queried and never named: pg_toast.pg_toast_<oid>. That's where PostgreSQL had been putting our JSONB arrays and objects. At its peak, pg_total_relation_size() for that TOAST relation was over 5TB, counting live data, dead tuples and its index.

TL;DR

Large JSONB documents don't just live in your table. PostgreSQL may compress them and move them into a separate TOAST table. That's usually invisible, until your documents get large and your workload keeps querying individual fields inside them.

In our indexer, unbounded arrays made this much worse. A bigger JSONB index wasn't the fix. We pulled the hot fields into normal columns, moved one-to-many data into child tables, and kept JSONB for the flexible data that actually benefits from it.

What actually caused the 5TB? Not TOAST. TOAST was PostgreSQL doing exactly what it was designed to do. The cause was putting unbounded one-to-many data inside a single JSONB value, then rewriting that value again and again. TOAST hid the problem. The schema created it.

The Setup: Why JSONB Felt Like the Right Call

If you've built an indexer for a Move-based chain, you know the pull. Resources have different shapes. Event payloads change by event type. A single transaction can carry a list of arguments, a list of events and a list of state changes, and each list can be any length.

Modeling all of that relationally means dozens of tables before you've shipped anything. JSONB lets you put the whole decoded transaction in one column and move on:

I wrote about this tradeoff in Your Blockchain Indexer Is Fine, Until It Isn't. JSONB really is useful for an indexer. What I underestimated was what happens when the documents inside it get big, and arrays are exactly what makes them big.

TOAST in One Analogy: The Off-Site Storage Unit

PostgreSQL stores rows in 8KB pages, and a row (a heap tuple, in PostgreSQL terms) can't span pages. When a row is wider than TOAST_TUPLE_THRESHOLD (roughly 2KB on a default 8KB page), PostgreSQL hands it to TOAST (The Oversized-Attribute Storage Technique) to shrink it.

Think of it as an off-site storage unit:

1. First, PostgreSQL tries to compress the large values so the row fits again. That's like vacuum-packing your clothes so they still fit in the closet.
2. If the row is still too big, PostgreSQL moves values out of the row into a separate TOAST table, cut into chunks of about 2KB each.
3. The row keeps a small TOAST pointer (18 bytes on disk), which works like a claim ticket for the storage unit.

A common misconception is that TOAST only kicks in above 8KB. It doesn't. The roughly 2KB threshold applies to the whole row, so one fat events[] array is often enough to push a transaction over it.

This is great for keeping rows small, and it's why PostgreSQL can store values far bigger than a page at all. The catch is what happens when you need something back from the storage unit.

Want the full internals: chunk layout, storage strategies, and how the toaster picks which column to move first? The Storage Engine module of the PostgreSQL course covers them.

Cost #1: Reading One Key Can Mean Detoasting the Document

This was the query we ran most often:

It looks cheap because we only want one small field. But to evaluate payload->>'sender', PostgreSQL generally has to detoast the JSONB value first. For a large, compressed, out-of-line document, that can mean reading its TOAST chunks through the TOAST table's index, rebuilding the value and decompressing it before JSONB can look at its binary layout.

JSONB's binary format really is fast to search, but only once the value is in memory. PostgreSQL doesn't jump straight to the bytes for sender inside the TOAST chunks. The trip to the storage unit happens first, for every row the filter touches. In one benchmark from Snowflake's engineering team, filtering on one JSONB key in a 10,000-row table took under 10ms when the documents were 40 bytes, and about 500ms (40x slower) when they were 40KB and TOASTed. Your numbers will differ, but the direction matches what we saw.

Two details make it worse in practice:

• Projecting several fields adds up. Every JSONB expression still has to work with the underlying value, so pulling five fields out of a large document can add a lot of CPU and memory work compared to reading five plain columns.
• The planner is guessing. PostgreSQL keeps no statistics on keys inside a JSONB document. For payload->>'sender' = $1, it falls back to a hardcoded selectivity estimate, so its row count predictions can be badly wrong, and so can the plans built on them.

What this looks like in production: queries that filter on one field are slow even when they return a handful of rows, CPU stays high on a read replica with no obvious hot query, and an index on block_height doesn't help as much as you expect.

Cost #2: Updating One JSONB Key Can Rewrite the Whole Value

Indexers update data. A reorg rewrites a block, a backfill adds a decoded field, a bug fix changes a format. For us it usually looked like this:

jsonb_set looks like a partial update, and at the SQL level it is. Physically, though, PostgreSQL doesn't patch the changed bytes in place. Changing one key in a large TOASTed document produces a brand-new value, so you get new TOAST chunks, new TOAST index entries and new WAL for all of it. The old chunks stay behind as dead tuples until vacuum gets to them.

One nuance worth knowing: if an UPDATE doesn't touch the TOASTed column, PostgreSQL reuses the existing pointer and leaves the value alone. Updating block_height by itself is cheap. Changing payload in any way is not.

What this looks like in production: WAL volume spikes during backfills, replica lag climbs (the same problem I described in Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers), and disk keeps growing after the backfill is done.

Cost #3: The Storage Unit Needs Its Own Cleaning Crew

The TOAST table is a real table. It gets dead tuples, it needs autovacuum, it bloats, and it has its own index that bloats along with it. Because it's hidden, nobody watches it. Your dashboards say transactions is healthy while pg_toast_16421 quietly grows by hundreds of gigabytes. (For how vacuum and WAL work in the background, see Every Postgres Write Triggers Five Background Processes.)

That's how our TOAST relation got past 5TB. No single decision did it. It was every array that grew a little, every backfill that rewrote documents, and every vacuum cycle that couldn't keep up.

How to Check Whether You Have the Same Problem

You can run all of these on your own database.

1. Find your biggest TOAST tables

The TOAST table has its own index, so toast_total is the number that shows the real off-site footprint. If toast_total is close to or larger than main_heap, a big share of the table's physical footprint lives out of line.

2. Measure your JSONB sizes

pg_column_size() reports the size of the value as stored, after any compression. That makes it useful for seeing how big your documents really are on disk. Don't treat 2KB as a hard "TOAST or no TOAST" line for a single column, though. TOAST looks at the size of the whole row, the column's storage strategy, and whether compression can bring the row back under the target size. Look at these percentiles together with toast_total from query 1 to see what's actually happening.

3. Check compression

NULL means the value isn't compressed. Otherwise you'll see pglz or lz4.

4. EXPLAIN ANALYZE, measured honestly

Run EXPLAIN (ANALYZE, BUFFERS) on your hot queries. When the JSONB is used in a filter, TOAST reads show up in the buffer counts. There's a gotcha: plain EXPLAIN ANALYZE doesn't detoast columns you only return, so SELECT payload ... looks faster than it is. On PostgreSQL 17 and later, add SERIALIZE to include that cost:

The Fixes, From Cheapest to Most Work

1. Switch to lz4

lz4 decompresses much faster than the older pglz default, at a similar compression ratio. PostgreSQL 19 changes the default TOAST compression to lz4 on builds with LZ4 support, as I covered in PostgreSQL 19 vs. 18. It's the cheapest change here, but it only affects newly written values. Existing rows keep their old compression until they're rewritten. Each trip to the storage unit gets faster, but you still make the trip.

2. Extract hot keys into real columns

This is the fix that matters most. If you filter, sort or join on sender all the time, it shouldn't live inside a document.

On a small or medium table, a stored generated column is the cleanest option:

PostgreSQL keeps the column in sync, it's normally stored inline with the row, it gets real planner statistics, and reading the small extracted value doesn't require detoasting the original JSONB document. In the same Snowflake benchmark, filtering on a stored generated column took 5ms, against 500ms for the same filter on the TOASTed JSONB.

It isn't free, though. Every write to payload now also computes and stores sender, and the column and its index take disk. You're paying a small write cost for a big read saving, which is the right trade for a key you read constantly.

PostgreSQL 18 gotcha: generated columns are now VIRTUAL by default, which means the value is computed on read. If your goal is to get a frequently queried JSONB key out of TOAST, write STORED explicitly. A virtual column detoasts the document every time it's read, which is exactly what you're trying to avoid.

3. Expression indexes for known lookups

If you can't add a column yet, an expression index stores the extracted value in the index itself:

A lookup by sender can now find matching rows through the index without opening the storage unit, as long as you don't also SELECT payload. The index fixes the lookup path, not the cost of fetching the document itself. PostgreSQL also collects statistics on indexed expressions, so the planner gets real data to estimate with instead of a hardcoded guess. The downsides are the same as any index (write overhead and disk), plus the query has to use exactly the same expression for the index to be used.

This works best for exact matches. For a prefix search like LIKE 'a%', PostgreSQL uses the index to narrow down the rows, then re-applies the original condition to each one, which means detoasting again. Snowflake measured exactly this: 80ms through an expression index, against 5ms on a real column.

4. GIN, for what it's good at

GIN is good at containment questions like "which documents contain this key and value?" It doesn't make extracting a value any cheaper. jsonb_path_ops stores hashes, so candidate rows get rechecked against the real document, and that recheck detoasts. On large documents, GIN indexes also get very large themselves. Use GIN when you really don't know the query shape ahead of time, not as a replacement for real columns on hot keys.

5. Normalize unbounded arrays

Everything above works around the actual mistake. An array that can grow without limit doesn't belong in a single column value. events[] is a one-to-many relationship, and relational databases already have a tool for that:

Each event is now its own small row. A query like "all transfer events for this coin" becomes an index scan instead of unpacking thousands of documents. JSONB is still there, but sized for what it's good at: a small, flexible bag of fields per event, not a whole transaction's history in one value.

If you still need the full raw payload for replays or debugging, move it out of the hot table entirely, into a narrow tx_raw (tx_hash, payload) table or object storage. The hot table stays small, and the big documents are read only when someone actually needs them.

Here's the shape before and after:

The Zero-Downtime Migration

At 5TB, don't casually add a stored generated column with ALTER TABLE. PostgreSQL has to rewrite the whole table and its indexes, and the ACCESS EXCLUSIVE lock that ALTER TABLE ... ADD COLUMN takes is held until the transaction commits. The rewrite copies every value, TOAST included, and needs free disk for that second copy until it finishes. At this size, that's a long stretch (realistically hours) where the indexer can neither read nor write.

So we did it in steps that are each cheap or non-blocking:

A few things make this work at scale:

• "Instant" still needs a lock. ALTER TABLE needs a brief ACCESS EXCLUSIVE lock and CREATE TRIGGER a SHARE ROW EXCLUSIVE one. If a long-running query is holding the table, the DDL waits, and every query that arrives after it queues behind it. A short lock_timeout makes it fail fast so you can retry. More on that trap in Why a Single ALTER TABLE Can Take Down Your Whole Database.
• Order matters. The trigger goes in before the backfill starts, so every row written during the migration is already correct. The backfill only has to cover history.
• The backfill doesn't rewrite the existing TOAST values. It reads each document from TOAST once, and after that, the sender lookup no longer needs to detoast the original payload. Because it never modifies payload, PostgreSQL reuses the existing TOAST pointers, and the trigger (which only fires on UPDATE OF payload) stays out of the way.
• Commit every batch. Run the backfill from a script, one transaction per block range, never as one giant UPDATE. A multi-hour transaction stops vacuum from cleaning up any row that dies while it runs, across the whole database, and if it fails at hour five, all of that work rolls back.
• The heap still churns. The UPDATE itself (not the TOAST reads) gives every backfilled row a new tuple version in the main table, and all of that is WAL-logged. Let autovacuum (or a manual VACUUM) catch up between batches, and size the batches by what you can observe, WAL volume and replica lag, rather than a round row count.
• Switch reads last. Once the index is built, change the application from payload->>'sender' to sender, check the new plan with EXPLAIN (ANALYZE, BUFFERS), and only then think about dropping any expression index you added as a stopgap.

The same pattern (new structure, trigger for new writes, batched backfill, concurrent index, cut over reads) also works for moving events[] into tx_events.

When JSONB Is Still the Right Answer

None of this means JSONB is bad. It works well when:

• Documents usually stay small enough that rows stay inline
• You read the whole document at once, so the trip to TOAST is worth making
• The structure really is unknown or user-defined, and you rarely filter on it

It goes wrong when a document grows without limit and you keep querying single keys inside it. In many indexers, both of those show up sooner than you expect. JSONB isn't the mistake. Using it in place of modeling hot fields and unbounded relationships is.

The Rule I Use Now

JSONB is for the shape you don't know yet. Columns are for the keys you query. Child tables are for anything that's a list.

PostgreSQL won't warn you when your rows start relying heavily on TOAST. It quietly starts building a storage unit, hands each row a claim ticket and keeps going. Run the TOAST size query above on your largest tables this week. If the off-site number is bigger than the table itself, you already know which fix to start with.

Related Articles

• Your Blockchain Indexer Is Fine, Until It Isn't
• Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers
• Why a Single ALTER TABLE Can Take Down Your Whole Database
• Every Postgres Write Triggers Five Background Processes
• PostgreSQL's Async I/O Engine: Why Your Sequential Scans Just Got 2-3x Faster
• PostgreSQL 19 vs. 18: What Actually Changed, Feature by Feature