Monitoraggio delle transazioni attive in tempo reale con pg_stat_activity

pg_stat_activity è, in sostanza, una finestra in tempo reale che ti aiuta a capire cosa sta succedendo nel tuo database proprio adesso. Nella lezione precedente abbiamo visto le basi, ora andiamo più a fondo con questo strumento super potente.

Esempio di query base su pg_stat_activity:

SELECT * 
FROM pg_stat_activity;

Questa query ti mostra tutte le connessioni attive e le query correnti. Ottimo! Ma i dati saranno tantissimi e rischi di perderci le ore a scorrerli. Quindi è utile filtrare solo le info più importanti.

Campi principali in pg_stat_activity

Dai, vediamo i campi chiave che ti serviranno oltre a quelli che già conosci. query_start mostra quando è partita l’esecuzione della query, fondamentale per capire quali operazioni sono lunghe. pid contiene l’ID del processo della connessione — ti serve per gestire (tipo terminare) la connessione. state_change mostra quando è stato impostato lo stato attuale della connessione, utilissimo per analizzare stati problematici che durano troppo.

Esempio di selezione dei processi attivi:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

Come tracciare le query lunghe?

Immagina di essere l’admin del database e all’improvviso il carico sul server va alle stelle. Che si fa? Prima di tutto devi capire quale query sta mangiando tutte le risorse. Usiamo pg_stat_activity per trovare queste query “affamate”.

SELECT pid, usename, query, state, now() - query_start AS durata
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

Questa query ti mostra tutte le query che girano da più di 10 secondi. Cambia il valore dell’intervallo come ti serve.

Terminare le query problematiche

Vediamo come sbarazzarsi delle query che stanno girando da troppo e bloccano il database. Usa la funzione pg_terminate_backend() per killare il processo.

Esempio di terminazione di un processo con un certo PID:

SELECT pg_terminate_backend(12345);

Dove 12345 è l’ID del processo (campo pid) preso da pg_stat_activity.

Importante: Terminare un processo può causare un rollback se la transazione non si chiude bene, quindi occhio!

Ora, se vuoi killare in automatico tutti i processi “appesi”, tipo le transazioni idle, puoi usare questo blocco PL/pgSQL. Visto che hai già studiato programmazione, il concetto di ciclo (loop) ti è familiare — è una struttura che ripete certe istruzioni finché c’è una condizione vera o finché non finisce di processare un set di dati:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Questa soluzione dinamica ti permette di ripulire il sistema da transazioni problematiche. Il ciclo FOR passa su ogni record del risultato della query e termina il processo per ogni PID trovato.

Tra poco iniziamo a studiare PL/pgSQL, manca poco :P

Filtrare per stato delle transazioni

A volte non vuoi solo trovare una query attiva, ma anche capire quali connessioni sono in uno stato particolare, tipo idle o idle in transaction. Questo ti aiuta a scovare problemi potenziali prima che diventino critici.

Esempio di query per trovare transazioni in idle in transaction:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Il campo state_change ti dice quando è stato impostato questo stato. Così puoi trovare transazioni che stanno lì da troppo senza fare nulla, ma che magari bloccano risorse del database.

Applicazioni pratiche

Monitoraggio delle query lunghe in produzione: puoi impostare un monitoraggio regolare delle query che superano una certa soglia di tempo, per ricevere notifiche su Slack, Telegram o qualsiasi altro tool di alert. Così reagisci subito ai problemi di performance.

Analisi delle query durante gli incidenti: se il server inizia a rallentare, la prima cosa da guardare è pg_stat_activity per trovare la causa. Questo dovrebbe essere il tuo protocollo standard per reagire ai problemi di performance.

Manutenzione del database: analizzare regolarmente pg_stat_activity ti aiuta a scovare query inefficienti e ottimizzarle (tipo aggiungendo indici o riscrivendo le query).

Quando si tratta di monitoraggio, gli errori possono capitare per filtri sbagliati o interpretazione errata dei dati. Ad esempio, se filtri solo per stato active, rischi di perderti le query che sono in stato idle in transaction, che pure possono bloccare risorse. Un altro errore è killare i processi troppo aggressivamente, il che può causare rollback indesiderati e perdita di dati. Analizza sempre il contesto prima di prendere decisioni drastiche.

Tecniche di monitoraggio avanzate

Per un monitoraggio più avanzato puoi creare query complesse che mostrano statistiche per utenti, database o tipi di query. Ad esempio, puoi vedere quanto tempo in media ogni utente impiega per eseguire le query, oppure trovare i database con più connessioni attive.

È anche utile impostare il logging automatico delle query lunghe nei log di PostgreSQL, usando i parametri di configurazione log_min_duration_statement e log_statement. Questo ti aiuta ad analizzare i problemi di performance dopo che sono successi e a trovare pattern nel comportamento delle applicazioni.