Re: Performance on Bulk Insert to Partitioned Table

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Charles Gomes <charlesrg(at)outlook(dot)com>, Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Performance on Bulk Insert to Partitioned Table
Date: 2012-12-21 00:19:52
Message-ID: CAOR=d=3iibsFY_6f7nyGOhwgcRFemaCXLHNjzHDX-v6kNRVTew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, Dec 20, 2012 at 4:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Charles Gomes <charlesrg(at)outlook(dot)com> writes:
>> Using rules would be totally bad as I'm partitioning daily and after one year having 365 lines of IF won't be fun to maintain.
>
> You should probably rethink that plan anyway. The existing support for
> partitioning is not meant to support hundreds of partitions; you're
> going to be bleeding performance in a lot of places if you insist on
> doing that.

A couple of points:

1: In my experience hundreds is OK performance wise, but as you
approach thousands you fall off a cliff, and performance is terrible.
So at the 3 to 4 year mark daily partition tables will definitely be
having problems.

2: A good way around this is to have partitions for the last x days,
last x weeks or months before that, and x years going however far
back. This keeps the number of partitions low. Just dump the oldest
day into a weekly partition, til the next week starts, then dump the
oldest week into monthly etc. As long as you have lower traffic times
of day or enough bandwidth it works pretty well. Or you can just use
daily partitions til things start going boom and fix it all at a later
date. It's probably better to be proactive tho.

3: Someone above mentioned rules being faster than triggers. In my
experience they're WAY slower than triggers but maybe that was just on
the older pg versions (8.3 and lower) we were doing this on. I'd be
interested in seeing some benchmarks if rules have gotten faster or I
was just doing it wrong.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Neill 2012-12-21 02:34:44 Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
Previous Message Tom Lane 2012-12-20 23:39:07 Re: Performance on Bulk Insert to Partitioned Table