Database Partitioning (Oracle, MySQL, PostgreSQL, SQL Server, DB2)

DIVIDE ET IMPERA !

Il partizionamento era gia' ben conosciuto dagli antichi romani che lo utilizzarono per gestire un impero enorme. Piu' tardi gli inglesi lo sfruttarono nell'amministrazione delle proprie colonie.
La tecnica del partizionamento e' stata applicata alle basi relazionali solo successivamente...

In questo breve documento vengono velocemente trattate le problematiche del partizionamento e la loro applicazione sui database relazionali piu' diffusi: Oracle, MySQL, PostgreSQL, DB2, SQL Server, MongoDB, ClickHouse, ... Il taglio del documento e' volutamente semplice ed introduttivo, per i dettagli si rimanda all'ampia documentazione disponibile sui siti ufficiali.
Un documento dallo stesso titolo, ma relativo alle problematiche di partizionamento sui sistemi ospite e su Unix, si trova su questo link.

Partitioning

Quando una base dati ha dimensioni molto elevate la gestione degli oggetti puo' risultare molto onerosa. Il partizionamento e' una tecnica che consente di "spezzare" i dati di una singola tabella su piu' dispositivi fisici/logici per poterli trattare piu' facilmente e con tempi inferiori. I vantaggi tipicamente sono due: le query che possono operare su una sola partizione sono nettamente piu' veloci che se operassero su un oggetto unico (eg. circa 10/100 volte piu' veloci per un full scan di una sola partizione, il 20% per un index scan), se si partiziona per data la storicizzazione viene eseguita con un semplice DROP.
Le tecniche disponibili sono molteplici e dipendono dalla tecnologia adottata dai singoli produttori di database. Tuttavia i concetti di base sono sempre gli stessi!

Sulle basi dati relazionali il partizionamento riguarda tabelle di grandi dimensioni e/o i relativi indici. Il partizionamento e' efficace solo su oggetti di dimensione elevata; con tabelle di dimensione modesta il partizionamento introduce solo un'ulteriore complessita' e rallentamenti. La chiave o le chiavi di partizionamento ed il metodo di partizionamento vanno scelti con cura perche' da questi dipendono il numero e la dimensione delle partizioni. Troppe partizioni o partizioni sbilanciate tra loro peggiorano le prestazioni anziche' migliorarle.

Gli oggetti partizionati vengono visti come un tutt'uno negli statement SQL. Tuttavia le singole partizioni possono essere gestite singolarmente (eg. dump, offline, backup, reindex, ...). Le query SQL eseguite su piu' partizioni vengono tipicamente elaborate in parallelo su processi/processori differenti. Se una query riguarda una sola partizione l'ottimizzatore la riconosce ed effettua il pruning eseguendo la ricerca solo sulla partizione interessata e non sull'intera tabella.
Il partizionamento teoricamente fa parte del disegno fisico di una base dati, non ha impatti sul disegno logico o sulle applicazioni. Anche se naturalmente applicazioni ben disegnate possono sfruttare il partizionamento ottenendo prestazioni migliori (eg con il pruning SQL: lo statement SQL agisce solo sulle partizioni interessate). Dal punto di vista fisico il partizionamento puo' riguardare i dati e/o gli indici relativi (e' significativo il miglioramento prestazionale anche per gli indici poiche' il partizionamento consente di diminuire il numero di livelli dell'indice).
Teoricamente sulle tabelle il partizionamento puo' essere orizzontale (righe diverse su dispositivi diversi) o verticale (colonne diverse su dispositivi diversi). Nella maggioranza dei casi e' implementato a livello di RDBMS il partizionamento orizzontale mentre quello verticale puo' essere introdotto facilmente overnormalizzando le tabelle (due tabelle con la stessa primary key).
Un corretto partizionamento puo' essere molto vantaggioso ed e' praticamente necessario nelle basi dati di grandi dimensioni. Un partizionamento errato non introduce invece alcun vantaggio ed anzi rallenta ogni operazione sulla tabella. E' pertanto necessaria una certa esperienza ed attenzione per sfruttare al meglio tale tecnica.

Riassumendo il partizionamento, dal punto di vista funzionale, introduce una serie di vantaggi:

