Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Definizione dei dati > Partizionamento
PrecedenteEreditarietàAltri Oggetti di DatabaseSuccessivo

5.9. Partizionamento

PostgreSQL™ supporta un partizionamento di base delle tabelle. Questa sezione descrive perchè e come implementare il partizionamento come parte del progetto del database.

5.9.1. Panoramica

Partizionare si riferisce a dividere quella che è una tabella grande in pezzi fisici più piccoli. Il partizionamento può portare diversi vantaggi:

  • Le prestazioni delle query possono essere drasticamente migliorate in certe situazioni, particolarmente quando la maggior parte delle righe della tabella accesse pesantemente sono in una singola partizione od un piccolo numero di partizioni. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.

  • Quando delle query o degli aggiornamenti accedono ad un'alta percentuale di una singola partizione, le prestazioni possono essere migliorate traendo vantaggio da uno scan sequenziale di quella partizione invece di usare un indice e letture acsuali sparse attraverso l'intera tabella.

  • Grossi caricamenti ed eliminazioni possono essere svolti aggiungendo o rimuovendo partizioni, se questo requisito è stato messo in conto nel progetto di partizionamento. ALTER TABLE è molto più veloce di una oprazione massiva. Inoltre viene interamente evitato l'overhead del VACUUM causato da una DELETE massiva.

  • I dati usati raramente possono essere migrati verso supporti di immagazzinamento meno costosi e più lenti.

I benefici varranno la pena solo quando una tabella è molto grande. Il punto esatto in cui una tabella beneficierà del partizionamento dipende dall'applicazione, sebbene una regola generale sia che la dimensione della tabella dovrebbe eccedere la memoria fisica del database server.

Attualmente, PostgreSQL™ supporta il partizionamento attraverso l'ereditarietà. Ogni partizione dev'essere creata come tabella figlia di una singola tabella genitrice. La tabella genitrice di per sè normalmente è vuota; Esiste solo per rappresentare l'intero insieme di dati. Si dovrebbe avere familiarità con l'ereditarietà (vedere Sezione 5.8, «Ereditarietà») prima di tentare di mettere su il partizionamento.

Le seguenti forme di partizionamento possono essere implementate in PostgreSQL™:

Partizionamento a intervallo (Range Partitioning)

La tabella è partizionata in «intervalli» definiti da una colonna chiave o insieme di colonne, senza sovrapposizioni tra gli intervalli di valori assegnati alle diverse partizioni. Per esempio si potrebbe partizionare per intervalli di date, o per intervalli di identificatori di particolari oggetti.

Partizionamento a elenco (List Partitioning)

La tabella è partizionata elencando esplicitamente quali valori chiave appaiono in ogni partizione.

5.9.2. Implementare il Partizionamento

