PostgreSQL: Trova la Query Killer con pg_stat_statements

Quando un’applicazione critica rallenta, il database è spesso il primo imputato. Indagare la causa di un rallentamento in PostgreSQL senza gli strumenti adeguati è come cercare un ago in un pagliaio, con il rischio di compromettere ulteriormente la stabilità del sistema in produzione. La capacità di identificare rapidamente le query problematiche è cruciale per mantenere le performance e la disponibilità del servizio. L’estensione pg_stat_statements di PostgreSQL offre una soluzione robusta e dettagliata per monitorare e analizzare le performance delle query SQL, permettendo ai DBA e agli sviluppatori di individuare con precisione i colli di bottiglia e di intervenire con ottimizzazioni mirate.

Testato su: PostgreSQL 16.3 · Ubuntu 24.04 LTS · Luglio 2026

Prerequisiti / Ambiente di test

Per utilizzare pg_stat_statements, è necessario un’installazione funzionante di PostgreSQL. L’estensione è disponibile di default nella maggior parte delle distribuzioni, ma richiede di essere abilitata. Assicurati di avere i permessi di superuser per modificare il file di configurazione postgresql.conf e per creare l’estensione all’interno dei database. Per i test, ho utilizzato una macchina virtuale Ubuntu 24.04 LTS con PostgreSQL 16.3 installato da repository ufficiali, simulando un carico di lavoro con pgbench.

Abilitare pg_stat_statements

L’abilitazione di pg_stat_statements richiede due passaggi principali: la modifica del file di configurazione di PostgreSQL e la creazione dell’estensione all’interno dei database desiderati.

Per prima cosa, individua il file postgresql.conf. La sua posizione può variare a seconda della distribuzione e del metodo di installazione. Su sistemi basati su Debian/Ubuntu, si trova tipicamente in /etc/postgresql//main/postgresql.conf.

Apri il file con un editor di testo e aggiungi pg_stat_statements alla direttiva shared_preload_libraries. Se la direttiva è già presente, aggiungi pg_stat_statements separato da una virgola, come mostrato nell’esempio:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Dopo aver modificato il file, è obbligatorio riavviare il servizio PostgreSQL affinché le modifiche abbiano effetto. Senza riavvio, l’estensione non verrà caricata.

sudo systemctl restart postgresql

Una volta riavviato il servizio, connettiti al tuo database (o al database postgres predefinito) e crea l’estensione:

psql -U postgres -d your_database_name

CREATE EXTENSION pg_stat_statements;

Questo comando rende la vista pg_stat_statements disponibile per l’interrogazione. È consigliabile creare l’estensione in ogni database che si desidera monitorare, anche se le statistiche vengono raccolte a livello globale dal server.

Individuare la query che sta uccidendo il server

Con pg_stat_statements abilitato, puoi iniziare a interrogare la vista per identificare le query problematiche. La vista pg_stat_statements contiene diverse colonne utili, tra cui query (il testo della query), calls (il numero di volte che la query è stata eseguita), total_exec_time (il tempo totale speso per l’esecuzione della query in millisecondi) e mean_exec_time (il tempo medio di esecuzione per chiamata in millisecondi).

Per trovare le query che hanno consumato più tempo complessivo, ordina per total_exec_time in ordine decrescente:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Questo ti mostrerà le 10 query più “costose” in termini di risorse. Una query con un total_exec_time elevato potrebbe indicare un’operazione che impiega molto tempo per completarsi o che viene eseguita molto frequentemente. Leggi anche: Elasticsearch OOM: Cluster RED per Shard Sbagliato

Se invece vuoi identificare query che sono lente nella singola esecuzione, ma magari non vengono chiamate spessissimo, puoi ordinare per mean_exec_time:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

È anche utile filtrare per calls per vedere quali query vengono eseguite con maggiore frequenza. Una query anche veloce, se chiamata milioni di volte, può saturare il server:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Analizzando queste metriche, puoi rapidamente circoscrivere le query che stanno impattando maggiormente le performance del tuo server PostgreSQL.

Analisi e ottimizzazione delle query

Una volta identificata una query problematica, il passo successivo è analizzarla in dettaglio e ottimizzarla. Lo strumento principale per questo è EXPLAIN ANALYZE. Leggi anche: LVM Snapshot: Rollback Sicuro per Aggiornamenti Critici

EXPLAIN ANALYZE esegue la query e mostra il piano di esecuzione effettivo, inclusi i tempi di esecuzione per ogni fase. Questo ti permette di capire dove il database sta impiegando più tempo, ad esempio a causa di scan sequenziali su tabelle grandi, join inefficienti, o mancanza di indici appropriati.

EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'value';

L’output di EXPLAIN ANALYZE può essere complesso, ma cerca nodi come Seq Scan, Hash Join su tabelle molto grandi, o tempi elevati in fasi specifiche. La soluzione spesso risiede nella creazione di indici (CREATE INDEX), nella riscrittura della query per renderla più efficiente, o nell’ottimizzazione della struttura del database.

Errori comuni e troubleshooting

• pg_stat_statements non abilitato: Il problema più comune è dimenticare di riavviare il servizio PostgreSQL dopo aver modificato shared_preload_libraries o di non aver eseguito CREATE EXTENSION. Controlla i log di PostgreSQL (/var/log/postgresql/postgresql--main.log) per eventuali errori al riavvio.
• Estensione non disponibile: Se CREATE EXTENSION pg_stat_statements; fallisce, potrebbe significare che l’estensione non è installata. Su Debian/Ubuntu, assicurati che il pacchetto postgresql-contrib- sia installato.

    sudo apt install postgresql-contrib-16

• Statistiche non aggiornate: Le statistiche vengono aggiornate in tempo reale, ma se hai appena abilitato l’estensione o resettato le statistiche, potrebbe volerci un po’ di tempo prima che vengano raccolti dati significativi. Assicurati che ci sia un carico di lavoro attivo sul database.
• Query troncate: La colonna query in pg_stat_statements ha una lunghezza massima configurabile (pg_stat_statements.max_query_len). Se le tue query sono molto lunghe, potrebbero apparire troncate. Puoi aumentare questo valore in postgresql.conf (richiede riavvio).

    # postgresql.conf
    pg_stat_statements.max_query_len = 2048 # Valore in byte

FAQ — Domande Frequenti

pg_stat_statements ha un impatto sulle performance?

Sì, pg_stat_statements introduce un overhead minimo, poiché deve raccogliere e memorizzare statistiche per ogni query eseguita. Tuttavia, per la maggior parte degli ambienti di produzione, l’impatto è trascurabile e ampiamente giustificato dai benefici in termini di diagnostica. È un compromesso accettabile per la visibilità che offre.

Serve riavviare il servizio dopo la modifica?

Assolutamente sì. L’aggiunta di pg_stat_statements a shared_preload_libraries nel postgresql.conf richiede un riavvio completo del servizio PostgreSQL per essere caricata nella memoria condivisa. Senza riavvio, l’estensione non sarà attiva e non raccoglierà dati.

Posso resettare le statistiche di pg_stat_statements?

Sì, è possibile resettare tutte le statistiche raccolte dall’estensione eseguendo SELECT pg_stat_statements_reset();. Questo è utile dopo aver implementato ottimizzazioni o per iniziare un nuovo periodo di monitoraggio, in modo da avere dati freschi e non influenzati da performance passate.

Come interpreto l’output della colonna query?

La colonna query mostra il testo della query normalizzato, ovvero i valori dei parametri vengono sostituiti con placeholder. Questo permette di raggruppare statistiche per query simili, indipendentemente dai valori specifici usati. Ad esempio, SELECT FROM users WHERE id = $1 raggrupperà tutte le query SELECT FROM users WHERE id = X.

Esistono alternative a pg_stat_statements?

Esistono altri strumenti per il monitoraggio di PostgreSQL, come pg_top per un’analisi in tempo reale dei processi e delle query attive, o soluzioni di monitoraggio esterne come Prometheus/Grafana con pg_exporter. Tuttavia, pg_stat_statements rimane lo standard de facto per l’analisi dettagliata delle performance delle singole query SQL grazie alla sua granularità e all’integrazione nativa.

Conclusioni con takeaway operativi

pg_stat_statements è uno strumento indispensabile per ogni DBA o sviluppatore che lavora con PostgreSQL. La sua capacità di fornire statistiche dettagliate sulle performance delle query trasforma un’indagine spesso complessa in un’analisi rapida e mirata. Abilitarlo e utilizzarlo regolarmente ti permetterà di mantenere il tuo database performante, identificando proattivamente i colli di bottiglia prima che diventino problemi critici. Ricorda di riavviare il servizio dopo aver abilitato l’estensione e di usare EXPLAIN ANALYZE per approfondire le query identificate. Leggi anche: Prometheus Alerting: Configurare AlertManager con Notifiche Telegram, Email e PagerDuty (2026)

Fonti

• PostgreSQL Documentation: pg_stat_statements
• PostgreSQL Documentation: EXPLAIN

Aggiornato: Luglio 2026