Re: Compression of text fields

Stephan Szabo wrote:
> On Wed, 20 Aug 2003, Brian McCane wrote:
>>    4) Can I disable the compression to improve storage speed
>>       if the compression algorithm is not as good as deflate
>
> See ALTER TABLE ALTER COLUMN SET STORAGE (I would guess you would want
> external, but I'm not 100% sure, check the docs :) )

Yes, EXTERNAL is correct. We had a good thread on this topic on PERFORM
a couple of weeks ago.

Starts here:
http://archives.postgresql.org/pgsql-performance/2003-08/msg00030.php
Ends here:
http://archives.postgresql.org/pgsql-performance/2003-08/msg00144.php

Note the issue with UPDATE in place of the data. You need to concatenate
an empty string to force the change of storage.

> Also, I'm not sure if storing a compressed version in a text field is a
> good idea.  I'd think that bytea would be a better match.
>

You can pretty much guarantee problems if you store binary (i.e.
pre-compressed) data in a text field. If your uncompressed data is not
binary, you should probably do one of these options:
1) pre-compress and store in a bytea field with storage set to EXTERNAL
2) store as-is in a text field and let Postgres do the compression for
    you
3) store as-is in a text field with storage set to EXTERNAL

Personally, I'd use #3 if you consider disk space cheap and performance
important, or #2 if you really need the data compressed. You might get
moderately better compression using an external program, but it is
probably at a big performance hit.

HTH,

Joe