Losing PostgreSQL Gains? Blame Inline JSONB!!

Losing PostgreSQL Gains? Blame Inline JSONB!!

PostgreSQL's jsonb is a favorite among developers for its flexibility - but it hides a dark side. When used carelessly, especially in-line within rows under 2KB, it can silently destroy performance, even if you're using indexes. Here's why.

🔍 The Hidden Cost of JSONB (Inline Storage)

PostgreSQL stores table rows in 8KB pages, packing as many tuples as possible. For a typical row with 10–12 columns, and small text/integers, 40–100 rows can easily fit per page.

Typically row count = Page Size(8kb) / row size + row metadata (30-50 bytes approx.)

But the game changes when you add jsonb.

Example

CREATE TABLE events (

id serial PRIMARY KEY,

user_id int,

action text,

metadata jsonb

);

Suppose metadata which is a jsonb column contains:

{

"ip": "127.0.0.1",

"device": "Android",

"country": "IN"

}

This JSON might be just 100–500 bytes, so PostgreSQL stores it in-line inside the same page (no TOASTing).

Result

Each row size jumps from ~80 bytes → ~200–400 bytes

Row count per page drops from 100 → 20–40

Index scan still needs to read each page for matching rows

More pages = more I/O, slower performance

🔢 Real Benchmark Insight

Performance comparisonEven with a GIN or B-tree index on the JSONB column, PostgreSQL still needs to scan all matching pages to retrieve the full tuple.

🧠 Why Index Doesn't Save You

Say you index a JSONB key like:

CREATE INDEX ON events ((metadata->>'ip'));

And query:

SELECT * FROM events WHERE metadata->>'ip' = '127.0.0.1';

PostgreSQL will:

Use the index to find matching tuples

Still need to fetch the row from disk

Because JSONB is in-line, many pages are touched

More page fetches = more IO = slower queries

🩹 What You Can Do

✅ Force TOAST: Add padding to make JSONB exceed 2KB:

UPDATE events SET metadata = metadata || jsonb_build_object('padding', repeat('x', 2000));

✅ Split into separate table: If JSONB is rarely queried

✅ Stick to well defined schema and avoid using jsonb unless absolutely necessary.

🧾 TL;DR

JSONB under ~2KB is stored inline

That bloats each row and reduces rows per page

More pages scanned = slower indexed reads

Even efficient indexes can't avoid this penalty

Force TOAST or redesign if performance matters

🔚 Final Thought

Indexes reduce logical lookup cost. But if rows are bloated due to in-line JSONB, you're paying a high physical I/O cost - and that's where PostgreSQL performance dies quietly.

📦 Source Code

You can find the source code and diagram files on GitLab:

👉 rohit yadav / Postgres-JsonB-Performance · GitLab