Index Types (B-tree, GIN, GiST)

What are Index Types?

PostgreSQL supports multiple index types, each optimized for different query patterns and data types. Choosing the right index type can dramatically improve query performance.

Index Types

-- B-tree (default): equality and range queries
CREATE INDEX idx ON students(age);

-- GIN: full-text search, arrays, JSONB
CREATE INDEX idx ON articles USING gin(to_tsvector('english', body));

-- GiST: geometric data, range types, full-text search
CREATE INDEX idx ON events USING gist(event_period);

-- BRIN: large, naturally ordered tables
CREATE INDEX idx ON logs USING brin(created_at);

-- Hash: equality-only lookups
CREATE INDEX idx ON students USING hash(email);

When to Use Each

• B-tree: Default. Works for =, <, >, <=, >=, BETWEEN, IN, IS NULL.
• GIN: Full-text search, JSONB containment (@>), array operations (&&, @>).
• GiST: Geometric/spatial queries, range overlaps (&&), nearest-neighbor.
• BRIN: Very large append-only tables (logs, time-series) where data is physically ordered.
• Hash: Pure equality checks (=). Smaller than B-tree but less flexible.

Key Points

1. B-tree Default — If you do not specify USING, PostgreSQL creates a B-tree.
2. GIN for JSONB — Essential for fast JSONB containment queries.
3. GiST for Ranges — Enables exclusion constraints (prevent overlapping bookings).
4. BRIN is Tiny — BRIN indexes are very small, ideal for huge time-series tables.
5. Partial Indexes — Any type can be partial: CREATE INDEX ... WHERE condition.