Partizionamento in PostgreSQL: Tabelle Grandi, Query Veloci e Manutenzione Sostenibile

C'è un momento preciso in cui un database smette di essere "veloce e basta" e comincia a richiedere manutenzione. Non è un numero magico: dipende dall'hardware, dal tipo di query e da quanto scrivi. Ma prima o poi arriva, e quasi sempre coincide con una tabella che cresce di milioni di righe al mese: movimenti di magazzino, log, eventi, transazioni.

In quel momento le opzioni sono tre: comprare hardware migliore, cancellare dati, oppure cambiare il modo in cui i dati sono organizzati fisicamente. Il partizionamento è la terza strada, e in PostgreSQL è diventato abbastanza maturo da essere una scelta ragionevole e non un'avventura.

Il problema concreto

Prendiamo una tabella di movimenti di magazzino. Ogni riga ha una data, un articolo, un negozio, una quantità, un tipo di movimento. Con il tempo si accumulano anni di storia. Le query tipiche sono due:

• Operative: "cosa si è mosso ieri?" — leggono una fetta minuscola della tabella.
• Analitiche: "confronta gli ultimi tre anni per categoria" — leggono molto, ma sempre per intervalli di date.

Il problema è che PostgreSQL, senza partizionamento, deve comunque gestire un unico oggetto enorme. Questo significa:

• indici B-tree molto profondi, con più pagine da leggere per ogni ricerca;
• VACUUM e ANALYZE che girano sull'intera tabella, anche se il 95% delle righe non cambia mai più;
• REINDEX che diventa un'operazione da pianificare con calma;
• cancellazioni storiche (DELETE FROM ... WHERE data < ...) che generano bloat e non restituiscono spazio al filesystem senza un VACUUM FULL.

Il partizionamento attacca tutti questi punti insieme.

Cos'è il partizionamento dichiarativo

Da PostgreSQL 10 esiste il partizionamento dichiarativo: dichiari che una tabella è divisa in parti e il database gestisce il routing automaticamente. Per chi scrive query, la tabella resta una sola; per il motore, sono molti oggetti fisici separati.

sql CREATE TABLE movimenti_magazzino ( id bigserial, data_movimento date NOT NULL, negozio_id integer NOT NULL, articolo_id integer NOT NULL, tipo text NOT NULL, quantita integer NOT NULL, PRIMARY KEY (id, data_movimento) ) PARTITION BY RANGE (data_movimento);

Poi crei le partizioni, che sono tabelle a tutti gli effetti:

sql CREATE TABLE movimenti_2024 PARTITION OF movimenti_magazzino FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE movimenti_2025 PARTITION OF movimenti_magazzino FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Un INSERT in movimenti_magazzino con data_movimento = '2025-03-10' finisce automaticamente in movimenti_2025. Una SELECT con lo stesso filtro legge solo quella partizione.

Esistono tre strategie principali:

Per dati storici il RANGE su una colonna data è la scelta quasi obbligata, perché riflette il modo in cui interroghi i dati.

Quando conviene davvero (e quando no)

Il partizionamento non è gratis. Aggiunge oggetti da gestire, complica le migrazioni e può peggiorare le query sbagliate.

Conviene quando:

• la tabella è grande (indicativamente decine di milioni di righe o più) e continua a crescere;
• le query filtrano quasi sempre su una dimensione naturale (di solito il tempo);
• hai bisogno di eliminare dati vecchi in modo rapido e prevedibile;
• la manutenzione (VACUUM, REINDEX, backup incrementali) sta diventando un problema.

Non conviene quando:

• la tabella è piccola o a crescita limitata: aggiungi complessità per nulla;
• le query non hanno un filtro naturale: se interroghi sempre "tutti i negozi, tutte le date", finirai per leggere tutte le partizioni comunque;
• la chiave di partizionamento cambia spesso nelle righe già inserite: spostare una riga tra partizioni è un DELETE + INSERT mascherato, quindi costoso;
• non hai un processo per creare le partizioni future: senza quello, gli INSERT falliscono con un errore di "no partition found".

Pruning: il meccanismo che rende tutto sensato

Il partition pruning è la fase in cui il planner esclude le partizioni che non possono contenere righe rilevanti. È il motivo per cui il partizionamento funziona.

sql EXPLAIN (ANALYZE, BUFFERS) SELECT negozio_id, sum(quantita) FROM movimenti_magazzino WHERE data_movimento >= '2025-01-01' AND data_movimento < '2025-02-01' GROUP BY negozio_id;

Nel piano vedrai un nodo Append con un solo figlio scansionato, invece di tutti. PostgreSQL 11 ha introdotto il pruning a runtime, che funziona anche quando il filtro arriva da un parametro o da una sottoquery — non solo da una costante scritta nella query. Questo ha reso il partizionamento utilizzabile nei casi reali, dove le date arrivano da un ORM o da un prepared statement.

Se il pruning non scatta, il piano legge tutte le partizioni e il risultato è più lento di una tabella singola, perché c'è comunque l'overhead di coordinare più scansioni.

Il parametro enable_partition_pruning è attivo di default. Se lo disattivi per fare test, ricorda di riattivarlo.

Chiavi primarie, indici e vincoli

Qui c'è la regola che sorprende chi arriva da MySQL o da versioni vecchie di PostgreSQL: la chiave primaria deve includere la colonna di partizionamento. Non è un capriccio, è una conseguenza logica: l'unicità di un indice può essere garantita solo all'interno di una singola partizione, quindi servono tutti i valori che determinano quale partizione contiene la riga.

Questo ha un impatto pratico. Se il tuo id è una sequenza e la chiave primaria è solo (id), non puoi partizionare per data. Devi scegliere:

