4 ms·
I do not agree that WAL mode should be enabled by default. It can break things if used incorrectly. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://
by chasil 2y ago
I do not agree that WAL mode should be enabled by default. It can break things if used incorrectly.
https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.”
“It is not possible to change the page size after entering WAL mode.”
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”
https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html
“SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”
- masklinn 2y agoThese all sounds like load-specific / ancillary concerns. I don't think most people are in one of those cases, and most people will greatly benefit from WAL. WAL should absolutely be the default unless you have a good reason to not use it.
- chasil 2y agoYou may prefer non-acid characteristics in your databases in general. I do not. There may be specific uses, but standards should be followed when possible.
- giraffe_lady 2y ago"When possible" in this case being simply don't enable the WAL if your use requires transactions across multiple attached sqlite DBs.
- masklinn 2y ago> You may prefer non-acid characteristics in your databases in general. No. Quite the opposite. However the multi-database case strikes me as a very limited edge case, I would be surprised if double digit % users of sqlite even knew it’s an option let alone used it, and the concern is further lessened because afaik things like FKs do not work cross-db.
- pdimitar 2y agoACID guarantees work just fine. They don't work perfectly in scenarios where SQLite should not be used anyway: multiple machines accessing the same DB file over network, and having multiple attached databases. None of these use cases are common.
- deleted 2y ago[deleted]
- striking 2y agoWAL isn't the footgun, SQLite over network attached storage is. Additional files are not a problem. If you need cross-DB file ACID you can just turn off WAL mode in that instance.
- bruce511 2y ago>> WAL isn't the footgun, SQLite over network attached storage is I agree that using SQLite over a network is not a good idea. But people do do that, and it works tolerably well. On most networks it'll work just fine, especially if writes are sparse. The point of defaults is that they should cover the "widest range". By all means read the docs, and choose to use Wal. But that's an action you take and understand. The point of articles like this though is to help alert people to the settings that exist. You should always read the docs and apply them to your context before using any setting.
- jklowden 2y agoIt’s unreliable on any network for good reason: SQLite relies on the kernel for state information on the file system. Two machines sharing one file over a network have two unsynchronized versions of that state. One machine’s write does not update the other’s in-kernel image.
- deleted 2y ago[deleted]