Centro di Conoscenza DevOps
Centro di Conoscenza DevOps

Capire e Implementare il Partizionamento Dichiarativo di Tabelle in PostgreSQL 14+

Esplora la funzionalità nativa di partizionamento dichiarativo di PostgreSQL nelle versioni 14+. Questa guida illustra i tipi di partizionamento range, list e hash, offrendo esempi pratici in SQL per la creazione e la gestione delle tabelle partizionate. Scopri come ottimizzare le prestazioni delle query e semplificare la gestione dei dati per dataset molto grandi sfruttando il partition pruning e strategie di manutenzione efficienti.

38 visualizzazioni

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

PostgreSQL è da tempo un database relazionale potente e versatile, ma con la crescita dei set di dati, la gestione e l'interrogazione di tabelle enormi possono diventare una sfida significativa. Le prestazioni degradano, le attività di manutenzione diventano ingombranti e l'efficienza generale del sistema ne risente. PostgreSQL 10 ha introdotto il partizionamento dichiarativo come soluzione nativa per affrontare questi problemi, e le sue capacità hanno continuato a maturare nelle versioni successive, in particolare PostgreSQL 14 e successive.

Il partizionamento dichiarativo consente di suddividere tabelle di grandi dimensioni in parti più piccole e gestibili chiamate partizioni. Questa strategia non solo migliora le prestazioni delle query consentendo al database di scansionare solo le partizioni pertinenti, ma semplifica anche le operazioni di manutenzione come l'archiviazione dei dati, l'eliminazione e la gestione degli indici. Questo articolo ti guiderà attraverso la comprensione dei concetti fondamentali del partizionamento dichiarativo in PostgreSQL, esplorando i suoi diversi tipi e fornendo esempi pratici su come implementarlo per ottimizzare il tuo database.

Cos'è il Partizionamento Dichiarativo delle Tabelle?

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

I principali vantaggi del partizionamento dichiarativo includono:

  • Migliori Prestazioni delle Query: Le query che filtrano sulla chiave di partizionamento possono essere significativamente più veloci perché PostgreSQL può potare (eliminare) le partizioni che non contengono i dati rilevanti, un processo noto come partition pruning.
  • Gestione dei Dati Più Semplice: Operazioni come l'eliminazione di dati vecchi o l'archiviazione possono essere eseguite in modo molto più efficiente staccando o eliminando singole partizioni anziché eseguire operazioni massive di DELETE su una singola tabella grande.
  • Manutenzione Semplificata: L'indicizzazione e il vacuuming possono essere gestiti su base per-partizione, riducendo l'impatto sull'intera tabella.
  • Disponibilità Migliorata: La manutenzione sulle singole partizioni può spesso essere eseguita con una minima interruzione dell'intera tabella.

Tipi di Partizionamento Dichiarativo

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

1. Partizionamento per Intervallo (Range Partitioning)

Il partizionamento per intervallo divide i dati in base a un intervallo continuo di valori in una colonna specifica (ad esempio, date, numeri).

Caso d'uso: Ideale per dati di serie temporali, come log, dati di eventi o registrazioni di vendita, in cui si interrogano frequentemente dati all'interno di specifici intervalli di date o numerici.

Esempio: Partizionamento di una tabella sales in base alla colonna sale_date.

Creazione di una Tabella Partizionata per Intervallo

Innanzitutto, crea la tabella padre, specificando il metodo di partizionamento e la chiave:

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. Ciascuna partizione è definita con una clausola FOR VALUES che specifica l'intervallo che conterrà.

-- Partizione per le vendite di gennaio 2023
CREATE TABLE sales_2023_01
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-01-31');

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

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

Suggerimento: Quando si definiscono gli intervalli, assicurarsi che siano contigui e coprano tutti i valori possibili. Evitare intervalli sovrapposti. Il valore TO è esclusivo.

2. Partizionamento per Elenco (List Partitioning)

Il partizionamento per elenco divide i dati in base a un elenco discreto di valori in una colonna.

Caso d'uso: Adatto a 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 in base alla colonna region.

Creazione di una Tabella Partizionata per Elenco

Definire la tabella padre 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);

Creare partizioni per regioni specifiche:

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

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

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

Importante: Se si inserisce un valore per region che non corrisponde all'elenco IN di alcuna partizione esistente e non esiste una partizione DEFAULT, l'inserimento fallirà. È possibile creare una partizione DEFAULT per catturare tutti gli altri valori.

Creazione di una Partizione Predefinita

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

3. Partizionamento per Hash (Hash Partitioning)

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

Caso d'uso: Utile quando si dispone di un grande volume di dati e si desidera distribuirlo uniformemente tra le partizioni senza una chiara distribuzione basata su intervallo o elenco. È utile per il bilanciamento del carico.

Esempio: Partizionamento di una tabella users in base a user_id.

Creazione di una Tabella Partizionata per Hash

Definire la tabella padre con PARTITION BY HASH e specificare 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 se non le si specifica, ma è generalmente consigliabile crearle esplicitamente, specialmente quando si desidera un controllo sul numero e sulla denominazione delle partizioni.

