Re: Is Synchronous Postgresql Replication Slower Than Asynchronous?

Peter,

I noticed there are several synchronous implementations (Postgre-XC, PGCluster, pgpool, rubyrep, built-in streaming, etc.). When you say, " you can dynamically change that right down to the transaction level...", are you referring specifically to one of these implementations?

By the way

Thanks,
Jerry

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Peter Geoghegan
Sent: Friday, January 20, 2012 1:37 PM
To: John R Pierce
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Is Synchronous Postgresql Replication Slower Than Asynchronous?

On 20 January 2012 21:17, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 01/20/12 12:31 PM, Jerry Richards wrote:
>>
>> Is synchronous postgresql replication slower than asynchronous?  If
>> so, how much?  I am looking into database replication for a phone
>> system, so the response time is of concern.
>
>
> when a client issues a COMMIT on the master, synchronous by definition
> has to wait for the replication to be committed on the slave.  this
> will of course be slower, how much slower depends on far too many
> variables to give an estimate, including things like speed of both
> servers disk systems, speed of the network communications, data volume and transaction rate.

Synchronous replication is, by its very nature, slower than asynchronous replication, and that has nothing to do with the PostgreSQL implementation in particular. The whole point of using it is to ensure that transactions have an additional level of durability.
The master must wait for confirmation from the standby before indicating the transaction committed, so latency/distance becomes a real concern.

Uniquely, the PostgreSQL implementation doesn't require that all transactions be synchronous or asynchronous; you can dynamically change that right down to the transaction level (or the client, or the database...). You might like to make really important transactions alone be replicated synchronously for super-durability. Many applications that use this feature only use it for a small though critical subset of transactions, like financial transactions.

--
Peter Geoghegan       http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general