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.
Many years ago ZFS checksums helped me figured out I had a bad DIMM.
Many years ago ZFS checksums helped me figured out I had a bad DIMM.
- 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.
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.
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
root@nas:/etc/apt/preferences.d# cat 80_kernel
Package: linux-image-amd64 linux-headers-amd64 linux-kbuild-*
Pin: release n=bullseye-backports
Pin-Priority: 1000
root@nas:/etc/apt/preferences.d# cat 90_zfs
Package: libnvpair* libuutil* libzfs* libzpool* spl-dkms zfs-* zfsutils-linux zfsutils-linux-dev
Pin: release n=bullseye-backports
Pin-Priority: 1000
root@nas:/etc/apt/preferences.d# cat 1000_backports
Package: *
Pin: release n=bullseye-backports
Pin-Priority: 10
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 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://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-...