Ottimizzare PostgreSQL: tracciamento query lente con pg_stat_statements e OpenTelemetry

Introduzione: il problema delle query lente e il loro impatto

Le query lente in PostgreSQL rappresentano uno dei problemi più subdoli per i team di backend e SRE. Spesso passano inosservate fino a quando non provocano un degrado delle prestazioni visibile all’utente finale, come latenze elevate nelle API o timeout nelle operazioni critiche. Il monitoraggio tradizionale, basato su log o metriche di sistema, non basta: serve un approccio strutturato che combini l’analisi delle query con il tracciamento distribuito.

In questa guida, ti accompagneremo in un progetto completo per identificare, analizzare e risolvere le query lente in PostgreSQL, integrando pg_stat_statements per la raccolta dei dati e OpenTelemetry per il tracciamento end-to-end. L’obiettivo è ridurre la latenza del backend in modo misurabile, con un impatto diretto su MTTR e SLA.

Fase 1: Configurare pg_stat_statements per il monitoraggio delle query

Perché pg_stat_statements è lo strumento giusto

pg_stat_statements è un’estensione nativa di PostgreSQL che traccia le statistiche di esecuzione delle query, inclusi:

Tempo totale di esecuzione
Tempo medio per query
Numero di chiamate
Dati letti e scritti

Questo strumento è fondamentale perché non richiede modifiche al codice applicativo e fornisce una visione immediata delle query più costose. Tuttavia, da solo non bastano: senza un contesto di tracciamento distribuito, non è possibile correlare le query con le richieste HTTP o le operazioni di business.

Passaggi per l’attivazione

Aggiungi l’estensione al database Connetti al tuo database PostgreSQL e esegui:
```
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```
Assicurati che l’utente abbia i permessi necessari (SUPERUSER o CREATE EXTENSION).
Configura il file postgresql.conf Aggiungi o modifica le seguenti impostazioni per attivare il tracciamento:
```
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
```
- track = all abilita il tracciamento di tutte le query, incluse quelle eseguite da utenti diversi.
- max definisce il numero massimo di query tracciate. Un valore troppo basso potrebbe escludere query rilevanti.
Riavvia PostgreSQL Dopo aver modificato il file di configurazione, riavvia il servizio:
```
sudo systemctl restart postgresql
```
Verifica l’attivazione Esegui questa query per confermare che l’estensione sia attiva:
```
SELECT * FROM pg_stat_statements LIMIT 5;
```
Se restituisce dati, la configurazione è corretta.

Analisi dei dati raccolti

Una volta attivato, pg_stat_statements popola una vista omonima con le statistiche delle query. Per identificare le query lente, puoi usare una query come questa:

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

I risultati mostreranno:

query: il testo della query.
calls: il numero di volte in cui la query è stata eseguita.
total_exec_time: il tempo totale di esecuzione in millisecondi.
mean_exec_time: il tempo medio di esecuzione per chiamata.
rows: il numero di righe restituite o modificate.

Attenzione: le query con un mean_exec_time alto ma un calls basso potrebbero non essere prioritarie. Concentrati su quelle con un prodotto elevato tra calls e mean_exec_time, che indicano un impatto significativo sulle prestazioni complessive.

Fase 2: Integrare OpenTelemetry per il tracciamento distribuito

Il limite di pg_stat_statements

pg_stat_statements fornisce dati preziosi, ma non correlati con il contesto applicativo. Ad esempio, non sai:

Quale endpoint HTTP ha triggerato una query lenta.
Quale utente o operazione di business è interessata.
Come la query si inserisce nel flusso di una richiesta.

Qui entra in gioco OpenTelemetry, lo standard aperto per il tracciamento distribuito. OpenTelemetry permette di:

Tracciare le richieste dall’ingresso nel backend fino al database.
Correlare le query con le operazioni di business.
Misurare la latenza end-to-end e identificare i colli di bottiglia.

Passaggi per l’integrazione

1. Strumentazione dell’applicazione

A seconda del tuo stack tecnologico, installa la libreria OpenTelemetry per il linguaggio di programmazione del backend. Ad esempio, per Node.js:

npm install @opentelemetry/sdk-node @opentelemetry/auto-instrumentations-node @opentelemetry/exporter-otlp-grpc @opentelemetry/resources @opentelemetry/semantic-conventions

Per Python (FastAPI/Flask):

pip install opentelemetry-sdk opentelemetry-exporter-otlp opentelemetry-instrumentation opentelemetry-instrumentation-flask

2. Configurazione del collector OpenTelemetry

Devi configurare un OpenTelemetry Collector per raccogliere, elaborare e inoltrare i dati di tracciamento. Un esempio di configurazione minima (config.yaml):

receivers:
  otlp:
    protocols:
      grpc:
      http:

processors:
  batch:

Exporters:
  logging:
    logLevel: debug
  otlp:
    endpoint: "lescopr-collector:4317"
    tls:
      insecure: true

service:
  pipelines:
    traces:
      receivers: [otlp]
      processors: [batch]
      exporters: [otlp, logging]

Questa configurazione:

Riceve i dati tramite protocollo OTLP (OpenTelemetry Protocol).
Elabora i dati in batch per ridurre il carico.
Inoltra i dati a un endpoint di Lescopr (o al tuo backend di osservabilità preferito).

3. Attivazione del tracciamento delle query PostgreSQL

