forcing compression of text field

On Mon, 2006-12-11 at 09:18 -0700, 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?

Are you sure PostgreSQL isn't compressing it? I didn't think there was a
minimum threshold for compression.

Regards,
Jeff Davis

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.

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.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/11/06 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?

What can be compressed? Trailing whitespace or repeating substrings?

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFfsnvS9HxQb37XmcRAuaeAKDRv9IUDH4zenHoVQPST0vFbpHLkwCdED9k
rvvOkNCRx/J8EkGtBF2Bs9Y=
=XANI
-----END PGP SIGNATURE-----

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. +