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

Question on moving data to new partitions

From: "Benjamin Krajmalnik" <kraj(at)illumen(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Question on moving data to new partitions
Date: 2010-01-14 00:51:51
Message-ID: F4E6A2751A2823418A21D4A160B68988613D50@fletch.stackdump.local (view raw or flat)
Thread:
Lists: pgsql-admin
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.

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?

 

 

Responses

pgsql-admin by date

Next:From: Scott MarloweDate: 2010-01-14 00:58:10
Subject: Re: Question on moving data to new partitions
Previous:From: J. HondiusDate: 2010-01-12 10:47:21
Subject: Oops! Disaster recovery

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