Does Postgres compress data?

On 05/23/2012 06:07 PM, Mike Christensen wrote:

If I run this query:

select sum(length(html)) from Indexer.Pages;

I get:

15,680,005,116

However, if I type:

C:\Program Files\PostgreSQL>dir /s

I get:

Total Files Listed:
5528 File(s) 7,414,385,333 bytes
575 Dir(s) 43,146,137,600 bytes free

So all the Postgres data on disk is a little over 7 gigs, however the
total sum of bytes in the HTML column of the Pages table is over 15
gigs.

Is PG compressing this data? I'm curious as I was considering
converting this column to a byte array and gzip'ing the data to save
space, however if PG is already doing this for me, then I'm not going
to bother. Thanks!

Yes. See here for complete answer:

http://www.postgresql.org/docs/9.1/static/storage-toast.html

Mike

--
Adrian Klaver
adrian.klaver@gmail.com

Mike --

<...>

Is PG compressing this data?  I'm curious as I was considering
converting this column to a byte array and gzip'ing the data to save
space, however if PG is already doing this for me, then I'm not going
to bother.  Thanks!

Mike

It may vary from version of postgres to version, but perhaps you are seeing the effects of TOAST kicking in ? Do a search in the documentation for your specific version (8.3, 9.1 etc.)

HTH,

Greg Williamson

On Wed, May 23, 2012 at 6:16 PM, Greg Williamson
<gwilliamson39@yahoo.com> wrote:

Mike --

<...>

Is PG compressing this data?  I'm curious as I was considering
converting this column to a byte array and gzip'ing the data to save
space, however if PG is already doing this for me, then I'm not going
to bother.  Thanks!

Mike

It may vary from version of postgres to version, but perhaps you are seeing the effects of TOAST kicking in ? Do a search in the documentation for your specific version (8.3, 9.1 etc.)

Yup, looks like this is the default behavior for TOAST-able columns.
I'm running on 9.0. Anyway, good information - I was mostly just
curious..

Mike