12 tecniche per ottimizzare le query PostgreSQL su dataset di grandi dimensioni
Quando una tabella PostgreSQL cresce oltre il milione di righe, query che prima restituivano risultati in millisecondi iniziano ad impiegare secondi — o peggio. La buona notizia è che PostgreSQL offre strumenti potenti per affrontare questo problema. La cattiva notizia è che molti sviluppatori conoscono solo una parte di questi strumenti.
In questo articolo passiamo in rassegna le 12 tecniche più efficaci per ottimizzare le query su grandi dataset, con esempi SQL concreti per ciascuna.
1. Creare indici sulle colonne frequentemente filtrate
Il consiglio più noto, ma non per questo meno importante. Un indice trasforma una scansione sequenziale (O(n)) in una ricerca B-tree (O(log n)). La differenza su una tabella da un milione di righe può essere di due ordini di grandezza.
-- Prima: full sequential scan su ordini
SELECT * FROM orders WHERE customer_id = 42;
-- Creazione dell'indice
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Dopo: index scan, da 240ms a pochi ms
Usate EXPLAIN ANALYZE per verificare che l’indice venga effettivamente utilizzato.
2. Normalizzare il database in modo strategico
La normalizzazione riduce la ridondanza e migliora la coerenza dei dati, ma va applicata con giudizio. Una normalizzazione eccessiva crea decine di JOIN che possono diventare colli di bottiglia. La regola pratica: normalizzate i dati che cambiano spesso o che hanno alta cardinalità (liste di prodotti, clienti, categorie), denormalizzate i dati storici o di report dove la velocità di lettura è critica.
3. Evitare SELECT *
Selezionare tutte le colonne ha due costi nascosti: aumenta il volume di I/O e impedisce a PostgreSQL di soddisfare la query direttamente dall’indice (index-only scan). Specificate sempre le colonne necessarie:
-- Evitare
SELECT * FROM orders WHERE customer_id = 42;
-- Preferire
SELECT id, created_at, total_amount FROM orders WHERE customer_id = 42;
Quando le colonne selezionate fanno parte di un indice composito, PostgreSQL può restituire i dati senza accedere all’heap, eliminando un intero livello di I/O.
4. Ordinare i JOIN in modo efficiente
Il query planner moderno di PostgreSQL determina autonomamente l’ordine ottimale dei JOIN grazie al cost-based optimizer. Tuttavia, in scenari con molte tabelle o con join_collapse_limit ridotto, conviene strutturare i JOIN in modo che le tabelle più piccole (o più filtrate) vengano processate per prime, riducendo la cardinalità delle operazioni successive.
5. Usare LIMIT durante l’esplorazione dei dati
Apparentemente ovvio, ma spesso trascurato: se l’interfaccia utente mostra al massimo 50 risultati, non ha senso recuperarne un milione dal database.
SELECT id, name, email
FROM customers
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;
Attenzione al pagination problem: con OFFSET elevati, PostgreSQL scansiona comunque tutte le righe precedenti. Per paginazione su grandi dataset, preferite il keyset pagination (cursor-based).
6. Indici parziali per subset frequenti
Un indice parziale indicizza solo le righe che soddisfano una condizione, riducendo dimensioni e costo di manutenzione:
-- Indice solo sugli ordini completati (subset più frequentemente interrogato)
CREATE INDEX idx_completed_orders
ON orders(customer_id)
WHERE status = 'Completed';
-- La query deve includere la stessa condizione per usare l'indice
SELECT id, total_amount
FROM orders
WHERE customer_id = 42 AND status = 'Completed';
In un test pratico, questo indice ha dimezzato i tempi rispetto a un indice standard su tutte le righe.
7. Usare i tipi di dato più piccoli necessari
Ogni byte conta quando moltiplicato per milioni di righe. Preferite sempre il tipo più compatto che soddisfa il requisito:
integer (4 byte) invece di bigint (8 byte) per chiavi primarie < 2 miliardismallint (2 byte) per enumerazioni con pochi valoritimestamp invece di timestamptz se il fuso orario è fissovarchar(n) con limite appropriato invece di text illimitato dove possibile
Tipi più piccoli significano pagine di dati più dense, quindi meno I/O per ogni query.
8. Non applicare funzioni sulle colonne indicizzate
Applicare una funzione a una colonna indicizzata invalida l’utilizzo dell’indice:
-- L'indice su name NON viene usato
SELECT * FROM customers WHERE LOWER(name) = 'mario rossi';
-- Soluzione: creare un indice funzionale
CREATE INDEX idx_customers_lower_name ON customers(LOWER(name));
-- Ora l'indice viene usato
SELECT * FROM customers WHERE LOWER(name) = 'mario rossi';
Lo stesso vale per funzioni su date come DATE(created_at): usate range di timestamp o create l’indice sulla funzione.
9. Partizionare le tabelle molto grandi
Il partizionamento divide una tabella logica in sotto-tabelle fisiche, permettendo a PostgreSQL di escludere partizioni irrilevanti (partition pruning) durante le query:
-- Tabella partizionata per anno
CREATE TABLE orders_partitioned (
id serial NOT NULL,
customer_id integer,
created_at timestamp NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- Creazione delle partizioni annuali
CREATE TABLE orders_2024 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders_partitioned
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
Una query che filtra per anno legge solo la partizione corrispondente, ignorando completamente le altre.
10. Usare le transazioni per operazioni bulk
PostgreSQL esegue un commit (e quindi una scrittura sincrona su WAL) dopo ogni statement. Raggruppare più operazioni in un’unica transazione riduce drasticamente i costi di I/O:
-- Lento: un commit per ogni INSERT
INSERT INTO log_events VALUES (...);
INSERT INTO log_events VALUES (...);
-- ... x 10.000
-- Veloce: un solo commit per tutto il batch
BEGIN;
INSERT INTO log_events VALUES (...);
INSERT INTO log_events VALUES (...);
-- ... x 10.000
COMMIT;
In test pratici, l’approccio con transazione singola completa lo stesso lavoro in meno della metà del tempo rispetto agli inserimenti individuali.
11. Evitare transazioni long-running
Il modello MVCC (Multi-Version Concurrency Control) di PostgreSQL mantiene versioni multiple delle righe per garantire la consistenza delle letture. Le transazioni long-running bloccano il processo di VACUUM dal rimuovere le versioni obsolete, causando table bloat: tabelle che crescono fisicamente anche quando i dati logici non aumentano.
Spezzettate le operazioni pesanti in batch più piccoli e monitorate le transazioni attive con:
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query_start IS NOT NULL
ORDER BY duration DESC;
12. Gestire il bloat con VACUUM
Ogni UPDATE e DELETE lascia righe “morte” sul disco. VACUUM le recupera:
-- VACUUM standard: recupera spazio senza bloccare le letture
VACUUM orders;
-- VACUUM FULL: recupera tutto lo spazio ma blocca l'accesso alla tabella
-- Usare solo in finestre di manutenzione programmate
VACUUM FULL orders;
-- Verificare lo stato del bloat
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
Per la maggior parte dei workload, autovacuum è sufficiente. Assicuratevi che sia abilitato e calibrate i threshold in base al volume di modifiche della vostra applicazione:
-- Verificare la configurazione autovacuum per una tabella specifica
SELECT reloptions FROM pg_class WHERE relname = 'orders';
Riepilogo operativo
Non tutte le tecniche si applicano a ogni scenario. Un approccio efficace inizia sempre dall’analisi con EXPLAIN (ANALYZE, BUFFERS) per identificare i reali colli di bottiglia, poi applica le ottimizzazioni in modo mirato. L’indice sbagliato o il partizionamento mal configurato possono peggiorare le prestazioni invece di migliorarle.
Il punto di partenza universale resta lo stesso: misurare prima, ottimizzare dopo.
Fonte: 12 practices for optimizing PostgreSQL queries for large datasets — elmah.io Blog
Gazzetta del Cadavere
Unknown parent • • •