Skip site navigation (1) Skip section navigation (2)

Re: Question on moving data to new partitions

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Benjamin Krajmalnik <kraj(at)illumen(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Question on moving data to new partitions
Date: 2010-01-14 00:58:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
On Wed, Jan 13, 2010 at 5:51 PM, Benjamin Krajmalnik <kraj(at)illumen(dot)com> wrote:
> I have some tables which have an extremely high amount of update activity on
> them.  I have changed autovacuum parameters (cost delay and limit), and
> whereas before they would never be vacuumed and bloat they are running fine.
> However, as the platform scales, I am afraid I will reach the same
> situation.
> As a result, I have decided to partition the table and add to each record a
> partition id, which can be used to route it to the correct partition.
> Presently, all of the records reside on what will ultimately become the
> parent partition.

Are you using table inheritance to do this?  or are they all independent tables?

> What would be the best way of moving the data to the pertinent partitions?
> I was thinking of copying the data to another table and then performing a
> insert into partitionedtableparent select * from temporary table, and then
> performing a delete from only partitionedtableparent.
> Does this sound like a reasonable way of doing this?  Is there a more
> efficient way of doing this?

You can probably skip a few steps there if you copy straight to the
destination table.

At work, where we have partitioned out some tables, I made a trigger
based inherited table setup, and basically did something like:

insert into master_table select * from master_table where id between 1
and 100000;
delete from only master_table where id between 1 and 100000;

Then incremented the between values until all the tuples had been moved, then I

truncate only master_table;

and it worked like a charm.

In response to


pgsql-admin by date

Next:From: Benjamin KrajmalnikDate: 2010-01-14 01:11:54
Subject: Re: Question on moving data to new partitions
Previous:From: Benjamin KrajmalnikDate: 2010-01-14 00:51:51
Subject: Question on moving data to new partitions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group