Postgres Admin: Table Partitioning on PostgreSQL

Table Partitioning on PostgreSQL:
+++++++++++++++++++++++
1)We are using Inheritance consept here to achive partitioning tables.
2)And also we have to use (function and triggers) or Rules to populate the data into partitioned tables.
3)Recomended to dont create any check onstarints or indexes on master table,if those are not comman for all child tables.

All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constr
aints) are not inherited.

Master Table:
---------------
DB1=# create table master1(id int,name text);
CREATE TABLE

Child tables:
---------------
DB1 =# CREATE TABLE child100(check(id >0 and id<=100))inherits(master1);
CREATE TABLE
DB1 =# CREATE TABLE child200(check(id >100 and id<=200))inherits(master1);
CREATE TABLE
DB1 =# CREATE TABLE child300(check(id >200 and id<=300))inherits(master1);
CREATE TABLE

Indexes:
------------
DB1 =# CREATE INDEX child100_id_indx on child100(id);
CREATE INDEX
DB1 =# CREATE INDEX child200_id_indx on child200(id);
CREATE INDEX
DB1 =# CREATE INDEX child300_id_indx on child300(id);

Function & Trigger to achive the data to be populated into partationed tables:
------------------------------------------------------------------------------
CREATE or replace function partirion_check() returns trigger as $$
begin
if (NEW.id >0 and NEW.id <= 100)
then
insert into child100 values(NEW.*);
elsif (NEW.id >100 and NEW.id <= 200)
then
insert into child200 values(NEW.*);
elsif (NEW.id >200 and NEW.id <= 300)
then
insert into child300 values(NEW.*);
else
RAISE EXCEPTION 'fix the partirion_check() function';
end if;
return null;
end;
$$ language plpgsql;

Triggers:
---------
DB1 =# CREATE trigger partation_insert before insert on master1 for each row execute procedure partirion_check();
CREATE TRIGGER
DB1 =#

(OR)Instead of trigger and function we can use Rules to achive partitioned data here:
--------------------------------------------------------------------------------------
DB1 =# CREATE RULE child100_insert as on insert to master1 where (id >0 and id<=100) do instead insert into child100 values(NEW.*);
CREATE RULE

Testing paratitioned tables:
------------------------------------
DB1 =# INSERT INTO master1 VALUES (100,'OPEN');
INSERT 0 0
DB1 =# INSERT INTO master1 VALUES (200,'OPEN');
INSERT 0 0
DB1 =# INSERT INTO master1 VALUES (300,'OPEN');
INSERT 0 0
DB1 =# SELECT * FROM master1;
id | name
-----+------
100 | OPEN
200 | OPEN
300 | OPEN
(3 rows)

DB1 =# SELECT * from child100;
id | name
-----+------
100 | OPEN
(1 row)

DB1 =# SELECT * from child200;
id | name
-----+------
200 | OPEN
(1 row)

DB1 =# SELECT * from child300;
id | name
-----+------
300 | OPEN
(1 row)

Note :

1) Maintenance Operations we needed to perform individually for all child tables tables.

Vacuum,Analyze,Freeze,Reidex .....etc.

2) Make sure that constraint_exclusion parameter should be "on" or "partition".

Case 1) off ,it will give bad performance on queries.

postgres=# show constraint_exclusion;
constraint_exclusion
----------------------
off
(1 row)

test=# explain select * from master1 where id=240;
QUERY PLAN
-----------------------------------------------------------------------------
Result (cost=0.00..31.50 rows=9 width=27)
-> Append (cost=0.00..31.50 rows=9 width=27)
-> Seq Scan on master1 (cost=0.00..25.38 rows=6 width=36)
Filter: (id = 240)
-> Seq Scan on child100 master1 (cost=0.00..2.25 rows=1 width=10)
Filter: (id = 240)
-> Seq Scan on child200 master1 (cost=0.00..2.25 rows=1 width=10)
Filter: (id = 240)
-> Seq Scan on child300 master1 (cost=0.00..1.62 rows=1 width=10)
Filter: (id = 240)
(10 rows)

Case 2) on or partition ,it will give good performance on queries.

test=# set constraint_exclusion=on;
SET
test=# explain select * from master1 where id=240;
QUERY PLAN
-----------------------------------------------------------------------------
Result (cost=0.00..27.00 rows=7 width=32)
-> Append (cost=0.00..27.00 rows=7 width=32)
-> Seq Scan on master1 (cost=0.00..25.38 rows=6 width=36)
Filter: (id = 240)
-> Seq Scan on child300 master1 (cost=0.00..1.62 rows=1 width=10)
Filter: (id = 240)
(6 rows)

(OR)

test=# set constraint_exclusion=partition;
SET
test=# explain select * from master1 where id=240;
QUERY PLAN
-----------------------------------------------------------------------------
Result (cost=0.00..27.00 rows=7 width=32)
-> Append (cost=0.00..27.00 rows=7 width=32)
-> Seq Scan on master1 (cost=0.00..25.38 rows=6 width=36)
Filter: (id = 240)
-> Seq Scan on child300 master1 (cost=0.00..1.62 rows=1 width=10)
Filter: (id = 240)
(6 rows)

----

How to create Partitions on bigint based column,it is storing date and time :

my_db=# \d my_test

Table "public. my_test"

Column | Type | Modifiers

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

id | integer | not null default nextval('my_test_id_seq'::regclass)

received_timestamp | bigint |

sourceip | numeric |

destip | numeric |

my_db=#

BEGIN;

Create table my_test_20140219 (check( ('1970-01-01 GMT'::timestamp + ((received_timestamp::bigint)::text)::interval)::date='2014-02-19')) inherits(my_test);

CREATE RULE my_test_20140219 as on insert to my_test where ( ('1970-01-01 GMT'::timestamp + ((received_timestamp::bigint)::text)::interval)::date='2014-02-19') do instead insert into my_test_20140219 values(NEW.*);

CREATE unique INDEX my_test_20140219_idx on my_test_20140219(id);

CREATE index timestamp_sourceip_destip_20140219_idx on my_test_20140219(sourceip, destip, received_timestamp);

END;

pg_dump --inserts --data-only -f /home/postgres/my_test2.dump my_db

my_db# \i my_test2.dump

my_db=# \dt+

List of relations

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

(2 rows)

Postgres Admin

Wednesday 29 May 2013

Table Partitioning on PostgreSQL

No comments:

Post a Comment