L'accesso ai dati e' piu' veloce
I backup ed i ripristini sono piu' veloci
Le partizioni possono essere gestite in modo indipendente rendendo piu' facili le allocazioni degli spazi, gli export/import, ...
Un'eventuale corruzione dati puo' essere gestita escludendo la sola partizione coinvolta
Operazioni come lo "svecchiamento dati" possono essere eseguite con semplicita' e velocemente
E non ultimo... e' facile da implementare e trasparente alle applicazioni!

Un'implementazione applicativa del partizionamento orizzontale e' quella di definire tabelle distinte con le stesse colonne e campi chiave, scegliendo una politica di indirizzamento delle INSERT e definendo una vista che e' l'unione di tutte le tabelle create. Tale banale implementazione non e' trasparente alle applicazioni, non e' gestita in modo ottimale dall'ottimizzatore, presenta parziali vantaggi dal punto di vista gestionare... ma e' comunque un punto di partenza per comprendere le tecniche di implementazione adottate dai diversi RDBMS che sono spesso diverse tra loro.

Di teoria abbiamo detto fin troppo, ora vediamo un poco di pratica con esempi su Oracle, MySQL, PostgreSQL, ... insomma continuate a leggere!

Oracle

L'implementazione del partizionamento in Oracle e' molto completa e permette la gestione efficiente di basi dati di grandi dimensioni. Oracle supporta, a partire dalla versione 8.0 del 1997 e via via con varie estensioni, diverse tipologie di partizionamento sulle tabelle:

RANGE: il partizionamento viene effettuato basandosi sul valore di una colonna chiave e confrontandolo su una serie di intervalli di valori.
HASH: il partizionamento viene effettuato basandosi su una funzione di hash applicata al valore di una colonna. Il meccanismo di hash non ha alcuna logica che possa essere utilizzata applicativamente per il pruning.
LIST: il partizionamento viene effettuato basandosi sul valore di una colonna chiave di cui vengono indicati i valori possibili con una lista.

Oracle supporta il sub-partitioning. Ovvero i dati partizionati secondo una certa logica possono essere ulteriormente partizionati secondo un altra tipologia di partizionamento (eg. RANGE-LIST).

Per gli indici sono possibili tre scelte:

Local: l'indice e' partizionato nello stesso modo in cui e' partizionata la tabella. La partizione dell'indice contiene solo i dati relativi alla partizione cui fa riferimento.
Global Partitioned: questo indice puo' essere creato su qualsiasi tabella che sia partizionata o meno. Il partizionamento dell'indice puo' essere diverso da quello della tabella (eg. avere una chiave differente o un numero differente di partizioni).
Global Non-Partitioned: si tratta di un normale indice. Non e' partizionato.

Tutto chiaro? Meglio vedere qualche esempio... Ecco un esempio di RANGE Partitioning:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
 ORDER_DATE DATE,
 CUST_NUM NUMBER,
 TOTAL_PRICE NUMBER,
 TOTAL_TAX NUMBER,
 TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(PARTITION SO07Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q1 VALUES LESS THAN TO_DATE(‘01-APR-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2009’, ‘DD-MON-YYYY’) );

Ecco un esempio di HASH Partitioning:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
 ORDER_DATE DATE,
 CUST_NUM NUMBER,
 TOTAL_PRICE NUMBER,
 TOTAL_TAX NUMBER,
 TOTAL_SHIPPING NUMBER,
 ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01, 
 PARTITION P2_ZIP TABLESPACE TS02,
 PARTITION P3_ZIP TABLESPACE TS03, 
 PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT);

Ecco un esempio di LIST Partitioning:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
 ORDER_DATE DATE,
 CUST_NUM NUMBER,
 TOTAL_PRICE NUMBER,
 TOTAL_TAX NUMBER,
 TOTAL_SHIPPING NUMBER,
 SHIP_TO_ZIP_CODE,
 SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01, 
 PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02, 
 PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03, 
 PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04, 
 PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
ENABLE ROW MOVEMENT);

Ecco un esempio di RANGE-LIST Partitioning:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
 ORDER_DATE DATE,
 CUST_NUM NUMBER,
 CUST_NAME VARCHAR2, 
 TOTAL_PRICE NUMBER,
 TOTAL_TAX NUMBER,
 TOTAL_SHIPPING NUMBER,
 SHIP_TO_ZIP_CODE,
 SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE) 
