pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Client-side compression 3+ messages / 3 participants [nested] [flat]
* Client-side compression @ 2009-06-23 20:30 Rob Sargent <robjsargent@gmail.com> 0 siblings, 2 replies; 3+ messages in thread From: Rob Sargent @ 2009-06-23 20:30 UTC (permalink / raw) To: pgsql-sql 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 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? I haven't found any thread on the subject of client-side compress so any pointer more than welcome. Is there a great penalty for a query which delves into the value, given that the server will not be aware it's compressed? I assume we're pretty much on our own to prevent such actions (i.e. the app can never query against this column via sql). ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Client-side compression @ 2009-06-24 12:13 Jasen Betts <jasen@xnet.co.nz> parent: Rob Sargent <robjsargent@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Jasen Betts @ 2009-06-24 12:13 UTC (permalink / raw) To: pgsql-sql On 2009-06-23, Rob Sargent <robjsargent@gmail.com> wrote: > > 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. there may already be compression of the communication stream (probably not on unix sockets) > We're considering minimizing bandwidth utilization 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? I haven't found any thread on the subject > of client-side compress so any pointer more than welcome. we recently switched from uncompressed pixmaps to JPEG data for some stored images. we have not tested performance but have certainly not noticed a decrease in performance. > Is there a great penalty for a query which delves into the value, given > that the server will not be aware it's compressed? I assume we're > pretty much on our own to prevent such actions (i.e. the app can never > query against this column via sql). It just looks like data to the server. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Client-side compression @ 2009-06-24 15:08 Filip Rembiałkowski <plk.zuber@gmail.com> parent: Rob Sargent <robjsargent@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Filip Rembiałkowski @ 2009-06-24 15:08 UTC (permalink / raw) To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql 2009/6/23 Rob Sargent <robjsargent@gmail.com> > > 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 app > can never query against this column via sql). certainly. -- Filip Rembiałkowski JID,mailto:filip.rembialkowski@gmail.com http://filip.rembialkowski.net/ ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-06-24 15:08 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-06-23 20:30 Client-side compression Rob Sargent <robjsargent@gmail.com> 2009-06-24 12:13 ` Jasen Betts <jasen@xnet.co.nz> 2009-06-24 15:08 ` Filip Rembiałkowski <plk.zuber@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox