Postgres and Postgres Plus tips and tricks
Sameer Kumar I DB Consultant, Ashnik
Ashnik’s DB Consultant, Sameer blogs and how! Picked up from his personal blogs, this one’s particularly edifying and useful. Check it out –
EnterpriseDB offers an easy way to ADD a partition CREATE TABLE employees (empno numeric(4,0), ename varchar(10), job varchar(9), hiredate timestamp ) PARTITION BY LIST (job) (PARTITION emp_mgmt VALUES (‘MANAGER’) tablespace tbsp_1, PARTITION emp_sales VALUES (‘SALESMAN’) tablespace tbsp_2, PARTITION emp_ops VALUES (‘CLERK’) tablespace tbsp_3 ); alter table employees add partition emp_president values(‘PRESIDENT’); and then you can insert data in this partition. Many a time you may want to attach a partition from an existing table. Suppose I have a table called employees_emp_president and I want to attach the table as a new partition to employees, given that the structure is same.
Now you want to add a new partition called emp_president and want to add data from employees_emp_president to it.
A conventional way is to add a new partition emp_president and then insert into employees_emp_president select * from employees_emp_president_temp;
But will that be the best way to do it for this huge no of records?
Completes in 2 seconds. Wow!!! Let’s see what is the effect of this.
Well I see there is a new child table. Let’s see it’s 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 2sec was too fast wasn’t it?).
Now let’s just test how much time an insert into..select * from would have taken:
Well, you what you will be doing the next time you need to attach a partition. oh! I forgot about attach_table_to_partition. It’s not an in-built function. I wrote it to ease my pain: CREATE OR REPLACE FUNCTION attach_table_to_partition(
new_partition_name varchar(50), partition_clause varchar(100),
- Sameer Kumar is the Database Solution Architect working with Ashnik. Sameer and his team works towards providing enterprise level solutions based on open source and cloud technologies. He has an experience in working on various roles ranging from development to performance tuning and from installation and migration to support and services roles.