SUBPARTITION BY LIST(SHIP_TO_STATE) 
 SUBPARTITION TEMPLATE(
  SUBPARTITION SP1 TABLESPACE TS01,
  SUBPARTITION SP2 TABLESPACE TS02,
  SUBPARTITION SP3 TABLESPACE TS03,
  SUBPARTITION SP4 TABLESPACE TS04,
  SUBPARTITION SP5 TABLESPACE TS05)
(PARTITION SO07Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q1 VALUES LESS THAN TO_DATE(‘01-APR-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2008’, ‘DD-MON-YYYY’),
 PARTITION SO08Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2009’, ‘DD-MON-YYYY’) )
ENABLE ROW MOVEMENT);

Le partizioni create possono essere gestite in modo dinamico. Con la clausola ALTER TABLE e' possibile utilizzare [ DROP | TRUNCATE | SPLIT ] PARTITION ...
Ma vediamo anche qualche esempio:

SELECT * FROM SAMPLE_ORDERS PARTITION(SO07Q4);

ALTER TABLE SAMPLE_ORDERS MOVE PARTITION p1_zip
TABLESPACE TS05 NOLOGGING;

CREATE INDEX ORDER_DATE_IDX ON SAMPLE_ORDERS(geom)
LOCAL (
 PARTITION P1 PARAMETERS (TABLESPACE=’P1_TBS’),
 PARTITION P2 PARAMETERS (TABLESPACE=’P2_TBS’),
 PARTITION P3 PARAMETERS (TABLESPACE=’P3_TBS’),
 PARTITION P4 PARAMETERS (TABLESPACE=’P4_TBS’))
UNUSABLE;

ALTER INDEX ORDER_DATE_IDX REBUILD PARTITION P1;
ALTER INDEX ORDER_DATE_IDX REBUILD PARTITION P2;

Come si vede dal primo esempio e' possibile indicare in modo esplicito la partizione su cui eseguire una query.
La gestione degli spazi e delle allocazioni e' dinamica e puo' essere eseguita una partizione alla volta.
Nell'ultimo esempio viene definito un indice partizionato ma la generazione dell'indice avviene con i comandi sucessivi che possono essere lanciati e controllati in parallelo.

Sono molte le tabelle e le viste del Data Dictionary di Oracle che si riferiscono al partizionamento. Tra queste la dba_tab_partitions probabilmente la piu' utile al DBA.

Evoluzione
Il partitioning e' stato introdotto in Oracle dalla versione 8.0 (1997), in tale versione era disponibile la sola tipologia di partizionamento RANGE. Con la 8i sono stati aggiunti l'HASH ed il composite partitioning; con la 9i e' stato introdotto il LIST partitioning; la versione 10g e 10g R2 hanno migliorato gli algoritmi per la gestione degli indici e la gestione degli oggetti partizionati; la versione 11g introduce l'INTERVAL Partitioning, il REF Partitioning (su tabelle legate con primary/foreign key), il Virtual Column Partitioning (basato su colonne metadati) ed un nuovo wizard: il Partition Advisor. Nella versione 12cR2 e' possibile convertire online una tabella al partitioning.

Merita una menzione a parte lo sharding introdotto in Oracle 12c R2. L'Oracle Sharding e' implementato sul partitioning: ogni partizione di una sharded table e' posta su un tablespace differente ed ogni tablespace e' assegnato ad uno shard. Dal punto di vista applicativo pero' non vi e' nessuna differenza dovuta al fatto che le partizioni non sono sullo stesso nodo ma su nodi diversi.
Riprendendo il solito esempio e' chiaro che la sintassi non e' cambiata:

CREATE SHARDED TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
 ORDER_DATE DATE,
 CUST_NUM NUMBER,
 TOTAL_PRICE NUMBER,
 TOTAL_TAX NUMBER,
 TOTAL_SHIPPING NUMBER,
 ORDER_ZIP_CODE)
PARTITION BY CONSISTENT HASH (ORDER_NUMBER)
TABLESPACE SET tbs1
PARTITIONS AUTO;

