DEV Community

Cover image for The Hidden Cost of PostgreSQL JSONB Columns Under High Write Load
𝗝𝗼𝗡𝗻
𝗝𝗼𝗡𝗻

Posted on Edited on AI-assisted

The Hidden Cost of PostgreSQL JSONB Columns Under High Write Load

Shows 8x WAL bloat from tiny updates

A single integer increment inside a 3.4 KB PostgreSQL JSONB column generated 142 MB of Write-Ahead Log (WAL) traffic across just 20,000 updates in my local benchmarkβ€”nearly 8x more WAL than updating dedicated scalar columns for the exact same logical change.

On paper, JSONB looks like the cleanest way to store evolving payloads, webhook metadata, and background job states without constant schema migrations. In read-heavy workloads, it delivers. Under frequent single-key UPDATE traffic, however, JSONB becomes a quiet write-amplification engine that inflates disk IOPS, saturates replication bandwidth, and forces VACUUM into overtime.

Rows of enterprise data center server cabinets representing high-throughput database storage hardware

Treating JSONB Like an In-Place Document Store

When building event pipelines, background job runners, or user preference stores, developers often collapse mutable state and static metadata into a single JSONB column:

CREATE TABLE webhook_deliveries (
    id bigserial PRIMARY KEY,
    payload jsonb NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

When a worker retries a delivery or records a heartbeat timestamp, the application runs jsonb_set():

UPDATE webhook_deliveries
SET payload = jsonb_set(
    jsonb_set(payload, '{retry_count}', to_jsonb((payload->>'retry_count')::int + 1)),
    '{updated_at}',
    to_jsonb(now())
)
WHERE id = 42;
Enter fullscreen mode Exit fullscreen mode

In application code, that looks like a tiny, surgical mutation. Inside PostgreSQL's storage engine, nothing about it is surgical.

MVCC, TOAST, and GIN Amplification

Three PostgreSQL storage mechanics collide whenever you mutate a key inside a moderately sized JSONB column:

1. Whole-Value Rewrites Under MVCC

PostgreSQL stores JSONB as a single binary value rather than individually addressable storage cells. Under Multi-Version Concurrency Control (MVCC), every UPDATE writes a brand-new row version. Even if only one integer inside payload changes, PostgreSQL must construct and serialize the entire JSONB binary object from scratchβ€”and acquire a row-level lock on the whole tuple, as noted in the PostgreSQL Documentation: JSON Types.

2. Out-of-Line TOAST Chunk Churn

PostgreSQL uses a fixed 8 KB page size and does not allow a single tuple to span multiple pages directly. When a row exceeds TOAST_TUPLE_THRESHOLD (roughly 2 KB after compression), PostgreSQL's TOAST (The Oversized-Attribute Storage Technique) compresses the JSONB value and slices it into chunks inside a hidden pg_toast table.

Here is the catch:

  • If an UPDATE modifies a normal scalar column and leaves the TOASTed JSONB column untouched, PostgreSQL reuses the existing TOAST pointer in the new tuple. Zero TOAST chunks are rewritten.
  • If an UPDATE modifies even one byte inside that JSONB column, PostgreSQL allocates a brand-new TOAST value OID, writes every chunk to pg_toast again, logs all of those chunks to WAL, and leaves the old chunks behind as dead tuples.

3. GIN Index Maintenance and Lost HOT Updates

Most JSONB columns carry a GIN index to keep key lookups fast. Once a column is indexed, modifying it disqualifies the row from Heap-Only Tuple (HOT) updates. Instead of keeping the update localized to a single heap page, PostgreSQL must insert new index entriesβ€”or push them into the GIN pending list (fastupdate = on), causing periodic latency spikes when a later query flushes the pending list.

Official References:

Pure JSONB vs. Hybrid Schema

To measure the exact overhead, I created two logged tables with 100,000 rows each. Each row contains ~3.4 KB of high-entropy JSONB data (using MD5 hashes so pglz/lz4 compression cannot shrink the payload below the 2 KB TOAST threshold).

Setup Script

-- Table 1: Everything inside a single JSONB column
CREATE TABLE events_jsonb (
    id bigserial PRIMARY KEY,
    payload jsonb NOT NULL
);

CREATE INDEX idx_events_jsonb_gin ON events_jsonb USING gin (payload);

-- Table 2: Mutable fields extracted to scalar columns + fillfactor for HOT updates
CREATE TABLE events_hybrid (
    id bigserial PRIMARY KEY,
    status text NOT NULL,
    retry_count int NOT NULL,
    updated_at timestamptz NOT NULL,
    payload jsonb NOT NULL
) WITH (fillfactor = 80);

CREATE INDEX idx_events_hybrid_gin ON events_hybrid USING gin (payload);
CREATE INDEX idx_events_hybrid_status ON events_hybrid (status) WHERE status = 'pending';

-- Seed 100,000 rows (~3.4 KB incompressible JSONB per row)
INSERT INTO events_jsonb (payload)
SELECT jsonb_build_object(
    'status', 'pending',
    'retry_count', 0,
    'updated_at', now(),
    'customer_id', g,
    'trace_chunks', (
        SELECT jsonb_agg(md5(g::text || '-' || i::text))
        FROM generate_series(1, 90) AS i
    )
)
FROM generate_series(1, 100000) AS g;

INSERT INTO events_hybrid (status, retry_count, updated_at, payload)
SELECT
    payload->>'status',
    (payload->>'retry_count')::int,
    (payload->>'updated_at')::timestamptz,
    payload - 'status' - 'retry_count' - 'updated_at'
FROM events_jsonb;

VACUUM ANALYZE events_jsonb;
VACUUM ANALYZE events_hybrid;
Enter fullscreen mode Exit fullscreen mode

Running 20,000 Single-Row Updates

-- Measure WAL and HOT updates on events_jsonb
SELECT pg_current_wal_lsn() AS lsn_before \gset

DO $$
BEGIN
    FOR i IN 1..20000 LOOP
        UPDATE events_jsonb
        SET payload = jsonb_set(
            jsonb_set(payload, '{retry_count}', to_jsonb((payload->>'retry_count')::int + 1)),
            '{updated_at}',
            to_jsonb(now())
        )
        WHERE id = (i % 100000) + 1;
    END LOOP;
END $$;

SELECT
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'lsn_before')) AS wal_generated,
    pg_size_pretty(pg_total_relation_size('events_jsonb')) AS total_size,
    pg_stat_get_tuples_hot_updated('events_jsonb'::regclass) AS hot_updates;
