Обсуждение: Very slow moving between partition
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.
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.
Le lundi 07 février 2011 à 02:42 -0600, Mario Medina a écrit : > 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. > well, that's a lot of records to move. I would guess you are I/O bound with your present disks, and faster ones are needed. Some study material here : http://wiki.postgresql.org/wiki/Performance_Optimization -- Vincent Veyron http://marica.fr/ Logiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique