AL.
🇪🇸 ES
Back to blog
Databases · 10 min read

Postgres JSON vs JSONB: Storing Data Chunks for AI Processing

The real differences between JSON and JSONB in PostgreSQL, how each one stores your data, when plain JSON wins, and how JSONB earns its place holding chunk metadata in an AI pipeline.

🇪🇸 Leer en Espanol

I recently started storing chunks of documents for AI processing, and a decision I had been postponing for years finally caught up with me. Every chunk carries a bag of metadata, the metadata had to live somewhere, and Postgres offers two column types for it: json and jsonb. They accept exactly the same input.

Picking between them takes one sentence of advice.

They accept the same input and store different things

The fastest way to see the difference is to feed both types the same slightly hostile payload: odd whitespace and a duplicated key.

SELECT '{"b": 1,  "a": 2, "a": 3}'::json  AS stored_as_json;
SELECT '{"b": 1,  "a": 2, "a": 3}'::jsonb AS stored_as_jsonb;
      stored_as_json
---------------------------
 {"b": 1,  "a": 2, "a": 3}

 stored_as_jsonb
------------------
 {"a": 3, "b": 1}

The json column gave me back the exact bytes I sent, double space and duplicate key included. The jsonb column reordered the keys, collapsed the duplicate to the last value, and normalized the whitespace, because it did not store my text at all. It parsed the document once, on write, into a decomposed binary format, and what I get back is a rendering of that structure.

Where the difference starts costing you

  • Reads. Every operator you apply to a json value re-parses the whole document from text. jsonb navigates its binary structure directly.
  • Writes. jsonb pays the parsing and conversion cost on insert.
  • Indexing. jsonb supports GIN indexes over the whole document. If GIN is an unfamiliar word, I wrote a primer on Postgres index types that pairs with this post.
  • Disk. jsonb tends to be slightly larger at rest. Both types participate in TOAST compression for large values. I will touch on what TOAST is below.

The table I built

Chunking for AI processing has a specific shape. A document gets split into pieces, each piece gets embedded and retrieved later, and each piece drags metadata behind it: where it came from, its position, its token count, the heading trail above it. The problem is that every source type produces a different bag. A PDF has pages and maybe an OCR flag. An HTML page has a heading hierarchy. A transcript has timestamps and speakers.

That split, stable versus volatile, is the design rule. Fields I always filter or join on become real columns with real types and real constraints. The long tail goes into one jsonb column.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL
);

CREATE TABLE chunks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents (id),
  chunk_index int    NOT NULL,
  content     text   NOT NULL,
  embedding   vector(1536),
  metadata    jsonb  NOT NULL DEFAULT '{}',
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (document_id, chunk_index)
);

CREATE INDEX chunks_metadata_idx ON chunks USING gin (metadata jsonb_path_ops);

Two notes on that DDL. The embedding column and the extension line require pgvector; it is where the retrieval side of this pipeline lives, it deserves its own post. If you want to run the examples without it, drop those two lines. The content itself is a plain text column on purpose, not a key inside the metadata, for reasons that will be obvious by the end.

To make the query plans below honest, here is a seed that produces a million chunks with the kind of varied metadata a real pipeline emits:

INSERT INTO documents (title)
SELECT 'Document ' || i FROM generate_series(1, 50) AS i;

INSERT INTO chunks (document_id, chunk_index, content, metadata)
SELECT
  (i % 50) + 1,
  i / 50,
  'Body of chunk ' || i,
  jsonb_build_object(
    'source_type', (ARRAY['pdf', 'html', 'transcript'])[1 + i % 3],
    'page',        1 + i % 40,
    'headings',    jsonb_build_array('Chapter ' || (i % 12)),
    'token_count', 200 + i % 300
  ) || CASE WHEN i % 97 = 0 THEN '{"ocr": true}' ELSE '{}' END::jsonb
FROM generate_series(0, 999999) AS i;

ANALYZE chunks;

Querying the bag

The two operators you use constantly are ->, which returns jsonb, and ->>, which returns text. Here’s an example of how that distinction really works:

SELECT metadata ->> 'source_type'  AS source_type,
       metadata -> 'headings' -> 0 AS first_heading
FROM chunks
WHERE id = 42;
 source_type | first_heading
-------------+---------------
 transcript  | "Chapter 5"

The heading kept its quotes because -> handed back a jsonb value, not a string. Chain with -> while you navigate, finish with ->> when you want text.

The operator that justifies the GIN index is containment, @>, which asks whether the document contains a given sub-document:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, content
FROM chunks
WHERE metadata @> '{"ocr": true}';
 Bitmap Heap Scan on chunks (actual rows=10310 loops=1)
   Recheck Cond: (metadata @> '{"ocr": true}'::jsonb)
   Heap Blocks: exact=10310
   ->  Bitmap Index Scan on chunks_metadata_idx (actual rows=10310 loops=1)
         Index Cond: (metadata @> '{"ocr": true}'::jsonb)
 Planning Time: 0.090 ms
 Execution Time: 30.110 ms

Ten thousand OCR chunks out of a million, found through the index, in 30 milliseconds. Without the index this is a full scan of the table on every call.

The index used jsonb_path_ops, which is a deliberate choice over the default jsonb_ops. The path variant only accelerates containment, and in exchange the index is considerably smaller and faster for exactly that operator. The default variant additionally supports key-existence operators like ?. My filtering is all containment, so I take the smaller index. If you need “does this key exist at all” queries, you should use the default.