• PRIMARY KEY (id, data_movimento), accettando che l'unicità di id non sia garantita a livello globale;
• oppure usare un indice unico separato per partizione, con la stessa limitazione;
• oppure rinunciare al partizionamento su quella tabella.

Gli indici sulla tabella padre si propagano alle partizioni. In PostgreSQL recenti puoi creare un indice sulla tabella partizionata e il database crea automaticamente gli indici figli su ogni partizione, attuali e future.

sql CREATE INDEX ON movimenti_magazzino (negozio_id, data_movimento); CREATE INDEX ON movimenti_magazzino (articolo_id);

Manutenzione: creare avanti, staccare indietro

Il vero guadagno del partizionamento è nella manutenzione, non nella singola query.

Eliminare anni di storia: invece di un DELETE da milioni di righe, stacchi la partizione e la butti via.

sql ALTER TABLE movimenti_magazzino DETACH PARTITION movimenti_2022 CONCURRENTLY;

DROP TABLE movimenti_2022;

Il DETACH ... CONCURRENTLY (da PostgreSQL 14) evita il lock pesante sulla tabella padre che il DETACH normale richiede. Il DROP restituisce spazio immediatamente, senza VACUUM FULL e senza bloat.

Archiviare invece di cancellare: puoi staccare la partizione, copiarla su storage economico, e riattaccarla se serve per un'analisi storica. Diventa una tabella normale, interrogabile, senza toccare il resto.

Vacuum e analyze: con le partizioni puoi pianificare VACUUM solo sulle partizioni attive, perché quelle storiche non cambiano più. Su una tabella monolitica questo non è possibile.

Automatizzare la creazione delle partizioni

Il rischio principale è dimenticarsi di creare la partizione del mese prossimo. Quando arriva il momento, ogni INSERT fallisce. Serve un processo automatico, non un promemoria su un calendario.

Una soluzione leggera è uno script schedulato che crea le partizioni future e stacca quelle troppo vecchie:

python import psycopg from datetime import date, timedelta

TABLE = "movimenti_magazzino"

sql = """ CREATE TABLE IF NOT EXISTS {name} PARTITION OF {parent} FOR VALUES FROM ('{start}') TO ('{end}'); """

with psycopg.connect("postgresql://...") as conn: with conn.cursor() as cur: # esempio: crea le prossime 3 partizioni mensili today = date.today().replace(day=1) for i in range(3): start = (today + timedelta(days=32 * i)).replace(day=1) end = (start + timedelta(days=32)).replace(day=1) name = f"{TABLE}{start.strftime('%Y%m')}" cur.execute(sql.format(name=name, parent=TABLE, start=start, end=end)) conn.commit()

Lo script va eseguito prima che la partizione serva, con margine sufficiente per accorgersi se fallisce. Un controllo di monitoraggio che verifichi l'esistenza della partizione del mese successivo è più utile di un alert sull'uso del disco.

Esiste un'estensione dedicata, pg_partman, che gestisce creazione, retention e manutenzione delle partizioni. Per casi semplici uno script di venti righe è sufficiente e ha meno parti in movimento; per molti casi complessi l'estensione fa risparmiare tempo.

Trappole comuni

• Nessuna partizione DEFAULT: se arriva un dato fuori intervallo, l'INSERT fallisce. Una partizione DEFAULT evita l'errore ma nasconde il problema, perché il pruning non può escluderla. Meglio monitorare che non si riempia.
• Troppe partizioni: centinaia sono gestibili, migliaia iniziano a pesare sul tempo di pianificazione di ogni query. Se stai pensando a partizioni giornaliere su dieci anni, ripensa la granularità.
• Indici duplicati: creare indici a mano su ogni partizione e anche sulla tabella padre porta a indici doppi e a spazio sprecato.
• Aggiornamenti che cambiano la chiave: UPDATE ... SET data_movimento = ... sposta la riga tra partizioni e non è gratis. Se succede spesso, la chiave di partizionamento non è quella giusta.
• Backup: pg_dump di una tabella partizionata con molte partizioni è più lento, e il restore lo è ancora di più. Vale la pena misurare prima.

Un criterio pratico per decidere

Prima di partizionare, prova a rispondere a queste domande:

1. Le mie query filtrano quasi sempre su una colonna specifica?
2. Ho bisogno di eliminare o archiviare dati vecchi in modo rapido?
3. La manutenzione della tabella sta diventando un problema operativo?
4. Ho un processo automatico che crea le partizioni future?
5. Ho accettato il vincolo sulla chiave primaria?

Se la risposta è sì a tutte, il partizionamento è probabilmente la scelta giusta. Se manca la 4, il partizionamento diventerà un incidente in produzione entro pochi mesi.

Conclusione

Il partizionamento dichiarativo in PostgreSQL è una tecnica matura, ma non è un interruttore da accendere: è un cambio nel modo in cui organizzi e mantieni i dati.

• Partiziona quando la tabella è grande, cresce nel tempo e le query filtrano su una dimensione naturale come la data.
• Il partition pruning è ciò che rende il tutto conveniente: senza filtro sulla chiave, le prestazioni peggiorano.
• La chiave primaria deve includere la colonna di partizionamento: è un vincolo reale, valuta se puoi accettarlo.
• Il guadagno più grande è nella manutenzione: DETACH e DROP al posto di DELETE, VACUUM mirati, archiviazione semplice.
• Automatizza la creazione delle partizioni future prima di tutto il resto: è il fallimento più comune e il più banale da prevenire.
• Misura sempre con EXPLAIN (ANALYZE, BUFFERS) prima e dopo: il partizionamento fatto male è più lento di una tabella singola.

Foto di copertina di Kevin Ache su Unsplash.