Comprendere e Implementare il Partizionamento Dichiarativo delle Tabelle in PostgreSQL 14+

Il partizionamento in PostgreSQL vale la pena di essere considerato quando una tabella è diventata scomoda da interrogare, eseguire il vacuum, archiviare o eliminare. L'esempio tipico è una tabella di eventi che riceve milioni di righe al giorno e viene quasi sempre interrogata per intervallo di tempo. Senza partizionamento, anche buoni indici possono lasciarti con una tabella costosa da mantenere e dolorosa da eliminare per obsolescenza.

Il partizionamento dichiarativo permette a una tabella logica di instradare le righe in tabelle fisiche più piccole chiamate partizioni. PostgreSQL 10 ha introdotto la sintassi nativa, e le versioni successive hanno migliorato la pianificazione, il pruning, l'indicizzazione e il comportamento di manutenzione. PostgreSQL 14+ è abbastanza maturo che molti team possono usare il partizionamento senza schemi di ereditarietà basati su trigger, ma richiede comunque una progettazione attenta. Una chiave di partizionamento sbagliata può rendere il sistema più complicato senza renderlo più veloce.

Cos'è il Partizionamento Dichiarativo delle Tabelle?

Il partizionamento dichiarativo è una funzionalità del database che ti consente di dividere una singola tabella logica (la tabella genitore o partizionata) in più tabelle fisiche (tabelle figlie o partizioni) basate su un insieme definito di regole. Ogni partizione contiene un sottoinsieme dei dati della tabella genitore. La chiave di partizionamento determina a quale partizione appartiene una riga.

I principali vantaggi del partizionamento dichiarativo includono:

• Prestazioni delle Query Migliorate: Le query che filtrano sulla chiave di partizionamento possono essere più veloci perché PostgreSQL può eliminare le partizioni che non possono contenere righe corrispondenti.
• Gestione dei Dati Più Semplice: Operazioni come l'eliminazione di dati vecchi o l'archiviazione possono essere eseguite in modo molto più efficiente scollegando o eliminando singole partizioni piuttosto che eseguire massicce operazioni DELETE su un'unica grande tabella.
• Manutenzione Semplificata: L'indicizzazione e il vacuum possono essere gestiti su base per-partizione, riducendo l'impatto sull'intera tabella.
• Unità di Manutenzione Più Piccole: Indici a livello di partizione, operazioni di scollegamento e vacuum mirato possono ridurre il raggio d'azione della manutenzione ordinaria.

Tipi di Partizionamento Dichiarativo

PostgreSQL supporta diversi metodi per il partizionamento dichiarativo, ciascuno adatto a diversi modelli di distribuzione dei dati:

1. Partizionamento per Intervallo (Range)

Il partizionamento per intervallo divide i dati basandosi su un intervallo continuo di valori in una colonna specifica (es. date, numeri).

Caso d'Uso: Ideale per dati di serie temporali, come log, dati di eventi o registrazioni di vendite, dove si interrogano frequentemente dati all'interno di intervalli di date o numerici specifici.

Esempio: Partizionamento di una tabella sales per la colonna sale_date.

Creazione di una Tabella Partizionata per Intervallo

Prima, crea la tabella genitore, specificando il metodo e la chiave di partizionamento:

