Index types

PostgreSQL offers more index families than a default B-tree. Pick the type that matches the predicate, not the column name.

| Index type | Typical use | |------------|-------------| | B-tree (default) | Equality, range, ORDER BY on scalars | | GIN | JSONB containment, arrays, full-text | | GiST | Geometry, ranges, nearest-neighbor | | BRIN | Very large, naturally ordered tables (time series) | | Hash | Legacy equality-only; rarely beats B-tree today |

Partial indexes shrink write cost when queries always filter the same way:

					CREATE INDEX idx_orders_open ON orders (created_at)
  WHERE status = 'open';

CREATE INDEX idx_users_email_lower ON users (lower(email));
				

Composite index column order must follow equality filters first, then range/sort columns. Expression indexes help when the query applies a function to the column.

On interviews: name the access path (equality vs range vs containment), explain why GIN fits JSONB/@> but not every column, and mention index maintenance on writes.

Common pitfalls: indexing every column; using GIN on low-cardinality booleans; wrong composite column order; forgetting partial indexes for hot subsets.

The trade-off is read speed versus write amplification, storage, and planner surprises — fewer, targeted indexes usually beat a blanket index strategy.

Checklist:

• Match index family to predicate shape.
• Use partial indexes for stable filter subsets.
• Order composite keys by equality then range.
• Verify with EXPLAIN (ANALYZE, BUFFERS).