Per tracciare le query PostgreSQL all’interno dei tuoi trace, devi strumentare il driver del database. Ad esempio, per Node.js con pg:

const { NodeSDK } = require('@opentelemetry/sdk-node');
const { getNodeAutoInstrumentations } = require('@opentelemetry/auto-instrumentations-node');
const { OTLPTraceExporter } = require('@opentelemetry/exporter-otlp-grpc');

const sdk = new NodeSDK({
  traceExporter: new OTLPTraceExporter({
    url: 'http://localhost:4317',
  }),
  instrumentations: [getNodeAutoInstrumentations()],
});

sdk.start();

Questo codice attiva automaticamente il tracciamento delle query eseguite tramite il driver pg. Ogni query sarà associata a uno span nel trace, con informazioni come:

Il tempo di esecuzione.
Il testo della query.
Il contesto della richiesta HTTP.

4. Visualizzazione dei dati in Lescopr

Una volta configurato OpenTelemetry, i dati di tracciamento verranno inviati a Lescopr, dove potrai:

Visualizzare i trace completi, incluse le query PostgreSQL.
Filtrare per latenza e identificare le query più lente.
Correlare le query con le operazioni di business e gli endpoint API.
Impostare alert per query che superano soglie di latenza predefinite.

Fase 3: Ottimizzazione delle query lente

Analisi dei trace e identificazione dei colli di bottiglia

Con i dati di pg_stat_statements e OpenTelemetry a disposizione, puoi ora analizzare i trace per identificare i colli di bottiglia. Ad esempio, un trace potrebbe mostrare che:

Una richiesta HTTP all’endpoint /api/orders impiega 500 ms in totale.
Di questi, 400 ms sono spesi in una query PostgreSQL per recuperare gli ordini di un utente.
La query in questione ha un mean_exec_time di 350 ms in pg_stat_statements.

Questo indica che la query è il principale responsabile della latenza. Ora puoi procedere con l’ottimizzazione.

Strategie di ottimizzazione

Ecco alcune strategie comuni per ottimizzare le query lente, con esempi pratici:

Aggiunta di indici Se la query esegue un WHERE su una colonna non indicizzata, PostgreSQL deve scansionare l’intera tabella. Ad esempio:

-- Query lenta
SELECT * FROM orders WHERE user_id = 123;

-- Soluzione: aggiungi un indice
CREATE INDEX idx_orders_user_id ON orders(user_id);

Ottimizzazione delle JOIN Le JOIN su colonne non indicizzate o con condizioni complesse possono essere costose. Assicurati che:
- Le colonne usate nelle JOIN siano indicizzate.
- Le tabelle siano normalizzate correttamente.

Limitazione dei risultati Se la query restituisce più dati del necessario, usa LIMIT o WHERE per ridurre il set di risultati:

-- Query che restituisce troppi dati
SELECT * FROM products;

-- Soluzione: limita i risultati
SELECT * FROM products WHERE category = 'electronics' LIMIT 100;

Riscrittura delle query Alcune query possono essere riscritte per essere più efficienti. Ad esempio, usa EXISTS invece di IN per sottquery:

-- Query con IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- Soluzione: usa EXISTS
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

Partizionamento delle tabelle Se hai tabelle molto grandi, considera il partizionamento per suddividerle in parti più piccole e gestibili.

Validazione delle ottimizzazioni

Dopo aver applicato le ottimizzazioni, valuta l’impatto:

Esegui di nuovo la query in pg_stat_statements e confronta i valori di mean_exec_time e total_exec_time.
Genera nuovo traffico sull’applicazione e osserva i trace in Lescopr. La latenza dovrebbe essere ridotta.
Misura l’impatto sugli SLA: verifica se il tempo di risposta dell’endpoint interessato è migliorato.

Fase 4: Monitoraggio continuo e alerting

Configurazione di alert per query lente

Per prevenire futuri problemi di latenza, configura alert automatici in Lescopr. Ad esempio:

Alert per query con mean_exec_time > 100 ms e calls > 100 al minuto.
Alert per trace con latenza totale > 500 ms che includono query PostgreSQL.

Questi alert ti permetteranno di intervenire proattivamente prima che le query lente impattino gli utenti finali.

Dashboard per il monitoraggio delle prestazioni

Crea una dashboard personalizzata in Lescopr per monitorare:

Le query più lente in PostgreSQL.
La latenza media delle richieste per endpoint.
Il tasso di errori correlato a query o operazioni sul database.
Le metriche di utilizzo del database (CPU, memoria, I/O).

Una dashboard ben configurata ti permette di avere una visione unificata delle prestazioni del backend e del database, facilitando il debug e l’ottimizzazione.

Conclusione: verso un backend più performante e osservabile

Il tracciamento delle query lente in PostgreSQL non è solo una questione di ottimizzazione del database, ma di osservabilità end-to-end del tuo backend. Integrando pg_stat_statements per l’analisi delle query e OpenTelemetry per il tracciamento distribuito, puoi:

Identificare le query che impattano le prestazioni.
Correlare le query con le operazioni di business.
Ottimizzare il database in modo mirato.
Monitorare le prestazioni in tempo reale.

Questo approccio non solo riduce la latenza, ma migliora l’affidabilità del tuo sistema e accelera il MTTR in caso di problemi.

Per approfondire, la documentazione di Lescopr descrive la configurazione passo dopo passo.