2

Just about every mainstream DB has a feature to calculate checksums per page, per sector, or per record.

Now for a DB that does full recover after any crash, like PostgreSQL, is a checksum even useful? There will be no data loss as long as the xlog is ok, no matter what kind of corruption happened to the data itself, as the redo log is replayed every committed transaction will be restored. So checksums are useless on restore.

Doesn't the filesystem or disk keep checksums anyway to detect corruption? So unless the checksum is per record, all it does is tell you there is corruption - which the OS should be yelling at you the minute you try to read it - so useless in operation?

I can't imagine how a checksum can be helpful in any sane database - but since they all use them - I'd say that's just failure of imagination on my part. So how is it useful?

3 Answers 3

4

Checksums are important, and useful, for all sorts of reasons. Probably the topmost include:

  • Checking that replicated/clustered databases are in sync
  • Checking that data is properly restored from backup
  • Ensuring that recovery processes did not fail, or hit a bug
  • Checking integrity of the database (for example, to see if anyone's played around with the file when you weren't looking)
4

The fact is, only a fraction of popular filesystems carry checksums of actual file contents [wikipedia article]. As for hard drives, their durability metrics have never caught up with soaring capacities, so with enough data and/or throughput, you're guaranteed to get undetected errors once in a while [CERN research]. That is, unless you have enough checksums to detect them, which at this point mandates app-level checking.

3

Since most file systems don't keep checksums (notable exception is ZFS), a checksum in the database would still be useful.

In PostgreSQL, the WAL records are checksummed, but not the data pages, so you won't be able to detect if the OS or hardware has shredded your data. Implementing a checksum for data pages is a planned feature in PostgreSQL, but there are some very difficult problems with the implementation if concurrency is to be maintained. Search for "postgresql block level crc" or something like that to learn more (or become totally confused).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .