Index Types: B-tree, Hash, GIN, GiST, BRIN

In one line

B-tree is the default and handles equality, ranges, sorting and prefix LIKE. Hash handles equality only. GIN indexes the elements inside values (JSONB keys, array elements, full-text lexemes). GiST supports ranges, geometry and nearest-neighbour. BRIN stores min/max per block range and is tiny, which suits huge append-only tables where values correlate with physical order.

0/5 · 0%

Think of it like this

Different ways to find things in a library. A sorted card catalogue (B-tree), a locker number lookup (hash), a subject index listing every book mentioning each keyword (GIN), a map of which shelves cover which regions (GiST), and a sign per aisle saying "books from 1990–1995" (BRIN).

Key ideas

01
B-tree supports =, <, <=, >, >=, BETWEEN, IN, IS NULL, ORDER BY in index order, and LIKE 'abc%' (with text_pattern_ops or C collation). It does not help LIKE '%abc' or functions on the column unless indexed as an expression.
02
Hash: equality only, slightly smaller for long keys; rarely better than B-tree in practice, and WAL-logged and crash-safe only since PG 10.
03
GIN: an inverted index from each element to the rows containing it. Used for jsonb @>, array && / @>, full-text @@, and trigram similarity (pg_trgm, which makes LIKE '%abc%' indexable). Fast reads, slower writes (the pending list helps; fastupdate).
04
GiST: a balanced tree of bounding predicates: range overlap (&&), exclusion constraints, PostGIS geometry, nearest-neighbour ORDER BY location <-> point. SP-GiST suits partitioned spaces (quadtrees, IP prefixes).
05
BRIN: one small summary per range of pages (default 128). A 1 TB time-ordered events table can have a BRIN on created_at of a few MB. It works only if the column correlates with physical order (check pg_stats.correlation).

Code & diagrams

index-types.sqlsql

CREATE INDEX orders_customer_created ON orders (customer_id, created_at DESC);    -- B-tree
CREATE INDEX product_attrs_gin ON product USING gin (attrs jsonb_path_ops);         -- GIN
CREATE EXTENSION pg_trgm;
CREATE INDEX customer_name_trgm ON customer USING gin (name gin_trgm_ops);          -- %abc% search
CREATE INDEX booking_stay_gist ON booking USING gist (stay);                         -- range overlap
CREATE INDEX events_created_brin ON events USING brin (created_at);                  -- tiny, for huge tables

SELECT pg_size_pretty(pg_relation_size('events_created_brin'));  -- 312 kB on a 400 GB table
SELECT correlation FROM pg_stats WHERE tablename = 'events' AND attname = 'created_at';  -- 0.998

Interview problem

The problem

Pick indexes for five queries

(1) Products where attrs @> '{"brand":"acme"}'; (2) customers whose name contains "shar"; (3) events in the last hour from a 2 TB append-only table; (4) rooms with bookings overlapping a date range; (5) orders by lower(email).

When it breaks

BRIN on a column that isn't physically ordered

What you see

Block ranges all span the whole value range, so BRIN excludes nothing and the planner scans everything anyway.

Fix & prevent

Check pg_stats.correlation (near ±1); use B-tree or partitioning when data arrives out of order.

Explain it without notes

How does a GIN index differ from a B-tree?

Practice

Make WHERE email ILIKE 'amit%' indexable.

Trade-offs

↔
Specialised indexes unlock query types but cost more on writes and are harder to reason about; B-tree covers most needs.

Done when you can

I can match an index type to an operator and data distribution.

Command Palette