Higher TOAST compression.

From: Laurent Laborde <kerdezixe(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Higher TOAST compression.
Date: 2009-07-17 08:16:54
Message-ID: 8a1bfe660907170116s3c168bd4re511e9e0cbf51db7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Friendly greetings !

I'd like to have a higher compression ratio on our base.

From pg 8.3 documentation :
The TOAST code is triggered only when a row value to be stored in a
table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB).
The TOAST code will compress and/or move field values out-of-line
until the row value is shorter than TOAST_TUPLE_TARGET bytes (also
normally 2 kB) or no more gains can be had.

According to the source code :
TOAST_TUPLE_THRESHOLD = a page (8KB) divided by TOAST_TUPLES_PER_PAGE
(4 by default) = 2KB.
TOAST_TUPLE_TARGET = TOAST_TUPLE_THRESHOLD = 2KB

If i understood correctly, the compression stop when the data to toast
is equal to TOAST_TUPLE_TARGET
What about trying to change the TOAST_TUPLE_TARGET to get a higher
compression (by having more toasted record) ?

I'd like to change the TOAST_TUPLES_PER_PAGE. Maybe from 4 to 8 ?
Is that correct ? Did i missed something ?

I did some statistics and i will have much more TOASTed record as most
of them are between 1KB and 2KB.

The servers have a lot of free cpu (2x4 core) and are running out of
IO, i hope to save some IO.
PS : The tables are clustered and all required index are present.

Any tought ? idea ?
Thank you.

--
F4FQM
Kerunix Flan
Laurent Laborde

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2009-07-17 08:19:48 Re: pg_stat_activity.application_name
Previous Message Peter Eisentraut 2009-07-17 08:03:07 Re: fmgroids.h not installed by "make install" in VPATH