---
title: "JSONB Indexing: GIN vs Expression Indexes for Mobile APIs"
published: true
description: "Learn why your PostgreSQL JSONB queries hit sequential scans at scale. Compare GIN, trigram, and expression indexes with EXPLAIN ANALYZE walkthroughs for mobile backends."
tags: postgresql, api, performance, mobile
canonical_url: https://blog.mvpfactory.co/jsonb-indexing-gin-vs-expression-indexes-for-mobile-apis
---
## What We Will Build
In this walkthrough, I will show you exactly why your GIN index is not doing what you think it is, and how to fix it. We will run `EXPLAIN ANALYZE` against a 2M-row table, compare three indexing strategies side by side, and land on a hybrid model you can migrate to without downtime.
By the end, you will know how to audit your JSONB queries, pick the right index type for each access pattern, and avoid the TOAST decompression trap that silently kills p99 latency.
## Prerequisites
- PostgreSQL 14+ (examples work through PG 17)
- A table with a JSONB column and at least 100K rows
- Access to `EXPLAIN ANALYZE`
- The `pg_trgm` extension (for fuzzy search)
## Step 1: Understand What GIN Actually Indexes
A GIN index builds a posting tree — a B-tree of keys mapped to sorted row pointer lists. For JSONB, each key-value pair becomes an entry. Here is the gotcha that will save you hours: **GIN supports containment operators (`@>`, `?`, `?|`, `?&`), not extraction operators (`->>`, `->`, `#>>`).**
| Query Pattern | Uses GIN? | Operator |
|---|---|---|
| `WHERE metadata @> '{"status":"active"}'` | Yes | `@>` containment |
| `WHERE metadata->>'status' = 'active'` | **No** | `->>` extraction |
| `WHERE metadata ? 'status'` | Yes | `?` key existence |
| `WHERE metadata->>'name' LIKE '%john%'` | **No** | `->>` + LIKE |
## Step 2: See the Difference With EXPLAIN ANALYZE
Let me show you the same data, same GIN index, two operators — 100x difference:
sql
-- This IGNORES your GIN index:
EXPLAIN ANALYZE SELECT * FROM events
WHERE metadata->>'status' = 'active';
-- Seq Scan on events (cost=0.00..285431.00 rows=10000 width=312)
-- Rows Removed by Filter: 1990000
-- Execution Time: 1842.331 ms
-- Rewrite with containment:
EXPLAIN ANALYZE SELECT * FROM events
WHERE metadata @> '{"status": "active"}';
-- Bitmap Index Scan on idx_events_metadata_gin
-- Execution Time: 18.442 ms
## Step 3: Add Expression Indexes for Hot Paths
For high-frequency equality lookups on known paths, expression indexes crush GIN:
sql
CREATE INDEX idx_events_status ON events ((metadata->>'status'));
EXPLAIN ANALYZE SELECT * FROM events
WHERE metadata->>'status' = 'active';
-- Index Scan using idx_events_status
-- Execution Time: 4.218 ms
Four milliseconds. No query rewrite needed. The tradeoff is one index per path, but honestly, 80% of JSONB queries in most mobile backends hit fewer than five paths. You probably know which fields they are right now.
## Step 4: Add Trigram Indexes for LIKE Queries
When your mobile app needs fuzzy search inside JSONB text fields:
sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_events_name_trgm ON events
USING GIN ((metadata->>'name') gin_trgm_ops);
SELECT * FROM events WHERE metadata->>'name' LIKE '%john%';
-- Bitmap Index Scan on idx_events_name_trgm
-- Execution Time: 12.108 ms (vs 1900ms sequential)
## Step 5: Migrate to a Hybrid Model
Here is the minimal setup to get this working without downtime:
sql
ALTER TABLE events ADD COLUMN status TEXT;
ALTER TABLE events ADD COLUMN event_type TEXT;
-- Backfill in batches of 10K-50K rows:
UPDATE events SET status = metadata->>'status',
event_type = metadata->>'type'
WHERE id BETWEEN $1 AND $2;
CREATE INDEX CONCURRENTLY idx_events_status_norm ON events (status);
Then update your application to write both paths, migrate reads, and optionally trim extracted keys from JSONB to reduce TOAST overhead.
## Gotchas
**The TOAST problem nobody talks about.** PostgreSQL TOAST-compresses any value exceeding ~2KB. Wide JSONB rows — common when teams dump entire API responses — mean every row access triggers decompression:
| JSONB Row Size | Avg Latency | p99 Latency |
|---|---|---|
| 500 bytes | 0.08 ms | 0.4 ms |
| 4 KB (TOASTed) | 0.31 ms | 2.1 ms |
| 32 KB (TOASTed) | 1.42 ms | 18.7 ms |
Even with a perfect index, fetching the row decompresses the entire JSONB value. The `jsonpath` expressions in PostgreSQL 17 (`@@` operator) improve predicate pushdown but do not eliminate TOAST overhead on wide rows.
**Check your exposure:** `SELECT avg(pg_column_size(metadata)) FROM your_table`. If it exceeds 2KB, your p99 is paying a decompression tax on every read.
**The `->>` trap.** Grep your codebase for `->>` used with GIN-indexed JSONB columns. Every one of those is a sequential scan waiting to happen. Rewrite to `@>` or add expression indexes.
**Always use `CREATE INDEX CONCURRENTLY`** — it will not lock your table during index creation.
## Conclusion
Keep JSONB for genuinely unstructured data — user preferences, feature flags, third-party webhook payloads. Normalize any field that appears in a `WHERE`, `JOIN`, or `ORDER BY` clause. The migration is mechanical and you can do it without downtime. Start by auditing your queries today — the docs do not mention this, but a single operator swap can mean the difference between 4ms and 1842ms.
Top comments (0)