Re: Postgresql Replication Performance

On Thu, Dec 29, 2011 at 11:33 AM, Aleksej Trofimov
<aleksej(dot)trofimov(at)ruptela(dot)lt> wrote:
> Postgres Streaming replication is WAL based replication, so using this type
> of replication you will have absolutely identical database servers, what is
> best choice for HA and scaling reads. Also this choice is not practically
> affecting performance, because it is not adding any latency to database
> layer.

Let me chime in, because I'm in a similar situation. I'm preparing a
POC WAL-replicated environment, and testing up until now has been
inconclusive since we lack the kind of hardware in our test
environment. I know I should require it, testing on similar hardware
is the only way to get reliable results, but getting the budget
approved would take way too long, and right now we're in a hurry to
scale reads.

So getting the hardware is not an option, my option is asking those
who have the experience :-)

I gather WAL replication introduces only a few possible bottlenecks.

First, network bandwidth between master and slaves, and my app does
write a lot - our monitoring tools show, today, an average of 1MB/s
writes on the WAL array, with peaks exceeding 8MB/s, which can easily
saturate our lowly 100Mb/s links. No worries, we can upgrade to 1Gb/s
links.

Second, is that WAL activity on streaming replication or WAL shipping
is documented to contain more data than on non-replicated setups. What
is not clear is how much more data. This not only affects our network
bandwidth estimations, but also I/O load on the master server, slowing
writes (and some reads that cannot happen on the slave).

So, my question is, in your experience, how much of an increase in WAL
activity can be expected?