3 ms·
I mean if you read the full quote: > When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often th
by pantsforbirds 4y ago
I mean if you read the full quote:
> When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.
It seems extremely reasonable to use WAL with synchronous=NORMAL
- gpderetta 4y agoYou might roll back committed transactions in case of a system crash. It might be acceptable for some applications, but I wouldn't certainly call it extremely reasonable. If you do not care about system crashes, why sync at all?
- wongarsu 4y agoI've written plenty systems that would be fine with a valid, consistent but maybe slightly older state (for example when the db describes both work to be done and work results, or the db otherwise implicitly records where to resume work). On the other hand there are obvious examples where this is unacceptable, say recording the transactions of a cash register.
- gpderetta 4y agoI have done as well! My concern is the assertion that this behavior is reasonable for the vast majority of applications.
- JohnBooty 4y agoIf you do not care about system crashes, why sync at all? Well, you have to sync sometime if you care about persist your data at all, right? And there are scads of use cases where you want to: - persist data - enforce some sort of schema (even though SQLite is lax here) - query/join it in SQL-y ways - are willing to trade ACID-compliant journaled durability in exchange for performance Cache stores are maybe the most obvious use case. This is roughly how Redis is configured out of the box after all (sans the SQL) IIRC. Not ideal if your cache is trashed but also, not a huge deal. There are also a lot of logging/timeseries type applications where it's just not a big deal if you lose, say, a few minutes of data per year.
- gpderetta 4y agoIf you only need best effort and preserve data only in the case of an application crash, you do not need sync(2) at all, just call write.