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