Enter fullscreen mode Exit fullscreen mode

Running the same 20,000-iteration loop against events_hybrid updates only the unindexed scalar columns (retry_count and updated_at):

SELECT pg_current_wal_lsn() AS lsn_before \gset

DO $$
BEGIN
    FOR i IN 1..20000 LOOP
        UPDATE events_hybrid
        SET retry_count = retry_count + 1,
            updated_at = now()
        WHERE id = (i % 100000) + 1;
    END LOOP;
END $$;

SELECT
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'lsn_before')) AS wal_generated,
    pg_size_pretty(pg_total_relation_size('events_hybrid')) AS total_size,
    pg_stat_get_tuples_hot_updated('events_hybrid'::regclass) AS hot_updates;
Enter fullscreen mode Exit fullscreen mode

Benchmark Results

Metric (20,000 Updates on 100k Rows) Pure JSONB Schema Hybrid Schema (fillfactor = 80) Difference
WAL Generated ~142 MB ~18 MB ~7.9x less WAL
TOAST Rewrites 20,000 full blobs 0 (pointer reused) 100% eliminated
HOT Updates 0 ~19,800+ No index writes
GIN Index Churn Every update None Zero GIN bloat

Hot Scalar Columns + Cold JSONB

You do not need to abandon JSONB to eliminate write amplification. Separate columns by mutation frequency:

  1. Promote hot fields to native columns: Any field updated frequently (status, retry_count, updated_at, worker_id, version) belongs in a typed scalar column.
  2. Keep JSONB for append-only or cold payloads: Store raw webhook bodies, arbitrary user metadata, or snapshot attributes in JSONB where they are written once and rarely mutated.
  3. Set fillfactor = 80 on update-heavy tables: Leaving 20% free space per 8 KB page allows PostgreSQL to place new row versions on the same page, unlocking HOT updates whenever indexed columns remain unchanged.
  4. Replace broad GIN indexes with targeted partial B-tree indexes: Instead of indexing the entire JSONB document, index only the specific extracted column and status values your queries actually filter on.

How to check if your production tables suffer from TOAST churn

Run this diagnostic query to compare main heap size against TOAST storage size and check your HOT update ratio:

SELECT
    relname AS table_name,
    pg_size_pretty(pg_relation_size(relid)) AS heap_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid) - pg_indexes_size(relid)) AS toast_size,
    n_tup_upd AS total_updates,
    n_tup_hot_upd AS hot_updates,
    ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_update_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY (pg_total_relation_size(relid) - pg_relation_size(relid)) DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

