How PostgreSQL Enforces Uniqueness via Heap Visibility, Not Index Entries

Unlike Oracle, PostgreSQL appends a tuple ID (TID) to every index key — even in unique indexes — meaning the index itself does not physically prevent duplicate logical keys. Actual uniqueness is enforced at the heap level by checking tuple visibility within an MVCC snapshot, rather than within the index structure. This design is necessary because PostgreSQL's MVCC system allows multiple versions of the same row to coexist in an index simultaneously. Oracle avoids this complexity by versioning index blocks directly, letting a single index entry maintain a truly unique key. A developer explored this behavior using PostgreSQL's pageinspect extension to inspect raw heap and index pages, revealing how multiple tuple versions are stored and how uniqueness checks work internally.