News for hackers. Stuff that matters.
Re:PSA: SQLite WAL checksums fail silently and may lose data by wahern on Thursday July 24, 2025 @02:20PM (#44674168)
But what ZFS provides isn't weaker, and in SQLite page checksums are opt-in: https://www.sqlite.org/cksumvfs.html

EDIT: It seems they're opt-in for PostgreSQL, too: https://www.postgresql.org/docs/current/checksums.html

Re:PSA: SQLite WAL checksums fail silently and may lose data by avinassh on Thursday July 24, 2025 @02:26PM (#44674260)
you might like my other post - https://avi.im/blag/2024/databases-checksum/

bad news is, most databases don't do checksums by default.

Re:PSA: SQLite WAL checksums fail silently and may lose data by lxgr on Thursday July 24, 2025 @02:33PM (#44674354)
This is in fact good news.

Redundantly performing the same performance-intensive tasks on multiple layers makes latency less predictable and just generally wastes resources.

Re:PSA: SQLite WAL checksums fail silently and may lose data by jandrewrogers on Thursday July 24, 2025 @03:06PM (#44674749)
Actually bad news. Most popular filesystems and filesystem configurations have limited and/or weak checksums, certainly much worse than you'd want for a database. 16-bit and 32-bit CRCs are common in filesystems.

This is a major reason databases implement their own checksums. Unfortunately, many open source databases have weak or non-existent checksums too. It is sort of an indefensible oversight.

Re:PSA: SQLite WAL checksums fail silently and may lose data by fc417fc802 on Friday July 25, 2025 @01:13AM (#44679765)
Assuming that you expect corruption to be exceedingly rare what's wrong with a 1 in 2^16 or 1 in 2^32 failure rate? That's 4 9s and 9 9s respectively for detecting an event that you hardly expect to happen in the first place.

At 32 bits you're well into the realm of tail risks which include things like massive solar flares or the data center itself being flattened in an explosion or natural disaster.

Edit: I just checked a local drive for concrete numbers. It's part of a btrfs array. Relevant statistics since it was added are 11k power on hours, 24 TiB written, 108 TiB read, and 32 corruption events at the fs level (all attributable to the same power failure, no corruption before or since). I can't be bothered to compute the exact number but at absolute minimum it will be multiple decades of operation before I would expect even a single corruption event to go unnoticed. I'm fairly certain that my house is far more likely to burn down in that time frame.

Re:PSA: SQLite WAL checksums fail silently and may lose data by lxgr on Friday July 25, 2025 @02:18PM (#44686411)
> Most popular filesystems and filesystem configurations have limited and/or weak checksums,

Because filesystems, too, mainly use them to detect inconsistencies introduced by partial or reordered writes, not random bit flips. That's also why most file systems only have them on metadata, not data.

Re:PSA: SQLite WAL checksums fail silently and may lose data by hawk_ on Thursday July 24, 2025 @05:48PM (#44676682)
So when checksums are enabled and the DB process restarts or the host reboots does the DB run the checksum over all the stored data? Sounds like it would take forever for the database to come online. But if it doesn't it may not detect bitrot in time...?