Re: Performance on Bulk Insert to Partitioned Table

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.