Disable synchronous commit by default

[acaloiaro]

Postgres wisely enables synchronous commit by default. This is a good choice for a general purpose relational database like Postgres, but less good for queue applications.

For small, short transactions (enqueueing and updating job status), synchronous commit quickly dominates queue performance.

By accepting the tradeoff of a small window of time (200ms by default) between writing to the WAL and persisting to permanent storage when data may be lost, an order of magnitude performance gain is realized.

https://www.postgresql.org/docs/current/wal-async-commit.html

[elliotcourant]

Synchronous commit would delay persistence to the WAL right, writes to the disk are still done asynchronously no matter what using the buffer manager?

This also only affects TXNs/Queries for that session right. So other sessions don't need to worry about their data potentially being lost?

[acaloiaro]

Synchronous commit would delay persistence to the WAL right, writes to the disk are still done asynchronously no matter what using the buffer manager?

---

I'm using https://www.postgresql.org/docs/current/wal-async-commit.html for reference here. My reading of it is that synchronous commit has no bearing on the WAL, but to "persistence to permanent storage".

As described in the previous section, transaction commit is normally synchronous: the server waits for the transaction's WAL records to be flushed to permanent storage before returning a success indication to the client.

This change allows the transaction to appear "committed" to the client as soon as its written to the WAL.

---

This also only affects TXNs/Queries for that session right. So other sessions don't need to worry about their data potentially being lost?

Right, we're using SET sychronous_commit = 'off' in the connection's AfterConnect rather than setting the same in the postgres server config.