3 ms·

I'm going to present a potentially controversial opinion: you should always consider adding checksums. Checksums are great, easy and almost assuredly cheap. CR

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.

bawolff 2y ago
What part are we checksumming? 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.
- ajsnigrutin 2y ago
  +1 for this. 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.
  - MertsA 2y ago
    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.
- heftig 2y ago
  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.
- eknkc 2y ago
  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?
anarazel 2y ago
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...

bawolff 2y ago

What part are we checksumming? 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.

ajsnigrutin 2y ago
+1 for this. 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.
- MertsA 2y ago
  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.
heftig 2y ago
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.
eknkc 2y ago
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?

anarazel 2y ago

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