5 ms·

The SYNCHRONOUS pragma is great but I'll mention that there is a durability trade-off. In "NORMAL" mode, there is not an fsync() after every transaction so you

The SYNCHRONOUS pragma is great but I'll mention that there is a durability trade-off. In "NORMAL" mode, there is not an fsync() after every transaction so you could lose recent transactions if you unexpectedly shutdown. The WAL is append-only so you don't risk data corruption (which is great).

bob1029 4y ago
We took the tradeoff in our product due to the strong performance upside. Our reasoning goes something like - whatever happened approximately at the edge of the power-loss abyss is considered part of the explosion. We have never held out hope that we'd be able to get information to disk up until the last microsecond. Letting WAL roll us back a few transactions is not a huge deal for our customers. Even if this breaks logical consistency with regard to external systems. We have many degrees of "redo" and the back office is always going to be a thing in our line of business. We file this under the "edge cases not economically worth targeting" bucket and take our 2 ~free orders of magnitude.
- vlovich123 4y ago
  Are you sure that those failures modes are the only ones guaranteed? I'd be worried about durability failures that happen due to non-obvious interactions (e.g. committing transaction A, losing transaction B, committing transaction C) or the database getting left in a state where it can't even start.
  - bob1029 4y ago
    WAL enforces serialization semantics at crash recovery time. The only real variable is how many WAL frames made it to disk before the rug pull.
TillE 4y ago
> unexpectedly shutdown Right, a power cut or kernel panic. It's certainly something to be aware of, but there are probably much more likely ways to lose some data (programming errors, etc).
- tptacek 4y ago
  Or a point-in-time snapshot of the block device being made.
  - bob1029 4y ago
    This is actually how we recommend our customers perform backups of our product. Crash-consistent snapshots of the entire VM's disk every so often. 100% of our installs are in virtualized/cloud environments, so this is a very convenient and natural way to go about things. Some loss around the edges (and between snapshots) is understood and accepted by all. We've made it clear to our customers that by making this trade-off, we can vastly simplify operational costs around the system (i.e. we only need 1 self-contained VM per environment).
    - tptacek 4y ago
      Oh, for sure. I'm just saying that in cloud environments the "power cut off" scenario is more common than it looks. :)
aidenn0 4y ago
The WAL is append-only so you don't risk data corruption (which is great). I'm not sure this is true with all filesystems. I think there are some filesystems in which a crash during append can end up with the file enlarged, but the data not written (IIRC I saw something like this with XFS when I was working on a kernel module that kept crashing the kernel).
- benbjohnson 4y ago
  > I think there are some filesystems in which a crash during append can end up with the file enlarged The SQLite WAL file itself has a running checksum that starts from the beginning so if you had an enlarged file or even a block of zero'd out bytes in the middle, SQLite would still recover gracefully. It recovers up to the last valid WAL frame that contains a set "commit" flag.
  - aidenn0 4y ago
    I'm honestly not surprised that SQLite handles this situation (I seriously doubt there's any file-system oddity I've run into that Hipp hasn't). But just being "append only" is insufficient.

bob1029 4y ago

We took the tradeoff in our product due to the strong performance upside. Our reasoning goes something like - whatever happened approximately at the edge of the power-loss abyss is considered part of the explosion. We have never held out hope that we'd be able to get information to disk up until the last microsecond. Letting WAL roll us back a few transactions is not a huge deal for our customers. Even if this breaks logical consistency with regard to external systems. We have many degrees of "redo" and the back office is always going to be a thing in our line of business. We file this under the "edge cases not economically worth targeting" bucket and take our 2 ~free orders of magnitude.

vlovich123 4y ago
Are you sure that those failures modes are the only ones guaranteed? I'd be worried about durability failures that happen due to non-obvious interactions (e.g. committing transaction A, losing transaction B, committing transaction C) or the database getting left in a state where it can't even start.
- bob1029 4y ago
  WAL enforces serialization semantics at crash recovery time. The only real variable is how many WAL frames made it to disk before the rug pull.

TillE 4y ago

> unexpectedly shutdown Right, a power cut or kernel panic. It's certainly something to be aware of, but there are probably much more likely ways to lose some data (programming errors, etc).

tptacek 4y ago
Or a point-in-time snapshot of the block device being made.
- bob1029 4y ago
  This is actually how we recommend our customers perform backups of our product. Crash-consistent snapshots of the entire VM's disk every so often. 100% of our installs are in virtualized/cloud environments, so this is a very convenient and natural way to go about things. Some loss around the edges (and between snapshots) is understood and accepted by all. We've made it clear to our customers that by making this trade-off, we can vastly simplify operational costs around the system (i.e. we only need 1 self-contained VM per environment).
  - tptacek 4y ago
    Oh, for sure. I'm just saying that in cloud environments the "power cut off" scenario is more common than it looks. :)

aidenn0 4y ago

The WAL is append-only so you don't risk data corruption (which is great). I'm not sure this is true with all filesystems. I think there are some filesystems in which a crash during append can end up with the file enlarged, but the data not written (IIRC I saw something like this with XFS when I was working on a kernel module that kept crashing the kernel).

benbjohnson 4y ago
> I think there are some filesystems in which a crash during append can end up with the file enlarged The SQLite WAL file itself has a running checksum that starts from the beginning so if you had an enlarged file or even a block of zero'd out bytes in the middle, SQLite would still recover gracefully. It recovers up to the last valid WAL frame that contains a set "commit" flag.
- aidenn0 4y ago
  I'm honestly not surprised that SQLite handles this situation (I seriously doubt there's any file-system oddity I've run into that Hipp hasn't). But just being "append only" is insufficient.