Pre-creating partitions incurs insert penalty

From: Anj Adu <fotographs(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Pre-creating partitions incurs insert penalty
Date: 2009-05-28 23:22:15
Message-ID: f2fd819a0905281622s2becf17atd28bb7e589e1f7a3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I noticed a very strange performance issue after I pre-create daily
partitions for the next month on a table that has a very large insert volume
(30 million a day). After the partitions are created..the inserts seem to
slow down. I verifiied that this was the issue by dropping the
partitions...When I dropped the pre-created partitions..the performance
issue disappeared. Looks like you cannot have too many partitions (in this
case..I had a total of 35 partitions when the performance issue was noticed)

Postgres version is 8.1.9 on a 8 core Red Hat linux box with a Raid 10
configured hard frive set up that has 6 15K disks . The load factor on the
box is very low ..just the insert performance suffers after the partition
creation.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Bruce Reed 2009-05-29 00:01:57 Continuous archiving fails routinely with "invalid magic number" error
Previous Message Atul Chojar 2009-05-28 18:06:27 Re: How to pass parameters into a sql script ?