CREATE TABLE sales (
    sale_id SERIAL,
    product_name VARCHAR(100),
    sale_amount NUMERIC(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

Successivamente, crea le singole partizioni. Ogni partizione è definita con una clausola FOR VALUES che specifica l'intervallo che conterrà.

-- Partizione per le vendite di Gennaio 2023.
-- Il limite superiore è esclusivo, quindi include il 31 gennaio.
CREATE TABLE sales_2023_01
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Partizione per le vendite di Febbraio 2023
CREATE TABLE sales_2023_02
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- Partizione per le vendite di Marzo 2023
CREATE TABLE sales_2023_03
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

Suggerimento: Quando definisci gli intervalli, assicurati che siano contigui e coprano tutti i valori possibili. Evita intervalli sovrapposti. Il valore TO è esclusivo.

2. Partizionamento per Elenco (List)

Il partizionamento per elenco divide i dati basandosi su un elenco discreto di valori in una colonna.

Caso d'Uso: Adatto per colonne con un insieme fisso e noto di valori, come regioni geografiche, codici di stato o categorie di prodotti.

Esempio: Partizionamento di una tabella orders per la colonna region.

Creazione di una Tabella Partizionata per Elenco

Definisci la tabella genitore con PARTITION BY LIST:

CREATE TABLE orders (
    order_id SERIAL,
    customer_name VARCHAR(100),
    order_total NUMERIC(10, 2),
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

Crea partizioni per regioni specifiche:

-- Partizione per ordini in 'North America'
CREATE TABLE orders_north_america
    PARTITION OF orders ()
    FOR VALUES IN ('North America');

-- Partizione per ordini in 'Europe'
CREATE TABLE orders_europe
    PARTITION OF orders ()
    FOR VALUES IN ('Europe');

-- Partizione per ordini in 'Asia'
CREATE TABLE orders_asia
    PARTITION OF orders ()
    FOR VALUES IN ('Asia');

Importante: Se inserisci un valore per region che non corrisponde all'elenco IN di nessuna partizione esistente e non c'è una partizione DEFAULT, l'inserimento fallirà. Puoi creare una partizione DEFAULT per catturare tutti gli altri valori.

Creazione di una Partizione Predefinita (Default)

-- Partizione predefinita per qualsiasi regione non elencata esplicitamente
CREATE TABLE orders_other
    PARTITION OF orders ()
    DEFAULT;

3. Partizionamento per Hash

Il partizionamento per hash distribuisce i dati su un numero di partizioni basandosi su un valore hash della chiave di partizionamento.

Caso d'Uso: Utile quando hai un grande volume di dati e vuoi distribuirli uniformemente tra le partizioni senza una chiara distribuzione basata su intervallo o elenco. È buono per il bilanciamento del carico.

Esempio: Partizionamento di una tabella users per user_id.

Creazione di una Tabella Partizionata per Hash

Definisci la tabella genitore con PARTITION BY HASH e specifica il numero di partizioni:

CREATE TABLE users (
    user_id BIGSERIAL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY HASH (user_id);

PostgreSQL creerà automaticamente le partizioni per te se non le specifichi, ma è generalmente consigliato crearle esplicitamente, specialmente quando vuoi controllare il numero e la denominazione delle partizioni.

Creazione di Partizioni Hash Esplicite

-- Crea 4 partizioni hash
CREATE TABLE users_p0
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0);

CREATE TABLE users_p1
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1);

CREATE TABLE users_p2
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2);

CREATE TABLE users_p3
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3);

Nota: Quando usi il partizionamento per hash, devi specificare il modulus (il numero totale di partizioni) e il remainder (a quale partizione questa appartiene).

Implementare il Partizionamento Dichiarativo: Migliori Pratiche

• Scegli la Giusta Chiave di Partizionamento: La chiave di partizionamento dovrebbe allinearsi con i tuoi filtri di query più frequenti e le operazioni di gestione dei dati. Una buona chiave migliora significativamente le prestazioni.
• Considera il Numero di Partizioni: Troppe poche partizioni potrebbero non fornire abbastanza benefici, mentre troppe possono aumentare il sovraccarico. Punta a un numero che bilanci gestibilità e prestazioni. Per il partizionamento per intervallo, considera il tuo tasso di crescita dei dati e le politiche di conservazione.
• Usa pg_partman per l'Automazione: Per il partizionamento per intervallo, specialmente con dati di serie temporali, considera l'uso di estensioni come pg_partman. Automatizza la creazione di nuove partizioni e l'archiviazione/eliminazione di quelle vecchie, riducendo significativamente lo sforzo manuale.
• Indicizza Strategicamente: Gli indici sono fisicamente memorizzati per partizione. Creare un indice sul genitore crea indici di partizione corrispondenti, ma dovresti comunque verificare se ogni partizione ha bisogno dello stesso schema di indici.
• Pruning delle Partizioni: Assicurati che le tue query siano scritte per sfruttare il pruning delle partizioni includendo la chiave di partizionamento nelle clausole WHERE. Il comando EXPLAIN può mostrare se il pruning sta avvenendo.
• Partizioni DEFAULT: Per il partizionamento per elenco, una partizione DEFAULT è cruciale per evitare errori di inserimento se compaiono nuovi valori inaspettatamente.
• Vincoli di Unicità: Un vincolo di unicità o chiave primaria su una tabella partizionata deve generalmente includere tutte le colonne della chiave di partizionamento. Questo coglie di sorpresa molti progetti alle prime armi.
• Tipi di Dati: Assicurati che il tipo di dato della chiave di partizionamento sia appropriato e coerente tra le tabelle genitore e figlie.

Gestire le Partizioni

Collegare e Scollegare Partizioni

Mentre le partizioni vengono create direttamente tramite CREATE TABLE ... PARTITION OF ..., puoi anche scollegare e collegare tabelle esistenti come partizioni. Questo è utile per migrare dati o gestire grandi set di dati.

Scollegare una Partizione: Scollegare trasforma la partizione in una tabella regolare mantenendo i suoi dati.

-- Scollega la partizione sales_2023_01
ALTER TABLE sales DETACH PARTITION sales_2023_01;

Collegare una Tabella come Partizione: Puoi collegare una tabella regolare che è conforme allo schema del genitore e ha dati che rientrano nei limiti della partizione.

