Schemas vs partitioning vs multiple databases for archiving

On 08/18/12 1:05 AM, 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. 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’m in the design faze of a new GUI and DB layout, what are my options.

Create a DB for each month.

Create a Schema for each month. Example

you should figure out why its slowing down, as it really shouldn't with
partitioned data. your schema idea is horrible, the seperate database
idea even worse.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

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

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

Craig Ringer <ringerc@ringerc.id.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. 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 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

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.

How do you know that memory is the reason? What behavior or
monitoring-tool output are you seeing that leads you to that conclusion?

I don’t want to delete old child tables even though they may be queried
seldom

If you did delete the old child tables, would it solve the problem? If the
problem is showing up specifically on inserts, and the inserts are
happening directly into the leading-edge partition, then older child tables
shouldn't have anything to do with it.

Cheers,

Jeff

On 18/08/12 20:05, Bartel Viljoen wrote:

[...]

I'm in the design faze of a new GUI and DB layout, what are my options.

[...]
I think you meant phase!
(Spell checkers can be quite stupid!)

Cheers,
Gavin

On Sat, Aug 18, 2012 at 9:30 PM, Gavin Flower <GavinFlower@archidevsys.co.nz

wrote:

On 18/08/12 20:05, Bartel Viljoen wrote:

[...]
****

I’m in the design faze of a new GUI and DB layout, what are my options.

[...]
I think you meant phase!
(Spell checkers can be quite stupid!)

Could be worse.... See the post here entitled "When Spellcheckers Attack."

http://blog.oup.com/2007/11/spellchecker/

Best Wishes,
Chris Travers

On 19/08/12 17:50, Chris Travers wrote:

On Sat, Aug 18, 2012 at 9:30 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>>
wrote:

On 18/08/12 20:05, Bartel Viljoen wrote:

[...]

I’m in the design faze of a new GUI and DB layout, what are my
options.

[...]
I think you meant phase!
(Spell checkers can be quite stupid!)

Could be worse.... See the post here entitled "When Spellcheckers
Attack."

http://blog.oup.com/2007/11/spellchecker/

Best Wishes,
Chris Travers

Cheers,
Gavin

Deliberate sabotage I tell you!

Do not go to the URL Chris provided if you are attempting to pretend to
work - you have been warned.

Honestly, I was just about to do some work when I read Chris's (my spell
checker wants to add a 't' after the first 's'!!!) post...

You can believe everything I write.

[Smilies omitted, due to budget restraints – so Americans, and other
humour impaired minorities, might need to seek professional advice.]\

Cheers,
Gavin