Per impostare una tabella partizionata, fare:

  1. Creare la tabella «master», dalla qualle tutte le altre erediteranno.

    Questa tabella non conterrà dati. Non definire nessun vincolo check su questa tabella, a meno che non si intenda applicarli equamente a tutte le partizioni. Non c'è neppure alcun motivo di definire un indice o vincolo unique.

  2. Crea diverse tabelle «figlie» ognuna delle quali eredita dalla tabella master. Normalmente, queste tabelle non aggiungeranno colonne all'insieme ereditato dalla master.

    Ci riferiremo alle tabelle figlie come "partizioni", nonostante siano normali tabelle PostgreSQL™.

  3. Aggiungere vincoli di tabella sulle tabelle-partizioni per definire i valori chiave permessi in ogni partizione.

    Tipici esempi potrebbero essere:

    CHECK ( x = 1 )
    CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
    CHECK ( outletID >= 100 AND outletID < 200 )
    

    Assicurarsi che i vincoli garantiscano che non ci siano sovrapposizioni tra i valori chiave permessi nelle varie partizioni. Un errore comune è impostare vincoli di range tipo:

    CHECK ( outletID BETWEEN 100 AND 200 )
    CHECK ( outletID BETWEEN 200 AND 300 )
    

    Questo è sbagliato dato che non è chiaro a quale partizione appartiene il 200.

    Notare che non c'è differenza nella sintassi tra il partizionamento di intervallo e il partizinamento di elenco; questi termini sono solo descrittivi.

  4. Per ogni partizione, creare un indice su ogni colonna/e, così come ogni altro indice potresti volere. (L'indice chiave non è strettamente necessario, ma in molte situazioni è utile. Se si vuole che i valori chiave siano unici allora si dovrebbe sempre creare un vincolo unique o chiave primaria per ogni partizione.)

  5. Opzionalmente, definire un trigger o una regola per redirigere i dati inseriti nella tabella master alle partizioni appropriate.

  6. Assicurarsi che il parametro di configurazione constraint_exclusion non sia disabilitato in postgresql.conf. Se lo è, le query non saranno ottimizzate come desiderato.

Per esempio, supponiamo di realizzare un databse per una grande ditta di gelati. La ditta misura le temperature massime ogni giorno così come le vendite di gelati in ogni regione. Concettualmente, la tabella sarà:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

La maggior parte delle query accederanno ai dati dell'ultima settimana, mese o trimestre, dato che l'utilizzo principale di questa tabella sarà di preparare rapporti online per la gestione. Per ridurre l'ammontare di vecchi dati che devono essere immagazzinati, vale la pena tenere solo i 3 anni di dati più recenti. All'inizio di ogni mese verranno cancellati i dati del mese più vecchio.

In questa situazione è possibile usare il partizionamento per far fronte a tutte le esigenze della tabella delle misurazioni. Seguendo i passi evidenziati sopra, il partizionamento può essere impostato come segue:

  1. La tabella master è measurement, dichiarata esattamente come sopra.

  2. Successivamente creare una partizione per ogni mese attivo:

    CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
    ...
    CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
    CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
    

    Ognuna delle partizioni è una tabella completa, ma eredita la sua definizione dalla tabella measurement.

    Questo risolve uno dei problemi: la cancellazione dei vecchi dati. Ogni mese, basterà eseguire una DROP TABLE sulla tabella figlia più vecchia e creare una nuova tabella per i dati del mese nuovo.

  3. E' necessario fornire vincoli di tabella non sovrapponibili. Piuttosto che creare semplicemente le tabelle partizione come sopra, lo script di creazione delle tabelle dovrebbe essere:

    CREATE TABLE measurement_y2006m02 (
        CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2006m03 (
        CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
    ) INHERITS (measurement);
    ...
    CREATE TABLE measurement_y2007m11 (
        CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2007m12 (
        CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
    ) INHERITS (measurement);
    CREATE TABLE measurement_y2008m01 (
        CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
    ) INHERITS (measurement);
    

  4. Probabilmente ci sarà bisogno di indici sulle colonne chiave:

    CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
    CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
    ...
    CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
    CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
    CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
    

    Per adesso scegliamo di non aggiungere ulteriori indici.

  5. Vogliamo che l'applicazione sia capace di indirizare nelle tabelle di partizione appropriate i dati inseriti con INSERT INTO measurement .... Per realizzare ciò, creare una funzione di trigger adatta e collegarla alla tabella master. Se i dati saranno aggiunti solo all'ultima partizione, usare una funzione trigger molto semplice:

    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEgin
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    

    Dopo aver creato la funzione, creare un trigger che la chiama:

    CREATE TRIGGER insert_measurement_trigger
        BEFORE INSERT ON measurement
        FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
    

    È necessario ridefinire la funzione trigger ogni mese così che punti sempre alla partizione corrente. La definizione del trigger, comunque, non deve essere aggiornata.

    Si potrebbe voler inserire i dati e far sì che il server automaticamente localizzi la partizione in cui la riga dovrebbe essere aggiunta. E' possibile farlo con una funzione di trigger più complessa, per esempio:

    CREATE OR REPLACE FUNCTION measurement_insert_trigger()
    RETURNS TRIGGER AS $$
    BEgin
        IF ( NEW.logdate >= DATE '2006-02-01' AND
             NEW.logdate < DATE '2006-03-01' ) THEN
            INSERT INTO measurement_y2006m02 VALUES (NEW.*);
        ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
                NEW.logdate < DATE '2006-04-01' ) THEN
            INSERT INTO measurement_y2006m03 VALUES (NEW.*);
        ...
        ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
                NEW.logdate < DATE '2008-02-01' ) THEN
            INSERT INTO measurement_y2008m01 VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
        END IF;
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    

    La definizione del trigger sarà la stessa di prima. Notare che ogni test IF deve esattamente corrispondere al vincolo CHECK per la sua partizione.

    Mentre questa funzione è più complessa che quella riguardante il singolo mese, non ha bisogno di essere aggiornata così spesso, dato che possono essere aggiunti rami in anticipo per ogni bisogno.

    [Nota]

    Nota

    In pratica potrebbe essere meglio controllare la partizione più nuova prima, se la maggior parte degli inserimenti vanno in quella partizione. Per simplicità si sono mostrati i test del trigger nello stasso ordine che in altre parti dell'esempio.

Come si può notare, uno schema di partizionamento complesso potrebbe richiedere una grande quantità di DDL. Nell'esempio sopra di dovrebbe creare una nuova partizione ogni mese, così potrebbe esser saggio scrivere uno script che generasse il DDL richiesto automaticamente.

5.9.3. Gestire le Partizioni

Normalmente l'insieme delle partizioni stabilite quando inizialmente si definisce la tabella non rimarrà statico. È comune voler rimuovere vecchie partizioni di dati e periodicamente aggiungere nuove partizioni per nuovi dati. Uno dei più importanti vantaggi del partizionamento è precisamente che permette che questo compito che altrimenti sarebbe penoso, sia eseguito quasi istantaneamente manipolando la struttura della partizione, invece che spostare fisicamente grandi quantità di dati.

L'opzione più semplice per rimuovere vecchi dati è semplicemente di eliminare la partizione che non è più necessaria:

DROP TABLE measurement_y2006m02;

Questo può cancellare milioni di record velocemente dato che non deve cancellare individualmente ogni record.

Un'altra opzione che spesso è preferibile è quella di rimuovere la partizione dalla tabella partizionata ma conservare l'accesso ad essa come tabella a sè stante:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

Questo permette di svolgere ulteriori operazioni sui dati prima di essere eliminati. Per esempio, spesso questo è utile per eseguire un backup dei dati usando COPY, pg_dump, o strumenti simili. Potrebbe anche essere utile per aggregare dati in formati più piccoli, eseguire altre manipolazioni di dati, o fare rapporti sui dati.

Similarmente, è possibile aggiungere una nuova partizione per trattare nuovi dati. E' possibile creare una partizione vuota nella tabella partizionata semplicemente come le partizioni originali create sopra:

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

Come alternativa, a volte è più comodo creare la nuova tabella fuori la struttura delle partizioni, e renderla una una partizione vera e propria più tardi. Questo permette ai dati di essere caricati, controllati e trasformari prima di apparire nella tabella partizionata:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibilmente qualche altro lavoro sulla preparazione dei dati
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.9.4. Partizionamento e Vincolo Exclusion

Il vincolo exclusion è una tecnica per ottimizzare una query che aumenta le prestazioni per le tabelle partizionate definite nel modo descritto sopra. Come esempio:

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

Senza il vincolo exclusion, la query sopra avrebbe controllato ogni partizione della tabella measurement. Con il vincolo exclude abilitato, verranno esaminati i vincoli di ogni partizione e si tenterà di provare che la partizione non necessita di essere controllata perchè non potrebbe contenere alcuna riga che rispetta la clausola WHERE. Quando si può provare questo, la partizione verrà esclusa dalla query.

È possibile usare il comando EXPLAIN per mostrare le differenze tra avere constraint_exclusion attiva e disattiva. Un esempio di query plan per questo tipo di tabella è:

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

Alcune o tutte le partizioni potrebbero usare la scansione degli indici invece di una scansione sequenziale di tutta la tabella, ma il punto qui è che non c'è bisogno di scansionare le vecchie partizioni per niente per rispondere a quella query. Con il vincolo exclusion abilitato, si ha un query plan significativamente più economico che porterà alla stessa risposta:

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

Notare che il vincolo exclusion è guidato solo dal vincolo CHECK, non dalla presenza degli indici. Perciò non è necessario definire indici sulle colonne chiave. Se un indice deve essere creato per una data partizione dipende dal fatto che ci si aspetti che le query che scansionano la partizione generalmente scansionino una grande parte della partizione o solo una piccola parte. Un indice sarà utile nell'ultimo caso ma non nel primo.

L'impostazione di default (e raccomandata) di constraint_exclusion attualmente non è nè onoff, ma un valore intermedio chiamato partition, che fa sì che la tecnica sia applicata solo su query che probabilmente lavorano su tabelle partizionate. L'impostazione on fa sì che i vincoli CHECK siano esaminati in tutte le query, anche quelle semplici che poco probabilmente ne trarranno beneficio.

5.9.5. Metodi di Partizionamento Alternativi

Un approccio differente per indirizzare gli inserimenti nell'appropriata tabella partizione è di impostare regole, invece di trigger, sulla tabella master. Per esempio:

CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

Una regola ha un costo significativamente maggiore rispetto a un trigger, ma il costo è pagato una volta per query invece di una volta per riga, così questo metodo potrebbe essere vantaggioso per inserimenti massivi. In molti casi, comunque, il metodo basato sui trigger offre migliori prestazioni.

Tenere presente che COPY ignora le regole. Se si vuole usare COPY per inserire dati, si dovranno copiare nella corretta tabella partizione piuttosto che nel master. COPY attiva i trigger, così puoi usarlo normalmente se usi l'approccio con i trigger.

Un altro svantaggio dell'approccio con le regole è che non c'è un modo semplice di forzare un errore se l'insieme di regole non soddisfa i dati da inserire; I dati verranno invece inseriti silenziosamente nella tabella master.

Il partizionamento può anche essere programmato usando una view UNION ALL, invece dell'ereditarietà. Per esempio:

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

Comunque, la necessità di ricreare la view aggiunge un ulteriore passo all'aggiunta ed eliminazione di partizioni singole dell'insieme di dati. In pratica questo metodo è poco raccomandabile rispetto all'uso dell'ereditarietà.

5.9.6. Avvertimenti

I seguenti avvertimenti si applicano alle tabelle partizionate:

  • Non c'è un modo automatico per verificare che tutti i vincoli CHECK sono mutualmente esclusivi. È più sicuro creare codice che genera le partizioni e creare e/o modificare gli oggetti associati che scriverle a mano.

  • Gli schemi mostrati qui assumono che la/e colonna/e chiave della partizione do una riga non cambia mai, o almeno non cambia abbastanza da richiedere che venga mossa verso un'altra partizione. Un UPDATE che tenta di fare ciò fallirà causa il vincolo CHECK. Se hai bisogno di far fronte a questi casi, puoi inserire trigger di aggiornamento sulle tabelle partizioni, ma questo renderà la gestione della struttura molto più complicata.

  • Quando si usano comandi come il VACUUM manuale o ANALYZE, non dimenticarsi che è necessario eseguirli individualmente sulle partizioni. Un comando tipo:

    ANALYZE measurement;
    

    Processerà solo la tabella master.

I seguenti avvertimenti sono validi per il vincolo exclusion:

  • Il vincolo exclusion funziona solo quando la lausola WHERE della query contiene costanti. Una query parametrizzata non sarà ottimizzata, fino a che non sia possibile stabilire il valore che quel parametro potrebbe avere. Per la stessa ragione, funzioni «stabili» tipo CURRENT_DATE dovrebbero essere evitate.

  • È consigliabile mantenere i vincoli di partizionamento semplici, altrimenti il planner potrebbe non essere capace di provare che le partizioni non hanno bisogno di essere visitate. Usare condizioni di uguaglianza semplici per partizionamenti a elenco, o semplici test di intervallo per partizionamenti di intervallo, come illustrato nei precedenti esempi. Una buona regola generale è che i vincoli di partizionamento dovrebbero contenere solo comparazioni tra colonna/e di partizionamento e costanti usando operatori indicizzabili B-tree.

  • Tutti i vincoli su tutte le partizioni della tabella master sono esaminati durante il vincolo exclusion, così grandi quantità di partizioni probabilmente aumenteranno considerevolmente il tempo di query planning. Pertizionare usando queste tecniche funzionerà bene con circa cento partizioni; Non tentare di usare molte migliaia di partizioni.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Definizione dei dati > Partizionamento
PrecedenteEreditarietàAltri Oggetti di DatabaseSuccessivo