JSONB vs columns in PostgreSQL, measured on 2 million rows

Originally published on ImportStatic.

Storing rows as a jsonb document in PostgreSQL instead of typed columns costs a lot in storage and updates and little in indexed lookups, and a GIN index beats a single-column btree when a query combines keys. Those are the results of loading the same 2 million orders into both layouts on PostgreSQL 18.6 and comparing storage, filters, aggregates, indexes, planner estimates and updates. The question came from a Hacker News thread about shredding JSON columns, which asked how much difference remains in Postgres once the JSON is properly indexed.

The planner's row estimates differ too. For status = 'pending' on the column table it estimates 487,533 rows; for doc->>'status' = 'pending' it estimates 10,000; 500,624 rows match. The planner uses that number to choose join orders and to decide between an index and a sequential scan, so a JSONB schema can produce bad plans even where its raw query timings look fine.

Test data

CREATE TABLE orders_col (
  id          bigint PRIMARY KEY,
  customer_id integer NOT NULL,
  status      text NOT NULL,
  country     text NOT NULL,
  amount      numeric(10,2) NOT NULL,
  items       integer NOT NULL,
  created_at  timestamptz NOT NULL,
  express     boolean NOT NULL
);

CREATE TABLE orders_doc (
  id  bigint PRIMARY KEY,
  doc jsonb NOT NULL
);

orders_col was filled from generate_series(1, 2000000) with values derived from hashint4(g), so the data is repeatable. orders_doc got the same rows through jsonb_build_object. Status is skewed on purpose: four values cover about 25% each and refunded is 1% (20,158 rows). Ten countries are spread evenly. A document looks like this:

{"items": 7, "amount": 500.17, "status": "pending", "country": "IN", "express": true, "created_at": "2025-09-07T01:27:51+08:00", "customer_id": 152520}

The created_at value is now a string carrying the session's time zone offset. JSON has no timestamp type, and the JSON types documentation maps its primitives to text, numeric and boolean only. Every date comparison on the document has to cast back.

Both tables were vacuumed and analyzed before measuring. The laptop's load average was around 9 to 10 during the runs, so timings are given as ranges over three runs after a warm-up and are best compared as ratios.

Storage

	columns	jsonb
Table size (pg_table_size)	158 MB	422 MB
Pages	20,164	54,055
Average row (pg_column_size)	71 bytes	211 bytes

The document alone averaged 179 bytes. Each one repeats all seven key names, and every number is stored as numeric, including customer_id and items, which were 4-byte integers in the column table. The 2.7x size difference is also the I/O difference for anything that reads the whole table.

Queries without secondary indexes

Query	columns	jsonb
status = 'refunded' AND country = 'DE' (2,011 rows)	45 to 52 ms	86 to 102 ms with ->>, 134 to 152 ms with @>
sum(amount) grouped by country	264 to 310 ms	2,113 to 2,963 ms

The filter is a parallel sequential scan on both tables, reading 20,164 pages against 54,055. The aggregate was 8 to 10 times slower on the document table, because every row pays for doc->>'amount', which returns text, and then for a cast back to numeric. Any reporting query that touches every row pays that per-row cost.

Indexes

We built four indexes: a btree on orders_col (status), an expression btree on orders_doc ((doc->>'status')), and both GIN operator classes on doc.

Index	Size	Build time (one run)
btree on status	13 MB	0.8 s
btree on (doc->>'status')	13 MB	1.1 s
GIN jsonb_path_ops	159 MB	15.0 s
GIN jsonb_ops (default)	262 MB	28.5 s

The expression index is the same size as the column index, because it stores the same values. The GIN indexes index every value in every document, which is why they're 12 and 20 times larger. The documentation says jsonb_path_ops is "usually much smaller" than the default class and supports @>, @? and @@ but not the key-exists operators ?, ?| and ?&. If you only use containment queries, it's the one to build.

Fetching the 20,158 refunded orders and summing their amounts:

Plan	Time
btree on status	16 to 38 ms
expression btree on doc->>'status'	50 to 104 ms
GIN jsonb_path_ops with doc @> '{"status":"refunded"}'	55 to 75 ms

The index lookups themselves took 1.4 to 1.9 ms in all three plans. The rest is the bitmap heap scan: 12,839 heap blocks for the column table and 16,933 for the document table, plus extracting amount from each document.

