PostgreSQL index types and when to use each

Need to choose the right index type for different query patterns: equality, range, text search, JSON, spatial.

PostgreSQL index types guide:

-- B-TREE (default): Equality and range queries
CREATE INDEX idx_users_email ON users (email);
-- Good for: =, <, >, <=, >=, BETWEEN, IN, IS NULL
-- Good for: ORDER BY, GROUP BY
-- Most common, works for 90% of cases

-- Composite index (leftmost prefix rule)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
-- Satisfies: WHERE user_id = ? AND created_at > ?
-- Satisfies: WHERE user_id = ?
-- Does NOT satisfy: WHERE created_at > ? (no leftmost column)

-- HASH: Equality only (rare to use explicitly)
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);
-- Only for: =
-- Smaller than b-tree for equality-only lookups

-- GIN: Full-text search, arrays, JSONB
CREATE INDEX idx_docs_search ON documents USING GIN (to_tsvector('english', content));
-- Full-text search queries

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
-- Array containment: WHERE tags @> ARRAY['python']

CREATE INDEX idx_data_meta ON data USING GIN (metadata jsonb_path_ops);
-- JSONB containment: WHERE metadata @> '{"type": "alert"}'

-- GiST: Range types, geometric, nearest-neighbor
CREATE INDEX idx_events_period ON events USING GIST (tsrange(start_at, end_at));
-- Range overlap: WHERE tsrange(start_at, end_at) && tsrange(?, ?)

-- BRIN: Very large tables with naturally ordered data
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);
-- Tiny index for time-series data
-- 1000x smaller than b-tree, good for append-only tables

-- Partial index: Index subset of rows
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';
-- Much smaller, only indexes pending orders

-- Expression index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- For: WHERE LOWER(email) = 'user@example.com'

The right index can make a query 1000x faster. The wrong index wastes disk space and slows writes. Understanding index types prevents both under- and over-indexing.

PostgreSQL query optimization