On 06/06/13 12:48, Colin S wrote:
Thanks for your answer. I find it very interesting that you say that synchronous setups should always be in two geographically separate locations. In this case they are on the same subnet. Adding the lag of committing to two, geographically separate, databases is not feasible for this OLTP application.
Well, if they're in the same building(s) then your transactions are all at the same risk from fire/earthquake/godzilla etc. Might/might not be important to you.
I also like your point that "mostly synchronous is just asynchronous." So, responding by switching to asynchronous as a response to slow-down is asynchronous anyway.
"Mostly synchronous" is like "a bit pregnant".
Any other comments, or examples, of when synchronous is worth implementing would be greatly appreciated.
Note that PostgreSQL's synchronous replication just guarantees that the commit has reached the transaction log on the slave. That doesn't mean the slave has replayed the transaction log and a query against the slave will show the transaction's results. So - it doesn't in itself guarantee that you can see issue read-only queries against either server indiscriminately.
However, if you really, really need to know that a committed transaction is on two physically separate sets of disks then synchronous is what you want. If both sets of disks are in the same building then you might be able to achieve the same result by other (cheaper/simpler?) means.
If you have a business e.g. selling books or train tickets or some such then you might decide it's better to have a simpler more robust setup from the point of view of providing continuous service to end-customers. In the (hopefully rare) event of a crash irreparably losing some transactions apologise to your customers and recompense them generously.
For a system handling multi-million pound inter-bank transfers you might decide it's better to have the system not working at all rather than have an increased risk of a lost transaction.
Of course in both cases you might well want a separate list/cache of pending/recently-committed transactions to check against in the event of a failure.
I believe what you should do from an engineering approach is to treat it in a similar way to security. What do you want to protect against? Make a list of possible failures and what they mean to the business/project and then decide how much time/money to spend protecting against each one.
-- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
