Message-ID: <506B0396.4030805@optionshouse.com>
Date: Tue, 2 Oct 2012 10:09:10 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:15.0) Gecko/20120912 Thunderbird/15.0.1
MIME-Version: 1.0
To: Stefan Keller <sfkeller@gmail.com>
CC: Ivan Voras <ivoras@freebsd.org>, <pgsql-performance@postgresql.org>
Subject: Re: Inserts in 'big' table slowing down the database
References: 
 <CAFcOn2_W6v_vqwomCf6DEtXk=N8iB5WU-7XEdcYgX-VFUE32+Q@mail.gmail.com>
	<k223s0$e83$1@ger.gmane.org>
	<CAFcOn2_zpBQ1Tky_vHOkBGMLwjHJFdNSUgyZhkg-i3b8-WWMLA@mail.gmail.com>
In-Reply-To: 
 <CAFcOn2_zpBQ1Tky_vHOkBGMLwjHJFdNSUgyZhkg-i3b8-WWMLA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit

On 10/01/2012 07:15 PM, Stefan Keller wrote:

> Any ideas? Partitioning?

Yes. Make sure you have a good column to partition on. Tables this large 
are just bad performers in general, and heaven forbid you ever have to 
perform maintenance on them. We had a table that size, and simply 
creating an index could take upwards of two hours.

If you can't archive any of the table contents, partitioning may be your 
only solution. If you have an EDB 9.1, you'll also have less problems 
with the legacy issues people had with planning queries using partitions.

Don't go crazy, though. I try to keep it under a couple dozen partitions 
per table, or under 100M records per partition.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email