Oltre alle sharded table Oracle 12cR2 supporta le table family e le duplicated table. Una table family e' un gruppo di tabelle distribuite sulle stesse chiavi: molto utile per le relazioni master-detail. Per indicare la relazione di partizionamento sulle tabelle dipendenti si utilizza la clausola BY REFERENCE o PARENT. Le duplicated tables sono tabelle duplicate su tutti gli shard come materialized view: in questo modo gli SQL che mettono in join sharded table e duplicated table operano sempre in locale.

Insomma e' un lunga storia!

Il partitioning e' una database Option quindi richiede l'utilizzo della Enterprise Edition la piu' completa e diffusa (NdE e costosa) edizione della base dati Oracle.

MySQL

MySQL, a partire dalla versione 5.1 del 2008, supporta diverse tipologie di partizionamento:

RANGE: il partizionamento viene effettuato basandosi sul valore di una colonna chiave e confrontandolo su una serie di intervalli di valori.
HASH: il partizionamento viene effettuato basandosi su una funzione di hash applicata al valore di una colonna. La funzione di hash viene definita dall'utente.
KEY: il partizionamento viene effettuato basandosi su una funzione di hash applicata al valore di una colonna. La funzione di hash e' interna a MySQL e puo' essere applicata ad una colonna di qualsiasi datatype.
LIST: il partizionamento viene effettuato basandosi sul valore di una colonna chiave di cui vengono indicati i valori possibili con una lista.

Il partizionamento, con la sola eccezione del tipo KEY, si basa sul valore intero della colonna. Tuttavia, utilizzando opportune funzioni di conversione (eg. YEAR(), TO_DAYS() per le date), e' possibile partizionare utilizzando colonne con qualsiasi tipo di dato. Le funzioni debbono restituire un valore intero ed e' importante effettuare una scelta corretta per mantenere il vantaggio prestazionale del partition pruning.
MySQL supporta il sub-partitioning. In questo caso ogni partizione deve avere lo stesso numero di sottopartizioni. Le partizioni possono essere avere nomi definiti al momento della creazione della tabella oppure prendono come default il nome p0, p1, p2, ...

Il partizionamento puo' essere utilizzato sulla maggioranza degli Engine (eg. MyISAM, InnoDB) ma su alcuni vi sono limitazioni (eg. NDB). Tutte le partizioni debbono utilizzare lo stesso Engine che in MySQL e' impostabile a livello di tabella.
Il partizionamento si applica alle tabelle e a tutti gli indici definiti su di esse. In MySQL la colonna su cui si effettua il partizionamento deve fare parte di ogni chiave univoca presente sulla tabella [NdA e' una condizione significativa perche' non e' presente altri database].

Qualche esempio? Ecco un esempio di RANGE Partitioning:

CREATE TABLE factTable (id INT, name VARCHAR(50), eventDate DATE)
    PARTITION BY RANGE( YEAR(eventDate) ) (
        PARTITION p0 VALUES LESS THAN (2005),
        PARTITION p1 VALUES LESS THAN (2006),
        PARTITION p2 VALUES LESS THAN (2007),
        PARTITION p3 VALUES LESS THAN MAXVALUE );

Ecco un esempio di HASH Partitioning:

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=INNODB
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;

Ecco un esempio di KEY Partitioning:

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

Ecco un esempio di RANGE Partitioning su una colonna di tipo DATE:

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN (2000),
    PARTITION p5 VALUES LESS THAN MAXVALUE );

Le partizioni possono essere gestite in modo dinamico. L'esempio piu' comune e' quello dello svecchiamento dati: con una semplice operazione di DDL e' possibile cancellare la parte di dati non piu' necessaria:

ALTER TABLE members DROP PARTITION p0;

E mi fermo qui... altrimenti al prossimo drop taglierei anche me stesso!

La vista del Data Dictionary MySQL che riporta i dati di partizionamento e' la information_schema.partitions che va interrogata con la condizione PARTITION_NAME is not NULL.
Oltre al file .frm, per le tabelle partizionate, e' presente il file .par. Nel partizionamento di una tabella MyISAM questa viene fisicamente spezzata in tanti file .MYD quante sono le partizioni (table_name#P#partition_id.MYD). Analogamente avviene per i file .MYI. Gli indici sono sempre partizionati e debbono contenere la chiave di partizionamento. Se il numero di partizioni e' elevato (eg. 100) e la chiave di partizionamento e' sempre o spesso utilizzata, i risparmi con il partition pruning possono essere di ordini di grandezza! Con i comandi SQL si agisce su file dati ed indice 100 volte piu' piccoli di quelli che avrebbe la tabella non partizionata.

