EDB Postgres Advanced Server
INTERVAL Partitioning

Il partitioning consente di gestire in modo efficiente tabelle di grandi dimensioni. PostgreSQL consente il partizionamento dichiarativo dalla versione 10 ma non supporta la creazione automatica di partizioni con la modalita' RANGE.
EDB Postgres Advanced Server (EPAS), tra le molte funzionalita' aggiuntive a PostgreSQL, rende disponibile la clausola INTERVAL che consente la generazione automatica della partizioni semplificando notevolmente la gestione del partizionamento.

Se avete fretta leggete il primo esempio, altrimenti vedremo le caratteristiche del partizionamento dichiarativo in PostgreSQL quindi la clausola di INTERVAL Partitioning di EPAS.

Ho fretta!

Per ottenere il partizionamento automatico con EDB basta seguire quest'esempio:

CREATE TABLE sales (
  prod_id SERIAL,
  prod_quantity int,
  sale_date date
)
PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION sales_part_202401 values LESS THAN (TO_DATE('01/02/2024','dd/mm/yyyy'))
);

alter table sales add constraint
 sales_pkey PRIMARY KEY (prod_id, sale_date);

In questo modo viene definita una tabella con partizionamento RANGE mensile automatico ed una prima partizione. La clausola INTERVAL consente di definire la dimensione di ogni singola partizione. Il secondo comando crea la chiave primaria che, come e' richiesto in PostgreSQL, contiene la colonna su cui si effettua il partizionamento.

Gia' fatto!
Se vengono inseriti dati non coperti dalle partizioni gia' definite, EPAS provvedera' a creare la nuova partizione necessaria.

Partizionamento dichiarativo in PostgreSQL

Il partizionamento di una tabella 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:

Migliori prestazioni: grazie al Query pruning [NdA esclusione delle partizioni non interessate] gli statement SQL risultano piu' veloci perche' accedono solo ad una parte dei dati e non all'intera tabella.
Svecchiamento dati: la cancellazione dei dati obsoleti da una tabella generalmente richiede una DELETE con un numero molto elevato di record da cancellare. Con il partizionamento, se e' possibile utilizzare eliminare partizioni intere, l'operazione utilizzata non e' piu' una DELETE (ovvero un'istruzione DML) ma una DROP (ovvero un'istruzione DDL) che viene eseguita in modo immediato o quasi.

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. meno di 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 della tabella possono crescere di ordini di grandezza senza avere rallentamenti significativi sulle prestazioni.

Su PostgreSQL Community Edition 10+ e' possibile creare una tabella partizionata con la seguente sintassi:

CREATE TABLE sales (
  prod_id SERIAL,
  prod_quantity int,
  sale_date date
)
PARTITION BY RANGE (sale_date);

La chiave primaria e le eventuali ulteriori chiavi univoche debbono contenere tutte le colonne di partizionamento:

alter table sales add constraint
 sales_pkey PRIMARY KEY (prod_id, sale_date);

Prima di poter inserire dati e' necessario creare le partizioni che li ospiteranno con:

CREATE TABLE sales_part_0      PARTITION OF sales FOR VALUES FROM (TO_DATE('01/01/2000','dd/mm/yyyy')) TO (TO_DATE('01/01/2024','dd/mm/yyyy'));
CREATE TABLE sales_part_202401 PARTITION OF sales FOR VALUES FROM (TO_DATE('01/01/2024','dd/mm/yyyy')) TO (TO_DATE('01/02/2024','dd/mm/yyyy'));
CREATE TABLE sales_part_202402 PARTITION OF sales FOR VALUES FROM (TO_DATE('01/02/2024','dd/mm/yyyy')) TO (TO_DATE('01/03/2024','dd/mm/yyyy'));

Gli statement SQL vengono normalmente eseguiti sulla tabella partizionata, sara' l'ottimizzatore con il pruning a decidere su quali partizioni agire; dal punto di vista prestazionale e' opportuno che le query siano scritte in modo che l'ottimizzatore riconosca le partizioni su cui agire. Se il pruning funziona correttamente le dimensioni fisiche ridotte delle partizioni consentono di avere un buon vantaggio prestazionale a volte fino ad un ordine di grandezza. Ma a parte questo [NdA e qualche altra piccola differenza] tutti gli statement SQL che possono essere eseguiti su una normale tabella possono anche essere eseguiti su una tabella partizionata.

Naturalmente debbono essere presenti partizioni per tutti i valori previsti nelle INSERT... altrimenti si avra' un errore:

INSERT INTO sales (prod_quantity, sale_date) VALUES (200, now()); ERROR: no partition of relation "sales" found for row DETAIL: Partition key of the failing row contains (sale_date) = (2024-04-01 08:00:00).

Gli statement SQL possono essere anche eseguiti direttamente sulle partizioni che per PostgreSQL sono tabelle a tutti gli effetti [NdA l'oggetto partizionato non e' una Relation ma lo sono tutte le sue partizioni a meno che non siano a loro volta partizionate].

Per "svecchiare i dati" basta cancellare le partizioni con:

 DROP TABLE sales_part_0;

Dalla versione 11 e' stata introdotta la DEFAULT partition che consente di evitare l'errore di assenza della partizione ospitante. La DEFAULT partition ha pero' un impatto significativo perche' richiede un check anche per l'eventuale successivo cambio di partizione.

La documentazione ufficiale riporta tutti i dettagli... ma come abbiamo visto con PostgreSQL e' necessario creare le partizioni prima di inserire i dati.

INTERVAL Partitioning

