TopolearnWhen B-tree can't serve a query, choosing the right access method is the difference between seconds and milliseconds.
PostgreSQL has several index access methods, each with operator classes that define which operators it accelerates.
- GIN (generalized inverted index) maps each element — array item, jsonb key/value, text-search lexeme, trigram — to the rows containing it. Use it for
@>,?,&&and full-text@@. For jsonb, thejsonb_path_opsclass is smaller and faster for@>but doesn't support?. GIN is slower to update; thefastupdatepending list batches inserts. - GiST (generalized search tree) indexes things that overlap or have distance: geometry (PostGIS), ranges, nearest-neighbour
ORDER BY point <-> $1. It powers exclusion constraints. - SP-GiST suits space-partitioned data like points and IP prefixes.
- BRIN (block range index) stores only min/max per range of pages. It's tiny — kilobytes for a huge table — and works when values correlate with physical order, like
created_atin an append-only log. - Hash: equality only.
The pg_trgm extension adds trigram operator classes for GIN/GiST, making LIKE '%term%', ILIKE and similarity search indexable — the usual answer to "my contains-search is slow".
Check which operators an index supports before creating it, and confirm with EXPLAIN that queries use it. Extensions such as pgvector add further methods (HNSW, IVFFlat) for vector similarity.
Example
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE doc (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text, created_at timestamptz NOT NULL);
INSERT INTO doc (title, created_at)
SELECT 'report ' || md5(g::text), '2026-01-01'::timestamptz + g * interval '1 min'
FROM generate_series(1, 200000) g;
CREATE INDEX doc_title_trgm ON doc USING gin (title gin_trgm_ops);
CREATE INDEX doc_created_brin ON doc USING brin (created_at);
ANALYZE doc;
EXPLAIN SELECT id FROM doc WHERE title ILIKE '%abc12%';
-- Bitmap Index Scan on doc_title_trgm
EXPLAIN SELECT count(*) FROM doc
WHERE created_at BETWEEN '2026-02-01' AND '2026-02-02';
-- Bitmap Index Scan on doc_created_brin
SELECT pg_size_pretty(pg_relation_size('doc_created_brin')); -- e.g. 24 kBRemember
- GIN for containment in jsonb, arrays, text search and trigrams
- GiST for ranges, geometry, nearest neighbour and exclusion constraints
- BRIN is tiny and great for naturally ordered, append-only data
Watch out for
- Creating BRIN on a column whose values are randomly distributed across the table.