Re: forcing compression of text field

Tom Lane wrote:
> Scott Marlowe <smarlowe(at)g2switchworks(dot)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(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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