Re: [SQL] Client-side compression
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 app > can never query against this column via sql). certainly. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [SQL] Client-side compression
On 2009-06-23, Rob Sargent 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Client-side compression
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). -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