If toast_size dominates your table footprint while hot_update_pct sits near 0.00%, frequent JSONB mutations are likely driving your WAL and IOPS overhead.

When to Keep vs. Extract JSONB Fields

Field Characteristic Keep in JSONB Extract to Scalar Column
Written once, read occasionally Yes No
Updated more than once per row lifecycle No Yes
Used in WHERE clauses on hot paths Only with targeted index Yes
Payload size regularly exceeds 2 KB Yes (if immutable) Yes (for mutable keys)
Needs strict NOT NULL or foreign keys No Yes

Migration Checklist for Existing JSONB Tables

Before refactoring a production table, walk through these steps:

  • [ ] Inspect pg_stat_user_tables (n_tup_upd, n_tup_hot_upd) and pg_total_relation_size vs. pg_relation_size to spot oversized TOAST tables.
  • [ ] Add nullable scalar columns for the top 2–4 frequently mutated keys.
  • [ ] Dual-write to both the new scalar columns and JSONB during rollout, then backfill historical rows in batches.
  • [ ] Switch UPDATE statements to target only the scalar columns so PostgreSQL reuses existing TOAST pointers.
  • [ ] Tune ALTER TABLE ... SET (fillfactor = 80) and drop redundant GIN indexes once query plans confirm index usage.

Quick Q&A

Does PostgreSQL compress JSONB automatically before TOASTing?
Yes. By default, JSONB uses the EXTENDED storage strategy, which attempts inline compression (pglz or lz4 depending on default_toast_compression) before moving values over ~2 KB out-of-line to pg_toast. However, high-entropy payloads (UUIDs, hashes, tokens, encrypted blobs) compress poorly and still trigger full TOAST rewrites on every mutation.

What if I use a GENERATED ALWAYS AS (...) STORED column for a JSONB key?
A stored generated column extracts the value for faster reads and indexing, but it does not eliminate JSONB write amplification if your UPDATE statement still mutates the source JSONB column. Make the scalar column the primary write target instead.

The Takeaway

JSONB is an outstanding read model for semi-structured data, but PostgreSQL's MVCC and TOAST architecture penalizes partial mutations on large documents. Extracting frequently updated keys into native scalar columns preserves schema flexibility for the payload while cutting WAL volume, TOAST churn, and index bloat by nearly an order of magnitude.

Have you ever caught a JSONB column quietly driving up your WAL size or IOPS in production? How did you refactor it?


Connect with Me

If you found this article helpful, let's connect!

johnnylemonny GitHub Banner

Open to opportunities. Looking for commisions and collaborations.

Top comments (23)

Collapse
 
nyaomaru profile image
nyaomaru •

I’m not very experienced with PostgreSQL, but this was really interesting! πŸ‘€

I always thought of JSONB mostly as a convenient flexibility tool, so seeing how a tiny update can create so much extra write work was surprising.

The β€œhot scalar columns + cold JSONB” idea makes a lot of sense. Thanks for the clear explanation! 😸

Collapse
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

Glad you found it helpful! That mental shift - from viewing JSONB purely as flexible storage to seeing how PostgreSQL's MVCC and TOAST engines handle row versions under the hood, saves a lot of headaches once write traffic picks up. Thanks for reading and leaving a comment!

Collapse
 
cloudcompare profile image
CloudCompare •

Thank you for writing this up so clearly β€” the whole-row rewrite plus TOAST churn point is exactly what we kept running into, and your hybrid schema (hot scalar columns alongside a JSONB blob) matches the shape we settled on. One datapoint from our side: our own Postgres runs in-cluster on a small PVC, and WAL volume was the first thing that told us a wide JSONB column had outgrown its welcome β€” your benchmark gave numbers to something we'd only felt. Curious which tool you used for the WAL measurements, if you don't mind sharing?

Collapse
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

Glad the numbers lined up with what you saw on that PVC! For the WAL measurements, I sampled pg_current_wal_lsn() right before and after each benchmark batch and calculated the exact byte delta with pg_wal_lsn_diff(), alongside EXPLAIN (ANALYZE, BUFFERS, WAL) to inspect full-page image writes per update. That built-in combination makes TOAST write amplification show up immediately without needing external instrumentation.

Collapse
 
aheadkit profile image
AheadKit •

Your 142 MB versus 18 MB for the same 20,000 updates is the number that stayed with me β€” especially because the logical change was just one integer.

