PostgreSQL JSONB Query Optimization with GIN Index

Problem

Stored user settings in a JSONB column, but querying by specific keys triggers a full table scan.

-- metadata column is JSONB
SELECT * FROM users
WHERE metadata->>'role' = 'admin';
-- Seq Scan... slow

Solution

A GIN index enables indexed lookups inside JSONB data.

-- Create GIN index
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- Use @> containment operator (uses GIN index)
SELECT * FROM users
WHERE metadata @> '{"role": "admin"}';

If you only query specific keys, an expression index is more efficient.

-- B-tree index on a specific path
CREATE INDEX idx_users_role ON users ((metadata->>'role'));

-- This query now uses the index
SELECT * FROM users
WHERE metadata->>'role' = 'admin';

Nested JSON is accessible via path operators.

-- Access nested keys
SELECT * FROM users
WHERE metadata #>> '{address,city}' = 'Seoul';

-- jsonpath also works (PostgreSQL 12+)
SELECT * FROM users
WHERE metadata @? '$.tags[*] ? (@ == "vip")';

Key Points

Use the @> containment operator to leverage GIN indexes. The ->> operator requires an expression index instead
GIN indexes slow down writes — best suited for read-heavy tables
Always use JSONB over JSON. JSONB stores data in binary format, making searches significantly faster