CODEWITHSUNDEEP
postgresql
Dakota
Dakota

Reputation: 183

Postgres BRIN Index with Partitioning

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.

Upvotes: 2

Views: 1089

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246653

Multi-column BRIN indexes index each column on its own, so a multi-column BRIN index will work just as well as a BRIN index on each individual column. As the documentation says:

The only reason to have multiple BRIN indexes instead of one multicolumn BRIN index on a single table is to have a different pages_per_range storage parameter.

However, a BRIN index only makes sense with columns whose values are ever-increasing (or decreasing). So 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.

Upvotes: 3

Related Questions