When Does PostgreSQL JSONB Performance Break Down? Scale Thresholds and Optimization Strategies
The Problem
I was happily storing JSON documents in PostgreSQL JSONB columns. GIN indexes were working, containment queries were fast, and my application was humming along nicely. Then we hit 500,000 documents and suddenly my queries started taking seconds instead of milliseconds.
Month 1: 10K docs → 5ms query time → HappyMonth 3: 100K docs → 15ms query time → Still happyMonth 6: 500K docs → 800ms query time → What happened?Month 9: 1M docs → 3s query time → PanicI assumed JSONB was just like MongoDB. I was wrong. PostgreSQL JSONB has fundamentally different performance characteristics at scale, and I hit three distinct thresholds where those differences became painful.
The Three Scale Thresholds
After digging into the problem and reading countless Reddit threads, I found that JSONB performance breaks down in predictable ways at three distinct scale thresholds:
┌─────────────────────────────────────────────────────────────────────┐│ ││ SMALL SCALE (<100K docs) ││ ───────────────────────── ││ GIN indexes work great ││ Storage overhead manageable ││ Query performance ~typed columns ││ ✓ HAPPY ZONE ││ ││ ─────────────────────────────────────────────────────────────── ││ ││ MEDIUM SCALE (Multi-tenant, complex queries) ││ ────────────────────────────────────── ││ Per-customer schemas explode ││ Query abstraction layers emerge ││ Index strategy critical ││ ⚠ DANGER ZONE ││ ││ ─────────────────────────────────────────────────────────────── ││ ││ LARGE SCALE (TBs, high write throughput) ││ ───────────────────────────────────── ││ Storage bloat from attribute name duplication ││ Query planner cannot optimize JSON structure ││ Write amplification from GIN updates ││ ✗ PAIN ZONE ││ │└─────────────────────────────────────────────────────────────────────┘Each zone requires different strategies. Let me explain what I learned.
Small Scale: The Happy Zone
Under 100,000 documents with simple lookups, JSONB is excellent. Here’s what worked for me:
-- Create table with JSONB columnCREATE TABLE user_events ( id SERIAL PRIMARY KEY, user_id INTEGER, event_data JSONB, created_at TIMESTAMPTZ DEFAULT NOW());
-- GIN index for containment queriesCREATE INDEX idx_event_data ON user_events USING GIN (event_data);
-- Fast containment querySELECT * FROM user_eventsWHERE event_data @> '{"event_type": "click"}'ORDER BY created_at DESCLIMIT 100;The @> containment operator combined with a GIN index gives you fast document lookups. At small scale, the performance difference between querying a typed column versus a JSONB attribute is unnoticeable.
✓ GIN indexes provide fast containment queries✓ Storage overhead is manageable (attribute names per row)✓ Development velocity is high (flexible schema)✓ No special architectural considerations neededI stayed in this zone for months and loved it. JSONB gave me schema flexibility without sacrificing query speed.
Medium Scale: The Danger Zone
The medium scale zone caught me off guard. It’s not about document count alone - it’s about query complexity and multi-tenant patterns.
I was building a SaaS application where each customer had their own document schema. One customer stored product attributes, another stored user preferences, another stored event metadata. All in the same JSONB column.
Customer A: {"product_sku": "ABC", "price": 99.99, "category": "electronics"}Customer B: {"user_pref": "dark_mode", "setting_value": true}Customer C: {"event_id": 123, "metadata": {"source": "mobile"}}
→ All in same JSONB column→ Per-customer query logic in application layer→ Basically reimplementing what MongoDB gives you nativelyI ended up building a query abstraction layer on top of JSONB. That query layer was basically reimplementing what a document database provides natively.
The Reddit discussion confirmed this pattern:
“Medium scale (multi-tenant, per-customer schemas): This is where it gets tricky. You end up building a query layer on top of JSON that’s basically reimplementing what a document database gives you natively.”
Hybrid Approach for Medium Scale
The solution I found: extract frequently queried fields to typed columns, keep JSONB for flexibility:
CREATE TABLE products ( id SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL, sku TEXT NOT NULL, -- Extracted for queries price DECIMAL(10,2), -- Extracted for queries attributes JSONB, -- Flexible storage for extras -- Generated column for indexing JSON field category TEXT GENERATED ALWAYS AS (attributes->>'category') STORED);
-- Index extracted fields for fast queriesCREATE INDEX idx_customer_sku ON products(customer_id, sku);CREATE INDEX idx_category ON products(category);
-- Query on extracted column (fast)SELECT * FROM products WHERE customer_id = 42 AND sku = 'ABC123';
-- Query on JSONB for rare cases (acceptable)SELECT * FROM products WHERE attributes @> '{"vendor": "ACME"}';Generated columns (GENERATED ALWAYS AS ... STORED) are a game-changer. They let you index JSONB fields without manually maintaining duplicate columns.
1. Extract frequently queried fields to typed columns2. Use generated columns for virtual indexing of JSON fields3. Keep JSONB for rarely queried or evolving attributes4. Consider materialized views for complex aggregationsLarge Scale: The Pain Zone
At terabyte scale with high write throughput, fundamental limitations emerged. This is where JSONB performance really breaks down.
Storage Bloat Problem
JSONB stores attribute names per row. Every single row repeats the same attribute names:
Row 1: {"status": "active", "type": "order", "amount": 100} → Stores: "status", "type", "amount" strings + values
Row 2: {"status": "active", "type": "order", "amount": 200} → Stores: "status", "type", "amount" strings + values again
Row 3: {"status": "active", "type": "order", "amount": 300} → Stores: "status", "type", "amount" strings + values again
1 million rows = 1 million repetitions of "status", "type", "amount"In a typed column, the column name exists once in the schema. In JSONB, every row carries its own attribute names. At scale, this bloats storage dramatically.
Query Planner Limitations
The query planner cannot reason about JSON structure the way it reasons about typed columns:
Typed column: JSONB attribute:┌─────────────────────────┐ ┌─────────────────────────┐│ Planner knows: │ │ Planner sees: ││ - Column type │ │ - Unknown structure ││ - Statistics │ │ - No statistics ││ - Index selectivity │ │ - Cannot estimate cost ││ - Optimal plan │ │ - Generic plan │└─────────────────────────┘ └─────────────────────────┘With typed columns, the planner has statistics, knows selectivity, and chooses optimal plans. With JSONB, it operates blind.
Write Amplification
GIN index updates cause write amplification. Each JSONB update potentially updates many index entries:
One JSONB document: {"a": 1, "b": 2, "c": 3, "d": 4}GIN index entries: a→row, b→row, c→row, d→row
Update one field: {"a": 1, "b": 2, "c": 3, "d": 5}GIN must update: d→row entry
But JSONB is stored as one value → entire row updated→ Dead tuple created→ Vacuum required→ At scale, vacuum becomes expensiveDetection and Monitoring
Here’s how I detected we were in the pain zone:
-- Check table sizesSELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size, pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as indexes_sizeFROM pg_tablesWHERE tablename LIKE '%json%'ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
-- Check average JSONB size per rowSELECT pg_size_pretty(avg(pg_column_size(event_data))) as avg_jsonb_size, count(*) as total_rowsFROM user_events;When my index size exceeded table size by 3x and average JSONB size was growing despite stable document structure, I knew we had a problem.
Migration Pattern: When to Transition
If you’re hitting the pain zone, here’s the migration pattern I used:
Step 1: Identify frequently accessed JSON pathsStep 2: Extract to typed columns or generated columnsStep 3: Create indexes on extracted columnsStep 4: Update application to use extracted columnsStep 5: Evaluate document database for remaining JSON workload-- Step 1: Find most accessed JSON keysSELECT jsonb_object_keys(event_data) as key, count(*) as frequencyFROM user_eventsGROUP BY keyORDER BY frequency DESCLIMIT 20;
-- Step 2: Extract to generated columnALTER TABLE user_eventsADD COLUMN event_type TEXT GENERATED ALWAYS AS (event_data->>'event_type') STORED;
-- Step 3: Index the extracted columnCREATE INDEX idx_event_type ON user_events(event_type);
-- Step 4: Update queries to use extracted columnSELECT * FROM user_events WHERE event_type = 'click';-- instead ofSELECT * FROM user_events WHERE event_data @> '{"event_type": "click"}';Decision Framework
Here’s my decision framework for JSONB usage:
┌─────────────────────────────────────────────────────────────────────┐│ ││ Question 1: Is JSON your PRIMARY data model? ││ ─────────────────────────────────────────── ││ YES + expecting TB scale → Start with document database ││ YES + small-medium scale → Use JSONB + plan extraction early ││ NO (supplementary data) → Use JSONB at any scale ││ ││ Question 2: What percentage of queries touch JSON? ││ ─────────────────────────────────────────── ││ <30% → JSONB is fine, hybrid approach ││ >70% → Document database may be better ││ ││ Question 3: Current scale? ││ ─────────────────────────────────────────── ││ <100K docs → JSONB happy zone ││ Multi-tenant → Hybrid approach required ││ TB scale → Document database or Citus sharding ││ ││ Question 4: Write throughput? ││ ─────────────────────────────────────────── ││ Low writes → JSONB fine ││ High writes → GIN index amplification problematic ││ │└─────────────────────────────────────────────────────────────────────┘Why This Matters
Understanding these thresholds prevents costly migrations. The Reddit discussion captured this perfectly:
“if JSON is your primary data model, evaluate whether a document database is actually the right fit”
I made the mistake of treating JSONB as a drop-in MongoDB replacement. At small scale, it worked. At large scale, I paid for that architectural shortcut.
Small scale: JSONB saves cost (no extra infrastructure)Medium scale: Hybrid approach balances cost and flexibilityLarge scale: Wrong choice = costly migration + downtimePerformance predictability matters too. Knowing when to expect performance cliffs enables proactive architecture changes instead of reactive firefighting.
Common Mistakes I Made
I made every mistake on this list:
Mistake 1: Schema-less at scale
WRONG: Store everything in JSONB, no schema constraints → Storage bloat, query degradation
RIGHT: Define core schema, use JSONB for truly flexible attributesMistake 2: Over-indexing JSONB
WRONG: GIN index on entire JSONB for every table → Write amplification explodes
RIGHT: Targeted indexes on specific paths, partial GIN indexesMistake 3: Trusting the query planner
WRONG: Assuming planner optimizes JSONB like typed columns → Generic plans, poor performance
RIGHT: Extract hot paths to typed columns, let planner optimizeMistake 4: Ignoring vacuum
WRONG: JSONB updates without monitoring vacuum → Dead tuples accumulate, bloat grows
RIGHT: Monitor bloat, tune autovacuum for JSONB-heavy tablesMistake 5: No benchmarking at realistic scale
WRONG: Testing with 10K docs, expecting 1M performance → Performance cliffs surprise you
RIGHT: Benchmark at 2x expected scale before committing to architectureSummary
PostgreSQL JSONB performs excellently within its performance envelope, but understanding scale thresholds is critical:
- Under 100K documents: JSONB is excellent with GIN indexes and containment queries
- Multi-tenant or complex patterns: Hybrid architecture with column extraction required
- Terabyte-scale workloads: Consider document databases or significant architectural modifications
JSONB’s flexibility costs storage overhead (repeating attribute names) and limits query planner optimization. At small scale, negligible. At large scale, architectural constraints.
The bottom line: JSONB performance breaks down when storage bloat meets query planner limitations at large scale. The solution is rarely “more JSONB” - it’s “right-sizing the storage model” through hybrid approaches or architectural changes.
Final Words + More Resources
My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me
Here are also the most important links from this article along with some further resources that will help you in this scope:
- 👨💻 Reddit: Store JSON in RDBMS Discussion
- 👨💻 PostgreSQL JSONB Documentation
- 👨💻 PostgreSQL GIN Index Types
- 👨💻 When to Choose PostgreSQL Over MongoDB
Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!
Comments