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@xxxxxxxxxxxxx <http://whitemiceconsulting.blogspot.com/> OpenGroupare & Cyrus IMAPd documenation @ <http://docs.opengroupware.org/Members/whitemice/wmogag/file_view> -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
