Come Ottimizzare le Query PostgreSQL: Guida Pratica per Backend Developer

Introduzione: Il Costo Silenzioso delle Query Lente

Una singola query lenta può trasformare un'applicazione veloce in una che frustre gli utenti. Nel mio lavoro come Database Architect, ho riscontrato che il 70% dei problemi di performance proviene da query mal ottimizzate, non dalla mancanza di RAM o CPU.

In questo articolo condivido le tecniche che ho utilizzato per identificare e correggere query lente in sistemi con milioni di righe, riducendo i tempi di risposta da secondi a millisecondi.

Perché le Query Lente Distruggono le Applicazioni

Una query che impiega 5 secondi non sembra drammatica finché:

• Un utente apre la stessa pagina 10 volte = 50 secondi sprecati
• 100 utenti concorrenti = database in deadlock
• Cumulo di query lente = OOM (Out of Memory) e crash
• Reputazione del brand = danneggiata

Step 1: EXPLAIN ANALYZE - Lo Strumento Più Potente

EXPLAIN ANALYZE è il tuo migliore amico. Ti mostra esattamente come PostgreSQL esegue una query, rivelando dove si nascondono i colli di bottiglia.

EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name;

Output tipico:

GroupAggregate  (cost=1000.00..2000.00 rows=5000)
  ->  Sort  (cost=1000.00..1200.00 rows=50000)
        ->  Hash Join  (cost=100.00..800.00 rows=50000)
              ->  Seq Scan on users u  (cost=0.00..200.00 rows=10000)
              ->  Hash  (cost=50.00..50.00 rows=1000)
                    ->  Seq Scan on orders o  (cost=0.00..50.00)

Cosa notare: "Seq Scan" = scansione sequenziale (lento). Significa che PostgreSQL sta leggendo ogni riga per trovare quella che vuoi. Se vedi Seq Scan dove non dovrebbe esserci, hai trovato il problema.

Step 2: Indici - Quando Usarli e Quando Evitarli

Gli indici sono come un indice di un libro: non leggi tutte le 500 pagine per trovare un capitolo, consulti l'indice.

Crea indici su:

• Colonne usate in WHERE (filtri)
• Colonne usate in JOIN ON
• Colonne usate in ORDER BY
• Colonne usate in GROUP BY

-- Indice semplice su user_id
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Indice composito: migliore per query multi-colonna
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

Evita indici su:

• Colonne con pochi valori univoci (boolean, status)
• Colonne raramente filtrate
• Colonne piccole (short strings)

Step 3: Query N+1 - Il Nemico Silenzioso

Questo è il problema più comune nel codice backend:

// SBAGLIATO: N+1 query
users = User.all()  // 1 query
users.each do |user|
  puts user.orders   // N query (1 per utente)
end
// Totale: 1 + N query

// CORRETTO: Join o preload
users = User.includes(:orders)  // 1 query con LEFT JOIN
users.each do |user|
  puts user.orders   // Nessuna query aggiuntiva
end

Step 4: Partitioning per Tabelle Grosse

Quando la tua tabella ha milioni di righe, anche gli indici iniziano a fatica. La soluzione è il partitioning: dividi la tabella in pezzi più piccoli.

-- Partionamento per range (data)
CREATE TABLE orders (
  id BIGINT,
  user_id INT,
  created_at TIMESTAMP,
  amount DECIMAL
) PARTITION BY RANGE (YEAR(created_at));

CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

PostgreSQL cercherà automaticamente nella partizione corretta, escludendo le altre. Dramma ridotto da milioni a centinaia di migliaia di righe.

Step 5: Checklist Finale di Ottimizzazione

Conclusione

L'ottimizzazione delle query non è magia: è metodo sistematico. EXPLAIN ANALYZE è il tuo miglior investigatore, gli indici sono la tua soluzione veloce, e il partitioning è la tua arma per i big data.

Se applichi anche solo 3 di queste tecniche, vedrai miglioramenti drammatici. Se le applichi tutte, il tuo database volerà.