Re: Higher TOAST compression.

On Fri, Jul 17, 2009 at 9:21 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:
> "Joshua D. Drake" <jd@commandprompt.com> wrote:
>> On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote:
>
>>> (3)  Allow override of the thresholds for individual columns.
>
>> I would skip 1 and 2 and have (3).
>
> Sure, pick the one which requires new syntax!  ;-)
>
> How about two new ALTER TABLE actions:
>
>   ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer
>   ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer
>
> Or can you suggest something better?
>
> Laurent, would something like this address your needs?  I was assuming
> that if the point is to reduce I/O, you were interested in doing more
> compression, not in storing more values out-of-line (in the separate
> TOAST table)?  Would it be manageable to tune this on a column-by-
> column basis?

Certainly !
We already alter storage type on some column, adding some more won't
be a problem. :)

But... on which version are you planning to do that ?
We're still using Postgresql 8.3, because we use Slony-1 1.2.15 and
upgrading to 8.4 is a *major* pain (discussed on slony mailling list).
Slony-1 1.2.15 won't compile on 8.4, and upgrading to Slony-1 2.0.x
require to rebuild the whole cluster (and upgrading to Pg 8.4 require
a rebuild too).
So we'd need to upgrade both slony and postgresql with an impossible downtime :)
We stay on Pg 8.3 until the slony developpers find a better upgrade solution.

The proposed solution sound really good to me.
But, for now, if i could have a simple patch for 8.3 (eg: changing a
#define in the source code), i'd be very happy :)

Is it ok to just change TOAST_TUPLES_PER_PAGE ?

Thank you for all your replies and proposed solutions :)

PS : i'm not a C coder, but if you know some perl to be
patched/cleaned, i'm here :)

--
Laurent Laborde
Sysadmin at http://www.over-blog.com/