tutorial all pp partition table

8/9/2019 Tutorial All PP Partition Table

1/14

How to Partition a Table in Postgres Plus

How To Partition a Table in Postgres Plus(R)

A Postgres Evaluation Quick TutorialFrom EnterpriseDB

ovember !"# $%%"

EnterpriseDB Corporation, 235 Littleton Road, Westford, MA 01866, UA

T !1 "#8 58" 5#00 F !1 "#8 58" 5#01 E info$enterprised%&'o( www&enterprised%&'o(

Copyright © 2009 EnterpriseDB Corporation. All rights reserved. 1


2/14


&ntro'uction

)*is t+torial s*os -o+ *o to .+i'/l- 'reate ta%le partitions in ostres l+s tandard

or Adan'ed erers and t*en .+er- t*e data a'ross t*e partitions&

)*is EnterpriseDB +i'/ )+torial *elps -o+ et started it* t*e ostres l+s tandarderer or ostres l+s Adan'ed erer data%ase prod+'ts in a Lin+4, Windos or Ma'

eniron(ent& t is ass+(ed t*at -o+ *ae alread- donloaded and installed ostres l+s

tandard erer or ostres l+s Adan'ed erer on -o+r des/top or laptop 'o(p+ter&

)*is +i'/ )+torial is desined to *elp -o+ e4pedite -o+r )e'*ni'al Eal+ation ofostres l+s tandard erer or ostres l+s Adan'ed erer& or (ore infor(ational

assets on 'ond+'tin -o+r eal+ation of ostres l+s, isit t*e self7seri'e e% site,

ostres l+s pen o+r'e Adoption&

n t*is +i'/ )+torial -o+ ill learn *o to do t*e folloin9

• 'reate a partitioned ta%le +sin r+les

• add test data to a r+le partitioned ta%le and ie it

• 'reate a partitioned ta%le +sin triers

• add test data to a trier partitioned ta%le and ie it

Usage Note: W*ile t*e e4a(ples in t*is t+torial are de(onstrated in a Windoseniron(ent, t*e steps are t*e sa(e for t*e Lin+4 and Ma' eniron(ents& :o+ ill

noti'e sli*t ariations %eteen t*e operatin s-ste(s; t*ere are differen'es in t*e tools

+sed e and (a/in it

(ore li/el- t*at t*e *eail- +sed parts of t*e inde4 fits in (e(or-&

2& W*en a .+er- or +pdate a''esses a lare per'entae of a sinle partition,

perfor(an'e (a- i(proe %e'a+se t*e serer ill perfor( a se.+ential s'an of


http://www.enterprisedb.com/http://www.enterprisedb.com/products/postgres_plus/overview.dohttp://www.enterprisedb.com/products/postgres_plus/overview.dohttp://www.enterprisedb.com/products/postgres_plus/overview.dohttp://www.enterprisedb.com/products/postgres_plus_as/overview.dohttp://www.enterprisedb.com/solutions/stages/overview.dohttp://www.enterprisedb.com/solutions/stages/overview.dohttp://www.enterprisedb.com/solutions/stages/overview.dohttp://www.enterprisedb.com/products/postgres_plus/overview.dohttp://www.enterprisedb.com/products/postgres_plus/overview.dohttp://www.enterprisedb.com/products/postgres_plus_as/overview.dohttp://www.enterprisedb.com/solutions/stages/overview.dohttp://www.enterprisedb.com/


3/14


t*e partition instead of +sin an inde4 and rando( a''ess reads s'attered a'rosst*e *ole ta%le&

3& A %+l/ load


4/14


n t*e e4a(ple, t*e r+le is a 'ode snippet t*at inter'epts t*e INSERT state(ent and re7

dire'ts t*e INSERT to add data to a partitioned ta%le %ased on t*e al+e of t*e data&

Step 1 (IMPORTANT): Enable constraint_exclusion.

in'e ostres l+s +ses t*e 'on'ept of 'onstraint e4'l+sion to ena%le partition %o+ndar-'*e'/in, it is 'riti'al t*at -o+ set t*e folloin para(eter in t*e postgresql.con

file& :o+ 'an open t*e postgresql.con file


5/14


C)*PostgresPl!s*+.,R-AS*$ser/er*#n

Enter t*e folloin 'o((and


6/14


W*en pro(pted, enter -o+r 'onne'tion infor(ation& W*en -o+e spe'ified -o+r

'onne'tion infor(ation


7/14


CREATE OR REPLACE RULE #nsert(sales(p9AS ON INSERT TO sales=ERE 6org 1?;

DO INSTEADINSERT INTO sales(part9 VALUES6NE.org7 NE.na3e;<

CREATE OR REPLACE RULE #nsert(sales(p-AS ON INSERT TO sales=ERE 6org 2@? an$ org 1@9: ;

DO INSTEADINSERT INTO sales(part- VALUES6Ne5.org7Ne5.na3e;<

NOTE: :o+ (+st 'reate additional r+les to *andle UPDATE and DELETE operations&

Step : A%% saple %ata to t!e new table.

INSERT INTO sales VALUES697Cra#g;< INSERT INTO sales VALUES6-7M#e;< INSERT INTO sales VALUES6,7M#c8elle;< INSERT INTO sales VALUES67oe;< INSERT INTO sales VALUES6F7Scott;< INSERT INTO sales VALUES6?7Roger;< INSERT INTO sales VALUES6G7Hre$;< INSERT INTO sales VALUES6+7Sa3;< INSERT INTO sales VALUES67SonnJ;< INSERT INTO sales VALUES69:7C8r#s;<

Step *: Con+$ t!at t!e %ata was a%%e% to t!e paent table.

postgres@' SELECT K HROM sales


8/14


postgres@' SELECT K HROM sales(part9< org na3e444444444444444 9 Cra#g - M#e , M#c8elle

oe F Scott6F ro5s;

postgres@' SELECT K HROM sales(part-< org na3e444444444444 ? Roger G Hre$ + Sa3 SonnJ 9: C8r#s

6F ro5s;

As -o+ 'an see, t*e data as sent to t*e appropriate partition&

)*e ostreL EPLAIN state(ent 'an proide so(e insi*t into *o t*e ostres l+s

.+er- opti(i>er ill e4e'+te a ien state(ent& :o+ 'an +se t*e EPLAIN state(ent to

anal->e SELECT, INSERT, DELETE, UPDATE and DECLARECURSOR 'o((ands& )*e

EPLAIN state(ent does not a't+all- e4e'+te t*e state(ent %ein anal->ed, and an-


9/14


n t*e e4a(ple, a trier inter'epts an INSERT state(ent t*at adds data to a ta%le, and

redire'ts t*e INSERT state(ent to add data to t*e '*ild@partitioned ta%les&

Step 1 (IMPORTANT): Enable constraint_exclusion.

in'e ostres l+s +ses t*e 'on'ept of 'onstraint e4'l+sion to ena%le partition %o+ndar-'*e'/in, it is 'riti'al t*at -o+ set t*e folloin para(eter in t*e postgresql.con

file& :o+ 'an edit t*e postgresql.con file %- openin t*e Start (en+ and

naiatin to t*e Postgres Pl!s (en+& C*oose E"pert Con#g!rat#on and t*en

E$#t postgresql.con to open t*e postgresql.con file


10/14


C)*PostgresPl!s*+.,R-AS*$ser/er*#n

Enter t*e folloin 'o((and9

pg(ctl 0D 1datadir 2 reloa$

datadir is t*e f+ll pat* to -o+r data dire'tor-&

B- defa+lt, t*e data for a standard installation of ostres l+s Adan'ed erer is storedin C)*PostgresPl!s*+.,R-AS*$ata& )*e lo'ation of t*e data dire'tor- (a- ar-

dependin on -o+r ersion of ostres l+s and t*e installation options '*osen at install

ti(e&

)*e folloin s'reens*ot s*os t*e pro'ess of reloadin t*e 'onfi+ration file in

Windos9

Step 2: Ceate t!e paent table.

Note: :o+ 'an +se t*e L 'o((and line to enter t*e rest of t*e 'o((ands +sed in t*is

t+torial&

:o+ 'an a''ess t*e L 'o((and line %- openin t*e Start (en+ and naiatin to

t*e Postgres Pl!s (en+& C*oose R!n S%L co33an$ l#ne, and t*en EDB4PS%L to

open t*e EDB4PS%L indo9



11/14


W*en pro(pted, enter -o+r 'onne'tion infor(ation& W*en -o+e spe'ified -o+r

'onne'tion infor(ation


12/14


INSERT INTO sales(part- VALUES6NE.K;< ELSE RAISE ECEPTION Organ#at#on o!t o range. H#"t8e sales(#nsert(tr#gger6; !nct#on< END IH< RETURN NULL<

END<QQLAN&UA&E plpgsql<

)*en 'reate t*e trier9

CREATE TRI&&ER #nsert(sales BEHORE INSERT ON sales HOR EAC= RO

EECUTE PROCEDURE sales(#nsert(tr#gger6;<

Step : A%% %ata to t!e new table.

Use t*e L pro(pt to add ros to t*e ne ta%le&

INSERT INTO sales VALUES697Cra#g;< INSERT INTO sales VALUES6-7M#e;< INSERT INTO sales VALUES6,7M#c8elle;< INSERT INTO sales VALUES67oe;< INSERT INTO sales VALUES6F7Scott;< INSERT INTO sales VALUES6?7Roger;< INSERT INTO sales VALUES6G7Hre$;< INSERT INTO sales VALUES6+7Sa3;< INSERT INTO sales VALUES67SonnJ;< INSERT INTO sales VALUES69:7C8r#s;<

Step *: Con+$ t!at t!e %ata was a%%e% to t!e paent table.

postgres@' select K ro3 sales< org na3e444444444444444 9 Cra#g - M#e , M#c8elle oe F Scott ? Roger G Hre$ + Sa3 SonnJ 9: C8r#s

As in o+r preio+s e4a(ple


13/14


'*ild partitions&

:o+ 'an see *i'* ros are stored in ea'* '*ild partition it* t*e folloin 'o((ands9

postgres@' select K ro3 sales(part9< org na3e444444444444444 9 Cra#g - M#e , M#c8elle oe F Scott

postgres@' select K ro3 sales(part-< org na3e444444444444 ? Roger G Hre$ + Sa3 SonnJ 9: C8r#s

Aain, t*e EPLAIN plan de(onstrates t*at t*e serer reads ea'* '*ild ta%le to satisf- an

+n'onstrained .+er- on t*e parent ta%le9

postgres@' e"pla#n select K ro3 sales< %UERY PLAN4444444444444444444444444444444444444444444444444444444444 Res!lt 6cost@:.::..?,.: ro5s@,,: 5#$t8@-; 42 Appen$ 6cost@:.::..?,.: ro5s@,,: 5#$t8@-; 42 Seq Scan on sales 6...; 42 Seq Scan on sales(part9 sales 6...; 42 Seq Scan on sales(part- sales 6...;

f 'o+rse, -o+ still need to add triers for UPDATE and DELETE operations& Creatin

inde4es on ea'* of t*e partitions is also adisa%le to a'*iee lo'al partitioned inde4es&

onclusion

n t*is +i'/ )+torial, e de(onstrated *o to +se partitioned ta%les it* ostres l+s

to proide (ore effe'tie storae of lare a(o+nts of data and i(proe .+er- perfor(an'e&

)*e ne4t topi' -o+ (i*t %e interested in is Enabling Row Movement & Ena%lin ro

(oe(ent allos t*e partition /e- to %e +pdatea%le, and a+to(ati'all- (oes t*e

+pdated re'ord to t*e appropriate partition& )*at topi' is 'oered in anot*er +i'/)+torial fro( EnterpriseDB&



14/14


f -o+ *aent '*e'/ed o+t EnterpriseDBs Eal+ation F+ide, please do so; it (a- *elp-o+ (oe onto t*e ne4t step in -o+r ostres l+s eal+ation& )*e +ide 'an %e a''essed

at9

*ttp9@@&enterprised%&'o(@learnin@do'+(entation&do

:o+ s*o+ld no %e a%le to pro'eed it* a )e'*ni'al Eal+ation of ostres l+s&

)*e folloin reso+r'es s*o+ld *elp -o+ (oe on it* t*is step9

• ostres l+s )e'*ni'al Eal+ation F +ide

• ostres l+s Fettin tarted reso+r'es

• ostres l+s +i'/ ) +torials

• ostres l+s User or+(s

• ostres l+s Do'+(entation

• ostres l+s We%inars

Copyright © 2009 EnterpriseDB Corporation. All rights reserved. 1?
http://www.enterprisedb.com/learning/documentation.dohttp://www.enterprisedb.com/learning/whitepapers.dohttp://www.enterprisedb.com/solutions/stages/overview.dohttp://www.enterprisedb.com/solutions/stages/overview.dohttp://www.enterprisedb.com/learning/tutorials.dohttp://forums.enterprisedb.com/http://www.enterprisedb.com/learning/documentation.dohttp://www.enterprisedb.com/learning/documentation.dohttp://www.enterprisedb.com/learning/webcasts.dohttp://www.enterprisedb.com/learning/webcasts.dohttp://www.enterprisedb.com/learning/documentation.dohttp://www.enterprisedb.com/learning/whitepapers.dohttp://www.enterprisedb.com/solutions/stages/overview.dohttp://www.enterprisedb.com/learning/tutorials.dohttp://forums.enterprisedb.com/http://www.enterprisedb.com/learning/documentation.dohttp://www.enterprisedb.com/learning/webcasts.do

tutorial all pp partition table

Documents