Evoluzione
Il partitioning e' stato introdotto in MySQL dalla versione 5.1 (2Q 2008). La versione 5.5 ha introdotto il comando di TRUNCATE PARTITION e la possibilita' di effettuare il partizionamento su colonne di tipo diverso da INTEGER.

L'implementazione MySQL e' molto piu' semplice (e con meno funzionalita') rispetto a quella di Oracle. MySQL non gestisce il Parallel Query, quindi il Partitioning e' efficace solo se avviene il pruning delle query. Sulle tabelle partizionate non sono utilizzabili la query cache e gli indici FULLTEXT. Storicamente l'engine MyISAM e' quello che ottiene maggiori benefici dal partitioning ma le versioni piu' recenti [NdA 5.7] hanno migliorato in modo significativo le prestazioni anche per l'Engine InnoDB. L'ottimizzatore di MySQL e' meno smart di quello Oracle, quindi alcune query possono non ottenere vantaggi anche se basate su tabelle partizionate. Oracle offre piu' modalita' di partizionamento rispetto a MySQL, quindi e' possibile che per alcuni tipi di dati la condizione di partizionamento di MySQL non sia la migliore. ...
Nonostante questo i vantaggi prestazionali che si possono avere, sfruttando correttamente il partitioning con MySQL, sono molto elevati. Su un caso particolare di una grande base dati di produzione abbiamo ottenuto un migliormento delle prestazioni di due ordini di grandezza (10000% dati trattati in piu' nello stesso tempo): non e' poco! Altro vantaggio e' nell'utilizzare la TRUNCATE o la DROP sulle singole partizioni per svecchiare i dati.

La versione 8.0 di MySQL utilizza solo il partizionamento nativo, di conseguenza supporta il partitioning solo sugli Engine InnoDB e NDB [NdA il partizionamento nativo e' piu' efficiente ed e' stato introdotto con la versione 5.7].

PostgreSQL

PostgreSQL implementa il partizionamento dalla prima versione [NdA 1997] grazie alle sue funzionalita' di database ad oggetti. Non sono quindi presenti costrutti SQL aggiuntivi per il partizionamento ma viene utilizzata l'erediterieta' degli oggetti [NdA vedere anche il partizionamento nativo in versione 10 ed 11].

I passi per definire una tabella partizionata sono:

Creare la tabella "master"
Creare le tabelle "child" che corrispondono alle diverse partizioni. Le tabelle debbono avere il relativo constraint.
Creare gli indici. Almeno l'indice sulla colonna utilizzata per la condizione di partizionamento!
Definire un trigger per far effettuare l'inserimento sulla partizione corretta (eg. l'ultima)

Vediamo tutti i passi con un semplice esempio (lo stesso di Oracle RANGE partitioning ;-):

CREATE TABLE sample_orders
(ORDER_NUMBER int,
 ORDER_DATE date not null,
 CUST_NUM int,
 TOTAL_PRICE int,
 TOTAL_TAX int,
 TOTAL_SHIPPING int);

CREATE TABLE SO_07Q4 ( CHECK (order_date < DATE '2008-01-01') )
    INHERITS (sample_orders);
CREATE TABLE SO_08Q1 ( CHECK ( order_date >= DATE '2008-01-01' AND order_date < DATE '2008-04-01' ) ) 
    INHERITS (sample_orders);
CREATE TABLE SO_08Q2 ( CHECK ( order_date >= DATE '2008-04-01' AND order_date < DATE '2008-07-01' ) ) 
    INHERITS (sample_orders);
...

CREATE INDEX i_SO07Q4_orderdate ON SO_07Q4 (order_date);
CREATE INDEX i_SO07Q4_orderdate ON SO_08Q1 (order_date);
CREATE INDEX i_SO07Q4_orderdate ON SO_08Q2 (order_date);
...

CREATE OR REPLACE FUNCTION order_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO SO_11Q1 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER order_trigger
    BEFORE INSERT ON sample_orders
    FOR EACH ROW EXECUTE PROCEDURE order_insert_trigger();

Per la gestione delle "partizioni" si utilizzano normali comandi SQL di ALTER e DROP sulle tabelle child.
Per cancellare una partizione: DROP TABLE SO_07Q4; o meglio, se si vuole solo "sganciare": ALTER TABLE SO_07Q4 NO INHERIT sample_orders;
Affinche' il pruning funzioni correttamente e' molto importante che sia abilitato il relativo parametro: SET constraint_exclusion = on; Altra indicazione importante e' quella di controllare in modo molto attento che non si verifichino condizioni per cui le condizioni di CHECK sono verificate da piu' partizioni.

La gestione del partizionamento con PostgreSQL ricade quindi nella piu' ampia gestione degli oggetti. Mancano clausole SQL specifiche, costringendo quindi ad un lavoro un poco piu' lungo con le DDL in fase di disegno della base dati... ma in compenso la flessibilita' e' totale.

Evoluzione
L'ereditarieta' e' una caratteristica di base del database ad oggetti PostgreSQL e quindi il partitioning e' virtualmente presente fin dalla prima versione di PostgreSQL (1997). Il parametro constraint_exclusion, che consente il pruning delle query, e' stato introdotto nella versione 8.1 (2005). Sulla scrittura tuttavia e' necessaria la gestione con trigger...
Le versioni successive di PostgreSQL hanno mantenuto la stessa gestione del partitioning migliorando negli algoritmi dell'ottimizzatore fino a che...

Partizionamento dichiarativo in PostgreSQL
La versione 10 di PostgreSQL (2017-10) introduce il native Table Partitioning che promette futuri significativi vantaggi prestazionali! Il vantaggio e' una sintassi semplificata alla Oracle delle tabelle partizionate che diventano una nuova tipologia di relazioni [NdA relkind='p' in pg_class].
La versione 11 di PostgreSQL (2018-10) presenta significativi miglioramenti al partizionamento dichiarativo introdotto con la versione 10:

Partizionamento per HASH
Supporto di PRIMARY KEY, FOREIGN KEY, indici e trigger sulle tabelle partizionate
Move automatico dei dati tra partizioni (eg. UPDATE)
Default partition per record che non soddisfano i criteri del partitioning
Ottimizzazione del pruning nell'ottimizzatore

Anche se e' sempre possibile utilizzare l'erediterieta' degli oggetti per definire il partizionamento, dalla versione 11 di PostgreSQL la modalita' dichiarativa del partizionamento diventa quella da preferire, anche per le evoluzioni sul parallelismo ed il foreign data wrapper.

La versione 12 di PostgreSQL (2019-10) ha prestazioni significativamente migliori rispetto alla precedenti quando il numero di partizioni e' elevato.

TimescaleDB
Postgres, ed in generale i database relazionali, sono meno performanti rispetto ad altri database NoSQL nella gestione dei dati su serie temporali. L'estensione TimescaleDB risolve questo problema partizionando le tabelle temporali sul tempo ed eventualmente anche sullo spazio utilizzando le dimensioni presenti. Una tabella o relazione Postgres che contiene Time Series viene trasformata in Hypertable con TimescaleDB. Le Hypertables sono utilizzabili dagli utenti senza alcuna differenza rispetto alle altre tabelle nei join e nelle altre normali clausole SQL. Dal punto di vista fisico le Hypertable sono memorizzate in Chunck di dimensioni ottimizzate ed e' molto semplice gestirne la retention con la funzionr drop_chunk().

DB2

DB2 ha introdotto il table partitioning nella versione 9 (2006). DB2 consente il range partitioning indicando, nello statement CREATE TABLE la clausola PARTITION BY RANGE.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
 ORDER_DATE DATE,
 CUST_NUM NUMBER,
 TOTAL_PRICE NUMBER,
 TOTAL_TAX NUMBER,
 TOTAL_SHIPPING NUMBER)
