Bhupendra Yadav

Postgres: JSON vs JSONB data type

· 9 min read

We have been using JSON & JSONB columns in Postgres in prod for years now. These 2 data types are used to store JSON in Postgres.

I faintly knew that JSONB is efficient n all but did not know “why”/enough details until today.

So I went through the Postgres source code with help of DeepWiki, and then tested what I learned on Postgres 18.

TLDR:

json parses your document on every read. jsonb parses it once, on write.

json parses the whole text on every read. jsonb does a binary search on sorted keys and jumps to the value.

We will go through the details in the rest of the post:

json is just text

When you insert into a json column, Postgres does two things:

  1. It runs the JSON parser over the input, only to check that it is valid.
  2. It stores your input exactly as you sent it, as text.

That is all. In the source, the input function for json ends with this comment:

/* Internal representation is the same as text */
PG_RETURN_TEXT_P(result);

Because it is text, Postgres keeps everything: whitespace, key order and duplicate keys.

postgres=> SELECT '{"name": "Alice",   "age": 30, "id": 7, "name": "Bob"}'::json;
                          json
--------------------------------------------------------
 {"name": "Alice",   "age": 30, "id": 7, "name": "Bob"}
(1 row)

Both name keys are stored. When you read it, ->> 'name' returns the last one, Bob.

jsonb is a parsed tree

JSONB came after JSON. The “b” stands for binary, though many people jokingly say it means better.

When you insert into a jsonb column, Postgres runs the same parser. But this time it also builds a tree from your document, and then writes that tree to disk in a binary format.

This is sometimes called a “decomposed binary format”. In simple words: Postgres takes the document apart and files each piece away with a small label.

For {"name": "Alice", "age": 30}, the stored object looks roughly like this:

header:   object, 2 pairs

entries:  [string, 3 bytes]    <- key 1
          [string, 4 bytes]    <- key 2
          [numeric, N bytes]   <- value of key 1
          [string, 5 bytes]    <- value of key 2

data:     agename<30 as numeric>Alice

The entries do not hold your keys or values. Each entry is a 4-byte label with only two things in it:

The actual bytes are stored only once, one after the other, in the data part. (true, false and null need no bytes there. The type in the entry says it all.) There are no quotes, commas or separators. Postgres adds up the lengths to find where each item starts.

All keys come first, in sorted order, then all values in the same order. So key 1 goes with value 1, and so on.

While building this tree, Postgres also does the following:

SELECT '{"name": "Alice",   "age": 30, "id": 7, "name": "Bob"}'::jsonb;

{"id": 7, "age": 30, "name": "Bob"}

Why length first? Binary search needs a fixed order. It does not need alphabetical order. Each entry already stores the key length. So Postgres compares two numbers first. It compares the key text only when the lengths are equal. This makes each step of the search cheap.

Nested objects are sorted and deduplicated the same way:

SELECT '{"user": {"name": "Alice", "age": 30, "age": 31}, "id": 7}'::jsonb;

{"id": 7, "user": {"age": 31, "name": "Alice"}}

Numbers are stored as numeric, so the exponent form is lost, but trailing zeros are kept:

SELECT '{"reading": 1.230e-5}'::json  AS j,
       '{"reading": 1.230e-5}'::jsonb AS jb;

           j           |           jb
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}

All this work is why a jsonb insert costs more than a json insert.

Reading one key

This is where the two types are really different.

With json, doc ->> 'name' gives the raw text to the JSON parser again. The parser walks the document and watches for the key you asked for. It does this for every row, on every query.

I expected it to be fast when the key is the first one in the document. It was not. The parser still goes through the whole document. In my benchmark below, reading the first key and the last key took the same time.

With jsonb, there is no parsing. The keys are already sorted, so Postgres does a binary search on the key entries. When it finds the key, the matching value entry tells it where the data is. It reads only that value.

json:   doc ->> 'name'
        text --> parse whole document --> find "name" --> return

jsonb:  doc ->> 'name'
        binary search on sorted keys --> jump to value --> return

A few more details I found interesting:

The numbers

I ran a small test on my Mac with Postgres 18.6. I made 200,000 rows. Each row had a document with 50 string keys, about 1.4 KB. I put the same data in a json table and a jsonb table. Each number is the median of three runs.

Operation (200k rows) json jsonb
Insert 1,013 ms 1,968 ms
Read one key (doc ->> 'key_1') 365 ms 25 ms
Read one key (doc ->> 'key_50') 369 ms 29 ms
Read the whole document as text 22 ms 326 ms

What this shows:

One thing to keep in mind: at 1.4 KB, these documents are small enough to stay inside the row, uncompressed. That is the best case for jsonb key reads. With bigger documents, both types must first load and decompress the value (see What jsonb does not fix), so the gap gets smaller.

On disk, both tables were about the same size for this data. For small documents, jsonb can be bigger, because each item has a 4-byte entry. A 3-key document was 41 bytes as json and 65 bytes as jsonb.

This is not a full benchmark. It is one machine and one shape of data. But it shows where each type pays its cost.

What else jsonb gives you

Because jsonb is a known structure, Postgres can do more with it:

What jsonb does not fix

Two things are true for both types:

When to use json

I use jsonb by default. I would pick json when:

If you query inside the document, filter on it, index it, or compare it, use jsonb. You pay the parsing cost once, instead of on every read.

References

Until next time.

← All posts