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