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
> 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
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
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 Krajmalnik||Date: 2010-01-14 01:11:54|
|Subject: Re: Question on moving data to new partitions|
|Previous:||From: Benjamin Krajmalnik||Date: 2010-01-14 00:51:51|
|Subject: Question on moving data to new partitions|