We felt the same trade-off this week in a different shape. We ran a claim-row table for webhook dedup, and every safety write is itself a write: the row that stops a duplicate being processed twice still has to be written, locked and replicated. You do not get safety for free; you choose where the cost lands.

That is why I liked the hot scalar / cold JSONB split rather than abandoning JSONB. It moves the cost instead of pretending it disappears. One thing I am curious about, though: your hybrid keeps the hot columns in the same table, which is what lets HOT updates do their work. Did you also try a normalised side table for those hot fields, and measure that against the 18 MB figure? A side table would keep the main row untouched, but every status change then becomes a second write and a join on read, so I wonder whether the cure ends up costing more than the in-table version in a retry-heavy webhook workload.

Collapse
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

I didn't include a separate side table in that benchmark run, though your intuition about the trade-off is spot on. Because the large JSONB payload already lives out-of-line in TOAST, an in-table HOT update only rewrites the narrow main-heap tuple. So moving hot fields to a side table saves very little WAL on UPDATE while adding a second INSERT plus primary-key index write upfront and a JOIN on reads. A side table mostly pays off when you switch from in-place updates to an append-only attempt log, or when the main row carries lots of wide, non-TOASTed columns that bloat every heap tuple version. Thanks for sharing that webhook dedup war story!

Collapse
 
aheadkit profile image
AheadKit •

The TOAST point settles it for me β€” HOT only rewrites the narrow tuple, so the side table buys almost nothing on UPDATE and costs an INSERT plus a JOIN every read. Did you measure the WAL on the UPDATE, or is that from the HOT mechanics?

Thread Thread
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

That conclusion comes directly from PostgreSQL's HOT and TOAST mechanics rather than a separate side-table run. On the main heap page, an out-of-line TOASTed JSONB value is just an 18-byte external pointer, so a HOT update only logs that narrow tuple version, essentially matching the WAL cost of updating a slim side-table row, without paying for an extra INSERT and primary-key index write upfront.

Thread Thread
 
aheadkit profile image
AheadKit •

That settles it for me β€” mechanics, not a measured run, which is exactly the gap. The 18-byte pointer line is the part I want to check: a HOT update logging the narrow tuple should match a slim side-table row on WAL, minus the extra INSERT and the JOIN. I'm measuring WAL per UPDATE on both shapes. One edge I'm wondering about: does that hold when the JSONB is small enough to stay inline, or does the pointer trick only kick in once it spills to TOAST?

Collapse
 
dafili profile image
Renhua Wei •

142 MB vs 18 MB for the same 20k updates is the TOAST bill: a jsonb value over ~2 KB lives out-of-line, so every touch rewrites the chunks even when the heap tuple itself is unchanged. Before you split the table, check the ratio: SELECT n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/nullif(n_tup_upd,0),1) AS hot_pct FROM pg_stat_user_tables WHERE relname='orders'; High hot_pct next to an 8x WAL gap means the payload is out-of-line, and pulling the hot scalar columns out of the jsonb is the cheap fix while the flexible blob stays for the cold fields.

Collapse
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

That is a great diagnostic catch. When the JSONB column isn't indexed, pg_stat_user_tables can show a deceptively healthy hot_pct on the main heap while out-of-line TOAST rewrites still quietly inflate WAL and IOPS. Pairing that HOT ratio check with WAL or pg_toast growth makes it immediately clear when pulling out hot scalar columns will pay off. Thanks for sharing that query!

Collapse
 
dafili profile image
Renhua Wei •

The split is one query away and it tells you how much the pull-out is worth before you touch the schema: SELECT pg_relation_size(oid) AS heap, pg_relation_size(reltoastrelid) AS toast FROM pg_class WHERE relname='orders'; toast growing while heap stays flat is exactly the signature you describe. On hot_pct one caveat worth keeping: it counts heap tuples, so it stays healthy even when most of the write cost has already moved into TOAST chunks - which is why pairing it with that toast size beats either number alone.

Thread Thread
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

Querying reltoastrelid straight from pg_class is super clean and gives an instant read on whether TOAST is quietly taking the brunt of the writes. Watching toast climb while heap stays flat is the clearest signal you can get before committing to a schema change. Thanks for adding that sharp query to the thread!

Thread Thread
 
dafili profile image
Renhua Wei •

Two gotchas if you wire that into monitoring. pg_relation_size(reltoastrelid) returns 0, not NULL, when the table has no toast relation at all, and that reads as a perfect zero in a dashboard, so filter reltoastrelid <> 0 before dividing. With default storage anything wider than ~2 KB per row goes out of line, so toast/heap ends up tracking column width more than write rate; I only start alerting once heap passes a few thousand rows.

