Also, you probably do not gain much with synchronization=FULL if the SSD does not really honor flushes correctly. Some SSDs historically lied - acknowledging flushes before actually persisting them. I don't know if this is still true.
However, it depends on the application - hence the need for clear docs and an understandable model.
If the app confirms to me my crypto transaction has been reliably queued, I probably don’t want to hear that it was unqueued because a node using SQLite in the cluster had died at an inconvenient specific time.
If that is the threat you want to defend against this is not the right setting. Maybe it would reduce the window for it a little bit, but power failures are basically a non existent threat anyways, does a solution that mildly reduces but not eliminate the risk really matter when the risk is negligible?
1. I general a keypair and commit it.
2. I send the public key to someone.
I *really* want to be sure that 1 is persisted. Because if they for example send me $1M worth of crypto it will really suck if I don't have the key anymore. There are definitely cases where it is critical to know that data has been persisted.
This is also assuming that what you are syncing to is more than one local disc, ideally you are running the fsync on multiple geographically distant discs. But there are also cryptography related applications where you must never reuse state otherwise very bad things happen. This can apply even for one local disc (like a laptop). In this case if you did something like 1. Encrypt some data. 2. Commit that this nonce, key, OTP, whatever has been used. 3. Send that datasome where. Then You want to be sure that either that data was comitted or the disc was permanently destroyed (or at least somehow wouldn't be used accidentally to be encrypt more data).
Not in the contexts sqlite3 is often used. Remember, this is an embedded database, not a fat MySQL server sitting in a comfy datacenter with redundant power backups, RAID 6 and AC regulated to the millidegree. More like embedded systems with unreliable or no power backup. Like Curl, you can find it in unexpected places.
If your application can tolerate writes silently failing then you can live without it. But a lot of applications can't, so it does matter.
https://sqlite.org/compile.html#default_synchronous
>SQLITE_DEFAULT_SYNCHRONOUS=<0-3> This macro determines the default value of the PRAGMA synchronous setting. If not overridden at compile-time, the default setting is 2 (FULL).
>SQLITE_DEFAULT_WAL_SYNCHRONOUS=<0-3> This macro determines the default value of the PRAGMA synchronous setting for database files that open in WAL mode. If not overridden at compile-time, this value is the same as SQLITE_DEFAULT_SYNCHRONOUS.
Many wrappers for sqlite take this advice and change the default, but the default is FULL.
$ sqlite3 test.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode=wal;
wal
sqlite> PRAGMA synchronous;
1
sqlite>
/opt/homebrew/opt/sqlite/bin/sqlite3 test.db
SQLite version 3.50.4 2025-07-30 19:33:53
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode=wal;
wal
sqlite> PRAGMA synchronous;
2
sqlite>
Another oddity: misteriously reserving 12 bytes per page for whatever reason, making databases created with it forever incompatible with the checksum VFS.
Other: having 3 different layers of fsync to avoid actually doing any F_FULLFSYNC ever, even when you ask it for a fullfsync (read up on F_BARRIERFSYNC).
and /usr/bin/sqlite3 is 1
> EXTRA provides additional durability if the commit is followed closely by a power loss.
means?
How can one have "additional" durability, if FULL already "ensures that an operating system crash or power failure will not corrupt the database"?
Is it that FULL only protects against "corruption" as stated, but will still lose committed transactions?
It seems so from the points on https://stackoverflow.com/questions/58113560/during-power-lo...
Which is also quite nasty. I want my databases to be fully durable by default, and not lose anything once they have acknowledged a transaction. The typical example for ACID DBs are bank transactions; imagine a bank accidentally undoing a transaction upon server crash, after already having acknowledged it to a third-party over the network.
It depends on your filesystem whether this is necessary. In any case I'm pretty sure it's not relevant for WAL mode.
> EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode
So it only has an effect in DELETE mode; WAL mode doesn't use a rollback journal.
That said, the documentation about this is pretty confusing.
Which in my opinion is worse than whatever may apply to WAL mode, because WAL is something a user needs to explicitly enable.
If it is true as stated, then I also don't find it very confusing, but would definitely appreciate if it were more explicit, replacing "will not corrupt the database" by "will not corrupt the database (but may still lose committed transactions on power loss)", and I certainly find that a very bad default.
I now filed a suggestion to clarify the docs on this:
Because DELETE depends on deleting a file (not modifying file contents), it much depends on the specific file system's (not SQLite's) journaling behavior.
In either case it is clear that (on Linux), if you want guarantees, you need to fsync (the file and the dir respectively).
That's why they don't try to do it that way! But it's still an informative way to think about it.
Also, while we're discussing defaults, your ACID db is probably running at READ COMMITTED by default, meaning that your bank transactions are vanishing/creating money:
* You read accounts A and B($30) in order to move $5 between them. The new balance for B should be $35. Just before you write the $35, someone else's transaction sets B to $100. Your transaction will proceed and blindly set it to $35 anyway.
But to your overall point, I'm also frustrated that these systems aren't as safe as they look on the box.
You're probably refering to PostgreSQL. Yes, I am also frustrated that that doesn't default to SERIALIZABLE.
I do wish the top two open-source "ACID" DBs (Postgres and SQLite) used guaranteed-safe, zero-surprise defaults.
In addition to the need for all clients to see shared memory, it disables acid transactions on attached databases (2nd to last paragraph below):
It only disables global cross-db transactions. It does not disable transactions in the attached db.
The fact that global transactions are a thing at all is the surprising bit to me. I think the WAL mode is the more expected behaviour.
And it's not reasonable to expect them to read through all the docs to figure out that the defaults are not safe.
Defaults should be safe, tune for performance. Not the other way around.
https://sqlite.org/compile.html
But there is a thing called NORMAL mode which, in WAL and non-WAL mode, does not fsync on each commit. In WAL mode, at least this doesn't cause corruption, but it can still lose data.
https://www.sqlite.org/pragma.html#pragma_synchronous is very explicit that the thing called NORMAL does have these risks. But it's still called NORMAL, and I'd think that's something of a foot-slingshot for newcomers, if not a full-fledged footgun.
In the sibling comment we are discussing how the default of sqlite is not durable, so it's only ACI, not ACID.
https://news.ycombinator.com/item?id=45005866
So you do get the foot guns automatically.
> rather than commenting on HN
I appreciate the parent commenting on HN, because they seem to be ... right.
The default setting for secure_delete is determined by the SQLITE_SECURE_DELETE compile-time option and is normally off. The off setting for secure_delete improves performance by reducing the number of CPU cycles and the amount of disk I/O.
Similarly, the fact that FKs aren’t actually enforced by default is a horrifying discovery if you’re new to it, but tbf a. MySQL until quite recently allowed CHECK constraints to be declared, which it then ignored b. The FK enforcement decision is for backwards-compatibility.
I do know the transaction is handled "differently".
Its an extra syscall. It depends on the use-case if its needed or not
- SurrealDB provides poor documentation about this default
- SQLite is typically run client side, while SurrealDB is typically run as a remote server
- SQLite is actually full sync by default, but distros may package it with other defaults - SurrealDB explicitly did this for benchmarking reasons (for comparison fairness) while SQLite distros turn off fsync for typically practical reasons as it's run pure client side.
Don't assume sqlite is doing an fsync on each commit.
While that's the "default default", you may be using a sqlite3 compiled by someone else or using non-default build options, or using a wrapper that sets its own defaults.
More generally, be careful about assuming any of the various levers sqlite3 has are set in a specific way. That is, take control of the ones that are important to your use case (well, and before that, review the levers so you know which ones those are).
conradev•9h ago
https://www.sqlite.org/pragma.html#pragma_checkpoint_fullfsy...
baruz•9h ago
mananaysiempre•8h ago
londons_explore•8h ago
I am happy to lose 5 or 10 seconds of data in a power failure. However I'm not okay with a file becoming so corrupted that it is unmountable when the power recovers.
Half arsed fsync provides exactly that - and considering you get way more performance this seems like a good tradeoff.
Avamander•8h ago
londons_explore•8h ago
I wish Linux and windows would have settings to change all fsyncs to barriers too.
Unfortunately I think Linux recently removed such an ability on the basis the code complexity wasn't worth it.
Avamander•8h ago
conradev•8h ago
ncruces•4h ago
https://bonsaidb.io/blog/acid-on-apple/