postgresql: partitioning ap-camp 2010

09/10/10 /Users/sscotty71/Desktop/partitioning_apcamp.odp page 1

PARTITIONING

AP CAMP 9 Ottobre 2010


Agenda

Cos'è il partitioning

Ereditarietà

Rules

Triggers

Metodi per eseguire il partitioning

Partition setup

Partition table explain plain

Tablespace


Ereditarietà

Alcuni esempi:

apcamp=# insert into citta (nome,popolazione,altitudine)values ('Alba Adriatica',10000,0);

INSERT 0 1

apcamp=# insert into capitali (nome,popolazione,altitudine,stato)values ('Roma',1000000,0,'IT');

INSERT 0 1

apcamp=# select * from citta;

nome | popolazione | altitudine

----------------+-------------+------------

Alba Adriatica | 10000 | 0

Roma | 1000000 | 0

(2 rows)



Clausola ONLY : ritorna i dati solo dalla tabella specificata

apcamp=# select * from only citta;


----------------+-------------+------------


(1 row)



----------------+-------------+------------


(1 row)

apcamp=# select * from only capitali;

nome | popolazione | altitudine | stato

------+-------------+------------+-------

Roma | 1000000 | 0 | IT

(1 row)

apcamp=# select * from capitali;


------+-------------+------------+-------

Roma | 1000000 | 0 | IT

(1 row)



ATTENZIONE : Con i comandi INSERT o COPY l'ereditarietà non propaga automaticamente i dati alle altre tabelle dell'albero genealogico

apcamp=# insert into citta (nome,popolazione,altitudine,stato)values ('Roma',1000000,0,'IT');

ERROR: column "stato" of relation "citta" does not exist

LINE 1: insert into citta (nome,popolazione,altitudine,stato)values ...


Rules (cenni)

Sintassi :

CREATE [ OR REPLACE ] RULE name AS ON event

TO table [ WHERE condition ]

DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

Modifica l'azione di un evento

Gli eventi modificabili sono INSERT,DELETE,UPDATE

E' possibile far partire azioni al posto di quelle di default o in concomitanza con quelle di default.


Rules (cenni)

Esempio:apcamp=# create table errori_capitali ( stato char(2) ) INHERITS (citta);

CREATE TABLE

apcamp=# create or replace rule my_rule as on insert to capitali where (popolazione < 0) do instead insert into errori_capitali (nome,popolazione,altitudine,stato) values (NEW.nome,NEW.popolazione,NEW.altitudine,NEW.stato);

CREATE RULE

apcamp=# insert into capitali (nome,popolazione,altitudine,stato) values ('Parigi','-10000',200,'FR');

INSERT 0 0

apcamp=# SELECT * from errori_capitali ;


--------+-------------+------------+-------

Parigi | -10000 | 200 | FR

(1 row)

apcamp=#


Rules (cenni)

Ogni volta che inseriamo un record il cui campo popolazione sia < 0, La rule, fa si che questo record non venga inserito nella tabella capitali ma in quella errori_capitali

apcamp=# create or replace rule my_rule as on insert to capitali where (popolazione < 0) do instead insert into errori_capitali (nome,popolazione,altitudine,stato) values (NEW.nome,NEW.popolazione,NEW.altitudine,NEW.stato);

PostgreSQL gestisce le rules prima dei triggers


Triggers (cenni)

E' un gestore complesso di eventi

NEW è la variabile che contiene il nuovo valore del record;

Schema classico :

EventoAzione

TriggerInsert, Update, Delete Funzione Trigger


Triggers (cenni)

Creazione della funzione che viene chiamata dal trigger

CREATE OR REPLACE function f_trigger() RETURNS trigger

as $$

BEGIN

.........

RETURN ......

END;

$$

language plpgsql;

Creazione del Trigger

CREATE TRIGGER miotrigger BEFORE INSERT ON citta FOR EACH ROW EXECUTE PROCEDURE f_trigger();


