Postgres JSONB Indexing
Improve lookup performance inside PostgreSQL JSONB structures with appropriate index declarations.
Querying deep fields within a PostgreSQL JSONB column without an index will result in a sequential table scan. For high-performance queries, declare a GIN (Generalized Inverted Index) or an expression index.
Use a GIN index when querying keys at arbitrary depths, or a functional index for specific fields:
-- GIN index for checking arbitrary key-value pairs:
CREATE INDEX idx_users_metadata_gin ON users USING gin (metadata);
-- Functional B-Tree index for a specific nested attribute:
CREATE INDEX idx_users_metadata_country ON users (((metadata->>'country')));With these indexes in place, querying fields via the containment operator (@>) or JSON extraction operators (->>) becomes significantly faster.