On Wed, May 20, 2026 at 09:16:28AM +0200, Jakub Wartak wrote: > From time to time we hit some corruption issue and usually we end up checking > for corruption with COPY to /dev/null and/or with verify_heapam(). Both seem > to detect different kind of corruption types, so I'm assuming they are somehow > complementary (e.g. seems that COPY is slower in cached case, but exercises > TOAST way harder than the amcheck routing even with check_toast=>true). Also > there's usecase that we often ask people to 'just run pg_dump -j', but that > requires -Fd <dir> which then requires plenty of disk space if you want to > verify whole DB in parallel, you need plenty of space (which is unrealistic on > big installations)
Another limitation of COPY is that it does not check the state of index pages, neither does pg_dump. If we need better tooling for the detection of corruption, we should have new tools or improve the existing tools that exist for this purpose. pg_dump and COPY are a popular way to check some data state, but it's been historically wrong because it just points to people not knowing what to do, because perhaps what we have is thought as hard to use or just bad. pg_catcheck is another tool of this kind that I like a lot, in terms of corruption check. That has helped me a lot in the past. Forcing consistency modes into queries that are not designed for this purpose is IMO a design mistake, making some code more complicated than it actually should. Providing more context regarding COPY and why this would be a bad idea: additions in its code path are *never* free, especially for row-level processing, and can show up very easily depending on the schema and/or the data pattern copied from/to. My 2c. -- Michael
signature.asc
Description: PGP signature
