Skip site navigation (1) Skip section navigation (2)

Re: Dynamic Partitioning using Segment Visibility Maps

From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Dynamic Partitioning using Segment Visibility Maps
Date: 2008-01-07 11:14:43
Message-ID: 1199704483.16301.106.camel@PCD12478 (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, 2008-01-02 at 17:56 +0000, Simon Riggs wrote:
> Like it?

Very cool :-)

One additional thought: what about a kind of "segment fill factor" ?
Meaning: each segment has some free space reserved for future
updates/inserts of records in the same range of it's partitioning
constraint. And when inserting/updating you put the new record into the
corresponding segment... just like a very coarse clustering. Then you
could vacuum the segments separately to keep the free space not running
out. For active segments you would then fix the partitioning constraint
range once the fill factor is reached, to allow for keeping it's
constraint even when heavily updating (heavily vacuuming it too as
response to that), and create a new segment for the unbounded range for
new inserts... this would work fine for tables where the constraint is
based on ever increasing keys and accidental inserts in old ranges
(which do happen occasionally in real life).

When the change rate of old segments get down, the segments could be
reorganized to have a smaller fill factor, so that you still allow for
accidental updates but keep space usage efficient. This would be some
similar action as a clustering, but hopefully not blocking (which might
be a hard thing to do)... and later again you could mark some of the
really old things as read only and put them in special segments with no
wasted space.

One problem would be when the segment's free space runs out, so you must
put records from the same constraint range in multiple segments - but
that could still work, you just would have multiple segments covering
the same range, but if the "segment fill factor" is chosen properly it
should not be the case... you could normally maintain a set of
non-overlapping segments in terms of the partitioning constraint.

Cheers,
Csaba.




In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2008-01-07 12:18:24
Subject: Re: Bug: Unreferenced temp tables disables vacuum toupdate xid
Previous:From: Michael AkindeDate: 2008-01-07 09:40:23
Subject: Re: VACUUM FULL out of memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group