Come una colonna non indicizzata ha ucciso il nostro database
Un singolo indice mancante ha trasformato un'API da 8 ms in un incubo di 8 secondi.
Abbiamo eseguito un test di carico prima di un rilascio importante. In ambiente di sviluppo locale, 100 righe non erano un problema. In staging, abbiamo simulato 500 utenti su 1,5 milioni di righe.
Tutto è saltato.
- I tempi di risposta dell'API sono passati da 45 ms a 8.000 ms.
- La CPU del database ha raggiunto il 100%.
- I pool di connessione si sono esauriti.
- Le richieste sono andate in timeout.
Il colpevole era una semplice query che recuperava la cronologia degli ordini tramite user_id e status.
Eseguendo EXPLAIN ANALYZE su PostgreSQL è emerso un Sequential Scan. Senza un indice su user_id, il motore ha letto tutti i 1,5 milioni di righe per ogni richiesta.
Con 100 richieste simultanee, il database ha scansionato 150 milioni di righe in un colpo solo.
La soluzione ha richiesto cinque minuti. Abbiamo creato un indice composto su (user_id, status, created_at DESC) utilizzando CREATE INDEX CONCURRENTLY per mantenere la tabella online.
Risultati:
- Il tempo di esecuzione della query è sceso da 8.150 ms a 0,14 ms.
- La latenza dell'API è scesa da 8 secondi a 12 ms.
- L'utilizzo della CPU è sceso dal 100% a meno dell'8%.
Lezioni imparate:
- I test locali possono trarre in inganno; 100 righe non sono un buon indicatore per un milione.
- Indicizza le chiavi esterne: la maggior parte degli ORM non lo fa.
- Esegui
EXPLAIN ANALYZE: il database ti indica il problema. - Ottimizza le query prima di acquistare altro hardware.
Non scalare prima i server. Scala le query.