Indici în PostgreSQL — când îi pui și ce tip alegi fără să ghicești

Indici prost puși fac queryurile mai lente. Indici corecți le fac de 10-100x mai rapide. Iată cum îmi aleg:

Regula 0: nu pui index înainte să ai problemă

pg_stat_statements + EXPLAIN ANALYZE îți arată exact query-urile lente. Optimizează acolo, nu preventiv.

BTREE (default)

• Pentru =, <, >, BETWEEN, ORDER BY
• 99% din cazuri
• Nu pui BTREE pe coloane cu 2-3 valori distincte (ex: isActive bool) — Postgres va face Seq Scan oricum

GIN

• Pentru jsonb, array-uri, text search
• Index pe jsonb permite where data->>'email' = '...' rapid
• Mai mare pe disk, mai lent la INSERT/UPDATE — dar read-ul e instant

BRIN (Block Range)

• Pentru tabele huge ordonate natural (ex: log-uri cu createdAt crescător)
• Ocupă spațiu de 1000x mai puțin decât BTREE
• Nu e bun pentru random writes

Partial index

• Gold mining pentru tabele cu 90% din rânduri "inactive"
• CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL
• Index mult mai mic, queryurile filtrate sunt instant

Composite index — ordinea contează

(user_id, created_at DESC) NU e același cu (created_at DESC, user_id).

Regula: filtrele egale întâi, range/sort la final. Dacă queryul e WHERE user_id = X ORDER BY created_at DESC, primul e câștigător clar.

Cum verific dacă indexul e folosit

EXPLAIN ANALYZE SELECT ... → vezi "Index Scan using idx_name" sau "Bitmap Index Scan". Dacă vezi "Seq Scan" pe o tabelă mare → ori lipsă index, ori Postgres a decis că e mai rapid fără (pe tabele mici, da).

Verificare periodică

pg_stat_user_indexes arată care indici sunt folosiți (idx_scan > 0) și care dorm degeaba — îi poți șterge.