I have a write-intensive database stored in PostgreSQL v9.4.4 and it causes IO on my device. I'd like to switch fsync off for only a single database, not PostgreSQL server wide. (I accept that it can cause corruption when unexpected shutdown occurs). Help me find out how to make it.

  • 3
    No, that's not possible. The fsync setting is always for the complete cluster (instance) – a_horse_with_no_name Sep 9 '15 at 14:44
  • 1
    You can keep two PostgreSQL clusters on the same machine - move that DB to a separate one and you can configure it as you wish. – dezso Sep 9 '15 at 14:46
  • You may want to play with checkpoints – Sahap Asci Sep 10 '15 at 6:45

You might want to check unlogged tables, they do not produce WALs at all. But of course if crash happen - all data from them will be lost and they cannot be replicated.

Also there is much safer setting synchronous_commit which could reduce IO and it's possible to set it per session or per user/database.

  • Which in this case, would look like alter database that_database set synchronous_commit TO off;. It will not take effect in existing connections, only ones started after the change is made. – jjanes Sep 13 '15 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.