Comandi base per il monitoring in PostgreSQL — pg_stat_activity e pg_stat_user_tables

Fare monitoring di un database senza pg_stat_activity e pg_stat_user_tables è come controllare la salute solo guardando la febbre. Non puoi capire dove sta il problema se guardi solo il quadro generale. Questi due comandi chiave di PostgreSQL ti aiutano non solo a osservare, ma anche ad analizzare attivamente cosa succede nel tuo database.

Cos’è pg_stat_activity?

pg_stat_activity è una vista di sistema di PostgreSQL che mostra info su tutte le connessioni al tuo database. Ti risponde a domande tipo: chi è connesso al database, quali query stanno girando in questo momento, e quali connessioni sono “bloccate” in stato di inattività. È il tuo strumento per analizzare l’attività attuale sul server.

Dai, vediamo i campi principali disponibili in pg_stat_activity. Il campo datname contiene il nome del database a cui il client è connesso, mentre usename mostra il nome dell’utente che ha fatto la connessione. application_name indica il nome dell’applicazione che usa la connessione, client_addr contiene l’indirizzo IP del client connesso al server. backend_start mostra quando il client si è connesso al server, state riflette lo stato attuale della connessione (active, idle, idle in transaction), e query contiene la query che è in esecuzione o che è stata eseguita per ultima.

Esempio 1: vedere tutte le connessioni attive

Per vedere le connessioni attive, lancia questa query:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

Occhio al campo query. Ti mostra le query che stanno girando adesso. Se una query ci mette troppo, forse c’è qualcosa che non va.

Esempio 2: Analisi dello stato delle transazioni

A volte le connessioni rimangono “bloccate” in stato idle in transaction. Vuol dire che una transazione è stata iniziata ma non chiusa, e questo può portare a lock fastidiosi.

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

Come risolvere? Se trovi una transazione “bloccata”, puoi chiuderla con questo comando:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Alcuni dev si fanno prendere la mano con questa roba. Ti consiglio prima di chiedere al team se puoi “killare” il processo. Ops, scusa, volevo dire — chiudere la connessione.

Monitoring dell’uso delle tabelle: la vista pg_stat_user_tables

Se pg_stat_activity ti fa tenere d’occhio le connessioni, pg_stat_user_tables ti racconta delle performance delle tabelle. Con questa puoi scoprire: quanto spesso vengono letti o scritti i dati nelle tabelle, quali tabelle sono usate di più, e dove potrebbero esserci problemi di performance.

Ecco i campi principali di pg_stat_user_tables che ti aiutano ad analizzare le tabelle. relname contiene il nome della tabella, seq_scan mostra quante volte la tabella è stata scansionata in modo sequenziale, idx_scan — quante volte è stato usato un indice. n_tup_ins indica quante righe sono state inserite nella tabella, n_tup_upd — quante righe sono state aggiornate, e n_tup_del — quante righe sono state cancellate.

Esempio 1: confronto tra uso di indici e scansioni sequenziali

Se un indice viene usato troppo poco (idx_scan vicino a zero), probabilmente puoi ottimizzare le query su quella tabella.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Esempio di risultato:

Se vedi che la tabella orders ha un sacco di scansioni sequenziali (seq_scan), pensa a mettere un indice. Immagina la tabella orders con 3500 scansioni sequenziali e solo 100 scansioni tramite indice, mentre la tabella employees ha 50 scansioni sequenziali e 1000 scansioni tramite indice — è un chiaro segnale che serve ottimizzazione.

Esempio 2: analisi del numero di operazioni sulle tabelle

Per vedere quanto sono “vive” le tabelle, chiedi info su righe inserite, aggiornate e cancellate:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

Cosa puoi scoprire? Tabelle con tanti insert (n_tup_ins) e delete (n_tup_del) possono essere hot spot nel tuo database. Vuol dire che le loro performance meritano attenzione speciale.

Uso pratico delle query per analizzare le performance: combinare i dati di pg_stat_activity e pg_stat_user_tables

Quando analizzi le performance del database, puoi unire i dati di queste due viste. Prima trova le query lente con pg_stat_activity, poi controlla quali tabelle vengono usate da queste query tramite pg_stat_user_tables. Se le query lente lavorano su tabelle con seq_scan alto, prova a ottimizzare le query o aggiungere un indice.

Esempio di query:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';