Introduzione a pg_stat_statements: installazione e configurazione dell'estensione

L'estensione pg_stat_statements in PostgreSQL è uno strumento per raccogliere statistiche sulle query. Ti permette di vedere quali query vengono eseguite più spesso, quali richiedono più tempo e come vengono usate le risorse del database. Invece di analizzare ogni query a mano con EXPLAIN, puoi avere una panoramica generale delle performance del database.

Vantaggi di usare pg_stat_statements:

Monitoraggio in tempo reale: puoi vedere quali query stanno caricando il database proprio adesso.

Analisi delle performance dell'intero sistema: le info sono disponibili per tutte le query del database, non solo per quelle che decidi di analizzare manualmente.

Individuazione delle query lente: è facile capire quali query richiedono più tempo.

Rilevamento delle query ripetitive: ti permette di ottimizzare la cache e aggiungere indici per le query più popolari.

Installazione e configurazione di pg_stat_statements

Ora che hai capito a cosa serve pg_stat_statements, vediamo come installarlo e configurarlo passo dopo passo.

1. Controlla che PostgreSQL sia pronto. Assicurati che il tuo PostgreSQL supporti l'estensione pg_stat_statements. Questa estensione è inclusa di default a partire da PostgreSQL 9.2. Per controllare se c'è, esegui:

SELECT extname FROM pg_extension;

Se pg_stat_statements non è nella lista, potrebbe voler dire che non è stata installata dall'amministratore.

Ecco come dovrebbe apparire l'estensione installata e attivata:

2. Aggiunta dell'estensione.

Per attivare pg_stat_statements devi aggiungerlo alla lista delle librerie da precaricare di PostgreSQL. Si fa nel file di configurazione postgresql.conf.

Passaggi:

1. Trova il file postgresql.conf. Di solito sta nella directory dei dati di PostgreSQL.
2. Aprilo per modificarlo.
3. Aggiungi o modifica la riga:

   shared_preload_libraries = 'pg_stat_statements'

Perché serve? Perché pg_stat_statements richiede il preload, visto che traccia le query a livello di sistema.

4. Salva le modifiche e riavvia il server PostgreSQL per attivare i cambiamenti. Qui sotto il comando per Linux:

   sudo systemctl restart postgresql
   

Se stai sviluppando o testando in locale, anche un semplice riavvio del server va bene.

3. Creazione dell'estensione nel database. Dopo aver riavviato PostgreSQL, puoi creare l'estensione pg_stat_statements nel database che ti interessa. Connettiti al database tramite psql o un altro tool ed esegui:

CREATE EXTENSION pg_stat_statements;

Se tutto va bene, il comando finisce senza errori. Ora pg_stat_statements è attivo per il tuo database.

4. Configurazione dei parametri di pg_stat_statements.

Dopo aver installato l'estensione, conviene settare i parametri per raccogliere bene le statistiche. I parametri principali si impostano nel file postgresql.conf.

Parametri principali

• pg_stat_statements.track
• Decide quali query tracciare.
• Valori:
  • all — traccia tutte le query (consigliato per debug e analisi).
  • top — traccia solo le query di livello superiore.
  • none — disattiva il tracciamento.
• Esempio di configurazione:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Indica il numero massimo di query che verranno salvate nelle statistiche.
  • Default: 5000.
  • Se hai tanti query nel sistema, alza questo valore, tipo:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Decide se salvare le statistiche tra i riavvii del server.
  • Valori: on o off.
  • Consigliato lasciare on:

    pg_stat_statements.save = on
    

Dopo aver cambiato i parametri, riavvia di nuovo il server PostgreSQL.

Verifica del funzionamento di pg_stat_statements

Ora che l'estensione è installata e configurata, vediamo se funziona. Per vedere le statistiche raccolte sulle query, esegui questa query:

SELECT
    queryid,        -- Identificatore unico della query
    query,          -- Testo della query
    calls,          -- Numero di chiamate della query
    total_time,     -- Tempo totale di esecuzione (in millisecondi)
    rows            -- Numero di righe restituite dalla query
FROM pg_stat_statements
ORDER BY total_time DESC;

Cosa significano le colonne?

• queryid: identificatore unico della query, utile per trovare query uguali con parametri diversi.
• query: testo della query SQL eseguita.
• calls: quante volte è stata chiamata la query.
• total_time: tempo totale (somma dei tempi di tutte le chiamate della query).
• rows: numero totale di righe restituite dalla query.

Per esempio, se vedi che una query con calls = 100 e total_time = 50000 (50 secondi) occupa la maggior parte del tempo nel sistema, è un chiaro segnale che va ottimizzata.

Scenari tipici di utilizzo di pg_stat_statements

1. Trovare le query più lente. Per trovare le query che richiedono più tempo, ordina i risultati per total_time:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Individuare le query più attive. Per trovare le query eseguite più spesso, ordina per calls:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Analisi dell'uso degli indici. Se vedi tante query lente, controlla l'uso degli indici. Per esempio, nelle query con filtro (WHERE), la mancanza di un indice spesso è la causa delle basse performance.

Pulizia dei dati di pg_stat_statements

A volte può servirti azzerare le statistiche raccolte, per iniziare l'analisi da zero. Puoi farlo con questo comando:

SELECT pg_stat_statements_reset();

Dopo il reset tutte le statistiche saranno pulite e la raccolta dati ripartirà da capo.

Consigli pratici

Limita la quantità di statistiche raccolte: se lavori in un sistema molto carico con milioni di query, lascia pg_stat_statements.max a un livello ragionevole per evitare carichi inutili.

Pulisci regolarmente le statistiche: è utile farlo prima di iniziare un'analisi delle performance, così non mescoli dati vecchi e nuovi.

Fai attenzione alle query lente: anche se vengono eseguite raramente, una singola query lenta può caricare parecchio il tuo database.

Ora sai come installare, configurare e usare l'estensione pg_stat_statements per analizzare le performance delle query. Nella prossima lezione vedremo più a fondo come trovare le query lente con questo strumento e ottimizzarne l'esecuzione.