Indexes

Postgres · Reference cheat sheet

📋 Overview

Indexes speed lookups and enforce uniqueness at the cost of write overhead and storage. Choose type based on query patterns (equality, range, JSON, text search).

🔧 Core concepts

💡 Examples

B-tree + partial:

CREATE INDEX CONCURRENTLY users_email_lower_idx
  ON users (lower(email));

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

JSONB GIN:

CREATE INDEX CONCURRENTLY events_payload_gin
  ON events USING GIN (payload jsonb_path_ops);

Find unused-ish indexes (heuristic):

SELECT * FROM pg_stat_user_indexes ORDER BY idx_scan NULLS FIRST LIMIT 20;

⚠️ Pitfalls

• Over-indexing slows writes and bloats storage.
• Non-concurrent index builds lock writes — use CONCURRENTLY in prod (with caveats).
• Function indexes only help when queries use the same expression.

🔗 Related

• explain_analyze.md
• vacuum_analyze.md
• json_jsonb.md
• migrations_ops.md