CODEWITHSUNDEEP
postgresql
OckhamsRazor
OckhamsRazor

Reputation: 4906

Postgres - unique index on primary key

On Postgres, a unique index is automatically created for primary key columns. From the docs,

When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.

From my understanding, it seems like this index only checks uniqueness and isn't actually present for faster access when querying by primary key id's. Does this mean that this index structure doesn't consist of a sorted table (or a tree) for the primary key column? Is this correct?

Upvotes: 0

Views: 3681

Answers (2)

David Aldridge
David Aldridge

Reputation: 52346

In theory a unique or primary key constraint could be enforced without the presence of an index, but it would be a painful process. The index is mainly there for performance purposes.

However some databases (eg Oracle) allow a unique or primary key constraint to be supported by a non-unique index. Primarily this allows the enforcement of the constraint to be deferred until the end of a transaction, so lack of uniqueness can be permitted temporarily during a transaction, but also allows indexes to be built in parallel and with the constraint then defined as a secondary step.

Also, I'm not sure how the internals work on a PostgreSQL btree index, but all Oracle btree's are internally declared to be unique either:

  1. on the key column(s), for an index that is intended to be UNIQUE, or
  2. on the key column(s) plus the indexed row's ROWID, for a non-unique index.

Upvotes: 2

haki
haki

Reputation: 9759

Quite the contrary, The index is created in order to allow faster access - mainly to check for duplicates when a new record is inserted but can also be used by other queries against PK columns. The best structure for uk indexes is a btree because during the insert the index is created - If the rdbms detects collision in the leaf he will raise a unique constraint violation.

Upvotes: 1

Related Questions