6 ms·

Exactly. To put it another way: Imagine the power goes out while sqlite is in the middle of writing a transaction to the WAL (before the write has been confirm

Exactly. To put it another way: Imagine the power goes out while sqlite is in the middle of writing a transaction to the WAL (before the write has been confirmed to the application). What do you want to happen when power comes back, and you reload the database? If the transaction was fully written, then you'd probably like to keep it. But if it was not complete, you want to roll it back. How does sqlite know if the transaction was complete? It needs to see two things: 1. The transaction ends with a commit frame, indicating the application did in fact perform a `COMMIT TRANSACTION`. 2. All the checksums are correct, indicating the data was fully synced to disk when it was committed. If the checksums are wrong, the assumption is that the transaction wasn't fully written out. Therefore, it should be rolled back. That's exactly what sqlite does. This is not "data loss", because the transaction was not ever fully committed. The power failure happened before the commit was confirmed to the application, so there's no way anyone should have expected that the transaction is durable. The checksum is NOT intended to detect when the data was corrupted by some other means, like damage to the disk or a buggy app overwriting bytes. Myriad other mechanisms should be protecting against those already, and sqlite is assuming those other mechanisms are working, because if not, there's very little sqlite can do about it.

malone 1y ago
Why is the commit frame not sufficient to determine whether the transaction was fully written or not? Is there a scenario where the commit frame is fsynced to disk but the proceeding data isn't?
- adambb 1y ago
  The disk controller may decide to write out blocks in a different order than the logical layout in the log file itself, and be interrupted before completing this work.
  - hinkley 1y ago
    We still have the elevator algorithm on NVMe?
    - lxgr 1y ago
      SQLite runs on anything from servers to Internet-connected lightbulbs.
      - jrockway 1y ago
        Which lightbulbs include SQLite? I kind of want one.
        natebc 1y ago
        these guys have a Cree logo on their homepage so maybe Cree? https://imaginovation.net/case-study/cree/ https://imaginovation.net/case-study/cree/ At least what I could turn up with a quick web search.
    - bob1029 1y ago
      Anything that uses NAND storage technology is going to be optimized in some way like this. NVMe is just the messenger.
    - jrockway 1y ago
      NVMe drives do their own manipulation of the datastream. Wear leveling, GC, trying to avoid rewriting an entire block for your 1 bit change, etc. NVMe drives have CPUs and RAM for this purpose; they are full computers with a little bit of flash memory attached. And no, of course they're not open source even though they have full access to your system.
      - djfivyvusn 1y ago
        Skynet gotta start somewhere.
  - johncolanduoni 1y ago
    It’s worth noting this is also dependent on filesystem behavior; most that do copy-on-write will not suffer from this issue regardless of drive behavior, even if they don’t do their own checksumming.
  - grumbelbart2 1y ago
    Just wondering how SQLite would ever work if it had zero control over this. Surely there must be some "flush" operation that guarantees that everthing so far is written to disk? Otherwise, any "old" block that contains data might have not been written. SQLite says: > Local devices also have a characteristic which is critical for enabling database management software to be designed to ensure ACID behavior: When all process writes to the device have completed, (when POSIX fsync() or Windows FlushFileBuffers() calls return), the filesystem then either has stored the "written" data or will do so before storing any subsequently written data.
    - mschuster91 1y ago
      A "flush" command does indeed exist... but disk and controller vendors are like patients in Dr. House [1] - everybody lies. Especially if there are benchmarks to be "optimized". Other people here have written up that better than I ever could [2]. [1] https://house.fandom.com/wiki/Everybody_lies https://house.fandom.com/wiki/Everybody_lies [2] https://news.ycombinator.com/item?id=30371403 https://news.ycombinator.com/item?id=30371403
hinkley 1y ago
For instance, running on ZFS or one of its peers.
- jandrewrogers 1y ago
  Apropos this use case, ZFS is usually not recommended for databases. Competent database storage engines have their own strong corruption detection mechanisms regardless. What filesystems in the wild typically provide for this is weaker than what is advisable for a database, so databases should bring their own implementation.
  - TheDong 1y ago
    > ZFS is usually not recommended for databases Say more? I've heard people say that ZFS is somewhat slower than, say, ext4, but I've personally had zero issues running postgres on zfs, nor have I heard any well-reasoned reasons not to. > What filesystems in the wild typically provide for this is weaker than what is advisable for a database, so databases should bring their own implementation. Sorry, what? Just yesterday matrix.org had a post about how they (using ext4 + postgres) had disk corruption which led to postgres returning garbage data: https://matrix.org/blog/2025/07/postgres-corruption-postmortem/ https://matrix.org/blog/2025/07/postgres-corruption-postmort... The corruption was likely present for months or years, and postgres didn't notice. ZFS, on the other hand, would have noticed during a weekly scrub and complained loudly, letting you know a disk had an error, letting you attempt to repair it if you used RAID, etc. It's stuff like in that post that are exactly why I run postgres on ZFS. If you've got specifics about what you mean by "databases should bring their own implementation", I'd be happy to hear it, but I'm having trouble thinking of any sorta technically sound reason for "databases actually prefer it if filesystems can silently corrupt data lol" being true.
    - zaarn 1y ago
      SQLite on ZFS needs the Fsync behaviour to be off, otherwise SQLite will randomly hang the application as the fsync will wait for the txg to commit. This can take a minute or two, in my experience. Btrfs is a better choice for SQLite.
sqweek 1y ago
> This is not "data loss", because the transaction was not ever fully committed. The power failure happened before the commit was confirmed to the application, so there's no way anyone should have expected that the transaction is durable. In the scenario outlined in the article, technically the lost transactions _were_ fully committed from the application's perspective. From sqlite's perspective, the updates were successfully fsync'd to the WAL and are now waiting for the next CHECKPOINT operation to be written back to the main database. But sqlite doesn't wait for the main DB to be updated to report success to the application, and nor should it -- the entire point of the WAL is that once data is fsynced to the journal sqlite is confident about its durability. The type of corruption induced in the article highlights this assumption: if the data on disk changes after fsync reports success, it can invalidate updates further ahead in the WAL that the application was previously told had been successfully committed (and thus may have triggered other external actions). To be clear, sqlite is doing the right thing. If any frame in the WAL does not match its checksum then the validity of all subsequent frames is called into question. And if fsync() is lying to this extent there's nothing sqlite can do -- that's a bug in the underlying storage layer. However I think it does leave us with a legitimate race: 1. application submits transactions 1..N 2. sqlite starts committing transactions 1..N to WAL 3. fsync reports success and data is valid on disk 4. sqlite reports success to application 5. application fires off actions in response to transaction N being committed 6. some external event (hardware?) causes a bit-flip in the WAL on disk affecting transaction 2 7. application closes/crashes without sqlite having an opportunity to CHECKPOINT 8. application restarts 9. sqlite recovers WAL and discards transactions 2..N There's a lot of caveats here. I think that both the bit-flip and the application crash are required, because while the application is running the WAL contents are likely duplicated in RAM (possibly in OS-buffers) and a bit-flip on disk alone may never be observed. The bit-flip also needs to be a silent error from the perspective of the storage layer to not result in an error/warning message from sqlite (eg. reporting I/O errors via the usual sqlite_config(SQLITE_LOG, ...) mechanism). Finally if you're considering this kind of data corruption, there's no need to involve the WAL at all. The same kind of silent bit-flip could equally affect the main database, randomly changing the contents of an arbitrary cell in the database. Sqlite _might_ detect that and report corruption (if it results in violating an index ordering or some other constraint), or it might just pass the data on. But as you summarised: > Myriad other mechanisms should be protecting against [damage to the disk or a buggy app overwriting bytes] already, and sqlite is assuming those other mechanisms are working, because if not, there's very little sqlite can do about it. tldr; the type of corruption simulated in the article is quite contrived, and sqlite does not protect against cosmic rays/subtle changes on disk to its database files

malone 1y ago

Why is the commit frame not sufficient to determine whether the transaction was fully written or not? Is there a scenario where the commit frame is fsynced to disk but the proceeding data isn't?

adambb 1y ago
The disk controller may decide to write out blocks in a different order than the logical layout in the log file itself, and be interrupted before completing this work.
- hinkley 1y ago
  We still have the elevator algorithm on NVMe?
  - lxgr 1y ago
    SQLite runs on anything from servers to Internet-connected lightbulbs.
    - jrockway 1y ago
      Which lightbulbs include SQLite? I kind of want one.
      - natebc 1y ago
        these guys have a Cree logo on their homepage so maybe Cree? https://imaginovation.net/case-study/cree/ https://imaginovation.net/case-study/cree/ At least what I could turn up with a quick web search.
  - bob1029 1y ago
    Anything that uses NAND storage technology is going to be optimized in some way like this. NVMe is just the messenger.
  - jrockway 1y ago
    NVMe drives do their own manipulation of the datastream. Wear leveling, GC, trying to avoid rewriting an entire block for your 1 bit change, etc. NVMe drives have CPUs and RAM for this purpose; they are full computers with a little bit of flash memory attached. And no, of course they're not open source even though they have full access to your system.
    - djfivyvusn 1y ago
      Skynet gotta start somewhere.
- johncolanduoni 1y ago
  It’s worth noting this is also dependent on filesystem behavior; most that do copy-on-write will not suffer from this issue regardless of drive behavior, even if they don’t do their own checksumming.
- grumbelbart2 1y ago
  Just wondering how SQLite would ever work if it had zero control over this. Surely there must be some "flush" operation that guarantees that everthing so far is written to disk? Otherwise, any "old" block that contains data might have not been written. SQLite says: > Local devices also have a characteristic which is critical for enabling database management software to be designed to ensure ACID behavior: When all process writes to the device have completed, (when POSIX fsync() or Windows FlushFileBuffers() calls return), the filesystem then either has stored the "written" data or will do so before storing any subsequently written data.
  - mschuster91 1y ago
    A "flush" command does indeed exist... but disk and controller vendors are like patients in Dr. House [1] - everybody lies. Especially if there are benchmarks to be "optimized". Other people here have written up that better than I ever could [2]. [1] https://house.fandom.com/wiki/Everybody_lies https://house.fandom.com/wiki/Everybody_lies [2] https://news.ycombinator.com/item?id=30371403 https://news.ycombinator.com/item?id=30371403

hinkley 1y ago

For instance, running on ZFS or one of its peers.

jandrewrogers 1y ago
Apropos this use case, ZFS is usually not recommended for databases. Competent database storage engines have their own strong corruption detection mechanisms regardless. What filesystems in the wild typically provide for this is weaker than what is advisable for a database, so databases should bring their own implementation.
- TheDong 1y ago
  > ZFS is usually not recommended for databases Say more? I've heard people say that ZFS is somewhat slower than, say, ext4, but I've personally had zero issues running postgres on zfs, nor have I heard any well-reasoned reasons not to. > What filesystems in the wild typically provide for this is weaker than what is advisable for a database, so databases should bring their own implementation. Sorry, what? Just yesterday matrix.org had a post about how they (using ext4 + postgres) had disk corruption which led to postgres returning garbage data: https://matrix.org/blog/2025/07/postgres-corruption-postmortem/ https://matrix.org/blog/2025/07/postgres-corruption-postmort... The corruption was likely present for months or years, and postgres didn't notice. ZFS, on the other hand, would have noticed during a weekly scrub and complained loudly, letting you know a disk had an error, letting you attempt to repair it if you used RAID, etc. It's stuff like in that post that are exactly why I run postgres on ZFS. If you've got specifics about what you mean by "databases should bring their own implementation", I'd be happy to hear it, but I'm having trouble thinking of any sorta technically sound reason for "databases actually prefer it if filesystems can silently corrupt data lol" being true.
  - zaarn 1y ago
    SQLite on ZFS needs the Fsync behaviour to be off, otherwise SQLite will randomly hang the application as the fsync will wait for the txg to commit. This can take a minute or two, in my experience. Btrfs is a better choice for SQLite.

sqweek 1y ago

> This is not "data loss", because the transaction was not ever fully committed. The power failure happened before the commit was confirmed to the application, so there's no way anyone should have expected that the transaction is durable. In the scenario outlined in the article, technically the lost transactions _were_ fully committed from the application's perspective. From sqlite's perspective, the updates were successfully fsync'd to the WAL and are now waiting for the next CHECKPOINT operation to be written back to the main database. But sqlite doesn't wait for the main DB to be updated to report success to the application, and nor should it -- the entire point of the WAL is that once data is fsynced to the journal sqlite is confident about its durability. The type of corruption induced in the article highlights this assumption: if the data on disk changes after fsync reports success, it can invalidate updates further ahead in the WAL that the application was previously told had been successfully committed (and thus may have triggered other external actions). To be clear, sqlite is doing the right thing. If any frame in the WAL does not match its checksum then the validity of all subsequent frames is called into question. And if fsync() is lying to this extent there's nothing sqlite can do -- that's a bug in the underlying storage layer. However I think it does leave us with a legitimate race: 1. application submits transactions 1..N 2. sqlite starts committing transactions 1..N to WAL 3. fsync reports success and data is valid on disk 4. sqlite reports success to application 5. application fires off actions in response to transaction N being committed 6. some external event (hardware?) causes a bit-flip in the WAL on disk affecting transaction 2 7. application closes/crashes without sqlite having an opportunity to CHECKPOINT 8. application restarts 9. sqlite recovers WAL and discards transactions 2..N There's a lot of caveats here. I think that both the bit-flip and the application crash are required, because while the application is running the WAL contents are likely duplicated in RAM (possibly in OS-buffers) and a bit-flip on disk alone may never be observed. The bit-flip also needs to be a silent error from the perspective of the storage layer to not result in an error/warning message from sqlite (eg. reporting I/O errors via the usual sqlite_config(SQLITE_LOG, ...) mechanism). Finally if you're considering this kind of data corruption, there's no need to involve the WAL at all. The same kind of silent bit-flip could equally affect the main database, randomly changing the contents of an arbitrary cell in the database. Sqlite _might_ detect that and report corruption (if it results in violating an index ordering or some other constraint), or it might just pass the data on. But as you summarised: > Myriad other mechanisms should be protecting against [damage to the disk or a buggy app overwriting bytes] already, and sqlite is assuming those other mechanisms are working, because if not, there's very little sqlite can do about it. tldr; the type of corruption simulated in the article is quite contrived, and sqlite does not protect against cosmic rays/subtle changes on disk to its database files