[Bucardo-general] Request: Compression on link?

[Karl Denninger]

Greg Sabino Mullane wrote:
> On Sun, Aug 01, 2010 at 03:30:15PM -0500, Karl Denninger wrote:
>   
>> Any thoughts about allowing Bucardo to do its connecting (at least for
>> full-table copies) via SSH as a tunnel so one can take advantage of the
>> compression switch?
>>     
>
> Well, it won't be Bucardo doing it per se, it will be the underlying 
> connection, which in this case is libpq via DBD::Pg. It should certainly 
> be possible to setup your own tunnel that would be transparent to all 
> involved. If you do so, please share any benchmarks, I'd be curious 
> how much of a speedup is accomplished and under what conditions.
>   
For routine syncs the savings are probably not worth it if you have to
set up a tunnel and maintain it manually.  But for maintenance COPY
style-syncs (e.g. when restoring a system or resync'ing after a problem)
it's an entirely different matter.  I have done this using ssh's
compression option to set up a manual tunnel, then directing the sync
through it.

For text tables (where the data in the table is mostly or all
textually-represented data) the savings should be ~30% or so.

For tables containing BYTEA fields the data transport savings can be 60%
or more, with some cases (mostly-BYTEA data in a given table, where the
data is actually binary with a random distribution aka images) savings
could easily approach or even exceed 75%!

Note that a table on my system that reports ~7GB consumed turns into
**30GB** when dumped with pg_dump, for exactly this reason - the
representation in the dump file is numerically-escaped, and thus
FREAKING HUGE compared to the actual data.

A COPY sync of this table will require 30GB of data transport.  If
COMPRESSED then the transport size will be 7GB **or less**.

This turns a ~5 hour table copy over a WAN link into a one-hour one (!)

It would be nice if Postgres' internal OpenSSL support handled this
transparently.  OpenSSL has the hooks to permit it, but Postgres doesn't
know how to use them.

-- Karl

-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20100804/f5d6f706/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: karl.vcf
Type: text/x-vcard
Size: 124 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20100804/f5d6f706/attachment.vcf