Compression of text fields

From: Brian McCane <bmccane(at)mccons(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Compression of text fields
Date: 2003-08-20 16:00:48
Message-ID: 20030820104038.P88296-100000@fw.mccons.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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
2) what compression method is used
3) is there a way to view actual storage space used versus
the value I get from length()
4) Can I disable the compression to improve storage speed
if the compression algorithm is not as good as deflate

- brian

Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Stephan Szabo 2003-08-20 16:26:17 Re: Compression of text fields
Previous Message scott.marlowe 2003-08-20 15:48:39 Re: changing field length