amcheck “table-matches-index” enhancement now available, detects "freeze-the-dead" corruption

I’m pleased to announce that v1.2 of amcheck, a tool for detecting that PostgreSQL relations are logically consistent (that they do not appear to be corrupt) is now generally available. This version adds a big enhancement - the optional ability to check if every tuple that should have an entry in the index does in fact have such an entry. Specifically, we check for a table entry with matching data, as well as a matching heap TID. This happens at the end of the existing tests, as an optional extra step.

This enhancement is significant because it seems much more likely to catch corruption in the wild. In general, inconsistencies between a table and its indexes are more likely to occur than inconsistencies between blocks within an index for many reasons. There is simply a much larger window for an inconsistency to arise when something is amiss with database storage that breaks the assumptions PostgreSQL makes during crash recovery, for example.

The enhancement is also significant because it played a role in identifying a PostgreSQL data corruption bug that will be fixed in the next point release, scheduled for November 9th, 2017. This bug affects all supported PostgreSQL versions. It was informally dubbed the “freeze-the-dead” bug.

November 6 2017 update: The fix was reverted due to additional concerns that came to light. The community is working on a new, more comprehensive fix for the next point release.

December 15 2017 update: A new fix has been committed, and will appear in 9.3.21, 9.4.16, 9.5.11, 9.6.7, and 10.2 point releases, scheduled for February 8th, 2018.

Packages for v1.2 are available from the community Debian/Ubuntu apt repository, as well as packages from the community Redhat/CentOS/SLES yum repository. Full details on installing these packages are available from the README:

https://github.com/petergeoghegan/amcheck/

"Freeze-the-dead" corruption detection

I should emphasize that the bug is something that I believe to be very unlikely to hit in the real world, because there is only a very small window. Moreover, it is probably virtually impossible to hit without a manual VACUUM FREEZE. It can only happen with the use of foreign keys (strictly speaking, directly allocating MultiXacts could cause the issue in environments where foreign keys are not used).

amcheck is effective in detecting corruption caused by the “freeze-the-dead” bug because the corruption results in a logical inconsistency in a heap page. There could be multiple versions of the same row visible at once in cases where a prune never took place. In other scenarios, there could be a HOT chain that is pruned prematurely, leading to wrong answers to query plans that use an index scan, while sequential scan plans (plans that should get the same answer without using an index) still give correct answers. Even though amcheck was not written with these specific inconsistencies in mind, it still seems to reliably detect them.

Managing risk

The PostgreSQL development community is well known for putting data integrity first. With that in mind, I think that it’s important to avoid overstating the significance of the fact that amcheck detects corruption caused by bugs in PostgreSQL itself. While it is true that finding data corruption bugs is one goal of amcheck, and while it is also true that it has actually done so more than once already, that in itself shouldn’t be seen as a blemish on the project’s reputation for ensuring data integrity. If your take-away about amcheck appearing necessary is along the lines of “that certainly doesn’t inspire confidence”, I would argue that you’re thinking about the issues in the wrong way. Besides, amcheck is hardly the first tool like this to appear - similar tools are available for all other major RDBMSs.

I think that it will prove useful to have an immediate way of mechanically detecting corruption caused by the “freeze-the-dead” bug. We may actually hear reports of corruption that it has caused in the wild (if you happen to have been affected, please let the pgsql-hackers list know about it). I also believe that the newly enhanced amcheck will detect corruption caused by other historic bugs, including the CREATE INDEX CONCURRENTLY bug detected in February of 2017, as well as a similar CREATE INDEX CONCURRENTLY bug from back in 2012. Estimating the prevalence of corruption of this general nature is a very tricky business, though; it largely comes down to workload, and those are incredibly varied. And, even if an issue is on average very unlikely to strike, that doesn’t help those that are affected despite those odds. In short, there is no such thing as the average PostgreSQL database, and there are many practical problems with applying statistical models to complex domains.

I would venture to predict that a more detailed picture of how prevalent corruption like this is across all PostgreSQL installations will emerge over time, as amcheck is used more widely, and that that picture will be fairly boring. It still seems important to make every effort here, though. Going forward, we’ll have more to go on than educated guesses. And, those unlucky few that turn out to be affected by bugs that lead to corruption will have a relatively simple, non-disruptive tool to isolate the problem. DBAs can use amcheck to help with managing the risk of data corruption, including but not limited to corruption originating from bugs in PostgreSQL.