Re: Partitioning an existing table

On Mon, Apr 25, 2011 at 7:40 PM, Vick Khera <vivek(at)khera(dot)org> wrote:

> On Mon, Apr 25, 2011 at 6:46 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>wrote:
>
>> 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?
>>
>
> I did this twice (several years ago). Basically, you create your
> partitions and set up the necessary triggers you want (I re-write the app to
> insert directly into the correct partition). Then all new data starts going
> into the partitions. Next, write a program that loops over the current
> master table, and moves the data into each partition some small hunk at a
> time, in a transaction. This can take a long time. For us, it took about 7
> days to move O(100m) rows. Then, when you're done, truncate the master
> table, and enforce that no new data is allowed to be inserted into it.
>
> If you can, of course, try this out on a spare copy of that table.
>
>
COPY command would be the best and fast way to do bulk inserts. Even for
partitioning table from the base table,

1. Send all the data to .csv file with COPY TO command of the base
table(which will undergo for partition).
2. Create the partition setup with TRIGGER's
3. Use COPY FROM command for inserting data into partition table.

This approach will fasten the inserts. Increasing the memory will also help.

Note: RULE based approach wont work with COPY command.

Best Regards,
Raghavendra
EnterpriseDB Corporation
The Enterprise Postgres Company
Email: raghavendra(dot)rao(at)enterprisedb(dot)com
Blog: http://raghavt.blogspot.com/