1

it turns out that our approach with list partitioning was not the best idea. We have way to much small partitions and a lot of time is wasted on partition choosing by query optimizer.

What's the easiest way to merge all partitions back into one table? I don't expect any data clash problems.

<!-- language: lang-sql -->
PARENT_TABLE(id,col1,col2,col3,client_id)

CREATE TABLE CHILD_TABLE_1 (CHECK (client_id = '1')) INHERITS (PARENT_TABLE);
CREATE TABLE CHILD_TABLE_2 (CHECK (client_id = '2')) INHERITS (PARENT_TABLE);
CREATE TABLE CHILD_TABLE_3 (CHECK (client_id = '3')) INHERITS (PARENT_TABLE);
  • 3
    create table new_table as select * from parent_table. Then drop the old ones. – a_horse_with_no_name Oct 8 '14 at 8:35
  • 3
    Or CREATE TABLE new_table (LIKE parent_table INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING DEFAULTS INCLUDING COMMENTS), INSERT INTO new_table SELECT * FROM parent_table and finally re-create the indexes. That way your defaults, constraints, etc, are copied. Make sure to do it all in one transaction to get the benefit of some WAL optimisations. – Craig Ringer Oct 8 '14 at 9:33
2

The easiest way is to first remove any triggers which direct new rows to the child tables, and then do this for each child table:

with t as (delete from child_table_1 returning *) 
    insert into parent_table select * from t;

The locking and performance implications may dictate against this method, but it is hard to beat it for simplicity.

You might want to wrap it in a transaction block that starts with locking the child table and ends with dropping it, to make sure no one can keep using it for new tuples while you are cleaning out the old ones.

| improve this answer | |
  • Just a note, this solution has some extra overhead vs. the CREATE TABLE ... AS + DROP TABLE ... suggestions from a_horse_with_no_name and Craig Ringer, as it will need to DELETE all the child partition rows, which may itself be several times slower than populating the rows in the fresh parent_table. – Josh Kupershmidt Oct 10 '14 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.