Asynchronous commit on Postgres over iSCSI with BBU storage It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller.

In my environment I am on a VM whose block storage is connected via iSCSI, where the iSCSI node is using DRBD and both the primary and secondary nodes have battery backed up controllers.

FWIW, here's the specifics: 


PostgreSQL 9.3
CentOS 6.3 (VM)
VMware 5.1
Infiniband QDR transport
SLES 11 iSCSI nodes
DRBD
LSI BBU SCSI controllers with CacheCade Pro 2.0 (in-use).


Can I safely turn off synchronous_commit?
It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller.


Actually, that's kind of backwards. Turning synchronous_commit off is unsafe no matter what, in that it permits the database to lose recently committed transactions if it crashes. This is true with, or without, BBU or crash-safe SSD storage.

What synchronous_commit = off does is allows you to trade durability for speed when you're on storage where flushes are expensive so you want to batch them up and want to avoid client latency while apps wait for flushes. It has much less effect on storage where flushes are fast, since you do less waiting for commits to happen - so it's pretty much all downside and little benefit.

In general, you should not set synchronous_commit off globally. As the documentation advises, you should SET LOCAL synchronous_commit = off in specific transactions that you don't need to be durable, leaving it enabled otherwise. That way you might lose transactions you're not so fussed about, but not the ones that're making important changes.

If you can't afford potential loss of transactions that clients think are committed at all, you may instead want to consider a commit_delay, which pauses to try to batch a few commits together before flushing. This can produce a throughput improvement on I/O subsystems with really slow flushes without sacrificing durability.

You may also want to consider using UNLOGGED tables for specific tables you can afford to lose in a crash. If Pg crashes while the table is dirty it'll truncate it and you'll have to re-populate it, but there'll be no wider database corruption.

If anyone tells you to turn fsync off - please don't. It should be called eat_my_data = on, and is totally unsuitable for anything except throw-away instances where you can easily reconstruct the lot after a crash.