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