Re: Schemas vs partitioning vs multiple databases for archiving

On 08/18/2012 04:05 PM, Bartel Viljoen wrote:
> Dear mailing list.
>
> My current application make use of partitioning by creating a new child
> table which holds transaction records for every month. I’ve 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 you're having trouble with constraint exclusion based partitioning
and inserts, try inserting directly into the target partition, not the
"common" table all the partitions inherit from.

Use `EXPLAIN ANALYZE` to examine some INSERTs and see what's going on.
Look at `vmstat`, `iostat`, etc for system load, see if you can tell
what's limiting the system. Turn checkpoint logging on and examine the
Pg log files to see if you're checkpointing too often.

--
Craig Ringer