SQLite does not do checksums

And FWIW, Oracle, SQL Server and DB2 enable page checksum storing/verification by default.

Personally I'm not sure it's the right call, as it comes with some increased write overhead.

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

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 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://archive.ph/dqFgh

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.

- 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.

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.

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.

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).

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.

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.)

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.

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?

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.