Collapse
 
iictp profile image
IICTP •

The TOAST pointer-reuse detail is the part that reframes it for me β€” an UPDATE that leaves the JSONB column alone costs almost nothing, while touching one byte rewrites every chunk and logs it all to WAL. That makes the migration checklist's "switch the UPDATE target to the scalar columns" step the real hinge of the whole refactor. One thing I'd love to see measured: how much of the 142 MB was the GIN index churn versus the TOAST rewrites β€” was the pure-JSONB side running with the GIN index in place for that number?

Collapse
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

Yes, idx_events_jsonb_gin was active during that 142 MB run. Roughly half of that WAL volume comes directly from rewriting the ~3.4 KB of incompressible TOAST chunks and their TOAST index entries across 20,000 updates, while the rest comes from losing HOT updates, forcing a new primary-key entry plus re-inserting all 90+ keys and values through the GIN pending list and its periodic flushes. Even with the GIN index dropped, TOAST rewrites alone still push WAL past 80 MB on that workload, which is why switching the UPDATE target to scalar columns delivers the biggest win. Thanks for digging into the numbers!

Collapse
 
iictp profile image
IICTP •

Thanks for the breakdown, John β€” the part that surprised me most is that even with the GIN index dropped, TOAST rewrites alone still push WAL past 80 MB. That really isolates the TOAST rewrite as the dominant cost. On the same 20,000-update workload, roughly how far did WAL fall once the UPDATE target moved to scalar columns?

Thread Thread
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

Once the UPDATE target moved to the unindexed scalar columns, WAL dropped to roughly 18 MB across the same 20,000 updates - nearly an 8x reduction from the 142 MB baseline and more than 4x lower than TOAST rewrites alone. Leaving the JSONB column untouched lets PostgreSQL reuse the existing 18-byte TOAST pointer and execute almost every update as an in-page HOT update, so WAL only logs the narrow main-heap tuple changes alongside initial full-page images.

Thread Thread
 
iictp profile image
IICTP •

That 18 MB figure is a striking payoff β€” nearly 8x down from the 142 MB baseline, and the 18-byte TOAST pointer reuse explains why the win is so clean. It makes the design rule simple to state: keep the JSONB column out of the UPDATE target and let HOT updates do the work. Did the scalar-column version need any change to how you read the values back, or was the write path the only thing that moved?

Collapse
 
kitwisestudio profile image
Kitwise Studio •

The GENERATED ALWAYS AS STORED warning is easy to miss, and it undercuts a common shortcut. Extracting a status key into a stored generated column makes that key indexable, but if updates still run through jsonb_set on the payload, the source blob is rewritten on every increment and the WAL bill does not move. The scalar column has to be the write target, and the JSONB has to be left untouched, before TOAST pointers get reused. A generated column is a read optimization, not a write one.

Collapse
 
johnnylemonny profile image
𝗝𝗼𝗡𝗻 •

Spot on. That distinction, read optimization versus write optimization, sums it up perfectly. A lot of teams reach for GENERATED ALWAYS AS STORED hoping to fix slow queries without touching their UPDATE statements, only to wonder why WAL volume and IOPS stay completely flat. Leaving the JSONB column untouched on the write path is what actually lets PostgreSQL reuse those TOAST pointers. Appreciate you highlighting that nuance.

Collapse
 
ezenwa_christian_5243b406 profile image
EZENWA CHRISTIAN CHIMEREMEZE •

Excellent breakdown of a PostgreSQL performance issue that can easily go unnoticed during application development. The 142 MB versus 18 MB WAL comparison makes the impact of JSONB write amplification particularly clear.

What I find most valuable is that you don't simply discourage developers from using JSONB. Instead, you explain where it fits best and why separating frequently updated fields from relatively immutable payloads can produce better results.

The connection between MVCC, TOAST rewrites, GIN index maintenance, and HOT updates is especially useful because it explains the underlying storage mechanics rather than just presenting benchmark numbers.

One question I'd be interested in exploring further: how would the results change under concurrent updates from multiple workers, particularly with row-lock contention, replication lag, and autovacuum activity included in the benchmark?

This is a great reminder that database design should account for write patterns and storage-engine behaviour, not just schema flexibility. Thanks for sharing the experiment!

Collapse
 
the_daily_flare profile image
The daily flare •

That 8x WAL gap is wild β€” definitely rethinking how I use JSONB now.