-- Supponiamo che sales_2022_12 sia una tabella regolare con le stesse colonne di sales
-- e solo righe di Dicembre 2022.
ALTER TABLE sales ATTACH PARTITION sales_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

Prima di collegare una tabella grande, aggiungi prima un vincolo CHECK corrispondente. PostgreSQL può usare quel vincolo per evitare di scansionare l'intera tabella per dimostrare che le righe rientrano nei limiti della partizione.

Eliminare Partizioni

Eliminare una partizione è un'operazione veloce poiché rimuove solo la tabella della partizione, non i dati al suo interno (a meno che non sia specificato esplicitamente). Questo è molto più veloce di DELETE.

-- Per eliminare una partizione, puoi semplicemente eliminare la tabella figlia
DROP TABLE sales_2023_01;

Esempio: Migliorare le Prestazioni delle Query con il Pruning delle Partizioni

Considera la tabella sales partizionata per sale_date come mostrato in precedenza.

Query senza pruning delle partizioni (ipotetica su una tabella non partizionata):

SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Se sales fosse una tabella enorme e non partizionata, questa query scansionerebbe l'intera tabella. Tuttavia, con il partizionamento dichiarativo:

-- Questa query scannerizzerà solo la partizione sales_2023_01
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Il pianificatore di query di PostgreSQL riconosce che sale_date è la chiave di partizionamento e che l'intervallo specificato cade interamente all'interno della partizione sales_2023_01. Quindi scannerizzerà solo quella partizione, riducendo drasticamente l'I/O e migliorando le prestazioni.

Per verificarlo, usa EXPLAIN:

EXPLAIN SELECT SUM(sale_amount) FROM sales WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

L'output dovrebbe mostrare solo la partizione pertinente, o potrebbe mostrare subplan rimossi a seconda della versione di PostgreSQL e della forma del piano. Il segno importante è che le partizioni non correlate non vengono scansionate.

Una Lista di Controllo Pratica per la Progettazione

Partiziona solo quando puoi nominare il vantaggio operativo. "La tabella è grande" non è sufficiente da solo. Una tabella grande con ricerche puntuali ben indicizzate potrebbe andare bene. Il partizionamento ha più senso quando la maggior parte delle query include la chiave di partizionamento, quando i dati vecchi vengono regolarmente archiviati o eliminati, o quando la manutenzione su un'unica tabella enorme sta già causando problemi.

Per le tabelle di serie temporali, scegli dimensioni delle partizioni che corrispondano ai tuoi modelli di query e conservazione. Le partizioni giornaliere sono utili per un'ingestione molto elevata e una conservazione breve. Le partizioni mensili sono spesso più facili da gestire per un volume di eventi moderato. Troppe partizioni minuscole possono rallentare la pianificazione e rendere rumorosa la manutenzione; troppo poche partizioni giganti potrebbero non risolvere il problema originale.

Pianifica gli inserimenti prima di distribuire. Se le righe possono arrivare in ritardo, mantieni le partizioni più vecchie disponibili abbastanza a lungo per riceverle. Se la chiave di partizionamento può contenere valori imprevisti, crea una partizione DEFAULT e monitorala. Una partizione predefinita dovrebbe essere una rete di sicurezza, non un luogo in cui i dati dimenticati si accumulano silenziosamente per mesi.

Infine, testa con forme di query reali. Il pruning delle partizioni funziona meglio quando la clausola WHERE espone chiaramente la chiave di partizionamento, come sale_date >= '2023-01-01' AND sale_date < '2023-02-01'. Avvolgere la chiave in funzioni può rendere il pruning più difficile:

-- Meno amichevole per il pruning
WHERE date_trunc('month', sale_date) = DATE '2023-01-01';

-- Più facile per il pianificatore
WHERE sale_date >= DATE '2023-01-01'
  AND sale_date <  DATE '2023-02-01';

Il partizionamento dichiarativo è uno strumento di manutenzione tanto quanto uno strumento di query. Usato bene, rende i dati vecchi economici da rimuovere e i dati caldi più facili da scansionare. Usato con noncuranza, aggiunge più tabelle, più indici e più casi limite. Inizia con il modello di accesso, scegli la chiave di partizionamento da quel modello e verifica il piano prima di considerare il progetto finito.

Per una tabella grande esistente, non pianificare una rischiosa conversione una tantum durante il traffico di punta. Un percorso di migrazione comune è creare una nuova tabella partizionata, copiare i dati in blocchi, mantenere i nuovi flussi di scrittura attraverso la logica dell'applicazione o un trigger attentamente testato, quindi scambiare i nomi durante una breve finestra di manutenzione. L'approccio esatto dipende dal volume di scrittura e dalla tolleranza ai tempi di inattività, ma il principio è lo stesso: dimostra la copia, dimostra i vincoli e prova il passaggio prima di toccare la produzione.