Very slow moving between partition

From: Mario Medina <osoverflow(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Very slow moving between partition
Date: 2011-02-07 08:42:02
Message-ID: 1297068122.32763.96.camel@haibane
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi! I have a big table with about 26 millon registers, and I have 3
partitions, one that handles daily information, only one register per
day for about 24,000 elements, that's it about 24,000 register daily,
other one that handles one register per minute per day, only 8 hours a
day, for about 9000 elements, that's 60*8*9000 registers daily, and
other one that works like an archive of that per minute information.

The archive partition don't have indexes, because it works as an
archive, I don't do queries on that table frequently. Well, maybe the
primary key (ID, bigserial is the only index)

The other tables have only have the primary key, the name of the element
(varchar16) and date (date) fields as indexes.

I use a boolean field to decide if a register is archived or not, so if
I update one field setting "archived=true", then a trigger deletes that
field from their original partition and reinserts it on the archive
partition.

That works good with small number of records, but if I have 300,000
records it can take a lot of time to move that records.

I wish to know if there is a better way to move records from one
partition to another partition, that does it fast.

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wouter D'Haeseleer 2011-02-07 08:57:17 Re: Question about switchover with PG9 replication
Previous Message Jean-Armel Luce 2011-02-07 08:20:36 Question about switchover with PG9 replication