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