9 ms·
SQLite (with WAL) doesn't do `fsync` on each commit under default settings
- felishiagreen12 1y ago[dead]
- conradev 1y agoOne Apple platforms, you also want to use F_FULLFSYNC instead of fsync if you need durability: https://www.sqlite.org/pragma.html#pragma_checkpoint_fullfsync https://www.sqlite.org/pragma.html#pragma_checkpoint_fullfsy...
- baruz 1y agoIs there a performance hit for f_fullfsync?
- mananaysiempre 1y agoYes, that’s why those systems can cheat on benchmarks by defaulting to half-arsed fsync() in the first place.
- londons_explore 1y agoHalf arsed fsync is all I want. 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 1y agoFsync is also overused at times, like some random web app's local storage does not need to be forced onto the disk.
- londons_explore 1y agoThat fsync behaviour I think is a good part of the reason that apps seem to run faster/better on osx than windows/Linux. 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 1y agoWould it not be possible to achieve this with something akin to how libeatmydata can be LD_PRELOAD-ed?
- conradev 1y agoYou don’t need fsync at all for that, just WAL. fsync is only half arsed on Apple platforms.
- mxey 1y agoYou need write barriers for the ordering guarantees of a WAL. that’s why Apple uses barrier sync and not full sync. AFAIK other operating systems do not have this distinction.
- ncruces 1y agoIf Apple compiled your SQLite library, not even the fullfsync PRAGMAs will do F_FULLFSYNC. Apple has “secretly” patched their SQLite to do F_BARRIERFSYNC instead. https://bonsaidb.io/blog/acid-on-apple/ https://bonsaidb.io/blog/acid-on-apple/
- weinzierl 1y agoI don't know why SQLite has this default, I suspect primarily performance, but I think even with modern SSD's there is a fsync frequency where it starts to hurt lifetime. 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.
- sethev 1y agoOne of the things I like about SQLite is how easy it is to understand its behavior if you read the docs. This is one of the potentially surprising defaults - but it seems reasonable for an embedded database, where a power loss is likely to cause the application to "forget" that something was committed at the same time that SQLite does. However, it depends on the application - hence the need for clear docs and an understandable model.
- deleted 1y ago[deleted]
- liuliu 1y agoDoes it matter? For all we know, it keeps the serializability. At this point (of computer hardware history), you would care more about serializability than making sure data written to disk after power loss, the latter would now depend on so many layers of drivers doing correct things (for things that is hard to test correctly).
- deleted 1y ago[deleted]
- wellpast 1y agoWouldn’t it depend on use case? 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.
- bawolff 1y agoIf you had a power failure between when the transaction was queued and the sqlite transaction was comitted, no amount of fsync will save you. 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?
- wellpast 1y agoOf course it will because same programmers don’t ack their customers until their (distributed, replicated) db says ack.
- wellpast 1y agosane*
- kevincox 1y agoA better example is probably 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).
- charleslmunger 1y agoThe default is FULL https://sqlite.org/compile.html#default_synchronous 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.
- avinassh 1y agohey, I just tested and `NORMAL` is default: $ 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> edit: fresh installation from homebrew shows default as FULL: /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> I will update the post, thanks!
- eatonphil 1y agoIs this sqlite built from source or a distro sqlite? It's possible the defaults differ with build settings.
- supriyo-biswas 1y agoThe one which avinassh shows is MacOS's SQLite under /usr/bin/sqlite3. In general it also has some other weird settings, like not having concat() method, last I checked.
- ncruces 1y agoThe Apple built macOS SQLite is something. 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).
- chasil 1y agoThere is enough going on with WAL mode that it really shouldn't be enabled unless all the limitations are understood. In addition to the need for all clients to see shared memory, it disables acid transactions on attached databases (2nd to last paragraph below): https://sqlite.org/lang_attach.html https://sqlite.org/lang_attach.html
- SoftTalker 1y agoThis is true for any database, for any concurrency or durability settings. You must understand the implications of the defaults and your choices if you change them.
- bawolff 1y ago> In addition to the need for all clients to see shared memory, it disables acid transactions on attached databases 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.
- boris 1y agoThe main reason you would attach a database and then jump through hoops like qualifying tables is to have transactions cover all the attached databases. If you don't need that, then you can just open separate connections to each database without needing to jump through any hoops. So the fact that WAL does not provide that is a big drawback.
- dangoodmanUT 1y agoSQLite defaults have many, many weird choices that make unsuspecting users choose dangerous configurations. 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.
- jabwd 1y agoSqlite defaults in many ways perfectly fine, you get the foot guns when you need the performance. Read the article rather than commenting on HN because WAL is not default.
- btown 1y agoThere's some nuance here. The compiler flags SQLITE_DEFAULT_SYNCHRONOUS and SQLITE_DEFAULT_WAL_SYNCHRONOUS are set to FULL by default, which does fsync on each commit. https://sqlite.org/compile.html 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 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.
- nutjob2 1y agoFULL can also lose data if you lose power or crash before the fsync. This is just a simple trade of losing slightly more data (possibly) in return for better performance. Fsync is relatively expensive. Recovery price is not going to differ much between the two settings. It's like 1 in 1000 loss for 999 in 1000 gain. Makes perfect sense to me.
- nh2 1y ago> Sqlite defaults in many ways perfectly fine 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 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.
- wiradikusuma 1y agoDoes anyone, preferably from Cloudflare, know the settings used in D1, since it's a SQLite dialect?
- koakuma-chan 1y agoIs D1 a dialect? I thought they actually run SQLite.
- wiradikusuma 1y agoI hit some issues where they worked in localhost but not in production. Fortunately, they're not dealbreakers. Unfortunately, my mind is very good at forgetting bad experiences (especially if they have a workaround), so I don't remember the issues. I do know the transaction is handled "differently".
- kentonv 1y agoD1 is built on Durable Objects, where SQLite is used on top of a custom distributed storage layer. This blog post describes in detail how the system works: https://blog.cloudflare.com/sqlite-in-durable-objects/ https://blog.cloudflare.com/sqlite-in-durable-objects/ In short, while we don't technically fsync, we do much better. If the local disk suddenly explodes, we won't lose confirmed writes. (I wrote much of said storage layer.)
- hoppp 1y agoCalling fsync on each commit makes it slower, doesn't it? Its an extra syscall. It depends on the use-case if its needed or not
- senderista 1y agoYou can enqueue fsync as an op in io_uring, using link flag to sequence it after previous ops and drain flag to ensure no new ops are started until it completes.
- dgshsg 1y agoYou still need to wait for it to complete to report the commit success to the user, the cost of syscall itself is rather minor here.
- senderista 1y agoAvoiding the syscall cost is about maximizing throughput, not minimizing latency. The media's time-to-durability is what it is.
- praptak 1y agoThat's why big DBs do group commits, trading latency for throughput.
- mxey 1y agoIt’s slow because it forces a write to disk. I don’t think the syscall itself matters much.
- sgammon 1y agoyeah it says that in the docs
- arpinum 1y agoThis is popping up because SurrealDB was found to turn fsync off by default. But there are important differences: - 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.
- jmull 1y agoThe takeaway should be: 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).
- nodesocket 1y agoRelated, I use Lightstream and their documentation[1] actually suggested to use synchronous=NORMAL. Any idea if this is a wise change? Should I revert back to the default of FULL using WAL + Lightstream? [1] https://litestream.io/tips/#synchronous-pragma https://litestream.io/tips/#synchronous-pragma
- Skinney 1y agoLitestream is already backing up your changes, so synchronous=normal seems reasonable.
- geysersam 1y agoIf you're using lightstream you still might loose transactions since the last checkpoint, if the server goes down, so I don't think it makes sense to make sure every single transaction is persisted to disk before success is returned. If my understanding is correct it will just slow the app down without reducing your chances of data loss.