Creazione di Partizioni Hash Esplicite

-- Creare 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 si utilizza il partizionamento per hash, è necessario specificare il modulus (il numero totale di partizioni) e il remainder (quale partizione è questa).

Implementazione del Partizionamento Dichiarativo: Best Practice

  • Scegliere la Chiave di Partizionamento Corretta: La chiave di partizionamento dovrebbe allinearsi con i filtri di query più frequenti e le operazioni di gestione dei dati. Una buona chiave migliora significativamente le prestazioni.
  • Considerare il Numero di Partizioni: Troppo poche partizioni potrebbero non fornire vantaggi sufficienti, mentre troppe possono aumentare l'overhead. Puntare a un numero che bilanci gestibilità e prestazioni. Per il partizionamento per intervallo, considerare il tasso di crescita dei dati e le politiche di conservazione.
  • Utilizzare pg_partman per l'Automazione: Per il partizionamento per intervallo, specialmente con dati di serie temporali, considerare 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.
  • Indicizzare in Modo Strategico: Gli indici sulle tabelle figlie sono indipendenti. È possibile creare indici su singole partizioni secondo necessità. Considerare la creazione di indici sulla chiave di partizionamento per un pruning efficiente.
  • Partition Pruning: Assicurarsi che le query siano scritte per sfruttare il partition pruning includendo la chiave di partizionamento nelle clausole WHERE. Il comando EXPLAIN può mostrare se il pruning è in atto.
  • Partizioni DEFAULT: Per il partizionamento per elenco, una partizione DEFAULT è cruciale per evitare errori di inserimento se compaiono valori nuovi inaspettatamente.
  • Tipi di Dati: Assicurarsi che il tipo di dati della chiave di partizionamento sia appropriato e coerente tra la tabella padre e le tabelle figlie.

Gestione delle Partizioni

Collegamento e Scollegamento delle Partizioni

Sebbene le partizioni vengano create direttamente tramite CREATE TABLE ... PARTITION OF ..., è anche possibile scollegare e collegare tabelle esistenti come partizioni. Ciò è utile per la migrazione dei dati o la gestione di grandi set di dati.

Scollegamento di una Partizione: Per scollegare una partizione, è prima necessario trasformarla in una tabella normale, quindi scollegarla dal padre. Nelle versioni recenti di PostgreSQL, è possibile scollegare direttamente.

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

Collegamento di una Tabella come Partizione: È possibile collegare una tabella normale (che rispetta lo schema del padre) come nuova partizione.

-- Si supponga che 'old_sales_data' sia una tabella normale con lo stesso schema di 'sales'
CREATE TABLE sales_2022_12
    PARTITION OF sales ()
    FOR VALUES FROM ('2022-12-01') TO ('2022-12-31');

-- Collegare la tabella esistente allo slot della nuova partizione
ALTER TABLE sales ATTACH PARTITION sales_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2022-12-31');

-- Se si avesse una tabella pre-creata, prima la si renderebbe una partizione:
-- CREATE TABLE sales_2022_12 (LIKE sales INCLUDING ALL);
-- ... popolare sales_2022_12 ...
-- ALTER TABLE sales ATTACH PARTITION sales_2022_12 FOR VALUES FROM ('2022-12-01') TO ('2022-12-31');

Eliminazione delle Partizioni

L'eliminazione di una partizione è un'operazione veloce poiché rimuove solo la tabella di partizione, non i dati in essa contenuti (a meno che non specificato esplicitamente). Questo è molto più veloce di DELETE.

-- Per eliminare una partizione, è sufficiente eliminare la tabella figlia
DROP TABLE sales_2023_01;

Esempio: Migliorare le Prestazioni delle Query con il Partition Pruning

Considerare la tabella sales partizionata in base a sale_date come mostrato in precedenza.

Query senza partition pruning (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 massiccia e non partizionata, questa query scansionerebbe l'intera tabella. Tuttavia, con il partizionamento dichiarativo:

-- Questa query scansionerà 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 rientra interamente nella partizione sales_2023_01. Pertanto, scansionerà solo tale partizione, riducendo drasticamente l'I/O e migliorando le prestazioni.

Per verificarlo, utilizzare EXPLAIN:

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

L'output mostrerà un passaggio PartitionPrune, che indica che le partizioni irrilevanti sono state escluse.

Conclusione

Il partizionamento dichiarativo in PostgreSQL 14+ è una funzionalità potente per la gestione e l'ottimizzazione di grandi set di dati. Dividendo in modo intelligente le tabelle in base a strategie di intervallo, elenco o hash, è possibile ottenere miglioramenti significativi nelle prestazioni delle query, nell'efficienza della gestione dei dati e nella manutenibilità generale del database. Comprendere i tipi di partizionamento disponibili e applicare le migliori pratiche durante l'implementazione sarà fondamentale per sbloccare il pieno potenziale di questa funzionalità per le vostre applicazioni.