Trigger (cenni)

Eseguiamo una procedura fatta prima con le rules utilizzando un trigger:

apcamp=# SELECT * from errori_capitali ;


--------+-------------+------------+-------

Parigi | -10000 | 200 | FR

La rule fatta agiscesolo sulla tabellacapitali non sulla

tabella città

apcamp=# insert into citta (nome,popolazione,altitudine) values ('Milano','-10000',200);

INSERT 0 1



----------------+-------------+------------


Milano | -10000 | 200

(2 rows)


Trigger (cenni)


as $$

BEGIN

IF NEW.popolazione < 0 THEN

insert into errori_capitali (nome,popolazione,altitudine)

values (NEW.nome,NEW.popolazione,NEW.altitudine);

END IF;

RETURN NULL;

END;

$$

language plpgsql;

Funzione chiamatadal Trigger

CREATE TRIGGER miotrigger BEFORE INSERT ON citta FOR EACH ROW EXECUTE PROCEDURE f_trigger();

Creazione del Trigger


Trigger (cenni)

apcamp=# insert into citta (nome,popolazione,altitudine) values ('Palermo','-10000',200);

INSERT 0 0



----------------+-------------+------------


Milano | -10000 | 200

(2 rows)

apcamp=# select * from errori_capitali;


---------+-------------+------------+-------

Parigi | -10000 | 200 | FR

Palermo | -10000 | 200 |

(2 rows)


Trigger (cenni)

Schema riassuntivo

città

Errori_capitali

capitali

Rule

Trigger


Domande ????????

Ancora Vivi?


Cos'è il partitioning?

Partitioning è dividere una tabella con tanti record in parti più piccole

Benefici:

Aumento delle prestazioni di ricerca e aggiornamento

Maggiore facilità nel migrare i dati della tabella su supporti di storage più economici.

Tabella originaria

Q1 Q2 Q3 Q4


Overview

Partitioning significa dividere in piccole tabelle i contenuto di una tabella di grande dimensione

Le perfomance possono essere incrementate drasticamente per certi tipi di ricerche .La performance di update vengono incrementate.

Gli indici possono entrare in memoria perchè di più piccola dimensione

Alle volte le operazioni di delete possono equivalere semplicemente con il rimuovere una delle partizioni

I dati posso essere passati su supporti più piccoli quindi più economici e/o più veloci

Postgresql realizza il partitioning attraverso l'ereditarietà delle tabelle. Ogni partizione deve essere creata come una tabella figlia di una singola tabella madre. La tabella madre esiste solo per rapprsentare il dataset ed è normalmente vuota.


Metodi per il partitioning

Range Partitioning

Le partizioni sono definite attraverso dei vincoli sulla chiave primaria e non devono essere presenti sovrapposizioni o buchi.

List Partitioning

Ogni valore della chiave primaria viene specificato in una lista


Partitioning Setup

Creare la master table (tabella madre)

Questa tabella non conterrà i dati, definirà solamente i vincoli che verranno applicati a tutte le partizioni.

Creare le tabelle figlie attraverso l'ereditarietà con la tabella master

Aggiungere i vincoli nella tabella figlia per definire i vincoli attraverso i quali i valori chiave vengono accettati

Alcuni esempi: check (x = 1)

Check (provincia in ('MI','TO','BO'))

Check (id > 100 and id < 200)

Verificare che non ci siano sovrappposizioni di valori nelle tabelle figlie


Partitioning Setup

Creare gli indici necessari in ogni tabella figlia

Definire le regole o i trigger per ridirigere i dati dalla tabella master verso la tabella figlia appropriata.

Assicurarsi che constraint_exclusion in postgresql.con sia impostato su true


Esempio

mov_mag

mov_mag2008

mov_mag2008

mov_mag2008

mov_mag2008


Partioned table explain plan

