Postgres BRIN Index with Partitioning

user_7692252 0
Viewed 584

The table in question has 3 relevant columns: A, B, and C. The table is expected to have north of 1B records.

A unique index can be created with A, B, C where A and B are text fields and C is a numeric id (incrementing)

C on it's own is not unique.

BRIN indexes seem to fit nicely with this model. We have an incrementing integer with data that never has updates to the indexed field.

Can BRIN indexes be used efficiently with a multi column index in a non-partitioned table or should partitions be used instead with an index on a single column in each partition?

For context, the 99% use case is a query by a, b, c where c is either in a range (or orordered by with a limit if that's possible). We also want to query the MAX c by a, b.

Thanks for any advice.

postgresql
1 Answers

It doesn't make much sense to have a multi-column BRIN index, because BRIN keeps the minimum and maximum value per block range. Now minimum and maximum of a composite are taken using lexicographic ordering, which is not what you want. That index would do nothing for you.

But with an insert-only table with incrementing c it would be useful to have a BRIN index on only c.

That index would be very small, and with the query you describe, it could quickly identify one or a few block ranges that may contain matching rows. These blocks have to be scanned, which is not as fast as a B-tree index, but probably fast enough. With BRIN you trade size for speed.

user_64643080
Related