Re: forcing compression of text field

Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
> > On Mon, 2006-12-11 at 10:18, Jonathan Ellis wrote:
> >> I have a table of log messages.  They are mostly in the 100-200
> >> character length, which apparently isn't large enough for PG to want
> >> to compress it (length == octet_length).  I really need to save disk
> >> space.  I can store it as a bytea and compress it manually (zlib level
> >> 1 compression gives about 50% savings), but is there a way to force
> >> pg's own compression before I resort to this?
>
> > http://www.postgresql.org/docs/8.1/interactive/storage-toast.html
> > Has all your answers.
>
> The bottom line is that PG doesn't bother trying to compress values
> less than about 2KB long.  While you could make a custom build with a
> different threshold, the fact remains that LZ-style compression is not
> real efficient on short stretches of text.  If you "really need to save
> disk space" it behooves you to consider that.  I'd suggest thinking about
> whether you can merge multiple log entries, or something, such that the
> field values you need to store are on the order of a few KB.

See ALTER TABLE ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL |
EXTENDED | MAIN }.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +