Possible to use a BRIN Index on a Primary Key in PostgreSQL

user_1001884 0
Viewed 1031

I was reading up on the BRIN index within PostgreSQL, and it seems to be beneficial to many of the tables we use.

That said, it applies nicely to a column which is already the primary key, in which case adding a separate index would negate part of the benefit of the index, which is space savings.

A PK is implicitly indexed, is it not? On that note, can it be done using a BRIN instead of a Btree, assuming the Btree is also implicit?

I tried this, and as expected it did not work:

create table foo (
  id integer,
  constraint foo_pk primary key using BRIN (id)
)

So, two questions:

  1. Can a BRIN index be used on a PK?
  2. If not, will the planner pick the more appropriate of the two if I have both a PK and a separate BRIN index (if performance means more to me than space)

And it's course possible that my understanding of this is incomplete, in which case I would appreciate any enlightenment.

primary-key indexing postgresql
1 Answers
  1. Primary keys are a logical combination of NOT NULL and UNIQUE, therefore only an index type that supports uniqueness can be used.

From the PostgreSQL documentation (currently version 13):

Only B-tree currently supports unique indexes.

  1. I'm not so sure BRIN would be faster than B-tree. It's a lot more space-efficient, but the fact that it's lossy and requires a secondary verification pass erodes any potential speed advantages. Once you are locked into having your B-tree primary key index, there's not much point to making a secondary overlapping BRIN index.
user_114713810
Related