Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Sat, 17 Mar 2012 10:46:00 -0500, dennis jenkins
<dennis.jenkins.75@gmail.com> wrote:
>
> Aleksey, a suggestion:  The vast majority of the postgresql wire
> protocol compresses well.  If your WAN link is not already compressed,
> construct a compressed SSH tunnel for the postgresql TCP port in the
> WAN link.  I've done this when rebuilding a 300GB database (via slony)
> over a bandwidth-limited (2MB/s) VPN link and it cut the replication
> resync time down significantly.
>

SSH with the HPN patchset[1] would help as well if it's higher latency or
if you're CPU limited as it can use multiple threads then. It works
wonderfully for me on a 35mbit link. If you have a lower sized link that
wouldn't benefit from the HPN patchset anyway it may be worth forcing
Blowfish instead of AES to keep the CPU load lower.

Hope that helps!


[1] http://www.psc.edu/networking/projects/hpn-ssh/

FYI, the HPN patchset is included the base OpenSSH of FreeBSD 9 now.