Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id BE69D6331D1 for ; Wed, 24 Jun 2009 12:09:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 25334-09 for ; Wed, 24 Jun 2009 12:09:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f206.google.com (mail-fx0-f206.google.com [209.85.220.206]) by mail.postgresql.org (Postfix) with ESMTP id 930566321BD for ; Wed, 24 Jun 2009 12:09:04 -0300 (ADT) Received: by fxm2 with SMTP id 2so886625fxm.19 for ; Wed, 24 Jun 2009 08:09:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=6U8F6lGgu4nPIRV9MZvDOdj7ap2EDDNBABoPW0pCUF4=; b=fAN6xN0X/nYyLEIFE5JZ0qmYFxAovtNrNrKNXMm/m1c7WhLzMoai2Lx2fqsPQjveuL YWpJn1WmP1Ij97e/XxMDuGaKDanGPatDn0dg3k4/3D90Wz+IbonCKcFe6qVBRSJwlWA1 S5O3Z5H0i0MY80Nob+nqJq9/mmn1gI4sDrpHo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=R01pa7yw+3saFzVkqPXInIia9z34HtmvEypsteoAIKrX1sehl15i0NpwDqS8+I6NUK Bf+dtJI3FkcC4hqbphULaQ9UPvXXca8q4ok5wD8IgAzbS0plJynTZsQDsll2tBJJCAMr yRAYTt+HF73fT0+fB45jZfU5fDC9Q95XnOWsg= MIME-Version: 1.0 Received: by 10.204.77.96 with SMTP id f32mr1377141bkk.88.1245856138954; Wed, 24 Jun 2009 08:08:58 -0700 (PDT) In-Reply-To: <4A413B6D.5070200@gmail.com> References: <4A413B6D.5070200@gmail.com> Date: Wed, 24 Jun 2009 17:08:58 +0200 Message-ID: <92869e660906240808vc476880sa4d005fba416de88@mail.gmail.com> Subject: Re: Client-side compression From: =?UTF-8?Q?Filip_Rembia=C5=82kowski?= To: Rob Sargent Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001485f812909a8a4d046d197c62 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.042 tagged_above=0 required=5 tests=AWL=0.041, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200906/99 X-Sequence-Number: 32704 --001485f812909a8a4d046d197c62 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 2009/6/23 Rob Sargent > > Not sure if this belongs here or on the admin or performance list. > Apologies if so. (And this may be a second posting as the first was from= an > un-registered account. Further apologies) > > My assumption is that any de/compression done by postgres would be > server-side. > > We're considering minimizing bandwidth utilization Why? Are you hitting some limit? > by using client-side compression on a column value that will typically be > multi-megabyte in size. We would use ALTER TABLE SET STORAGE EXTERNAL to > prevent the server from un-necessary compression. > > Is this generally worthwhile? No general answer, sorry. It depends on other conditions and on what you want to achieve. 1. Almost always you will get better on-disk compression ratio. That's because, quote from pg docs: "The compression technique used is a fairly simple and very fast member of the LZ family of compression techniques. See src/backend/utils/adt/pg_lzcompress.c for the details." 2. You will force all client apps to decompress data on their side. 3. To minimize bandwith utilisation, there are other ways (think compressed tunnels, SSL and so on - but it adds per-connection overhead) > > Is there a great penalty for a query which delves into the value, given > that the server will not be aware it's compressed? not clear. do you mean something like SELECT ... FROM table where decompress(compressed_data) LIKE 'whatever' ??? of course it will be a great penalty. I assume we're pretty much on our own to prevent such actions (i.e. the ap= p > can never query against this column via sql). certainly. --=20 Filip Rembia=C5=82kowski JID,mailto:filip.rembialkowski@gmail.com http://filip.rembialkowski.net/ --001485f812909a8a4d046d197c62 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
2009/6/23 Rob Sargent <= robjsargent@gmail.com>

Not sure if this belongs here or on the admin or performance list. =C2=A0Ap= ologies if so. (And this may be a second posting as the first was from an u= n-registered account. =C2=A0Further apologies)

My assumption is that any de/compression done by postgres would be server-s= ide.

We're considering minimizing bandwidth utilization
Why? Are you hitting some limit?

=C2=A0
by using client-side compression on a column value that will typically be = multi-megabyte in size. =C2=A0We would use ALTER TABLE SET STORAGE EXTERNAL= to prevent the server from un-necessary compression.

Is this generally worthwhile? =C2=A0

No general answer= , sorry.

It depends on other conditions and on what you want to achi= eve.

1. Almost always you will get better on-disk compression ratio.= That's because, quote from pg docs:
"The compression technique used is a fairly simple and very fast membe= r of the LZ family of compression techniques. See s= rc/backend/utils/adt/pg_lzcompress.c for the details."

2. = You will force all client apps to decompress data on their side.

3. To minimize bandwith utilisation, there are other ways (think compre= ssed tunnels, SSL and so on - but it adds per-connection overhead)

<= br>


Is there a great penalty for a query which delves into the value, given tha= t the server will not be aware it's compressed?

n= ot clear. do you mean something like

SELECT ... FROM table where dec= ompress(compressed_data) LIKE 'whatever'
???

of course it will be a great penalty.


=C2=A0I assume we're = pretty much on our own to prevent such actions (i.e. the app can never quer= y against this column via sql).

certainly.



--
Filip Rembia=C5=82kow= ski
JID,mailto:filip.re= mbialkowski@gmail.com
ht= tp://filip.rembialkowski.net/
--001485f812909a8a4d046d197c62--