PostgreSQL: ottimizzazione query per applicazioni real-world

PostgreSQL in produzione

PostgreSQL è il mio database preferito per progetti seri. Ma senza ottimizzazione, anche il database migliore può diventare un collo di bottiglia.

EXPLAIN ANALYZE: il tuo migliore amico

Prima di ottimizzare, devi capire. EXPLAIN ANALYZE mostra esattamente cosa fa PostgreSQL:

1EXPLAIN ANALYZE 
2SELECT * FROM users 
3WHERE email = 'test@example.com';

Cosa cercare:

• Seq Scan: Scansione sequenziale (spesso problematica)
• Index Scan: Uso di indice (generalmente buono)
• Actual time: Tempo reale di esecuzione

Indici: quando e quali

Indici B-tree (default)

Perfetti per: uguaglianza, range, ordinamento

1CREATE INDEX idx_users_email ON users(email);
2CREATE INDEX idx_orders_date ON orders(created_at);

Indici parziali

Per query su subset di dati:

1CREATE INDEX idx_active_users 
2ON users(email) 
3WHERE status = 'active';

N+1 queries: il killer silenzioso

Il problema classico degli ORM:

1// ❌ N+1: 1 query + N query per i post
2const users = await User.findAll();
3for (const user of users) {
4  const posts = await user.getPosts();
5}
6
7// ✅ Eager loading: 2 query totali
8const users = await User.findAll({
9  include: [{ model: Post }]
10});

Connection pooling

Non sottovalutare il connection pooling. Con PgBouncer o il pooling integrato:

• Riduci overhead di connessione
• Gestisci più richieste concurrent
• Eviti "too many connections"

Conclusione

L'ottimizzazione database è un'arte. Misura prima, ottimizza dopo, e ricorda: l'indice migliore è quello che non devi creare perché la query è già efficiente.