EDB Postgres Advanced Server (EPAS) fornisce una ampia serie di funzionalita' aggiuntive a PostgreSQL. Le funzionalita' aggiuntive sono rivolte alla sicurezza (eg. EDB AUDIT, Data Redaction), alle performance (eg. Dynatune), ed alla compatibilita'/portabilita' con l'RDBMS Oracle. Tra le funzionalita' di compatibilita' con Oracle e' presente la clausola INTERVAL nella definizione delle partizioni.
Il partizionamento a intervalli (clausola INTERVAL) consente di creare automaticamente una nuova partizione quando i dati da inserire non sono collocabili nelle partizioni esistenti. Questa caratteristica e' presente solo su EPAS e non per PostgreSQL Community Edition. E' una funzionalita' che rende piu' simile il partizionamento PostgreSQL a quello Oracle.

E' possibile sfruttare il partizionamento automatico ad intervalli, utilizzando la clausola INTERVAL nella tabella partizionata e creando una partizione iniziale.

CREATE TABLE sales (
  prod_id SERIAL,
  prod_quantity int,
  sale_date date
)
PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION sales_part_202401 values LESS THAN (TO_DATE('01/02/2024','dd/mm/yyyy'))
);

A questo punto e' possibile sia creare manualmente una nuova partizione, come si farebbe con un servizio PostgreSQL Community Edition:

  ALTER TABLE sales ADD PARTITION sales_part_202402 VALUES LESS THAN (TO_DATE('01/03/2024','dd/mm/yyyy'));

Ma anche sfruttare il partizionamento ad intervalli automatico.
Nel momento dell'inserimento di un record, la cui data non fosse collocabile nelle partizioni presenti, ne viene creata una nuova con un nome generico SYS%:

INSERT INTO sales (prod_quantity, sale_date) VALUES (200, now());

SELECT table_name, partition_name, high_value from user_tab_partitions WHERE table_name ='SALES';
 table_name |  partition_name   |      high_value
------------+-------------------+----------------------
 SALES      | SALES_PART_202401 | '01-FEB-24 00:00:00'
 SALES      | SALES_PART_202402 | '01-MAR-24 00:00:00'
 SALES      | SYS17860103       | '01-MAY-24 00:00:00'

Gia fatto!
Quindi una volta definita la tabella con la modalita' vista con EPAS non e' piu' necessario creare manualmente tutte le partizioni necessarie ai dati.

La documentazione ufficiale riporta tutti i dettagli... ma nel seguito vedremo ancora qualche elemento utile.

Qualche elemento in piu'...

La funzione NUMTOYMINTERVAL e' una funzione tipica di Oracle che restituisce un intervallo con il numero di mesi o anni desiderati; il primo parametro e' il numero il secondo e' una stringa che puo' valere 'MONTH' o 'YEAR'. E' molto comoda per indicare il partizionamento.

L'INTERVAL partitioning ha alcune restrizioni:

limitazione ad una singola chiave di partizione
la chiave di partizione deve essere di tipo numerico o con intervallo di date
DEFAULT e MAXVALUE non possono essere definiti per una tabella partizionata a intervalli
i dati da inserire nella colonna della chiave di partizionamento non possono essere: NULL, Not-a-Number, Infinity
l'intervallo di partizionamento deve restituire un valore costante e non negativo
la tabella deve essere definita con almeno una partizione

Si tratta di limitazioni molto ragionevoli che sono presenti in modo da poter definire con esattezza l'appartenenza di una riga ad una partizione.

In PostgreSQL l'importantissima vista di sistema pg_class riporta tutti i dettagli degli oggetti presenti nella base dati. Le normali tabelle hanno il campo relkind='r' e se sono partizioni relispartition=1. Le tabelle partizionate non contengono dati servono solo come contenitori sintattici ed hanno il campo relkind='p'. Con questi elementi e' possibile interrogare il data dictionary per conoscere il dettaglio di ogni partizione.

Con EPAS in aggiunta a questo utilizzando la vista di sistema USER_TAB_PARTITIONS [NdE che appartiene alle Oracle catalog views di EPAS] si ha la possibilità di vedere facilmente tutte le partizioni collegate ad una certa tabella [NdA il nome deve essere maiuscolo]:

SELECT table_name, partition_name, high_value from user_tab_partitions WHERE table_name ='SALES';
 table_name |  partition_name   |      high_value
------------+-------------------+----------------------
 SALES      | SALES_PART_202401 | '01-FEB-24 00:00:00'
 SALES      | SALES_PART_202402 | '01-MAR-24 00:00:00'

Varie ed eventuali

Per utilizzare l'interval partitioning e' necessario creare almeno una partizione:

CREATE TABLE sales ( prod_id SERIAL, prod_quantity int, sale_date date ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')); INSERT INTO sales (prod_quantity, sale_date) VALUES (200,now()); ERROR: cannot create interval partition for relation "sales" DETAIL: Interval partitioned table must have at least one partition.

Anche se il LIST partitioning e' meno utilizzato rispetto al RANGE partitioning, EPAS consente la creazione automatica delle partizioni anche per il LIST partitioning.

Tutti i dettagli sul partizionamento in PostgreSQL si trovano ovviamente sulla documentazione ufficiale.
Mentre la documentazione ufficiale EDB riporta tutti i dettagli sul partitioning con Postgres^TM Advanced Server.

Titolo: EPAS INTERVAL Partitioning
Livello: Intermedio (2/5)
Data: 14 Febbraio 2023
Versione: 1.0.1 - 1 Aprile 2024
Autori: Lavinia Niro, mail [AT] meo.bogliolo.name