GIN Indexes for JSONB and Full-Text

~12 min · indexes, gin

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

GIN — Generalised Inverted iNdex

B-tree indexes one value per row. GIN indexes many values per row — the words inside a text field, the keys inside a JSONB document, the elements inside an array. The 'inverted' part means: for each value, GIN stores the list of rows that contain it (just like a book index).

JSONB containment

The killer GIN use case for JSONB is the containment operator @>: "rows whose JSONB contains this sub-document." Without a GIN index, every row is parsed and checked. With one, it's a direct lookup.

Full-text search

GIN on a tsvector column lets PostgreSQL answer "rows whose document contains these words" in milliseconds across millions of articles — no Elasticsearch needed.

Code

GIN index on JSONB·sql

CREATE TABLE events (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data JSONB NOT NULL
);

-- General-purpose GIN: any @> query benefits
CREATE INDEX events_data_gin ON events USING gin (data);

-- Faster, smaller GIN: only path-ops queries
CREATE INDEX events_data_path_gin ON events USING gin (data jsonb_path_ops);

SELECT *
FROM events
WHERE data @> '{"type":"click"}';

GIN for full-text search·sql

ALTER TABLE articles
ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

CREATE INDEX articles_tsv_gin ON articles USING gin (tsv);

SELECT id, title
FROM   articles
WHERE  tsv @@ plainto_tsquery('english', 'postgres performance')
ORDER  BY ts_rank(tsv, plainto_tsquery('english', 'postgres performance')) DESC
LIMIT  10;

GIN for arrays·sql

CREATE INDEX products_tags_gin ON products USING gin (tags);

-- Find all products tagged with both 'red' AND 'wool'
SELECT * FROM products WHERE tags @> ARRAY['red', 'wool'];

-- Or with any of these tags
SELECT * FROM products WHERE tags && ARRAY['red', 'blue'];

External links

Exercise

Add a GIN index to a JSONB column you query with @>. Compare EXPLAIN before and after. Then add a tsvector + GIN to a text column and run a full-text query.