During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the documentation of PostgreSQL that Asynchroneous commits might be an option for boosting performance when small data loss in case of a server crash is acceptable. We run the backend for an iPhone multiplayer game with quite simple, but a lot of, requests per second. Since it's a game and not a financial system we are running, the risk of data loss seems acceptable.
Does anyone have experience in difference in performance comparing synchroneous/asynchroneous commits?
Is there any risks with using this option, apart from data loss in case of a server crash?
Is there any risk that the queue of uncommitted transactions will keep growing since we will serve more requests?
Our setup
PostgreSQL, Nginx, Ruby on Rails, Unicorn
Best Answer
Your risk is data loss, not data corruption. An ACID platform guarantees transactions are
With asynchronous commits, you give up "durable". For your application, that seems a defensible decision.
If you have some transactions that need to be durable (new user registrations?) you can have that, too. PREPARE TRANSACTION is always synchronous; so is SET synchronous_commit = on. But it will interfere a little with all those asynchronous commits.
No, not if my understanding is correct. The WAL writer periodically writes all unwritten WAL records to disk. It doesn't see 1000 unwritten WAL records and decide, "Well, I can write 17 of them this time." If there are 1000 unwritten WAL records when the timer fires, it writes all 1000 to disk.
If your database server is constantly and heavily overloaded, I guess users might experience some "jerks" in performance if the WAL writer pushes a huge backlog to disk.
In every useful database application, you eventually have to think about upgrading hardware. Sometimes, the only defensible solution is a faster box.
PostgreSQL 9.1's wal_writer_delay setting.