Re: Compression of text fields

On Wed, 20 Aug 2003, Brian McCane wrote:

> I have read somewhere that text fields are "compressed". What I
> am curious about is how the compression of text fields by PostgreSQL might
> be affecting the performance of my software. I currently store entire
> copies of documents in a table called "fulltext" as such:
>
> CREATE TABLE fulltext (
> uid serial8 PRIMARY KEY,
> content text NOT NULL,
> contentidx txtidx
> ) ;
>
> As you can see, I am using contrib/tsearch to find documents for display,
> and then I dump out 'content' to the user.
>
> Anyway, when I first created this table, I was concerned about the size of
> 'content' so I linked my program to zlib and deflate the content field
> before storing it into the table. This means that every time someone
> views a document I have to inflate it, also if what I have read is correct
> about the text fields, PostgreSQL is trying to deflate/compress/whatever
> the field when it stores it so I am duplicating effort. This probably
> slows down the performance of my software, and because compressed
> documents are often larger when re-compressed (at least with older
> algorithms like LZW) I might be using extra space to store my data.
>
> So:
> 1) do text fields get compressed

They can (if large enough and depending on their storage attributes).
If the value turns out bigger it won't store the larger compressed
one though (but it'll still attempt to compress it on insert).

> 2) what compression method is used

Looks like some LZ.

> 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 :) )

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.