Skip site navigation (1) Skip section navigation (2)

Re: Compression of text fields

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Brian McCane <bmccane(at)mccons(dot)net>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Compression of text fields
Date: 2003-08-20 16:26:17
Message-ID: 20030820092151.H8293-100000@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.


In response to

Responses

pgsql-admin by date

Next:From: Joe ConwayDate: 2003-08-20 17:11:23
Subject: Re: Compression of text fields
Previous:From: Brian McCaneDate: 2003-08-20 16:00:48
Subject: Compression of text fields

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group