INSERT INTO child SELECT FROM parent WHERE ....; Once you have fully allocated all the records on the parent table you can then: TRUNCATE parent; Then install the triggers. The only real downside is that the queries are going to take forever to run. If you can naturally sub-divide the child tables (say into timestamp ranges) you might try moving one month at a time just to keep your eye on query progress. Make use of functions to perform the actual INSERT INTO SELECT; and probably have it capture and log statistics regarding each "INSERT INTO SELECT" statement that is fired (data start time, data end time, target child, number of records, execution start/end times). David J. -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Cédric Villemain Sent: Monday, April 25, 2011 6:58 AM To: Phoenix Kiula Cc: PG-General Mailing List Subject: Re: Partitioning an existing table 2011/4/25 Phoenix Kiula <phoenix.kiula@xxxxxxxxx>: > Hi. > > The partitioning documentation in PG is very clear on how to partition > a new table. Create child tables, and have triggers that manage > INSERT, UPDATE and DELETE commands. > > How about doing this with existing massive tables? (Over 120 million > rows) > > I could create a new parent table with child tables, and then INSERT > all these millions of rows to put them into the right partition. But > is that recommended? It will be faster to INSERT directly in the good child tables and you can make multiple INSERT in parrallel (depend of your IO system and number of tables) > > Thanks > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To > make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ ; PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
