5

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
  • 3
    No, that's not possible. The fsync setting is always for the complete cluster (instance)
    – user1822
    Commented Sep 9, 2015 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. Commented Sep 9, 2015 at 14:46
  • You may want to play with checkpoints
    – Sahap Asci
    Commented Sep 10, 2015 at 6:45

1 Answer 1

5

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.

1
  • 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
    Commented Sep 13, 2015 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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