How does unique constraint affect write performance in Postgres DB

Question

Does the UNIQUE constraint specified on a column or group of columns affect the write performance of Postgres DB in any way? How does it internally function?

I mean, does it perform unique checking at the time of insertion of a new record? If yes, how does it do that, does it do a linear search for a duplicate value already existing in the DB? In that case, it is deemed to affect the performance i.e. more the number of unique constraints worse would be the write/insert performance? Is it true?

Solution

The creation of a UNIQUE constraint or a PRIMARY KEY results in the creation of a UNIQUE btree index. This index must be updated whenever any record is INSERTed, UPDATEed, or DELETEd if any indexed column is changed. If no indexed columns are changed then HOT (heap-only tuple optimisation) may kick in and avoid the index update, especially if you have a non-default FILLFACTOR to make space in pages.

The index update on insert/update takes time, so inserting into a UNIQUE indexed table is slower than inserting into one without any unique index or primary key. The same is true for UPDATE, but if the index is used to find the tuple to update (and avoid a seqscan) it's usually a net win vs not having the index at all. If a different index is used to find the tuple, or if a seqscan is faster (as is true on small tables) then just like an INSERT the index has no benefit and just incurs a write cost to update it for that operation. This is true for all indexes, not just UNIQUE indexes.

Each INSERT or UPDATE on a UNIQUE indexed column requires an index lookup to verify that the key doesn't conflict with an existing key. From vague memory this is combined with the process of inserting the new entry into the index, but I'm not 100% sure there.

AFAIK DELETE doesn't affect the index. It just sets the xmax for the tuple in the heap.

The index gets updated even if you ROLLBACK the transaction or the transaction aborts with an error after the successful insertion or update on the UNIQUE constrained column. VACUUM work by autovacuum cleans the dead index entries up later. See Concurrency Control in the PostgreSQL manual.

All this is also true of a PRIMARY KEY, which is also implemented using a UNIQUE index.

Every index, including the indexes used by PRIMARY KEY and UNIQUE constraints, incurs a penalty on write performance.

Comments

[Abhishek Jain]

Thanks for the answer. This was very well explained. Does the primary key affect the sorting order of records as well? It will be great if you can provide an answer to my other question at : stackoverflow.com/questions/13190720/…

[dierre]

Would partitioning the table help with the problem?