What locks occur in PostgreSQL when creating a unique index on a column? How does PostgreSQL create and modify records (MVCC)?

Locks when creating a unique index in PostgreSQL:

When creating a unique index, PostgreSQL uses locks to ensure data integrity:

• During index creation, the table is locked in ACCESS EXCLUSIVE LOCK mode — this is the strictest lock, blocking all read and write operations.
• This is necessary to prevent data modifications during index building and to guarantee uniqueness.

MVCC (Multi-Version Concurrency Control) in PostgreSQL:

PostgreSQL implements MVCC to provide transaction isolation without read locks:

• Each row contains system fields with transaction identifiers that created and deleted it.
• During a read, transactions see only the versions of rows that were current at the start of the transaction.
• When updating, a new version of the row is created with a new xmin (transaction ID), and the old version is marked as deleted (with xmax).
• Deleted versions remain in the table until vacuumed.

Thus, records are not locked during reads, and locks are only applied during modifications, increasing concurrency and performance.