Re: Schemas vs partitioning vs multiple databases for archiving

Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> writes:
> On 08/18/2012 04:05 PM, Bartel Viljoen wrote:
>> My current application make use of partitioning by creating a new child
>> table which holds transaction records for every month. Ive notice that
>> after a couple of months depending on the hardware at some of our
>> clients the inserts become very slow.

> Look into the cause of that before trying to fix it. Why do they slow
> down? "Memory" is unlikely to be the explanation, unless there's more
> going on than you're saying, like a big trigger function.

If he's getting into the hundreds of partitions, I could believe that
memory would be a problem for both planning and execution. Otherwise
this sounds more like a table or index bloat problem (are there a lot of
updates per row?).

If it is too-many-partitions, my recommendation would be to question
whether partitioning is useful at all. The main thing it is really good
for is dropping old partitions cheaply ... so if he's not going to do
that, I wonder what it's buying for him.

regards, tom lane