Should we trade durability for speed?

Model review

Request changes. These are two very different risks, and one of them is not acceptable for a database that holds anything you cannot rebuild.

1. fsync = off can destroy the database. With it off, PostgreSQL no longer forces anything to permanent storage: not the log, not the data files. After a power failure or operating system crash, the data files can be left inconsistent in ways the log cannot repair, and the documentation’s word is “unrecoverable”. It is meant for databases you can recreate from scratch, like a one-off bulk load, and the documentation says good hardware alone does not justify it.

2. synchronous_commit = off risks something smaller and well defined. A commit returns before its log record is flushed. If the server crashes, the last fraction of a second of commits can be lost, but recovery replays the log in commit order up to the last flushed record, so the database is always consistent. On our test machine it made a single-row commit about 14 times faster (31 µs down to 2.2 µs).

3. It does not need to be server-wide. Asynchronous commit can be set per session or per transaction. Use it only for the ingestion path, where losing the last few events in a crash is acceptable, and keep everything else (orders, payments, accounts) fully durable:

BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO events (...) VALUES (...);
COMMIT;

4. Batching is often the bigger win. The cost here is one log flush per event. Inserting 100 events per transaction pays that flush once per 100, with no change to durability at all.

What I would ask for: remove fsync = off. Batch inserts if the producer allows it. If that is not enough, use SET LOCAL synchronous_commit = off for event inserts only, and write down that a crash can lose the most recent events.