PostgreSQL: unique constraint or unique index

Question

Should I create unique index if a column contains unique constraint and I want to fast search by this column?

For example I have a table users with column login that should be unique. I need fast search user by the login column.

Which is the best way to do it:

create a unique constraint (it creates internal unique index - is it used in select queries with WHERE login = 'something'?)
create a unique index
create a unique index and unique constraint (index duplicates internal index?)

Second case is unique login on not locked users (column locked = false). Postgres does not support partial conditions. Should I create a unique conditional and a partial index or is only a partial index enough?

And one more question: should I create new index for a column with a foreign key? For example: users.employee_id relates to employees.id, should I create an index on employee column for optimized query SELECT * FROM users WHERE employee_id = ....? When are internal indexes used by the optimization engine and when not?

The Impaler · Accepted Answer · 2019-02-12 22:33:09Z

7

I have a table 'users' with column login that should be unique

If this is the case you need a unique constraint. Unique constraints are enforced (behind the scenes) by unique indexes.

Conceptually:

The unique constraint defines the uniqueness.
The unique index implements the unique constraint.
The unique index provides speedy searches since it allows operations such as Index Range Scan and Index Seeks.

Is it used in select queries with WHERE login = 'something'?

Yes, it is.

Second case is unique login on not locked users (column locked = false).

If this is the case a unique constraint won't work. Maybe a trigger on insert could help here.

should I create new index for column with foreign key?

No, it's not needed (at least in the current version 10 and perhaps the later versions), s. documentation:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. [...] There's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

edited Feb 12, 2019 at 22:33

answered Jun 23, 2018 at 23:11

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Impaler Over a year ago

@automatix I think PostgreSQL does not create indexes for foreign keys by default, and your edit may be incorrect. The documentation you included is related to unique indexes, not foreign keys.

automatix Over a year ago

Unfortunately I read the quote wrong. The context of the question was the UNIQUE CONSTRAINT, so I read the text as "should I create new index for column with unique constraint?". So, yes, my edit is incorrect. Please reject it.

Collectives™ on Stack Overflow

PostgreSQL: unique constraint or unique index

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related