Item — HN Reader

One of the interesting things I came up against while writing this post was the pretty common misconception that SQLite and Postgres will validate your data with checksums [0]. SQLite leaves this to an optional extension (this is a little more commonly known). Here was an HN comment I stumbled on about Postgres [1]:

> Postgres (and other databases that actually care about data integrity) have per page checksums:

Postgres does have support for data checksumming (but not metadata checksumming) but defaults to disabling data checksumming. MongoDB (WiredTiger) on the other hand defaults to checksumming. I was told after publishing this post that MySQL (InnoDB) also does checksum by default but I did not check that in my survey.

Links for proof are in the post.

[0] https://x.com/eatonphil/status/1807572135340134687

[1] https://news.ycombinator.com/item?id=25231308

4 comments

That's interesting. [1] seems to be the relevant page in the postgres documentation.

tl;dl: run `SHOW data_checksums` to see if checksums are enabled (they were enabled by default on our GCP Cloud SQL instance), to enable them shut down postgres and run `pg_checksums --enable --progress`

1: https://www.postgresql.org/docs/current/checksums.html

I’ve come around to the idea that checksumming isn’t some universal panacea. Once you have checksums, you need to start thinking about error *recovery* and not just detection. What happens to linked data? Do you just declare the whole SQLite file corrupted and just throw up your hands?

If you have backups, that’s one obvious solution, but people might not have uncorrupted backups.

I still think you should do it and emit a warning about it at the very least, but it’s not trivial to handle.

Just use ZFS. :)

Running a database on zfs can work well but introduces a whole new world of whitepapers to read and tuning options to consider :/

You don't have to read any whitepapers. Just copy the `zfs create` command from some guide, it'll work fine.

Leave zfs at the defaults and in the case of mysql/mariadb, turn off the "double writes" setting. It's not hard.

Database engines that live as libraries inside application processes (like WT) have a more pressing need for checksums because the most common attack vector is buggy pointer arithmetic outside the library that stomps on data in memory randomly.

Checksumming is also useful when storage goes bad or other random hardware corruption like cosmic rays or overheating/old computers.

This reminds me of the time I had a disk go 'random' in a RAID. Disk didn't fail, instead it just returned junk for every Nth read. Happened to be in a big database server too. Crazy part is it took far longer than I would have expected to catch data showing up corrupted in user applications so an entire 28 hours of data had to be rolled back.

yeah, that one is a path dependency issue, re: postgres. I'm not sure if it's well reasoned past inertia after the initial "it's new, let's not throw the entire user base into it at once", but at the very least, it will complicate pg_upgrade somewhat. WiredTiger didn't really have that path dependency, being itself new to Mongo to shore up the storage situation.

It's probably about time to swap the default, but some people's once-working pg_upgrade programs that they haven't looked at in a while might break. Probably okay; those things need to happen...once in a while. I suppose some people that resent the overhead of Postgres checksumming atop their ZFS/btrfs/dm-integrity/whatever stacks, but they are somewhat rarer.

Most weird thing: PostgreSQL's checksum algorithm is almost free on modern processors. It uses SIMD instructions extremely well in optimized builds. I've never seen him in CPU profile despite I always enable it.

There are no any reasons to not enable it (except pg_upgrade).