GIN, GiST, BRIN and hash indexes

conceptThe concept

GIN (generalized inverted index) maps each element (a jsonb key or value, an array element, a lexeme, a trigram) to the rows containing it. Great for "contains" queries; slower to update. GiST is a balanced tree of bounding predicates: ranges, geometry, nearest-neighbour (ORDER BY x <-> y) and exclusion constraints. SP-GiST suits space-partitioned data such as IP addresses or points.

BRIN (block range index) stores only a min/max summary per range of pages. It is tiny and effective only when values correlate with physical order, such as timestamps in an append-only log. The planner reads the matching page ranges and rechecks every row in them.

Hash indexes support only equality and are WAL-logged and crash-safe since 10; they can be smaller than a B-tree on long keys but cannot enforce uniqueness across multiple columns or support ranges or sorting, so B-tree is usually still the default choice.

whyWhy it matters

A 2 TB event table with a B-tree on its timestamp carries a B-tree of tens of gigabytes. A BRIN index on the same column can be a few megabytes and still prune almost everything for a one-day query.

Use caseReal-world scenario

The click-stream events table only ever grows in time order, so a BRIN index on at fits. Product attributes use GIN, and the maintenance calendar uses GiST for its exclusion constraint.

Try itExamples

Run and edit these in the interactive lesson.

SELECT tablename, attname, round(correlation::numeric, 3) AS correlation
FROM pg_stats
WHERE schemaname = 'public' AND attname IN ('at', 'created_at', 'customer_id')
ORDER BY tablename, attname;

BEGIN;
CREATE INDEX events_at_brin ON events USING brin (at) WITH (pages_per_range = 8);
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events WHERE at >= '2025-12-01' AND at < '2025-12-02';
SELECT pg_size_pretty(pg_relation_size('events_at_brin')) AS brin_size,
       pg_size_pretty(pg_relation_size('events_pkey')) AS btree_pk_size;
ROLLBACK;

BEGIN;
CREATE INDEX products_tags_gin ON products USING gin (tags);
SET LOCAL enable_seqscan = off;
EXPLAIN SELECT name FROM products WHERE tags @> '{eco,sale}';
ROLLBACK;

SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       i.indisunique AS is_unique
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;

internalsUnder the hood

pg_stats.correlation near 1 or -1 means physical order follows the column, which is what BRIN needs. Updates and deletes followed by inserts into freed space scramble the order and weaken BRIN over time. pages_per_range trades index size for precision.

Avoid / preferDo this, not that

CREATE INDEX ON products (attrs);
SELECT * FROM products WHERE attrs @> '{"brand": "Acme"}';

CREATE INDEX ON products USING gin (attrs jsonb_path_ops);

CREATE INDEX ON customers USING brin (email);

CREATE INDEX ON events USING brin (at);  -- physically ordered column

PitfallsCommon mistakes

• Using BRIN on a column whose values are scattered across the table. It will read most of the table anyway.
• Expecting a GIN index to support ordering or ranges. It answers membership questions only.
• Dropping an index because idx_scan is 0 on the primary while a replica uses it for reporting. Statistics are per server.

LimitsWhen not to use it

Hash indexes rarely beat B-tree in practice: they only support equality, cannot be unique on multiple columns, and offer no ordering. Choose them only after measuring.

SecuritySecurity notes

Indexes hold copies of the indexed values. Dropping unused indexes on sensitive columns reduces the places that data lives, and indexes on encrypted columns should be on hashes, not plaintext.

ChallengeTry it yourself

ANALYZE events;
SELECT attname, round(correlation::numeric, 3) AS correlation
FROM pg_stats
WHERE tablename = 'events'
ORDER BY abs(correlation) DESC NULLS LAST;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Index types
• BRIN indexes
• GIN indexes