My Go driver adds support for this by default, and I provide a simple API to enable it.
I'm of the opinion that the VFS shim should ship by default, certainly on the CLI, because it still needs to be enabled for each DB, and all it takes is one instance that doesn't know about checksums to modify a checksummed DB for it to appear corrupt.
Weirdly, databases created with the macOS version of the sqlite3 CLI reserve 12 bytes, for some reason, so you can't ever enable checksums on those DBs.
Also, I'm not 100% certain what the interaction is between the checksums of the VFS shim and the WAL checksums during checkpointing w.r.t. silent durability loss.
The shim's checksums are explicitly ignored during checkpointing so I'd need to think it through.
The VFS is focused on not (silently) accepting corrupted data, not necessarily on not (silently) ignoring it.
Honestly seems pretty reasonable to me. SQLite operates above the filesystem, and it seems to me the filesystem is the appropriate place to do this kind of error correction/detection. If you need this kind of durability of your data, why are you storing anything on a filesystem that doesn't do this?
Yeah. I agree. That being said we do line in a world where non-checksummed filesystems and non-ECC RAM are common especially on consumer devices. That being said disk or file-level checksums are an incomplete solution without ECC RAM anyways.
I'm going to present a potentially controversial opinion: you should always consider adding checksums.
Checksums are great, easy and almost assuredly cheap. CRC32C has hardware acceleration support on any modern CPU. Writing a masked CRC32C is incredibly cheap and you'd be surprised at all the places you can catch corruption.
The sooner you write a checksum, the more value it adds. If you calculate a checksum later on, such as when you're rotating files on disk, or archiving to cold storage, it may be too late. What do those checksums represent? The checksums may be accurate, such as those used by your filesystem, but the data is already corrupted before they were calculated.
No matter how fast CRC32 is, you still have to transfer the data from disk to cpu. I suspect reading the entire db would introduce unacceptable latency & i/o strain in many usecases for sqlite.
I use sqlite for some "logging-like" thing a lot, the file is in the ~1.5GB range and growing, and every minute some data is logged to it. Having to read 1.5GB of data from the disk every minute, to add a (few times) timestamp and one 64 bit number to that data seems pointless.
If you already know the checksum for some huge chunk of the message then you don't need to recompute it to append some data and get a new checksum (at least for CRC). On the read side you would want to have checksums at whatever granularity you want to be able to read but for a larger combined CRC checksum you don't need to ever reread data to append or prepend to it.
All of it. With a checksums-of-checksums scheme like a Merkele tree, you can effectively and efficiently checksum all the data and keep incremental changes cheap. You only need to update the checksums of the data blocks you touched and their ancestor nodes in the tree.
Pages?. Postgres does that apparently if enabled. And when a page is read, it is already read… The server just checks the checksum. Only overhead should be recalculating the checksum and validating it.
Now, in case a bit flips somewhere you do not touch often, it would probably keep chugging along without noticing that. Which kind of makes sense?
CRC32C is not that cheap, even with hardware acceleration. Postgres uses CRC32C for the WAL an it definitely shows up in profiles, despite HW acceleration. And that's at a far lower throughput than you can have with e.g. sequential scans. Which is why postgres chose a different checksum (FNV) for data checksums...
And if you care about data integrity, you shouldn't be using these. It's zfs, btrfs if you don't want to deal with the licensing issue, or bcachefs (though it is pretty new at this point)
> And if you care about data integrity, you shouldn't be using these [ext4 or XFS]
FUD. i've been using ext4 since it came out (and ext2 and ext3 before that), on dozens of computers, and have literally never once had any corruption with it. With XFS i had one instance of filesystem corruption way back in the early 2000's. i've read of _many_ more cases of data loss with ZFS and Btrfs on Linux than i have read about due to ext4 corruption.
If you truly believe this, there’s no reason you would want to add checksumming to SQLite either. Either the data you write can be corrupted or it can’t.
That's poor advice. Use a database with checksums enabled like DB2, Oracle, Postgres, SQL Server or SQLite with the Checksum VFS Shim. TFA recommends the latter for good reason.
Running a database on ZFS results in reduced throughput, while Btrfs and Bcachefs are outright performance disasters. [1][2]
I think it's a question of trust and responsibility, and the client should decide where to draw those lines. Depending on the environment and context, it could be totally acceptable to trust the filesystem, but not in other cases.
Yes. It is a "storm in a glass of water", as we say. Probably the client application doesn't assume cosmic bit-flipping events for its own files, which would have equally bad consequences, either. Probably the likeliness for this happening and not being caught and corrected by the storage medium is as low as a hash or UUID collision, which everyone conveniently round down as "impossible".
There is: It’s already done at the layer below the file system.
In the best case, repeating it at the FS layer wastes performance; in the worst case, it causes confusion as to which layer has to do it and then maybe nobody does.
For the same reason, e.g. HTTP doesn’t have a checksum either, and IPv6 even got rid of its header checksum.
HTTP depends on the TCP and Ethernet layer checksums, which is not great. At today’s bitrates, it is remarkable how frequent a bitflip can occur without invalidating the TCP or Ethernet checksums.
HTTPS effectively gets integrity validation by default, as TLS takes care of that. TLS is much more effective at this than the checksums are.
> ZFS & BTRFS both have native FS checksumming, I don't think there's a strong reason not to use it, except maybe in a contained embedded context.
There are many good reasons not to use BTRFS. :)
Also ZFS is hell to use if you're not on an OS that has native support. You become reliant on e.g. kernel modules on Linux , and on Debian this means using Backports which is not something any sensible sysadmin wants to do because it introduces the hell of having a mismatch of all sorts of software unless you spend your life defining exclusions in the apt config.
Also in terms of SQLite, ZFS/BTRFS have no understanding of what's running on them. Personally, when it comes to databases I'd rather the database does its integrity checks. If the OS does some too that's great, but the database blindly assuming the OS will do integrity checking is not a good look in my book.
That article is from 9 years ago. Is there any more recent review which comes to similar conclusions?
But also, if you’re running sqlite, your main goal is probably not to be a database server but to do some other primary objective that happens to benefit from using a database for some things.
> on Debian this means using Backports which is not something any sensible sysadmin wants to do because it introduces the hell of having a mismatch of all sorts of software unless you spend your life defining exclusions in the apt config.
Pretty sure this is well documented in many places, I definitely didn’t invent this wheel myself. Been running zfs on Debian this way for the last 3 releases. Had one instance of drama when the kernel was updated before the zfs package was updated, making me need to downgrade the kernel, which was a faff for sure.
The benefits of using ZFS in my environment greatly outweigh the downsides of using backports. I vastly prefer Debian to alternatives, so here we are (for me).
In the context of databases there are a few issues with relying on the filesystem for checksums:
- The default checksum algorithms used in many filesystems are too weak (e.g. CRC32), you really want 128-bits for modern databases.
- While some filesystems give you a selection of checksum algorithms ranging from weak to very strong (e.g. SHA256), the databases like SQLite are often deployed in an environment where you have no control over the algorithm selected.
- The checksum algorithms available in most filesystems do not provide adequate performance on modern storage hardware. It is much easier to evolve these in database engines than in the filesystem.
- Filesystem suitability for database workloads varies widely. Few people are going to trade the performance and scalability of XFS for the built-in integrity mechanisms of ZFS. Implementing checksums in the database gets you the best of both worlds. Also databases are not always deployed on filesystems, so you need checksums for these cases.
From a database engineering perspective, the checksumming in filesystems to the extent it exists is often not fit for purpose. Many databases implement their own integrity mechanisms for this reason.
ClickHouse uses and validates checksums for both compressed blocks of data and uncompressed data; it also checksums and validates data during network transfers. Additionally, every replica validates checksums with other replicas when doing deterministic computations.
It is the only reasonable way to work with data, and if someone thinks otherwise, I will happily provide motivating examples.
Fully agree. There are many bad things that can happen to data. Another good feature is that ClickHouse is fairly tolerant of torn blocks and handles many failures automatically. This fault tolerance actually makes some corner cases hard to illustrate in demos, because ClickHouse repairs them silently. Users never see them.
The checksum in the WAL is to detect incomplete writes (due to crash or power failure). That data was never committed successfully. Throwing an error for this scenario would mean that sqlite is unable to recover from a crash.
It was committed successfully, if there's a valid commit frame in the WAL. It simply wasn't checkpointed at this point.
If the WAL has valid data frames and a valid commit frame after a corrupted frame, there's a strong likelihood this was caused by accidental corruption, so an argument could be made that silently deleting such a WAL is not necessarily the best idea.
A lesson to be learned here, is that the durability of stuff in the WAL is reduced, even if you use PRAGMA synchronous=FULL.
You're right that write reordering might cause this (an invalid frame followed by a few valid frames).
That shouldn't happen if you use PRAGMA synchronous=FULL, but in WAL mode it's very common to use NORMAL.
Not sure what's the better strategy here, but I'd definitely appreciate a mode that warns me before silently truncating a WAL that has valid frames in it.
That's true. The protocol aware recovery paper [1] talked about the challenges of disentangling corruption in the middle of the log vs uncommitted data at the end of the log. This is an issue in other log-based systems as well.
The OP made it sound like an oversight rather than an implication of assuming that the filesystem won't return corrupt data that was successfully written and fsync'ed. Sqlite is pretty upfront about the tradeoffs: https://www.sqlite.org/howtocorrupt.html#_failure_to_sync
I don't think any banking apps are using SQLite as their main database storage in any capacity, so the example in the article seems a tad extreme. The worst use case for SQLite that I've seen in production was a shop using it with some minimal UI on top as an order tracking system. Even in that case, the orders were accepted on paper, they just used the SQLite db as a way to track material usage so they knew what to order from the suppliers. I wonder if anyone has a more egregious or worse example of SQLite in production.
The behaviour of SQLite in not raising an error when detecting a corrupt WAL frame also seems to be on purpose.
SQLite is a (maybe unexpectedly) powerful database. I would not be surprised at all if it’s used for financial data in at least some capacity.
I’m also not sure every “proper” database has application layer checksums, as that seems like a lower layer concern costing performance and possibly providing a false sense of security (it can’t help with durability at all, for example).
> The worst use case for SQLite that I've seen in production was a shop using it with some minimal UI on top as an order tracking system. Even in that case, the orders were accepted on paper, they just used the SQLite db as a way to track material usage so they knew what to order from the suppliers.
I feel it's quite unfair for the title to call out sqlite on checksums, when in reality, as the very closing line of the article states most databases don't do checksum verifications.
The submission doesn't seem unfair. It's simply pointing out something for users of a specific database to be aware of, and I don't really think SQLite is maligned or impugned in any way.
And FWIW, Oracle, SQL Server and DB2 enable page checksum storing/verification by default.
People are just bored of security sensationalism I guess. Too many people want to gain visibility just by reporting either something little-known (but still known) or that needs stars to align in a proper way to be exploitable at all.
A long time ago there was a famous / infamous ad campaign for some food product like bread or milk, and the ad merely stated the true fact that their milk didn't contain any bleach. Of course no one's milk (or whatever it was) had any bleach (or whatever it was).
Specially highlighting something true, but out of context and with no equally special justification, is not an innocent act and is misleading. And yes, absolutely, very clearly, causes harm, and does so unfairly.
A not-unfair version of this same article would just talk about databases, and include sqlite with others, and not only sqlite and not be titled sqlite.
And then there is this:
"Hey there I am v. I work at Turso Database."
Your milk/bread comparison is specious and invalid. I might say "As an apple enthusiast -- the fruit kind -- I want you to know that apple seeds have arsenic so don't eat lots of them" without having to disclaim every other fruit existing and possibly toxins found in parts of them. If some true-believer apple fan felt victimized, well that would be bizarre, right?
This is a SQLite guy talking about SQLite to SQLite users. They're describing a feature/possible downside of SQLite that users might want to be aware of. They don't need "balanced" coverage of every other DB because it hurts someone's bizarrely fragile feelings. And as I mentioned elsewhere, almost all "enterprise" databases do do checksums by default, if people really want to lean on this "no one does! Leave SQLite alone" argument.
And Turso is literally a SQLite-based firm. This isn't the aha you think it is.
Major corporations are not paying exorbitant licensing fees to have checksumming enabled by default. In fact, for enterprises running things like vSAN, ECC DRAM, etc, database checksumming is probably nothing more than additional overhead.
Database defaults in general are a touchy topic. Whatever set of defaults are chosen will be suboptimal for almost any serious user. A far more serious issue is figuring out the actual behavior of a database in different configurations. For instance, Oracle's SERIALIZABLE transaction isolation level only offers snapshoot isolation.
You don’t understand the critical problem that checksums solve at the I/O boundary. PCIe has weak error detection and correction. To transfer your data from ECC memory to your favorite super-robust storage technology requires transiting the PCIe bus, where for a brief moment it becomes relatively easy to corrupt data without anyone noticing. This is the problem that can’t be solved any other way and why checksums are primarily done at the I/O boundary in databases. It is an issue seen in real systems.
PCIe v6 is intended to materially improve the integrity of data transfers but what we are using today is much worse.
Yes, it's a balancing act. I think it's better to have safer defaults here, not prioritizing extra performance. People who are concerned with performance to the degree where checksums would matter need to consult configuration anyway (in many aspects, not only this one) and can disable this specific thing easily.
> I feel it's quite unfair for the title to call out sqlite on checksums, when in reality, as the very closing line of the article states most databases don't do checksum verifications.
Hi, author of the post here. I work mostly with SQLite and that's the database I'm most familiar with, hence mentioning it in the title. I have also noted at the bottom:
> Again, this is not a bug. Most databases (except a few) assume that the OS, filesystem, and disk are sound. Whether this matters depends on your application and the guarantees you need.
Every crumb of information you encounter doesn't have to be subjected to a lens of "but what if the entities involved were sports teams? Would this be "fair" to "them"?"
Application level checksums aren't for when the hard drive fails -- the OS will catch that, as every single operating system is already assuring hard drive blocks.
Application level checksums are for memory, driver, subsystem faults. And many enterprise database systems still do it by default, regardless of how redundant it might seem. Even though your NAS, file system, OS, and even the individual drives are computing it too, systems like SQL Server, Oracle, DB2 and others default to calculating and verifying checksums on all pages.
They should have an option to write every block twice including the WAL. SQLite is already so robust, why not go the extra mile
Has anyone seen a study of the pattern of bit errors experienced on disks? Like are they a single bit or multiple consecutive sectors?
At Quantcast in our QFS distributed file system we used a very efficient reed-solomon coding to get redundancy as good as 3x blocks with only 1.5x storage and still got gigabytes/sec encoding speed. We spread blocks out over different cabinets (not just machines or drives) but that would be overkill here. Obviously we still had end to end checksums because errors can happen anywhere, especially in disk and network controllers whose firmware is usually a one off hack
SQLite has a very well defined and documented set of abstractions about what can and can’t go wrong on the layers of abstraction it relies on. “Out of two writes, at least one will be successful” is not part of that, and doesn’t make sense to me.
Second-guessing the storage layer like that will never be able to catch all problems with it (e.g. drives or file systems lying about the durability of write commands).
Sorry but bit errors happen all the time at scale, and most commonly in controllers. SQLite is used in so many environments for its robustness rather than actually for SQL. Seems odd to argue agsinst it actually
Drives, controllers, and file systems lying about write durability is another great reason to add more robustness, good point
Sqlite is the wrong layer at which to care about storage hardware failures. That would be the job of the disks themselves, the hardware or software volume manager, or filesystem.
SQLite or any other mainstream database like Postgres don't do checksums. Wouldn't you be concerned if you are using it for financial data or medicine or something crucial?
A couple of my friends who work in the financial domain showed surprise at this post - they had no idea this was possible. I guess most people just assume databases 'just work'?
If I could get ECC ram for my consumer hardware, I would - but I'm not paying the xeon tax to store my photos, in addition to the necessary 20% premium for the ram sticks themselves, for my personal workloads.
I'm much less worried about storage hardware failures than other parts of the process scribbling over memory that sqlite has mapped. Being able to detect incorrect software behavior, regardless of the source, is useful in its own right.
This algorithm remains mysterious to me to this day. It allows you not only to indicate the presence of an error, but also to recover any data with a small redundancy (e.g. 5%).
> This algorithm remains mysterious to me to this day.
The basic idea of Reed-Solomon is to represent a piece of data as though it's the coefficients on a polynomial and then transmitting the value of various points on the polynomial. You have the opportunity to provide an infinite number of points, and you only need the <order of the polynomial> to be correct to derive the original polynomial. If the polynomial is overspecified, then you have the opportunity to derive the original polynomial for each set of points and see whether or not you get the same result each time. Various combinations of this outcome result in no errors, errors that are corrected, or errors that are detected but not corrected.
The rest of Reed-Solomon is about which polynomial to pick, as the selection of polynomial and where to evaluate it makes the coefficients easier to derive, and how "finite field" math works. We're used to math over all real numbers from math class; finite fields define the same operations but over a finite number of numbers (i.e. "all uint8s"). This is convenient when implementing this algorithm on a computer. (The math, of course, still works when "all real numbers" is the domain of the coefficients and points; restricting to fixed-size integers just makes things easier for computers.)
I'm not a mathematician. It works like magic for me.
Simply put. Suppose you have 64 bits (8 bytes) of regular RAM. For ECC-enabled memory, a similar block of memory will occupy 72 bits (9 bytes). The first 64 bits will be identical. What would need to be stored in the remaining 8 bits so that any of the 72 bits could be detected and recovered, assuming only one bit was corrupted?
IMO, checksums at the filesystem and higher level are only useful for maybe detecting software problems, not cosmic ray RAM bitflips. ECC RAM is the only way to reliably detect random RAM bit flips.
Without ECC, if you read a block of data from disk into a RAM page, do whatever checksums you want - filesystem page, database page, whatever - and THEN a bit flip occurs, you are now processing bad data but think it's okay because all of the checksums passed.
Every disk block has an ECC code recorded with it to detect and correct errors. Hardware busses have checksums to detect transmission errors. But without ECC RAM, there is no protection for RAM bit flips, and higher-level checksums won't help with that.
It's ridiculous that all computers don't come with ECC RAM, and it's mainly that way because Intel wants to charge a premium for data center servers.
103 comments
[ 4.6 ms ] story [ 179 ms ] threadI'm of the opinion that the VFS shim should ship by default, certainly on the CLI, because it still needs to be enabled for each DB, and all it takes is one instance that doesn't know about checksums to modify a checksummed DB for it to appear corrupt.
https://github.com/ncruces/go-sqlite3/tree/main/vfs#checksum...
Weirdly, databases created with the macOS version of the sqlite3 CLI reserve 12 bytes, for some reason, so you can't ever enable checksums on those DBs.
The VFS is focused on not (silently) accepting corrupted data, not necessarily on not (silently) ignoring it.
The checksum VFS shim does nothing to fix the (silent) data loss a random bit flip in the WAL causes.
If you want long term durability, checkpoint your WAL. Then the checksum VFS will help.
Checksums are great, easy and almost assuredly cheap. CRC32C has hardware acceleration support on any modern CPU. Writing a masked CRC32C is incredibly cheap and you'd be surprised at all the places you can catch corruption.
The sooner you write a checksum, the more value it adds. If you calculate a checksum later on, such as when you're rotating files on disk, or archiving to cold storage, it may be too late. What do those checksums represent? The checksums may be accurate, such as those used by your filesystem, but the data is already corrupted before they were calculated.
No matter how fast CRC32 is, you still have to transfer the data from disk to cpu. I suspect reading the entire db would introduce unacceptable latency & i/o strain in many usecases for sqlite.
I use sqlite for some "logging-like" thing a lot, the file is in the ~1.5GB range and growing, and every minute some data is logged to it. Having to read 1.5GB of data from the disk every minute, to add a (few times) timestamp and one 64 bit number to that data seems pointless.
Now, in case a bit flips somewhere you do not touch often, it would probably keep chugging along without noticing that. Which kind of makes sense?
After some googling it seems bcachefs is related to bcache but is a fully fledged filesystem rather than just a cache. Looks interesting.
FUD. i've been using ext4 since it came out (and ext2 and ext3 before that), on dozens of computers, and have literally never once had any corruption with it. With XFS i had one instance of filesystem corruption way back in the early 2000's. i've read of _many_ more cases of data loss with ZFS and Btrfs on Linux than i have read about due to ext4 corruption.
Running a database on ZFS results in reduced throughput, while Btrfs and Bcachefs are outright performance disasters. [1][2]
[1] https://www.enterprisedb.com/blog/postgres-vs-file-systems-p...
[2] https://www.phoronix.com/review/bcachefs-benchmarks-linux67
https://blogs.oracle.com/linux/post/formatting-an-xfs-filesy...
Many years ago ZFS checksums helped me figured out I had a bad DIMM.
In the best case, repeating it at the FS layer wastes performance; in the worst case, it causes confusion as to which layer has to do it and then maybe nobody does.
For the same reason, e.g. HTTP doesn’t have a checksum either, and IPv6 even got rid of its header checksum.
HTTPS effectively gets integrity validation by default, as TLS takes care of that. TLS is much more effective at this than the checksums are.
https://archive.ph/dqFgh
https://docs.oracle.com/cd/E36784_01/html/E36845/chapterzfs-...
Google also returned zfs general database tuning:
https://docs.oracle.com/en/operating-systems/solaris/oracle-...
There are many good reasons not to use BTRFS. :)
Also ZFS is hell to use if you're not on an OS that has native support. You become reliant on e.g. kernel modules on Linux , and on Debian this means using Backports which is not something any sensible sysadmin wants to do because it introduces the hell of having a mismatch of all sorts of software unless you spend your life defining exclusions in the apt config.
Also in terms of SQLite, ZFS/BTRFS have no understanding of what's running on them. Personally, when it comes to databases I'd rather the database does its integrity checks. If the OS does some too that's great, but the database blindly assuming the OS will do integrity checking is not a good look in my book.
https://archive.ph/dqFgh
Oracle explicitly does not support brtfs for their database.
But also, if you’re running sqlite, your main goal is probably not to be a database server but to do some other primary objective that happens to benefit from using a database for some things.
A quick search suggests:
https://news.ycombinator.com/item?id=10257466
I am sure that you will find far more detail in your own research, should this motivate you. Good luck!
[1] https://www.enterprisedb.com/blog/postgres-vs-file-systems-p...
[2] https://www.phoronix.com/review/bcachefs-benchmarks-linux67
The benefits of using ZFS in my environment greatly outweigh the downsides of using backports. I vastly prefer Debian to alternatives, so here we are (for me).
- The default checksum algorithms used in many filesystems are too weak (e.g. CRC32), you really want 128-bits for modern databases.
- While some filesystems give you a selection of checksum algorithms ranging from weak to very strong (e.g. SHA256), the databases like SQLite are often deployed in an environment where you have no control over the algorithm selected.
- The checksum algorithms available in most filesystems do not provide adequate performance on modern storage hardware. It is much easier to evolve these in database engines than in the filesystem.
- Filesystem suitability for database workloads varies widely. Few people are going to trade the performance and scalability of XFS for the built-in integrity mechanisms of ZFS. Implementing checksums in the database gets you the best of both worlds. Also databases are not always deployed on filesystems, so you need checksums for these cases.
From a database engineering perspective, the checksumming in filesystems to the extent it exists is often not fit for purpose. Many databases implement their own integrity mechanisms for this reason.
It is the only reasonable way to work with data, and if someone thinks otherwise, I will happily provide motivating examples.
No need to hash the entire file unless you want to scrub it though this would automatically happen when the database is vacuumed.
If the WAL has valid data frames and a valid commit frame after a corrupted frame, there's a strong likelihood this was caused by accidental corruption, so an argument could be made that silently deleting such a WAL is not necessarily the best idea.
A lesson to be learned here, is that the durability of stuff in the WAL is reduced, even if you use PRAGMA synchronous=FULL.
In this case, it makes sense to cut short the WAL application, but you are probably right in saying it should throw an error (or at least a warning).
That shouldn't happen if you use PRAGMA synchronous=FULL, but in WAL mode it's very common to use NORMAL.
Not sure what's the better strategy here, but I'd definitely appreciate a mode that warns me before silently truncating a WAL that has valid frames in it.
The OP made it sound like an oversight rather than an implication of assuming that the filesystem won't return corrupt data that was successfully written and fsync'ed. Sqlite is pretty upfront about the tradeoffs: https://www.sqlite.org/howtocorrupt.html#_failure_to_sync
1: https://blog.acolyer.org/2018/02/27/protocol-aware-recovery-...
The behaviour of SQLite in not raising an error when detecting a corrupt WAL frame also seems to be on purpose.
I’m also not sure every “proper” database has application layer checksums, as that seems like a lower layer concern costing performance and possibly providing a false sense of security (it can’t help with durability at all, for example).
This doesn't seem particularly egregious.
What interesting things could it do in an aircraft?
"By default, data pages are not protected by checksums, but this can optionally be enabled for a cluster. " https://www.postgresql.org/docs/current/checksums.html
I feel it's quite unfair for the title to call out sqlite on checksums, when in reality, as the very closing line of the article states most databases don't do checksum verifications.
And FWIW, Oracle, SQL Server and DB2 enable page checksum storing/verification by default.
Specially highlighting something true, but out of context and with no equally special justification, is not an innocent act and is misleading. And yes, absolutely, very clearly, causes harm, and does so unfairly.
A not-unfair version of this same article would just talk about databases, and include sqlite with others, and not only sqlite and not be titled sqlite.
And then there is this: "Hey there I am v. I work at Turso Database."
This is a SQLite guy talking about SQLite to SQLite users. They're describing a feature/possible downside of SQLite that users might want to be aware of. They don't need "balanced" coverage of every other DB because it hurts someone's bizarrely fragile feelings. And as I mentioned elsewhere, almost all "enterprise" databases do do checksums by default, if people really want to lean on this "no one does! Leave SQLite alone" argument.
And Turso is literally a SQLite-based firm. This isn't the aha you think it is.
https://x.com/bluthquotes/status/732749820348096512
Database defaults in general are a touchy topic. Whatever set of defaults are chosen will be suboptimal for almost any serious user. A far more serious issue is figuring out the actual behavior of a database in different configurations. For instance, Oracle's SERIALIZABLE transaction isolation level only offers snapshoot isolation.
PCIe v6 is intended to materially improve the integrity of data transfers but what we are using today is much worse.
Personally I'm not sure it's the right call, as it comes with some increased write overhead.
For the reference, change is in https://github.com/postgres/postgres/commit/04bec894a04cb0d3... (so should land in PostgreSQL 18 in a year, unless it will get reverted for some reason).
Hi, author of the post here. I work mostly with SQLite and that's the database I'm most familiar with, hence mentioning it in the title. I have also noted at the bottom:
> Again, this is not a bug. Most databases (except a few) assume that the OS, filesystem, and disk are sound. Whether this matters depends on your application and the guarantees you need.
now I wrote a follow up post - https://avi.im/blag/2024/databases-checksum
Application level checksums are for memory, driver, subsystem faults. And many enterprise database systems still do it by default, regardless of how redundant it might seem. Even though your NAS, file system, OS, and even the individual drives are computing it too, systems like SQL Server, Oracle, DB2 and others default to calculating and verifying checksums on all pages.
Has anyone seen a study of the pattern of bit errors experienced on disks? Like are they a single bit or multiple consecutive sectors?
At Quantcast in our QFS distributed file system we used a very efficient reed-solomon coding to get redundancy as good as 3x blocks with only 1.5x storage and still got gigabytes/sec encoding speed. We spread blocks out over different cabinets (not just machines or drives) but that would be overkill here. Obviously we still had end to end checksums because errors can happen anywhere, especially in disk and network controllers whose firmware is usually a one off hack
SQLite has a very well defined and documented set of abstractions about what can and can’t go wrong on the layers of abstraction it relies on. “Out of two writes, at least one will be successful” is not part of that, and doesn’t make sense to me.
Second-guessing the storage layer like that will never be able to catch all problems with it (e.g. drives or file systems lying about the durability of write commands).
Drives, controllers, and file systems lying about write durability is another great reason to add more robustness, good point
No, the solution for that is to not use these, not to greatly entangle layers via workarounds in the ones above them.
The Oracle "dbv" utility will verify all checksums by file that compose a table space. This includes the free XE edition.
A couple of my friends who work in the financial domain showed surprise at this post - they had no idea this was possible. I guess most people just assume databases 'just work'?
SQLite isn’t doing anything wrong here, IMO — people who use non-ECC ran for critical workloads are.
But people do wrong things all the time, so it doesn’t surprise me.
https://en.wikipedia.org/wiki/Error_correction_code
I was introduced to the first ECC algorithm in high school: the Reed-Solomon Code:
https://en.wikipedia.org/wiki/Reed%E2%80%93Solomon_error_cor...
This algorithm remains mysterious to me to this day. It allows you not only to indicate the presence of an error, but also to recover any data with a small redundancy (e.g. 5%).
Reed-Solomon Code principles are embedded in the workings of WinRAR. When creating RAR-archive, you can add redundant data to restore the archive in case of storage interference: https://thuthuattienich.com/wp-content/uploads/2015/05/cach-...
The basic idea of Reed-Solomon is to represent a piece of data as though it's the coefficients on a polynomial and then transmitting the value of various points on the polynomial. You have the opportunity to provide an infinite number of points, and you only need the <order of the polynomial> to be correct to derive the original polynomial. If the polynomial is overspecified, then you have the opportunity to derive the original polynomial for each set of points and see whether or not you get the same result each time. Various combinations of this outcome result in no errors, errors that are corrected, or errors that are detected but not corrected.
The rest of Reed-Solomon is about which polynomial to pick, as the selection of polynomial and where to evaluate it makes the coefficients easier to derive, and how "finite field" math works. We're used to math over all real numbers from math class; finite fields define the same operations but over a finite number of numbers (i.e. "all uint8s"). This is convenient when implementing this algorithm on a computer. (The math, of course, still works when "all real numbers" is the domain of the coefficients and points; restricting to fixed-size integers just makes things easier for computers.)
Simply put. Suppose you have 64 bits (8 bytes) of regular RAM. For ECC-enabled memory, a similar block of memory will occupy 72 bits (9 bytes). The first 64 bits will be identical. What would need to be stored in the remaining 8 bits so that any of the 72 bits could be detected and recovered, assuming only one bit was corrupted?
Without ECC, if you read a block of data from disk into a RAM page, do whatever checksums you want - filesystem page, database page, whatever - and THEN a bit flip occurs, you are now processing bad data but think it's okay because all of the checksums passed.
Every disk block has an ECC code recorded with it to detect and correct errors. Hardware busses have checksums to detect transmission errors. But without ECC RAM, there is no protection for RAM bit flips, and higher-level checksums won't help with that.
It's ridiculous that all computers don't come with ECC RAM, and it's mainly that way because Intel wants to charge a premium for data center servers.