Partitioning in postgres

Partioning is useful to drop group of data in a table in bulk. In most cases year old data is purged on a regular basis by an application. Partitions are a useful design design to manage the data.

create table master (i int);

create table slave1 ( CHECK ( i > 0 AND i <> 10 AND i <> 20 AND i < postgres="#"> 0 AND i <> 10 AND i <> 20 AND i < 30) ) inherits (master);

postgres=# insert into master values(5);
INSERT 0 1
postgres=# insert into master values(15);
INSERT 0 1
postgres=# insert into master values(25);
INSERT 0 1
postgres=# select * from master;
i
----
15
25
5
(3 rows)
postgres=# select * from slave1;
i
---
5
(1 row)
postgres=# select * from slave2;
i
---
15
(1 rows)
postgres=# select * from slave3;
i
---
25
(1 rows)

Note: Copy command of postres does not copy the rules associated with the table. So to make sure the rules are reflected, create partitions as a trigger.

Also here is an interesting thing

postgres=# update master set i=15 where i=5;
ERROR: new row for relation "slave1" violates check constraint "slave1_i_check"

This says it all