On Sat, Aug 18, 2012 at 1:05 AM, Bartel Viljoen
<bartel@ncc.co.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