explain SELECT * from mov_mag where campo1 = 'val1' and campo2 = 'val2';

QUERY PLAN --------------------------------------------------------------------------------------------

Result (cost=0.00..29.83 rows=2 width=80)

-> Append (cost=0.00..29.83 rows=2 width=80)

-> Seq Scan on mov_mag (cost=0.00..21.55 rows=1 width=76)

Filter: ((campo1 = 'val1'::bpchar) AND ((campo2)::text = 'val2'::text))

-> Index Scan using citidx1 on mov_mag_2008 mov_mag (cost=0.00..8.28 rows=1 width=80)

Index Cond: ((campo2)::text = 'val2'::text)

Filter: ("campo1" = 'val1'::bpchar)

(7 rows)


Creazione Master Table

CREATE TABLE vendite (

id INTEGER NOT NULL,

data_vendita DATE,

somma NUMERIC(9,2)

);


Creazione delle tabelle figlie

CREATE TABLE vendite_08q1 (

CHECK (data_vendita >= DATE '2008-01-01' AND data_vendita < DATE '2008-04-01' )

) INHERITS (vendite);





Creazione delle tabelle figlie








Creazione degli indici

● CREATE INDEX vendite_08q1_data_vendita on vendite_08q1 (data_vendita);





Trigger o Rules?

Possiamo ora eseguire la suddivisione dei dati utilizzando Triggers o Rules

Le Rules vengono gestite prima dei trigger

I trigger consentono una gestione più approfondita degli eventi

Triggers o Rules ?


Creazione delle RULES

CREATE RULE inserimento_vendita_08q1 AS

ON INSERT TO vendite WHERE

(data_vendita >= DATE '2008-01-01' AND

data_vendita < DATE '2008-04-01')

DO INSTEAD

INSERT INTO vendite_08q1(NEW. id,NEW.data_vendita,NEW.somma);

STESSA COSA PER LE ALTRE TABELLE FIGLIE


CREAZIONE DEL TRIGGER


as $$

begin

IF

NEW.data_vendita >= DATE '2008-01-01'

AND NEW.data_vendita < DATE '2008-04-01' THEN

INSERT INTO vendite_08q1 values (NEW.id,NEW.data_vendita,NEW.somma);

ELSIF



NEW.data_vendita >= DATE '2008-04-01'

AND NEW.data_vendita < DATE '2008-07-01' THEN

INSERT INTO vendite_08q2 values (NEW.id,NEW.data_vendita,NEW.somma);

ELSIF

......

END IF;

RETURN NULL;

END;

$$

language plpgsql;



CREATE TRIGGER miotrigger BEFORE INSERT ON vendite FOR EACH ROW EXECUTE PROCEDURE f_trigger();

A questo punto la procedura di partitioning è terminata


Partitioning : manuntenzione

Esistono diversi modi per rimuovere una partizione

DROP TABLE nome_partizione

E' preferibile di più però ALTER TABLE nome_partizione_NO INHERIT master_table

Se invece vogliamo cancellare la master_table e tutte le sue partizioni :

DROP TABLE master_table CASCADE


CAVEATS

Non c'è un metodo per verificare che tutti i vincoli imposti siano mutuamente esclusivi

Non c'è un metodo per specificare che nessuna riga deve essere inserita nella master_table → senza TRIGGER ovviamente

Dal momento che la master_table non contiene dati non è possibile assegnare alcuna foreign key alla master_table


PARTITIONING

Cos'è il partitioning

Ereditarietà

Rules

Triggers

Metodi per eseguire il partitioning

Partition setup

Partition table explain plain

Tablespace


Contatti

Relatore Enrico Pirozzi

www.pgtraining.com - [email protected]

Contatti Personali:

www.enricopirozzi.info - [email protected]

http://www.pgtraining.com/

mailto:[email protected]

http://www.enricopirozzi.info/

postgresql: partitioning ap-camp 2010

Education