The documentation seems pretty clear to me - it describes specifically what each option controls and the implications of using it. Besides debating whether the default behavior is should be described as durable or not, this post's author seems to understand exactly what each option actually does.

Perhaps what's unclear is when to select which option?

▲

avinassh 4 days ago | parent | next [-]

I wrote the first article, and I thought documentation is clear, but then I saw comment by Hipp which got confused me:

> If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored.

https://news.ycombinator.com/item?id=45014296

the documentation is in contradiction with this.

▲

agwa 4 days ago | parent | prev [-]

I found the documentation much harder to parse than the equivalent PostgreSQL docs (https://www.postgresql.org/docs/current/wal-async-commit.htm...).

Also, even if I've understood the docs correctly, a number of people in this thread and elsewhere have come to a different interpretation. I think that's much less likely to happen with the PostgreSQL docs.

And I'm sure you can understand why I began to doubt my own interpretation of the docs when SQLite's creator posted a comment saying the exact opposite of what I thought the docs said!

	▲	sethev 4 days ago \| parent [-]
		Fair! Fwiw, I enjoyed the post - hopefully my comment wasn't harsh. I think it does come down to the definition of durable. The default in SQLite is that the data for a transaction will be written all the way to disk (with an fsync) and the rollback journal will be deleted (but without an fsync). In this model, there is chance of losing only the last transaction and only if the whole system crashes (like a power failure) after deleting the journal but before your file system makes that durable. If your application crashes, you're still fine. That's significantly more durable than an asynchronous commit in Postgres, though. In an asynchronous commit, the transaction will complete before any data is written to disk at all - the data is only in memory, and many transactions can queue up in memory during the wal_writer_delay. All of those will definitely be lost if Postgres stops uncleanly for any reason (for example a crash, oomkill, or power failure).