PRAGMA integrity_check: What It Catches and What It Completely Misses

PRAGMA integrity_check is the first tool people reach for when a SQLite database misbehaves. It is a good tool — and it has a hard limit that most people do not know about. I verified this limit by deliberately corrupting databases.

What integrity_check verifies

It walks the file's structure: pages, B-tree balance, cell pointers, freelist consistency. A healthy database returns a single ok. Structural problems come back as specific errors like:

*** in database main ***
Tree 2 page 7: btreeInitPage() returns error code 11

That is genuinely useful. Truncated files and zeroed pages are caught loudly.

The limit: it does not check your content

Here is the part that surprised me. I corrupted a database by flipping bytes inside a row's payload — the text of a record, not the structure. The result:

PRAGMA integrity_check: 1 row(s) returned
   [1] ok

integrity_check said ok while the data was wrong. SQLite does not checksum payload bytes, so content-level corruption is invisible to the structure check. The file is structurally perfect and semantically corrupted.

What this means in practice

1. integrity_check validates structure, not data. Passing does not mean "your data is correct," only "the file is well-formed."
2. The only defense against silent content corruption is a backup you can compare against. This is why "test your restores" is not optional.
3. Schedule it anyway. It is cheap on small databases and catches structural rot early — just do not mistake it for a data guarantee.

The pattern that holds

In my tests, corruption split cleanly into two buckets:

Run integrity_check on a schedule, back up with VACUUM INTO, and restore-test your backups. Then the things this check misses stop being a surprise. Sizing a database to back up? The estimator below helps you plan ahead.