IN ts1, ts2, ts3, ts4, ts5 
PARTITION BY RANGE(ORDER_DATE) (STARTING FROM ('01/01/2008') 
ENDING AT ('12/31/2009') EVERY (3 MONTHS))

L'organizzazione dei dati in DB2 e' molto flessibile ma anche complessa: sono presenti altre funzionalita' con caratteristiche simili al partizionamento delle tabelle.

Con la clausola ORGANIZE BY DIMENSION (disponibile dalla versione 8) viene utilizzata un'organizzazione a cluster dei dati su una o piu' tabelle.

Con la Database Partitioning Feature (disponibile con l'Edition InfoSphere Warehouse) i dati vengono distribuiti su partizioni, a livello dell'intero database, dotate ciascuna di risorse computazionali distinte. Queste "partizioni" possono risiedere su sistemi differenti e tipicamente cosi' avviene per disporre della massima scalabilita' negli accessi. Viene attivato dalle clausola DISTRIBUTE BY HASH.

SQL Server

Per gestire tabelle di grandi dimensioni SQL Server ha introdotto il partizionamento a partire dalla versione SQL Server 2005.
Il partizionamento di una tabella avviene in tre passi: definizione della funzione di partizionamento, definizione dello schema di partizionamento e partizionamento della tabella. Vediamolo con il solito esempio!

Funzione di partizionamento:

CREATE PARTITION FUNCTION OrderDatePF(datetime)
  AS 
  RANGE LEFT FOR VALUES ('20071231 23:59:59.997',
            '20080331 23:59:59.997',
            '20080630 23:59:59.997',
            '20080930 23:59:59.997')

Schema di partizionamento:

CREATE PARTITION SCHEME OrderDatePS AS 
  PARTITION OrderDatePF
  TO (fg1, fg2, fg3, fg4, fg5)

Creazione tabella:

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER INT,
 ORDER_DATE DATETIME
   CONSTRAINT OrdersRange
     CHECK (ORDER_DATE >= '20071231' AND ORDER_DATE <= '20081231 11:59:59.997'),
 CUST_NUM INT,
 TOTAL_PRICE MONEY,
 TOTAL_TAX MONEY,
 TOTAL_SHIPPING MONEY) 
