Thread: 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

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

[...]

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

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