Postgres Pro
Downloads
Home   >   mailing lists

Thread: Does Postgres compress data?

Does Postgres compress data?

From
Mike Christensen
Date:
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!

Mike

Re: Does Postgres compress data?

From
Adrian Klaver
Date:
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

Re: Does Postgres compress data?

From
Greg Williamson
Date:
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
searchin the documentation for your specific version (8.3, 9.1 etc.) 

HTH,

Greg Williamson

Re: Does Postgres compress data?

From
Mike Christensen
Date:
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
searchin 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