Il partitioning consente di gestire in modo efficace tabelle
di grandi dimensioni sui piu' moderni database relazionali.
In questa pagina vediamo, in modo semplice e con esempi pratici, il partizionamento
su PostgreSQL.
Dopo una prima
introduzione,
vedremo le caratteristiche del nuovo partizionamento dichiarativo
[NdA introdotto dalla versione 10],
quindi alcuni esempi di partizionamento,
qualche elemento sulla gestione
ed infine alcuni buoni consigli.
Il partizionamento di una relazione in pratica consiste nell'utilizzare strutture fisiche separate per ogni partizione sia per la tabella che per gli indici. I vantaggi del partizionamento sono sopratutto due:
Ulteriori vantaggi si hanno nella gestione perche' le strutture dati risulta piu' piccole e quindi piu' facilmente trattabili nelle normali attivita' di manutenzione (eg. vacuum).
Il partizionamento e' utile solo con tabelle di grandi dimensioni (eg. tabelle con oltre un milione di record oppure >>2GB),
le partizioni debbono essere in numero ragionevole (eg. <<1000) e bilanciate tra loro,
le query piu' frequenti e/o le modalita' di svecchiamento
dei dati debbono sempre essere eseguite utilizzando la chiave di partizionamento.
Se una tabella e' di piccole dimensioni o non vengono utilizzate correttamente le chiavi di partizionamento
le prestazioni di una tabella partizionata sono peggiori di quelle di una tabella ordinaria.
Al contrario se si utilizza correttamente il partizionamento la base dati risulta piu' scalabile
e le dimensioni possono crescere di ordini di grandezza senza avere rallentamenti significativi sulle prestazioni.
Postgres consentiva il partizionamento fin dalla prime versioni
grazie alle sue caratteristiche di database ad oggetti.
Tuttavia l'utilizzo dell'ereditarieta' per la definizione delle partizioni risultava un poco complesso e,
sopratutto, meno efficiente rispetto a quello disponibile su altri database.
La versione 10 di PostgreSQL (2017-10) introduce il Declarative Table Partitioning
che presenta significativi vantaggi prestazionali ed una sintassi semplificata.
La versione 11 di PostgreSQL (2018-10) contiene miglioramenti
con ulteriori importanti funzionalita'
(eg. HASH, primary key, default partition, miglior query pruning).
La versione 12 di PostgreSQL (2019-10) ha prestazioni significativamente migliori
quando il numero di partizioni e' elevato.
L'evoluzione di PostgreSQL e' continua ed anche le versioni successive hanno introdotto
miglioramenti sul partizionamento,
ma nel seguito del documento faremo semplicemente riferimento ad una versione di PostgreSQL>11.
Insomma con una versione recente di PostgreSQL e' possibile sfruttare appieno il partizionamento utilizzando l'efficiente modalita' nativa.
Il partizionamento nativo in PostgreSQL consente di partizionare le tabelle al momento della creazione e viene quindi chiamato Declarative Partitioning [NdA anche per distinguerlo dal tipo di partizionamento precedente chiamato Partitioning Using Inheritance]. Per definire una tabella come partizionata si utilizza la clausola PARTITION BY nello statement di CREATE TABLE.
Una tabella definita come partizionata non contiene alcun dato:
i dati sono mantenuti nelle partizioni che sono normali relazioni.
Nella tabella di data dictionary pg_class
il relkind vale 'r' per la partizioni, come per le normali relazioni non partizionate,
mentre il relkind e' ='p' per la tabella partizionata.
A loro volta le partizioni possono essere partizionate, per farlo
basta utilizzare la clausola PARTITION BY
per generare una gerarchia di partizionamento
(subpartitioning).
Gli indici definiti sulla tabella partizionata
vengono fisicamente creati su ogni partizione presente.
Dal punto di vista di utilizzo una tabella partizionata e' analoga ad una tabella
non partizionata.
Le differenze si hanno nel vantaggio della gestione delle partizioni e
nelle prestazioni purche' le query siano scritte in modo da accedere
ad una sola o ad un numero limitato di partizioni.
Il partizionamento e' basato utilizzando partizioni diverse a fronte di valori diversi presenti nei campi definiti come chiavi di partizionamento.
Vi sono diversi tipi di partizionamento supportati da PostgreSQL:
La scelta della colonna o delle colonne su cui effettuare il partizionamento ed il tipo di partizionamento e' fondamentale per ottenere migliori prestazioni.
Vediamo qualche esempio di utilizzo del partizionamento! Iniziamo con il RANGE Partitioning.
CREATE TABLE measurement (
component_id int not null,
logtime timestamp not null,
peaktemp int
) PARTITION BY RANGE (logtime);
ALTER TABLE measurement ADD CONSTRAINT
measurement_pkey PRIMARY KEY (component_id, logtime);
CREATE INDEX ON measurement (logtime);
CREATE TABLE measurement_202201 PARTITION OF measurement
FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE measurement_202202 PARTITION OF measurement
FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
...
insert into measurement values(3, '2022-02-14 08:00:00', 9);
Con questi comandi abbiamo creato una tabella partizionata e le prime due partizioni. Dal punto di vista degli utenti o delle applicazioni, anche se i dati sono fisicamente presenti nelle partizioni tutte i comandi SQL operano sulla tabella originale senza differenze.
Anche se non e' strettamente necessario abbiamo creato
un indice sulla colonnna utilizzata per il partizionamento
e la primary key.
Tutti gli indici definiti sulla tabella partizionata
vengono automaticamente creati su tutte le partizioni.
Gli indici univoci o le primary key definite debbono
soddisfare alcune regole:
debbono contenere tutte le colonne usate per il partizionamento
e non possono utilizzare espressioni.
In caso contrario viene restituito un errore:
E' possibile creare indici direttamente sulle partizioni. In questo caso gli indici vengono mantenuti ed utilizzati solo sulle partizioni in cui sono stati creati.
Lo svecchiamento dei dati e' molto semplice ed efficiente con il range partitioning: basta cancellare la partizione con i dati piu' vecchi!
DROP TABLE measurement_202201;
L'esecuzione di una DROP rispetto ad una DELETE e' molto piu' veloce, non genera dead rows e non richiede alcun vacuum perche' la partizione e' cancellata completamente liberando tutto lo spazio al sistema operativo.
Naturalmente debbono essere presenti partizioni per tutti i valori previsti nelle INSERT... altrimenti si avra' un errore, ma su questo torneremo anche dopo:
Quando non sono presenti colonne partizionabili per RANGE o LIST e' sempre possibile utilizzare l'HASH Partitioning. Ecco un esempio:
CREATE TABLE measurement (
component_id int not null,
logtime timestamp not null,
peaktemp int
) PARTITION BY HASH (component_id);
ALTER TABLE measurement ADD CONSTRAINT
measurement_pkey PRIMARY KEY (component_id, logtime);
CREATE TABLE measurement_001 PARTITION OF measurement
FOR VALUES WITH (modulus 50, remainder 0);
CREATE TABLE measurement_002 PARTITION OF measurement
FOR VALUES WITH (modulus 50, remainder 1);
...
In questo caso abbiamo deciso di utilizzare 50 partizioni
ed utilizziamo la colonna component_id per determinare l'associazione
alla partizione corretta.
Con l'hash partitioning non si puo' sfruttare la DROP delle partizioni
per svecchiare i dati
ma questa tecnica consente partizionare in modo uniforme
praticamente tutte le tabelle.
Come ultimo esempio utilizziamo il LIST Partitioning. Non e' il mio preferito, perche' raramente un list partitioning produce partizioni bilanciate, pero' vi sono casi in cui risulta utile:
CREATE TABLE measurement (
component_id int not null,
logtime timestamp not null,
city text,
peaktemp int
) PARTITION BY LIST (city);
ALTER TABLE measurement ADD CONSTRAINT
measurement_pkey PRIMARY KEY (component_id, logtime, city);
CREATE TABLE measurement_to
PARTITION OF measurement FOR VALUES IN ('Torino');
CREATE TABLE measurement_mi
PARTITION OF measurement FOR VALUES IN ('Milano');
CREATE TABLE measurement_oth PARTITION OF measurement DEFAULT;
Innanzi tutto notiamo che nella chiave primaria abbiamo dovuto inserire la
colonna city anche se potrebbe dipendere dal component_id...
Ma l'esempio e' utile anche per introdurre la partizione di DEFAULT
in cui vengono inseriti i dati quando non esistono partizioni
definite per i valori delle chiavi utilizzate [NdA solo per il partizionamento RANGE o LIST].
Anche se molto comoda dal punto di vista dichiarativo la partizione di DEFAULT
presenta alcuni problemi dal punto di vista delle prestazioni...
va utilizzata con moderazione e solo nei casi in cui
e' effettivamente necessaria.
Gli indici presenti sulla tabella partizionata vengono automaticamente creati su tutte le partizioni. Quando viene creata una nuova partizione vengono immediatamente creati gli indici relativi [NdA l'operazione e' in genere molto veloce perche' una nuova partizione tipicamente e' vuota].
E' possibile creare indici sulle singole partizioni. Ad esempio e' possibile che le partizioni piu' recenti abbiano un uso diverso rispetto alle partizioni storiche e quindi abbiano un insieme di indici differente.
Se viene creato un indice sulla tabella partizionata verra'
clonato su tutte le partizioni. Questa operazione richiede
un lock alle operazioni di scrittura e puo' impiegare un certo tempo
perche' non e' possibile utilizzare
la clausola CONCURRENTLY a tabelle partizionate.
Questo puo' essere problematico quando si vuole aggiungere un indice
ad una tabella partizionata di grandi dimensioni che e' gia' attiva
in produzione.
Tuttavia la clausola CONCURRENTLY puo' essere utilizzata
nella creazione di indici sulle singole partizioni.
Per aggirare il problema e' possibile creare l'indice sulla
tabella partizionata con la clausola ONLY.
In questo modo l'indice viene definito sulla tabella partizionata
ma non viene creato nella gerarchia delle partizioni.
E' quindi possibile creare gli indici su ogni partizione
con la modalita' CONCURRENTLY e poi eseguire l'ATTACH dell'indice.
-- Creare un indice sulla parent table (metadata only). L'indice risultera' INVALID
CREATE INDEX pgbench_accounts_idx_bid ON ONLY pgbench_accounts (bid);
-- Creare gli indici con CONCURRENTLY per ogni partizione (non blocca le DML)
CREATE INDEX CONCURRENTLY pgbench_accounts_idx_bid_1
ON pgbench_accounts_1 (bid);
-- Associare gli indici delle partizioni all'indice parent
ALTER INDEX pgbench_accounts_idx_bid
ATTACH PARTITION pgbench_accounts_idx_bid_1;
...
-- Quando tutte le partizioni avranno un indice associato anche l'indice parent diventera' VALID
Abbiamo gia' visto quanto e' facile creare e cancellare partizioni... senza entrare nel dettaglio di tutte le operative possibili diamo ora qualche cenno alle ulteriori possibilita' introdotte dalle partizioni.
Come abbiamo gia' visto, le partizioni sono tabelle a tutti gli effetti per PostgreSQL. E' possibile agire singolarmente su ciascuna di esse con comandi di DDL come DROP e TRUNCATE che sono molto piu' efficienti di una DELETE.
E' possibile "staccare" una partizione dalla tabella senza cancellare i dati con lo statement ALTER TABLE... DETACH PARTITION... [ CONCURRENTLY | FINALIZE ]; La partizione non viene piu' considerata come parte della tabella partizionata ma resta valida ed utilizzabile. E' possibile ricollegare la partizione con un ATTACH o cancellarla definitivamente con una DROP.
Per aggiungere nuove partizioni e' sufficiente utilizzare la CREATE TABLE... PARTITION OF che abbiamo gia' visto. La creazione di una partizione e' tipicamente immediata ma se e' presente una partizione di DEFAULT eventuali record da inserire nella nuova partizione verranno controllati e spostati: questo puo' richiedere un certo tempo.
Le query che vengono lanciate sulle tabelle partizionate
cercano di eseguire i passi dell'execution plan sul numero
minore possibile di partizioni.
Questa tecnica viene chiamata partition pruning
ed e' abilitata per default ma puo' essere disabilitata
agendo sul parametro enable_partition_pruning.
La verifica e' molto importante e si esegue con l'EXPLAIN:
explain analyze select count(*) from measurement where logtime between '2022-01-01' and '2022-01-31';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=14.83..14.84 rows=1 width=8) (actual time=0.009..0.010 rows=1313 loops=1)
-> Bitmap Heap Scan on measurement_202201 measurement (cost=4.24..14.81 rows=9 width=0) (actual time=0.007..0.008 rows=1313 loops=1)
Recheck Cond: ((logtime >= '2022-01-01 00:00:00'::timestamp without time zone) AND (logtime <= '2022-01-31 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on measurement_202201_logtime_idx (cost=0.00..4.24 rows=9 width=0) (actual time=0.003..0.003 rows=1313 loops=1)
Index Cond: ((logtime >= '2022-01-01 00:00:00'::timestamp without time zone) AND (logtime <= '2022-01-31 00:00:00'::timestamp without time zone))
Planning Time: 0.157 ms
Execution Time: 0.045 ms
Nell'esempio la query viene eseguita su un'unica partizione perche'
la condizione utilizzata consente di determinare dove sono memorizzati i dati.
Il partizionamento risulta vantaggioso solo se la maggioranza delle query
riescono ad effettuare il partition pruning.
Oltre a enable_partition_pruning, che e' sicuramente il piu' importante [NdA disponibile dalla 11 ed impostato ad on per default], vi sono ulteriori parametri di tuning specifici per il paritioning:
name | context | setting | unit | min_val | max_val | source | category
--------------------------------+---------+-----------+------+---------+--------------+---------+---------------------------------------------
constraint_exclusion | user | partition | | | | default | Query Tuning / Other Planner Options
enable_partition_pruning | user | on | | | | default | Query Tuning / Planner Method Configuration
enable_partitionwise_aggregate | user | off | | | | default | Query Tuning / Planner Method Configuration
enable_partitionwise_join | user | off | | | | default | Query Tuning / Planner Method Configuration
max_locks_per_transaction | postm. | 64 | | 10 | 2147483647 | default | Lock Management
L'ultimo parametro riportato in realta' e' relativo al lock management ma potrebbe essere necessario aumentarlo nel caso si presentasse l'ERRROR: out of shared memory dovuto al grande numero di oggetti/partizioni su cui e' richiesto un lock.
Particolare attenzione va posta nella definizione di CHECK constraint e dei trigger quando applicati a tabelle partizionate... ma i dettagli sono troppo sottili per descriverli in un documento introduttivo come questo!
Oltre al partizionamento nativo o dichiarativo e' sempre utilizzabile il meccanismo dell'ereditarieta' che e' stato per molti anni il modo in cui veniva eseguito il partizionamento in PostgreSQL. Riprendendo il nostro esempio:
CREATE TABLE measurement (
component_id int not null,
logtime timestamp not null,
peaktemp int
);
CREATE TABLE measurement_202201 ( CHECK ( logtime >= DATE '2022-01-01' AND logtime < DATE '2022-02-01' ) )
INHERITS (measurement);
CREATE TABLE measurement_202202 ( CHECK ( logtime >= DATE '2022-02-01' AND logtime < DATE '2022-02-01' ) )
INHERITS (measurement);
...
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logtime >= DATE '2022-01-01' AND
NEW.logtime < DATE '2022-02-01' ) THEN
INSERT INTO measurement_202201 VALUES (NEW.*);
ELSIF ( NEW.logtime >= DATE '2022-02-01' AND
NEW.logtime < DATE '2022-03-01' ) THEN
INSERT INTO measurement_202202 VALUES (NEW.*);
...
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Dall'esempio dovrebbe essere chiaro che la gestione con trigger per indirizzare la corretta partizione e' molto flessibile... ma anche assai piu' complessa gestione rispetto al partizionamento nativo [NdA l'esempio e' semplificato perche' e' trattato solo il caso dell'INSERT].
Si sa che la gente da' buoni consigli... se non puo' piu' dare il cattivo esempio.
Innanzi tutto non sempre e' opportuno utilizzare il partizionamento.
Consigli:
Cattivi esempi:
Se si decide di partizionare va fatto nel modo giusto.
Consigli:
Cattivi esempi:
Quanto riportato nei consigli precedenti vale in larga misura per tutti i database relazionali che supportano il partizionamento. Vediamo ora qualche indicazione specifica per PostgreSQL.
E' meglio anticipare il problema della crescita delle tabelle (cfr.2) perche' passare al partizionamento con tabelle gia' di grandi dimensioni e' complesso. Sebbene vi siano alcuni trucchi per migrare dati nelle tabelle partizionate, creare nuovi indici, ... alcune operative possono essere di lunga durata e/o richiedere lock esclusivi [NdA i comandi merge e split partition sono previsti in PG18].
La gestione dell'MVCC con il VACUUM e' particolarmente pesante. L'utilizzo del partizionamento per la storicizzazione dei dati (cfr.3) presenta quindi molti vantaggi.
La scelta delle chiavi di partizionamento (cfr.7) va eseguita con molta attenzione tenendo conto anche di alcuni limiti. Infatti ogni primary key ed ogni indice unique definito su una tabella partizionata dovra' contenere tutti i campi della chiave di partizionamento.
Se le partizioni risultano ancora di grandi dimensioni (cfr.8) e' possibile utilizzare il sub-partitioning. L'implementazione e' molto semplice: basta partizionare le partizioni di primo livello.
Anche se l'ottimizzatore viene costantemente migliorato a volte e' necessario aggiungere condizioni ulteriori alle query (cfr.10) per consentire un pruning piu' efficace.
L'assenza di global index e il livello di locking richiesto per alcune operative sulle partizioni rendono piu' pesante l'utilizzo del partizionamento quando non e' correttamente implementato. E' importante evitare l'over-partitioning (cfr.13).
Le partizioni sono tabelle a tutti gli effetti e possono utilizzare indici specifici se necessario (cfr.14). Per esempio potrebbe essere utile un indice nelle partizioni piu' recenti ma non risultare necessario per quelle precedenti: e' molto semplice da ottenere, basta creare direttamente l'indice solo sulla partizione desiderata.
Per evitare errori in caso di partizioni non esistenti (cfr.16) conviene crearle in anticipo. Postgres consente di definire una DEFAULT partition, ma il suo utilizzo ha impatti prestazionali. Possono essere utili alcune estensioni non core come pg_partman, pg_scheduler, pg_cron, ...
Oltre agli aspetti prestazionali, l'utilizzo dell'ereditarieta' per il partizionamento e' oggettivamente piu' complesso come definizione e gestione rispetto al partizionamento nativo anche se consente maggiore flessibilita' (eg. la struttura dati delle partizioni puo' essere differente). Con le attuali versioni e' fortemente consigliabile utilizzare il solo partizionamento nativo.
Le partizioni possono essere anche definite come FOREIGN TABLE: utilizzando il partizionamento dichiarativo ed i foreing data wrapper e' possibile implementare lo sharding in Postgres.
Non fa parte delle estensioni core distribuite con PostgreSQL Community ma per il partizionamento e' molto utile l'extension pg_partman. Molti servizi Postgres in Cloud dispongono dell'estensione pg_partman...
L'estensione TimescaleDB utilizza le Hypertable per gestire in modo efficiente dati di tipo Time Series. Un Hypertable presenta molte analogie ad una tabella partizionata in modo nativo ma possiede ulteriori funzionalita' come la creazione e cancellazione automatica dei chunks oltre ad una serie di funzioni di raggruppamento temporale.
Il fork EDB Postgres Advanced Server (EPAS) fornisce l'Oracle table partitioning con ulteriori funzionalita' rispetto a quanto presente in PostgreSQL Community. Ad esempio in EPAS e' disponibile l'interval range partitioning, che crea automaticamente le partizioni come avviene in Oracle.
Un altro documento utile sull'argomento e' Ottimizzazione SQL in PostgreSQL. Si tratta di un documento complementare rispetto a questa pagina: anche sulle tabelle partizionate valgono tutte le indicazioni generali relative all'utilizzo dell'SQL, sulla corretta definizione degli indici, ...
Tutti i dettagli sul partizionamento si trovano ovviamente sulla documentazione ufficiale.
Titolo: Declarative partitioning in PostgreSQL
Livello: Intermedio
Data:
14 Febbraio 2023 ❤️
Versione: 1.0.5 - 31 Ottobre 2024 🎃
Autore: mail [AT] meo.bogliolo.name