Database Indexing

Why indexes?

Indexes accelerate lookups and joins by avoiding full table scans. The cost is write overhead and space. The art is picking the right few that serve most queries.

Structures

B‑Tree (general purpose): equality and range.
Hash (equality only): limited in Postgres.
Bitmap (analytics): great for low‑cardinality columns.
GIN/GiST: full‑text, arrays/JSONB, geospatial.

Selectivity and order

Put the most selective column first in composite indexes.
Align index order with WHERE and ORDER BY to allow index‑only scans.

Covering indexes

Include all referenced columns to avoid heap fetches.

-- language-sql
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC) INCLUDE (status, total_cents);

Sargability

Make predicates index‑friendly: avoid functions on the indexed column (LOWER(col)), ORs over different columns, wildcard prefixes.

Query planning

-- language-sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE user_id = $1 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

JSON/array indexing

-- language-sql
CREATE INDEX idx_users_attrs_tier ON users USING GIN ((attributes->>'tier'));
SELECT * FROM users WHERE attributes->>'tier' = 'pro';

Maintenance

Monitor bloat and vacuum; set appropriate fillfactor for heavy updates.
Rebuild rarely; prefer REINDEX CONCURRENTLY when needed.

Anti‑patterns

Indexing every column. Bloats writes and memory.
Ignoring write paths: hot tables with too many indexes will slow down.

Analogy

An index is like a well‑organized table of contents. The wrong TOC (wrong order or too many entries) can be slower than flipping pages.

FAQ

Why is my index unused? Query shape doesn’t match, or planner estimates prefer a scan. Check stats.
Are partial indexes worth it? Yes, for hot subsets: WHERE status = 'ACTIVE'.

Try it

Use pg_stat_statements to find top queries; add one index and re‑measure p95 before adding more.

Structures

B-Tree (généraliste), Hash (égalité), Bitmap (analytique), GIN/GiST (full-text/JSON/géo).

Principes

Haute sélectivité, colonnes de filtre en tête, index couvrant (INCLUDE).
Éviter les fonctions non sargables; utiliser colonnes dérivées si besoin.
Maintenance: vacuum/analyze, fillfactor, reindex périodique.

*Database Indexing