ON OrderDatePS(ORDER_DATE)

Creata la tabella possono ora essere aggiunti indici sull'intera tabella o sulle partizioni...
Anche con SQL Server il partizionamento delle tabelle risulta efficace se accompagnato da un corretto disegno fisico delle partizioni sul sistema di storage.

MongoDB

MongoDB non e' un database relazionale e non ha il partitioning. Quindi teoricamente non c'entra nulla con questa pagina che tratta del partitioning sui database relazionali!
MongoDB e' uno dei piu' utilizzati database NoSQL. MongoDB utilizza la tecnica dello sharding per la distribuzione dei dati. Lo sharding e' un'evoluzione del partitioning nel senso che i dati vengono partizionati su nodi diversi e non solo su strutture fisiche differenti. Lo sharding permette un'ulteriore livello di scalabilita' dei database ed e' orientata ai Big Data.

L'architettura dello sharding prevede diverse componenti distribuite su piu' nodi: routers (mongos), config database server, due o piu' shard implementati su replica set.
Lo sharding introduce un elevato di scalabilita' orizzontale senza introdurre complessita' dal punto di vista applicativo. La chiave di sharding va scelta con attenzione perche' una scelta errata ha impatti prestazionali significativi e non puo' essere modificata senza ricostruire le collection.
Lo sharding e' rivolto alle installazioni piu' significative con decine o centinaia di server dedicati.

ClickHouse

ClickHouse e' un velocissimo database colonnare Open Source.
ClickHouse supporta nativamente il partizionamento e lo sharding. Con il partizionamento le query vengono eseguite con thread paralleli che agiscono su partizioni differenti sfruttando cosi' al massimo le capacita' elaborative degli attuali processori. Con lo sharding tabelle di dimensioni particolarmente elevate possono essere distribuite su piu' nodi sfruttando cosi' piu' sistemi per eseguire la stessa query.

Ecco un esempio di partitioning:

