Thread: Schemas vs partitioning vs multiple databases for archiving

Schemas vs partitioning vs multiple databases for archiving

From

Bartel Viljoen

Date:

18 August 2012, 08:07:47

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

Schema layout

Public.schema (will have all tables and the current months transaction table)

Jan2012.schema (This will just have the archive transaction table for Jan 2012)

Feb2012.schema

Mrt2012.schema

I’ve red a couple of articles regarding data warehousing but they don’t mention schema’s to split large transaction tables.

Will multiple schema’s solve my problem ?

Regards

Bartel Viljoen

	Network & Computing Consultants (Pty) Ltd
	E-mail	: bartel@ncc.co.za
	Phone	: 086 155 5444
	Fax	: 051 448 1214
	Url	: www.ncc.co.za
E-mail Disclaimer

Disclaimer added by CodeTwo Exchange Rules 2007
www.codetwo.com

Attachment

Re: Schemas vs partitioning vs multiple databases for archiving

From

John R Pierce

Date:

18 August 2012, 08:14:03

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

Re: Schemas vs partitioning vs multiple databases for archiving

From

Craig Ringer

Date:

18 August 2012, 08:29:30

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

Re: Schemas vs partitioning vs multiple databases for archiving

From

Chris Travers

Date:

18 August 2012, 11:01:16

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

Re: Schemas vs partitioning vs multiple databases for archiving

From

Tom Lane

Date:

18 August 2012, 17:22:34

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

Re: Schemas vs partitioning vs multiple databases for archiving

From

Jeff Janes

Date:

18 August 2012, 19:16:25

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

Re: Schemas vs partitioning vs multiple databases for archiving

From

Gavin Flower

Date:

19 August 2012, 05:34:19

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

Re: Schemas vs partitioning vs multiple databases for archiving

From

Chris Travers

Date:

19 August 2012, 05:50:39

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

Cheers,
Gavin

Re: Schemas vs partitioning vs multiple databases for archiving

From

Gavin Flower

Date:

20 August 2012, 04:42:25

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

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

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