Re: Asynchronous commit | Transaction loss at server crash

Balkrishna Sharma wrote:
> I need to support several hundreds of concurrent update/inserts from
> an online form with pretty low latency (maybe couple of milliseconds
> at max). Think of a save to database at every 'tab-out' in an online form.

I regularly see 2000 - 4000 small write transactions per second on
systems with a battery-backed write cache and a moderate disk array
attached.  2000 TPS = 0.5 ms, on average.  Note however that it's
extremely difficult to bound the worst-case behavior possible here
anywhere near that tight.  Under a benchmark load I can normally get
even an extremely tuned Linux configuration to occasionally pause for
1-3 seconds at commit time, when the OS write cache is full, a
checkpoint is finishing, and the client doing the commit is stuck
waiting for that.  They're rare but you should expect to see that
situation sometimes.

We know basically what causes that and how to make it less likely to
happen in a real application.  But the possibility is still there, and
if your design cannot tolerate an occasional latency uptick you may be
disappointed because that's very, very hard to guarantee with the
workload you're expecting here.  There are plenty of ideas for how to
tune in that direction both at the source code level and by carefully
selecting the OS/filesystem combination used, but that's not a very well
explored territory.  The checkpoint design in the database has known
weaknesses in this particular area, and they're impossible to solve just
by throwing hardware at the problem.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us