CREATE TABLE factTable (
    id UInt16,
    name String,
    eventDate DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(eventDate)
ORDER BY id;

In ClickHouse il partizionamento e' la base della struttura fisica delle tabelle, e' il riferimento per una serie di funzionalita' (eg TTL, il Time To Live dei dati) ed e' fondamentale per le prestazioni.

Conclusioni

Il partizionamento e' una ottima tecnica per la gestione di basi dati di grandi dimensioni. Con il partizionamento viene accresciuta di ordini di grandezza la massima dimensione degli oggetti gestiti in modo efficiente nelle basi dati consentendo una maggiore scalabilita' verticale dei sistemi.

La maggioranza delle basi dati, come Oracle, MySQL ed SQL Server, lo implementano utilizzando costrutti simili. PostgreSQL invece sfrutta le sue caratteristiche di database Object-Oriented. Ma le differenze non finiscono qui...
Con Oracle il partizionamento si aggiunge ad una completa e sofisticata gestione degli spazi (tablespace, datafile, storage clause, ASM, ...) mentre l'allocazione fisica in MySQL e' demandata agli Engine. Ad esempio con MyISAM si ha una semplice directory con una coppia di file per dati ed indici per ogni tabella. Con il partizionamento il DBA MySQL ha quindi un controllo sulle allocazioni di spazio che prima gli era impossibile (a parte le indicazioni DATA DIRECTORY o "trucchi" con i link simbolici)... Con PostgreSQL si ha una grande flessibilita' ma e' necessario un impegno maggiore nella creazione degli oggetti. L'ottimizzatore tratta il partizionamento semplicemente basandosi sulle condizioni di CHECK.
Con Oracle e' possibile scegliere la gestione degli indici tra tre differenti modalita' mentre con MySQL gli indici seguono il partizionamento della tabella (modalita' Local di Oracle).
DB2 e SQL Server forniscono modalita' di trattamento degli spazi disco molto sofisticate, complesse e, se ben utilizzate, molto efficienti come in Oracle. Anche se non hanno tutte le differenti modalita' di partizionamento presenti in Oracle la gestione del partizionamento con DB2 ed SQL Server e' comunque molto efficace.
Con PostgreSQL il disegno e' molto differente poiche' sfrutta le sue proprieta' di database ad oggetti. Per esempio e' completamente libera la gestione degli indici che e' infatti relativa ad ogni singola tabella child. Se il partizionamento con PostgreSQL e' libero e puo' essere modellato a piacere e' in questo caso importante disegnare la struttura e le query in modo che l'ottimizzatore sia in grado di effettuare il pruning.
L'implementazione dello sharding in MongoDB mostra la nuova frontiera del partizionamento implementando lo sharding tra nodi diversi.
Concludendo l'implementazione di Oracle e' sicuramente molto completa e complessa avendo piu' di 10 anni di storia, ma anche un semplice partizionamento come quello disponibile in MySQL risulta comunque assai efficace.

Database Administrator, non importa se usi Oracle, MySQL, PostgreSQL, ... comincia a partizionare!

etc

Questo documento ha trattato, in modo solo parziale ed introduttivo, il partizionamento orizzontale delle tabelle su basi dati relazionali. L'utilizzo di una vista UNION e' banale e presenta diversi svantaggi... quindi ovviamente non lo abbiamo trattato. Il partizionamento verticale e' banale su un relazionale (basta overnormalizzare usando piu' tabelle con la stessa chiave). Quello obliquo... non esiste ancora!
Ma quanto visto non e' l'unico modo di "partizionare" i dati! Altri argomenti vicini a questo sono le basi dati distribuite, la replicazione, il teorema CAP (Consistency, Availability, Partition tolerance), il database Sharding, l'utilizzo in Cloud, ... ovviamente le basi concettuali dei DB relazionali (12 rules, normalizzazione, ACID, MVCC) e come tutti questi temi vengono trattati su basi dati non-SQL! Ma lo vedremo in qualche altro documento, se mai riusciro' a scriverlo!

Alcuni documenti "ufficiali" sul partizionamento dei DB:

Oracle: Partitioning, Partitioning on Oracle Spatial
MySQL: Partitioning
PostgreSQL: Partitioning
DB2: Partitioning, Article
SQL Server: Partitioning
MongoDB: Sharding
ClickHouse: Partitioning Sharding

E non bisogna dimenticare che si possono anche partizionare i sistemi Unix!

Testo: DIVIDE ET IMPERA ! (Database Version)
Data: 1 Maggio 2008
Versione: 1.0.8 - 1 Gennaio 2018
Autore: mail@meo.bogliolo.name