Re: Core team statement on replication in PostgreSQL

On Sat, May 31, 2008 at 2:18 AM, Mike Rylander <mrylander(at)gmail(dot)com> wrote:
> On Fri, May 30, 2008 at 6:47 PM, Andreas 'ads' Scherbaum
> <adsmail(at)wars-nicht(dot)de> wrote:
>> On Fri, 30 May 2008 17:05:57 -0400 Andrew Dunstan wrote:
>>> Andreas 'ads' Scherbaum wrote:
>>> > On Thu, 29 May 2008 23:02:56 -0400 Andrew Dunstan wrote:
>>> >
>>> >> Well, yes, but you do know about archive_timeout, right? No need to wait
>>> >> 2 hours.
>>> >
>>> > Then you ship 16 MB binary stuff every 30 second or every minute but
>>> > you only have some kbyte real data in the logfile. This must be taken
>>> > into account, especially if you ship the logfile over the internet
>>> > (means: no high-speed connection, maybe even pay-per-traffic) to the
>>> > slave.
>>>
>>> Sure there's a price to pay. But that doesn't mean the facility doesn't
>>> exist. And I rather suspect that most of Josh's customers aren't too
>>> concerned about traffic charges or affected by such bandwidth
>>> restrictions. Certainly, none of my clients are, and they aren't in the
>>> giant class. Shipping a 16Mb file, particularly if compressed, every
>>> minute or so, is not such a huge problem for a great many commercial
>>> users, and even many domestic users.
>>
>> The real problem is not the 16 MB, the problem is: you can't compress
>> this file. If the logfile is rotated it still contains all the
>> old binary data which is not a good starter for compression.
>
> Using bzip2 in my archive_command script, my WAL files are normally
> compressed to between 2MB and 5MB, depending on the write load
> (larger, and more of them, in the middle of the day). bzip2
> compression is more expensive and rotated WAL files are not
> particularly compressable to be sure, but due to (and given) the
> nature of the data bzip2 works pretty well, and much better than gzip.

Compression especially is going to negate one of the big advantages of
wal shipping, namely that it is cheap investment in terms of load to
the main. A gigabit link can ship a lot of log files, you can always
bond and 10gige is coming. IMO the key trick is to make sure you
don't send the log file more than once from the same source...i.e
cascading relay.

merlin