Time for yet another “first patch” idea post ;-) This time it’s about BRIN indexes. Postgres has a contrib module called amcheck, meant to check logical consistency of objects (tables and indexes). At the moment the module supports heap relations (i.e. tables) and B-Tree indexes (by far the most commonly used index type). There is a patch adding support for GiST and GIN indexes, and the idea is to also allow checking BRIN indexes.

If you didn’t read the first post about how to pick the first patch, maybe do so now. Everything I wrote in that post still applies - you should pick a patch that’s interesting (and useful) for you personally. Don’t jump on this idea simply because I posted about it.

If you decide to give this patch idea a try, let me know. Maybe not right away, but once you get past the initial experiments. Otherwise multiple people might be working on it, having to throw the work away after the first one submits it to pgsql-hackers. It gives insights and ability to review the submitted patch, so not a total waste of time. But if your goal was to write the first patch …

Motivation

The ability to check consistency is immensely useful, especially during development. That is the origin of this patch idea - I did a bit of hacking on BRIN indexes, and having an easy way to check consistency of the index would have been very useful.

Whenever I tweaked the BRIN code in a way that could have affected the index, I had to do rigorous testing to make sure I didn’t break it in some subtle way. That means building an index on a range of data sets, modifying it in different patterns, etc. And then you need to check if the index is correct. Which is tricky if you can do that only indirectly by running queries and validating the results.

Sometimes I used pageinspect to perform basic checks on the revmap or page summaries. But for oplasses other than minmax it quickly gets difficult to do anything but basic checks.

It would have been very nice to have a function in amcheck doing various basic low-level checks on a BRIN index. It can never 100% prove the index is not broken in some subtle way, but it would still be much better than the checks I did using pageinspect. And 99% of the time I found a bug by checking query results, it was something this amcheck function would probably find.

Implementation

As for the postgres_fdw batching idea, a big advantage is most of the functionality is in an extension, thus quite isolated. All the changes and new code will go into amcheck.

In particular, the patch will need to

• define a new amcheck version, and create an “upgrade” SQL script
• declare new functions in the SQL script, pointing to C functions
• implement a couple C function actually doing the consistency checks

The first two steps are mostly mechanical stuff that has been done many times in the past, both for amcheck and other extensions. There’s nothing very difficult about that, it’s mostly copy-paste.

Implementing the C functions doing the actual consistency checks is what I expect to take most of the time. You’ll need to choose what to check, which requires learning a bit about BRIN indexes. There’s a nice README explaining the basics.

Once you understand how BRIN builds summaries for page ranges, and what a revmap is, take a quick look at the APIs in brin_revmap.h and brin_tuple.h. Perhaps look at brin.c too - whatever you’ll need to do with a BRIN index, it’s probably done somewhere that code already.

What checks could be implemented for a BRIN index? A couple ideas:

• The revmap must not contain duplicate ranges, and the ranges must start on multiples of pages_per_range specified for the index.

• The BRIN tuples should have valid structure, i.e. correct number of values stored for each indexed column (in a multi-column index), as determined by the opclass.

• The summaries must be consistent with the table. That is, if you read all rows in a given page range of the table, the values should match the range (per the “consistent” procedure of the opclass). Same for NULL values and empty ranges.

It will take time to learn how to do this for BRIN indexes, but all of this is already implemented somewhere. For example, reading the revmap and evaluating values is done in bringetbitmap AM function.

Another place doing a lot of this stuff is the pageinspect extension, which already knows about BRIN indexes.

I myself wouldn’t be writing any of this from scratch. I would search for existing code doing something similar, and then I’d copy it and tweak it to do what I need it to do.

Also, you don’t need to invent the amcheck stuff from scratch. Take a look at what bt_index_check does and how, and try to do stuff in a similar way. For example, something like a heapallindexed parameter could enable the cross-check that all heap rows are properly indexed.

Risks

I’m pretty sure everything I described here is possible and feasible.

It will take time to learn enough about BRIN to implement some of the more complex checks. In particular the cross check that all rows are covered by the index may be more complex to implement, at least for some BRIN opclasses.

Of course, there’s also the rist that this uncovers some pre-existing case where we build an invalid index, but that’s a “good risk” ;-)

Conclusions

So that’s the third patch idea. If you’re interested, feel free to reach out to me directly by email. Or you can talk to other Postgres developers in pgsql-hackers, or the new discord channel.