Indexes and Query PerformanceLesson 4.3

PostgreSQL index types: Hash, GIN, GiST, BRIN — when to use each

Hash index equality, GIN for arrays JSONB full-text, GiST for geometric and range types, BRIN for sequential data, partial indexes, expression indexes

Specialized Index Types

B-tree handles most cases. These four types solve problems B-tree cannot.

Hash

CREATE INDEX idx_hash_email ON users USING HASH (email);

Faster than B-tree for pure equality lookups. Cannot support range queries or sorting.

GIN (Generalized Inverted Index)

-- For JSONB
CREATE INDEX idx_gin_meta ON products USING GIN (metadata);

-- For full-text search
CREATE INDEX idx_gin_fts ON articles
  USING GIN (to_tsvector('english', body));

GIN indexes each element inside a composite value (array, JSONB keys, text lexemes). Use for @>, ?, @@ operators.

GiST

CREATE INDEX idx_gist_range ON bookings
  USING GiST (daterange(start_date, end_date));

Use for range types, PostGIS geometry, and nearest-neighbor searches.

BRIN (Block Range Index)

CREATE INDEX idx_brin_created ON events
  USING BRIN (created_at);

Stores min/max per block range. Tiny size, useful for naturally-ordered append-only tables (logs, events). Useless for random data.

Partial and expression indexes

CREATE INDEX idx_active_users ON users(email) WHERE active = true;
CREATE INDEX idx_lower_email ON users(LOWER(email));