Thread: Need help - Regarding Data Compression in PostgreSQL.

On Tue, Dec 16, 2008 at 6:52 AM, salma <salma.khanam@applabs.com> wrote:
> Hi,
>
> I'd like to know:
> 1. If it is possible to compress data in the PostgreSQL.

Ayup.  Anything over a standard size gets compressed out of line into
TOAST tables.

> 2. What kind of compression, if any, is available in PostgreSQL.

Standard unix compress I believe

> 3. If compression is available, what kind of disk space savings can I expect
> using it.

depends on how compressible your data is.  The compression that's
built into pgsql is focused on speed, not compression.  So don't
expect 99% compression or anything.  But on highly compressible data
you can expect it to compress quite well.  Do an experiment with du
and see what you get.

A lot of this info is probably in the docs somewhere, but I'm not sure
it's a faq or not.

Scott Marlowe escribió:
> On Tue, Dec 16, 2008 at 6:52 AM, salma <salma.khanam@applabs.com> wrote:

> > 2. What kind of compression, if any, is available in PostgreSQL.
>
> Standard unix compress I believe

No, it's our own LZ implementation, which is focused on speed but does
not compress as well as gzip or compress (see
src/backend/utils/adt/pg_lzcompress.c for more details)

> > 3. If compression is available, what kind of disk space savings can I expect
> > using it.
>
> depends on how compressible your data is.  The compression that's
> built into pgsql is focused on speed, not compression.  So don't
> expect 99% compression or anything.

Well, you can compress 200 constant bytes to 4 with our algorithm, so
it's 98% :-) (plus the varlena header though)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

I started feeling nobody wants to answer those questions but finally got a
reply :-)
Thanks a lot Scott.

- Salma

----- Original Message -----
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "salma" <salma.khanam@applabs.com>
Cc: <pgsql-novice@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Friday, December 19, 2008 5:36 AM
Subject: Re: [GENERAL] Need help - Regarding Data Compression in PostgreSQL.


> On Tue, Dec 16, 2008 at 6:52 AM, salma <salma.khanam@applabs.com> wrote:
>> Hi,
>>
>> I'd like to know:
>> 1. If it is possible to compress data in the PostgreSQL.
>
> Ayup.  Anything over a standard size gets compressed out of line into
> TOAST tables.
>
>> 2. What kind of compression, if any, is available in PostgreSQL.
>
> Standard unix compress I believe
>
>> 3. If compression is available, what kind of disk space savings can I
>> expect
>> using it.
>
> depends on how compressible your data is.  The compression that's
> built into pgsql is focused on speed, not compression.  So don't
> expect 99% compression or anything.  But on highly compressible data
> you can expect it to compress quite well.  Do an experiment with du
> and see what you get.
>
> A lot of this info is probably in the docs somewhere, but I'm not sure
> it's a faq or not.