Re: How does TOAST compare to other databases' mechanisms?

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> In the case of images, is there a way to tell TOAST not to bother even
> trying to compress the data? (eg. JPEG files). If so, would I be right in
> assuming that this would be better for fast retrieval (even for text
> files)?

TOAST will not store a compressed value unless the compressed value is
smaller than the uncompressed by some reasonable amount (which looks to
be 20% by default). I'd expect JPEG-like data always to fail the
compression ratio check. So there's no significant CPU cost at read
time, but there's some cost at write time to try to do the compression.

There is a provision to discourage the toaster from even trying to
compress a particular column --- see the attstorage column in
pg_attribute. At the moment there's no user interface for that :-(
so you'd have to reach in with a manual "UPDATE pg_attribute" to
change it from the default value. Someone should work on adding an
ALTER command to change it in a more user-friendly fashion.

regards, tom lane