On Wed, Jan 13, 2010 at 5:51 PM, Benjamin Krajmalnik <kraj@xxxxxxxxxxx> 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. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
