About Me

My photo
I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!

Wednesday, May 29, 2013

Easy way to Attach a partition in EnterpriseDB 9.2

For some people, partitioning is always a pain when using PostgreSQL. You have to create child tables, constraints and then add triggers/rules for re-directing the data. Maintenance is an even painful task. Imagine if you have to add a new partition. You will end up following below tasks:

1) Add new child table and constraint
2) Modify/Add trigger functions or rules for re-directing proper data in this new partition

Well EnterpriseDB offers an easy way to ADD a partition.

Now you can insert data in the new partition emp_president.

But many a time you may want to attach a partition from an existing table. This is particularly important when you are doing some maintenance activities on the underlying tables or during migration when you have loaded the child tables using bulk loader script and now want to attach them to the main table.

Suppose I have a table called "employees_emp_president_temp" and I want to attach the table as a new partition to employees, given that the structure is same.

So the purpose is to add a new partition called "emp_president" and to add data from "employees_emp_president_temp" to this new partition.

A conventional way is to add a new partition "emp_president" and then insert data from "employees_emp_president_temp".
insert into employees_emp_president select * from employees_emp_president_temp;
But will that be the best way to do it for huge no of records?

Let's use a "magic" function to achieve the task:

Completes in 2 seconds. Wow!!! 
All I have to do is, pass the name of my main table (to which I want to attach a new partition, the name of new partition, the domain of values for the new partition either as VALUES clause of RANGE clause and then name of the table whose data needs to be migrated to the new partition.

Let's see what is the effect of this.

Well we see that there is a new child table. Let's see the structure (I am particularly interested in indexes).

So the table structure of our new partition is same as the old table plus it holds the indexes as well.

Now let's confirm if it copied the data (well 2 seconds was too fast for copying the data wasn't it?).

Now let's just test how much time an insert into..select * from would have taken:

Now you know what you will be doing the next time you need to attach a partition.

I forgot about attach_table_to_partition. It's not an inbuilt function. I wrote it using some features available in EnterpriseDB's PPAS. Here you go:

No comments:

Post a Comment