← Back to Home

Why Updating a JSONB Column Can Be Expensive in PostgreSQL

2025-12-31

PostgreSQL uses a fixed page size (usually 8KB) and typically does not allow tuples (rows) to span multiple pages. This restriction prompts the question: How does Postgres store large field values, like long text or JSONB documents, that exceed this page size?

The answer is TOAST (The Oversized-Attribute Storage Technique).

The Restriction: 8KB Pages

Postgres stores data in 8KB pages. To fit on a page, a tuple must be effectively smaller than 8KB (actually slightly less due to headers). If you try to insert a row larger than this into a standard table, Postgres needs a strategy to handle it without breaking the page abstraction.

How TOAST Works

When a row is too large, TOAST does two things to the large column values (varlena types):

  1. Compression: First, it tries to compress the large field values using LZ4 (if configured) or pglz.
  2. Out-of-line Storage: If compression isn't enough to make the row fit (typically ensuring the tuple is under ~2KB to allow multiple rows per page), it slices the data into smaller chunks and stores them in a separate "TOAST table."

The main table then stores a "TOAST pointer" (reference) to where the actual data lives.

Chunking

The out-of-line data is broken into chunks (usually 2KB). These chunks are stored in a secondary table associated with the main table (e.g., pg_toast_2619 for table 2619).

TOAST Strategies

You can control how columns are TOASTed using storage strategies (ALTER TABLE ... ALTER COLUMN ... SET STORAGE ...):

  • PLAIN: The value must always be stored inline. No compression, no out-of-line storage. Valid only for fixed-length types (like integer) or non-toastable types. (Note: text etc. can use this but will fail the insert if it exceeds page size).
  • EXTENDED (Default for most varlena types): Try compression first. If still too big, move out-of-line.
  • EXTERNAL: Move out-of-line without trying to compress first. This is useful for data that doesn't compress well or if you want partial access (substrings) without decompressing the entire value.
  • MAIN: Try compression, but strictly try to keep it inline. Only move out-of-line as a last resort.

MVCC and Performance Impact

Because Postgres uses MVCC (Multi-Version Concurrency Control), an UPDATE generally creates a new tuple version.

  • Optimization: If a TOASTed value has not changed during an update, Postgres does not copy the TOAST data. The new row tuple simply points to the same existing TOAST blobs (deduplication of TOAST storage).
  • Cost: If you update the TOASTed field (even slightly), expensive new chunks must be written for the new version.

The JSONB Caveat

This is why JSONB updates can be tricky. Prior to very recent optimization concepts (and generally in standard usage), modifying one key in a large JSONB document is treated as a change to the entire column value.

  1. The old JSONB value is read (and decompressed if needed).
  2. The modification is applied in memory.
  3. The entire new JSONB document is compressed and written out to new TOAST chunks.

This "full rewrite" behavior generates significant write amplification and WAL traffic, making frequent updates to large JSONB documents an anti-pattern in Postgres.

Summary

TOAST acts as transparent "overflow storage" for Postgres, allowing it to handle fields up to 1GB. While it magically solves storage limits, be wary of the performance cost when updating these large oversized attributes.