> By default, SQLite is not durable, because the default value of journal_mode is DELETE, and the default value of synchronous is FULL, which doesn't provide durability in DELETE mode.
From the documentation, it seems like synchronous being FULL does provide durability of the database in DELETE mode, as FULL means it calls fsync after the transaction is completed. I think you may be confusing durability of the journal file with durability of the database. I don't think WAL can ever really have a durable transaction; it is essentially based on leaving a transaction open until it gets "check-pointed" or actually committed to the database file.
> I don't think WAL can ever really have a durable transaction; it is essentially based on leaving a transaction open until it gets "check-pointed" or actually committed to the database file
why not? if you use synchronous=FULL, then WAL does provide durable transactions, no?
This is disingenuous and probably was written this way for HN cred and clicks. Sqlite's test suite simulates just about every kind of failure you can imagine - this document is worth reading if you have any doubts: https://www.sqlite.org/atomiccommit.html
SQLite is an incredible piece of software, and its commitment to backward compatibility is deeply admirable. But that same promise has also become a limitation.
v3.0 was first released in 2004—over 20 years ago—and the industry has changed dramatically since then.
I can’t help but wish for a “v4.0” release: one that deliberately breaks backward compatibility and outdated defaults, in order to offer a cleaner, more modern foundation.
Note: I'm not asking for new functionality per se. But just a version of SQLite that defaulted to how it should be used, deployed in 2025.
Durability also requires the file system implementation and the disk to do the right thing on fsync, which, if I recall past discussions correctly, isn’t a given.
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?
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!
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.
> More broadly, I don't think a single definition of 'durable' (as in ACID D) for transactions is particularly useful.
> Much more useful is to ask "what kinds of failures could cause committed transactions to be lost?"
All these articles talking about durability as a singular term should be warned. Writing to disk is not durable under certain circumstances. fsync is not durable under certain circumstances. Two-phase commit is not durable under certain circumstances. Multi-data center commit is not durable against the Death Star too.
fsync is rarely truly durable in the sense the article describes. it does help with loss ordering in a lot of cases, and flushes often do some work, but true durability, the idea that after fsync there will not be any rollback or tail loss unless there's catastrophic failure, sorry, nope. everyone in the chain ends up in the hot path being the hotspot, then they break it because y'all are addicted to spamming syncs then they move it to a new api, then slowly the syncs come back on the new api, then everyone moves again layer by layer. somewhat common examples in recent years are nvme vendors who implement nvme flush in terms of just pushing the write cache down, but won't always also finalize and flush in flight or scheduled ftl operations due to the insane worst case latency costs associated. weren't apple also caught doing the same in recent years, in part because their ftl shared memory, bus and privilege with the higher exception levels? there's also the rumors people say about enterprise drives being better here, but not doing so is even a saleable product in those environments: https://www.atpinc.com/technology/ssd-flush-cache-technology and some other vendors just have arbitrary firmware patches to compete (that is: you could buy "enterprise grade" hardware second hand and be entirely unaware of the actual command behavior).
16 comments
[ 4.1 ms ] story [ 42.5 ms ] threadFrom the documentation, it seems like synchronous being FULL does provide durability of the database in DELETE mode, as FULL means it calls fsync after the transaction is completed. I think you may be confusing durability of the journal file with durability of the database. I don't think WAL can ever really have a durable transaction; it is essentially based on leaving a transaction open until it gets "check-pointed" or actually committed to the database file.
why not? if you use synchronous=FULL, then WAL does provide durable transactions, no?
v3.0 was first released in 2004—over 20 years ago—and the industry has changed dramatically since then.
I can’t help but wish for a “v4.0” release: one that deliberately breaks backward compatibility and outdated defaults, in order to offer a cleaner, more modern foundation.
Note: I'm not asking for new functionality per se. But just a version of SQLite that defaulted to how it should be used, deployed in 2025.
SQLite (with WAL) doesn't do `fsync` on each commit under default settings - https://news.ycombinator.com/item?id=45005071 - Aug 2025 (90 comments)
with a relevant comment by the creator of SQLite here: https://news.ycombinator.com/item?id=45014296
(via https://news.ycombinator.com/item?id=45068594 - thanks int_19h!)
Perhaps what's unclear is when to select which option?
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!
> 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.
> More broadly, I don't think a single definition of 'durable' (as in ACID D) for transactions is particularly useful.
> Much more useful is to ask "what kinds of failures could cause committed transactions to be lost?"
All these articles talking about durability as a singular term should be warned. Writing to disk is not durable under certain circumstances. fsync is not durable under certain circumstances. Two-phase commit is not durable under certain circumstances. Multi-data center commit is not durable against the Death Star too.