Re: Schemas vs partitioning vs multiple databases for archiving

On Sat, Aug 18, 2012 at 1:05 AM, Bartel Viljoen <bartel(at)ncc(dot)co(dot)za> 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. The reason memory. I don’t want to delete old
> child tables even though they may be queried seldom and we can’t upgrade
> memory since most clients are far and remote.
>

I don't think your root issue is memory constraints. It may be a proximal
cause, but you say you are writing frequently and reading seldom. At any
rate it really is you *really* don't want to use layers of complexity to
try to hide a poorly understood problem.

Things I would be thinking about:

1) Other stuff running on the same system. Is it possible that a memory
leak somewhere else is causing the slowdown? The first place I always
start is with top (or the tax manager if on Windows). In the event that it
is not PostgreSQL, you don't want to spend all your time tuning the db.
That's a good way to waste a ton of time.

2) If you are doing a INSERT INTO ... SELECT ... the result may be
somewhat slow at some point due to memory causing plan changes. The
correct solution here is indexing. Partitioning makes some sense in
occasional circumstances, but you really need to have clear understandings
of how the data is going to be used because it is far easier to hurt
performance than to help it.

3) You may want to look carefully at your indexes. Here's another area
where if you are indexing too many columns it may get slow for writes over
time. Especially in constrained memory environments not only do missing
indexes cause performance problems but so do spurious indexes. You might
also try partial indexes instead of total indexes where appropriate.

But yeah, the general view you need to really understand exactly where the
problem is happening on the remote site (not always easy, I know) is very
important, and this is particularly important if on-site maintenance is a
problem.

Best Wishes,
Chris Travers