Re: Compression in PG - Mailing list pgsql-performance

On Sun, 2009-11-01 at 18:53 +0200, Shaul Dar wrote:
> I am aware of the TOAST mechanism (actually complained about it in
> this forum...). The text fields I have are below the limits that
> trigger this mechanism,

Have you proved somehow that compressing tiny values has any value?

>  and also I may want to compress *specific* fields, not all of them.

You can do that.

“ALTER TABLE table ALTER COLUMN comment SET STORAGE mechanism;"

For example:

ALTER TABLE job_history_info ALTER COLUMN comment SET STORAGE
EXTERNAL;

Where mechanism is -

<quote source="WMOGAG"
url="http://docs.opengroupware.org/Members/whitemice/wmogag/file_view">
* Extended – With the extended TOAST strategy the long value, once it
exceeds the TOASTing threshold will be compressed. If the compression
reduced the length to below the TOAST threshold the value will be
stored, compressed, in the original table. If compression does not
reduce the value to below the TOAST threshold the value will be stored
uncompressed in the table's TOAST table. Because the value is stored
compressed it most be uncompressed in order to perform value
comparisons; for large tables with many compressed values this can
result in spikes of processor utilization. On the other hand this
storage mechanism conserves disk space and reduces the need to perform
seek-and-read operations on the TOAST table. Extended is the default,
and usually recommended, TOAST storage mechanism.
* External – With the external TOAST strategy a long value is
immediately migrated to the TOAST table, compression is disabled.
Disabling compressions can increase the performance for substring
searches on long text values at the cost of increasing seeks in the
TOAST table as well as disk consumption.
* Main – Main enables compression and uses any means available to avoid
migrating the value to the TOAST table.
</quote>

As I recall all the above is in the PostgreSQL TOAST documentation; you
should go look at that.

>  And also I have performance concerns as TOAST splits tables and can
> potentially cause a performance hit on queries.

Then change your TOAST mechanism to "MAIN".

But benchmarking [aka: knowing] is always preferable to having
"concerns".  I'd wager your biggest bottlenecks will be elsewhere.

> My question is if PG can compress smaller text fields e.g 0.5-1KB, or
> must I do this outside PG?

I just think compressing small documents seems pointless.
--
OpenGroupware developer: awilliam@whitemice.org
<http://whitemiceconsulting.blogspot.com/>
OpenGroupare & Cyrus IMAPd documenation @
<http://docs.opengroupware.org/Members/whitemice/wmogag/file_view>