When a query combines keys, one jsonb_path_ops index answers doc @> '{"status":"refunded","country":"DE"}' in 4.3 to 6.4 ms, because it intersects the entries for both values before touching the heap. The column table, with only the status btree, took about 37 ms for the same filter, since it fetched all 20,158 refunded rows and threw away 18,147. A composite btree on (status, country) fixes that (13 MB, 1.1 to 1.3 ms with a warm cache), but you need one per combination you query. GIN can't help with ranges such as (doc->>'amount')::numeric > 990; that needs its own expression index.

Planner estimates

Without an index on the expression, the planner has no statistics for doc->>'status' and falls back to DEFAULT_EQ_SEL, which selfuncs.h defines as 0.005:

EXPLAIN SELECT * FROM orders_doc WHERE doc->>'status' = 'pending';

 Gather  (cost=1000.00..68554.82 rows=10000 width=186)
   Workers Planned: 2
   ->  Parallel Seq Scan on orders_doc  (cost=0.00..66554.82 rows=4167 width=186)
         Filter: ((doc ->> 'status'::text) = 'pending'::text)

0.5% of 2 million is 10,000, for pending (500,624 rows) and refunded (20,158 rows) alike. Ranges are worse. (doc->>'amount')::numeric > 990 was estimated at 666,739 rows, the one-third of DEFAULT_INEQ_SEL, when 20,120 rows matched; the same filter on the amount column was estimated at 20,932.

The containment operator does better. @> on jsonb uses matchingsel, which (through generic_restriction_selectivity in selfuncs.c) applies the operator to the column's histogram sample. It estimated 20,202 rows for refunded and 545,464 for pending, off by 9% instead of 50x.

There are two ways to give ->> real statistics. An expression index gets them at the next ANALYZE: with doc_status_expr in place, the pending estimate became 505,142. If you don't want the index, CREATE STATISTICS on the expression collects the same univariate statistics without index maintenance:

CREATE STATISTICS doc_status_stats ON (doc->>'status') FROM orders_doc;
ANALYZE orders_doc;
-- Seq Scan on orders_doc  (cost=0.00..101466.27 rows=508722 width=186)

For any JSON key that appears in a WHERE clause or a join, one of the two is worth adding. Note that the statistics follow the exact expression: (doc->>'amount')::numeric needs its own.

Updates

Changing one field in a document writes a new version of the whole row, as it does for a column, but there is a second cost. The HOT documentation lists the conditions for a heap-only update, which skips index maintenance: the update "does not modify any columns referenced by the table's indexes", and the page has room for the new version. An expression index on doc->>'status' references doc, so changing amount inside the document disqualifies HOT even though status didn't change.

We added 1 to amount on 100,000 rows in each table and measured time, WAL generated and HOT updates. Each configuration ran once, on a different range of ids:

Setup	columns	jsonb
Full pages; jsonb has expression btree and both GIN indexes	1.02 s, 41 MB WAL	9.78 s, 394 MB WAL
Full pages; jsonb has only the expression btree	1.14 s, 41 MB WAL	1.87 s, 68 MB WAL
fillfactor = 80; jsonb has only the expression btree	1.30 s, 35 MB WAL, 25,372 HOT	1.67 s, 63 MB WAL, 0 HOT

With freshly loaded, full pages almost no update can be HOT on either table (80 of 100,000 on the column table). After fillfactor = 80 and VACUUM FULL, a quarter of the column updates became HOT, since amount has no index. The document table stayed at zero. The GIN indexes are the expensive part: on the document table they turned a 1.87 s update into 9.78 s and 68 MB of WAL into 394 MB, because each new row version adds an entry for every value in its document to both of them.

Choosing between columns and jsonb

Put a field in a real column if you filter, join, sort, aggregate or update it on its own. Use jsonb for the parts you read and write as a whole, or whose keys you don't know in advance: payloads from an API, user-defined attributes, settings. The JSON documentation says the two approaches can "co-exist and complement each other within the same application", and recommends that documents keep "a somewhat fixed structure" even when flexibility is the goal.

If a key in the document does end up in WHERE clauses, give it an expression index or CREATE STATISTICS so the planner stops guessing 0.5%. Add a GIN index when queries combine arbitrary keys with @>, and accept that writes to that table get several times more expensive. To see how far an estimate is off in your own plans, compare rows= in the estimate with the actual row count, as described in reading EXPLAIN ANALYZE. SQLite users have a different set of options for the same problem: see VIRTUAL vs STORED generated columns.

---

Originally published on ImportStatic. This article was drafted with AI assistance; every claim was checked against the sources linked in it and the code samples were compiled and run.