PostgreSQL supports several index types, each optimized for different kinds of queries and data structures. Here’s a breakdown:

1. B-tree (Default)

• Use for: Equality and range queries (=, <, >, BETWEEN, ORDER BY)

• Best for: Most use cases (numeric, text, date/time)

• Limitations: Not ideal for arrays, full-text, or geometric data

2. GIN (Generalized Inverted Index)

• Use for: Multi-valued columns (arrays, jsonb, full-text search)

• Best for: Full-text search (to_tsvector, @@), jsonb containment (@>), array matching

• Limitations: Slower to write, larger index size

3. GiST (Generalized Search Tree)

• Use for: Range types, geometric types, custom data types

• Best for:

• Spatial data (point, polygon, box)

• Fuzzy text search (via pg_trgm)

• Full-text search (alternative to GIN)

• Customizable: You can define your own GiST operator classes

4. SP-GiST (Space-partitioned GiST)

• Use for: Spatial or hierarchical partitioned data

• Best for: Data that can be divided (e.g., quadtrees, tries)

• Use case: IP address lookups, phone directories, routing

5. BRIN (Block Range Index)

• Use for: Very large tables where data is naturally ordered

• Best for: Time-series, log tables, append-only data

• Mechanism: Stores min/max values per block instead of every row

• Super lightweight, but approximate → ideal for big data

6. Hash

• Use for: Equality (=) comparisons only

• Best for: When you need fast exact-match lookups

• Caveats:

• Rarely used

• Not WAL-logged before PostgreSQL 10

• B-tree is often faster and more versatile

7. Expression Indexes

• Indexes on computed expressions:

CREATE INDEX idx_lower_name ON users (LOWER(name));

8. Partial Indexes

• Indexes only a subset of rows:

CREATE INDEX idx_active_users ON users (email) WHERE is_active = true;

Summary Table

Index Type Best For Examples

B-tree General purpose =, <, BETWEEN

GIN Full-text, JSONB, arrays @@, @>, ?

GiST Spatial, custom search pg_trgm, cube, geometric

SP-GiST Tree/partitioned data Prefix trees, ranges

BRIN Large, ordered data Timestamps, logs

Hash Exact matches WHERE id = 5

Partial Subsets WHERE active = true

Expression Transformed values LOWER(col)