One more indexing tool matters in practice. When one scalar key is hot, a plain btree expression index beats GIN for it:

CREATE INDEX chunks_source_type_idx ON chunks ((metadata ->> 'source_type'));

That gives equality filters on metadata ->> 'source_type' an ordinary btree, with ordinary planner statistics, which GIN does not provide. It is also the only kind of index a plain json column could get, which tells you how much of the story indexing is.

For conditions that operators cannot express, jsonb speaks the SQL/JSON path language:

SELECT count(*)
FROM chunks
WHERE metadata @? '$.token_count ? (@ > 450)';

The path string: ‘$.token_count ? (@ > 450)’

  • $ is the root of the JSON document, so for each row it means “this row’s metadata value”.
  • $.token_count navigates to the token_count key, producing that value.
  • ? (…) is a filter expression. It takes whatever the path produced so far and keeps only the items that satisfy the condition in parentheses.
  • @ inside the filter means “the current item being tested”, the same role x plays in a lambda like x => x > 450.

So the whole path reads: “go to token_count, and keep it only if its value is greater than 450.”

And since Postgres 17 there is JSON_TABLE, which flattens documents into rows mid-query so the rest of your SQL can treat the bag like a table. Here it unnests the headings array and aggregates over it:

SELECT jt.source_type, jt.heading, count(*) AS chunks
FROM chunks,
     JSON_TABLE(metadata, '$' COLUMNS (
       source_type text PATH '$.source_type',
       NESTED PATH '$.headings[*]' COLUMNS (heading text PATH '$')
     )) AS jt
GROUP BY jt.source_type, jt.heading
ORDER BY chunks DESC, jt.source_type, jt.heading
LIMIT 3;

FROM chunks, JSON_TABLE(metadata, …) is an implicit lateral join: for every row of chunks, Postgres feeds that row’s metadata into JSON_TABLE, which produces zero or more rows, and each produced row is stitched to the chunk row that generated it.

metadata is the document to consume, and ’$’ is the row-generating path: “start from the root of the document”. With ’$’ as the row pattern, each document initially produces one row.

The COLUMNS clause defines the schema of the virtual table, one entry per output column:

  • source_type text PATH ‘$.source_type’ declares a column named source_type of SQL type text, filled by evaluating the path $.source_type against the current row’s document. The path is relative to the row pattern, so $ here means “the metadata object”.
  • NESTED PATH ‘$.headings[]’ COLUMNS (heading text PATH ’$’) is the unnesting part. $.headings[] means “every element of the headings array”, and the nested COLUMNS runs once per element. Inside it, the path ’$’ now refers to the current array element itself, not the document root; the meaning of $ re-anchors at each nesting level. So a chunk whose metadata holds three headings produces three rows, each repeating the same source_type next to a different heading.
 source_type |  heading  | chunks
-------------+-----------+--------
 html        | Chapter 1 |  83334
 pdf         | Chapter 0 |  83334
 pdf         | Chapter 3 |  83334

The update trap

This is the part I wish someone had put in front of me earlier. Postgres stores any column value larger than roughly two kilobytes through TOAST: compressed, sliced, and moved out of the main row. A fat jsonb document goes through exactly that machinery, and two consequences follow. More about TOAST here

First, reading one key from a TOASTed document detoasts the whole document. Second, and worse, there is no such thing as a partial update. This looks surgical:

UPDATE chunks
SET metadata = jsonb_set(metadata, '{status}', '"embedded"')
WHERE id = 42;

It is not. jsonb_set builds a complete new document in memory and the UPDATE writes a complete new row version, TOAST and all. Flip one status flag on a chunk whose metadata weighs 50 KB and you have rewritten 50 KB, plus index maintenance.

The design rule: mutable state does not belong inside a large jsonb document. Processing status, retry counts, timestamps that change, all of those are small scalars that update often, so they get their own columns where an update touches bytes instead of kilobytes. The same logic is why content is a text column in my table instead of a key in the metadata. The chunk body is the biggest thing in the row, and I refuse to drag it through a rewrite because a flag flipped. Cold data in the bag, hot data in columns.

Is this not what document databases are for

Fair question. A jsonb column inside a relational table is Postgres quietly absorbing the document-database use case: schemaless storage, indexed queries into arbitrary structure, and you keep transactions, joins, foreign keys, and SQL around it. For the chunk pipeline, that combination is the argument. The metadata is schemaless, but the chunks still need referential integrity to their documents and the embeddings sit in the same row. A separate document store or a dedicated vector database would give me two more systems to operate and a consistency problem I currently do not have. There are workloads where the dedicated systems genuinely win, mine was not the case.

Where this leaves you

The decision itself stays small. jsonb by default, json only when the original bytes are the point. What made the type earn its keep in my pipeline was everything around that choice: real columns for the fields I always touch, one GIN index matched to the operator I actually use, and mutable state kept out of the bag so TOAST never punishes a status flag.

The rule I have settled on is that columns hold what I know, jsonb holds what I cannot know yet, and keys get promoted to columns once they prove they are permanent.

If you are about to store chunks, payloads, or model outputs and were about to reach for a second database, try one jsonb column and a GIN index first.

#postgresql #jsonb #json #database-design #ai #rag