How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

Question

I've been trying to sort out the relationship between unique and index in Postgres after reading the docs on index uniqueness being an implementation detail:

The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

So taking the docs at their word I'm going to just declare things as unique and use the implicit index - or - create an index and not assume that the values are unique. Is this a mistake? 

What kind of index will I be getting from unique? Given that only a btree will accept the unique constraint and unique implicitly creates an index is it true that UNIQUE creates a btree index? I don't want to be running ranges on a hash index inadvertently. 

Dulaney

"I don't want to be running ranges on a hash index inadvertently." But again: consider that an implementation detail. Constraints exist to impose/ensure the datamodel, they don't care about performance per se. This is a logic/semantic difference. (BTW: it is a btree) – Cheslie

Interestingly, the quoted preference has disappeared from the PostgreSQL docs starting with v9.5. See postgresql.org/docs/9.5/static/indexes-unique.html – Drida

Answer 1

create an index and not assume that the values are unique

It is safe to assume that values are unique, if you have a unique index defined. That's how unique constraints are implemented (at the time being, and probably in all future versions as well).

Defining a UNIQUE constraint does effectively the same (almost, see below) as creating a unique index without specifying the index type. And, I quote the manual:

Choices are btree, hash, gist, and gin. The default method is btree.

Adding a constraint is just the canonical way that would not break in future versions where it could be implemented differently. That's all.

And no, a unique constraint can only be implemented with a basic btree index in all versions up to and including PostgreSQL v14. Quoting the paragraph "ADD table_constraint_using_index" in the manual:

The index cannot have expression columns nor be a partial index. Also, it must be a b-tree index with default sort ordering.

Other differences?

• Unique constraints can be deferred. That is not possible for unique indexes. Have a look at the SET CONSTRAINTS command and follow the links for more.

Related:

• NULL values for referential_constraints.unique_constraint_* columns in information schema
• Is unique index better than unique constraint when I need an index with an operator class

Snakeroot

"btree indexes are the default..." this is true regardless of how that index came to be (ie INDEX vs UNIQUE) – Dulaney

So is it possible to force unique to use a hash or rtree index for enforcement? – Dulaney

@Finn: I added another bit to my answer to address that. – Snakeroot

In v9.4.11 I can add a foreign key that references a column with just a unique index, not a unique constraint. – Drida

nvm, today I learned PostgreSQL has shit hash support blog.andrebarbosa.co/hash-indexes-on-postgres – Ritz

@EvanCarroll: That's going to change with pg 10: postgresql.org/docs/devel/static/… – Snakeroot

@ErwinBrandstetter any love is nice, but those changelogs (and the docs) don't seem to imply that they'll be usable as unique indexes, or that they're getting Index Only Scans (or maybe I'm missing it). – Ritz

@Evan: That's true, still no unique hash index. Only btree. The workaround is a btree index on a hash expression. Like dba.stackexchange.com/a/115316/3684. As for index-only scan: How could a hash index ever be a candidate? The original value is not in the index. – Snakeroot

Nothing stopping the index from reading the table in the event of a hash match. This would give you the benefit of a smaller index along with slower inserts to check the uniqueness.... – Hunyadi

@EvanCarroll. Hash indexes are now cool: rhaas.blogspot.com/2017/09/… – Hunyadi