I recently commented on Jonathan Lewis’s blog, Savepoint Funny, where I compared how PostgreSQL handles uniqueness differently: “PostgreSQL resolves uniqueness through heap tuple visibility". This deserves a more detailed explanation.
In Oracle, unique indexes store unique entries because the B-tree key is the index key, preventing duplicates. Non-unique indexes add the ROWID to ensure that all entries are physically unique, even when indexed column values are duplicated.
In PostgreSQL, all indexes, even unique ones, created explicitly by CREATE UNIQUE INDEX or implicitly to enforce a unique constraint, behave like non-unique indexes by appending the TID (tuple ID, similar to Oracle's ROWID) to the index key. This indicates that the index itself doesn't guarantee physical uniqueness, allowing multiple entries to have identical logical keys but point to different heap tuples. The actual uniqueness verification occurs at the heap level, not within the index entries.
Initially, this might seem unusual—a unique index that permits duplicates. However, PostgreSQL requires this because of its MVCC system. MVCC allows duplicate entries to coexist in an index, since they can represent different versions of the same logical row. Still, PostgreSQL must guarantee that no MVCC snapshot views two rows with the same index key. Oracle doesn't face this issue because its MVCC implementation also versions index blocks, allowing a single index version to maintain unique keys.
Let’s show that.
Page inspect
In PostgreSQL, the heap contains the table data, and index entries point to heap tuples. Visibility depends on the heap header, especially the transaction information. Index scans often visit the heap pages to check visibility, except for index-only scans, which use the heap's visibility maps as an optimization. B-tree indexes can store entries for multiple versions of the same logical row, including versions that are no longer visible to current snapshots. To ensure uniqueness, the B-tree must check the heap for matching keys to verify whether multiple entries point to visible heap tuples in the same MVCC snapshot.
I used pageinspect to look at heap and index pages. This is not application-level SQL. This is a physical page inspection, useful for debugging and understanding internals. I used it to see all tuple versions, including those not visible to my MVCC snapshot. In some ways, you can compare it to Oracle flashback query, which shows all versions of a row.
CREATE EXTENSION IF NOT EXISTS pageinspect;
DROP TABLE IF EXISTS demo_unique_mvcc;
CREATE TABLE demo_unique_mvcc
(
id bigint generated always as identity,
email text not null,
payload text not null,
marker int not null,
CONSTRAINT demo_unique_mvcc_email_key UNIQUE (email)
) WITH ( FILLFACTOR = 10 );
CREATE INDEX demo_unique_mvcc_marker_idx
ON demo_unique_mvcc(marker);
ALTER TABLE demo_unique_mvcc SET (autovacuum_enabled = false);
I disabled auto-vacuum to avoid garbage collection during my experimentation.
Unique index
I created a unique constraint on email:
postgres=# \d demo_unique_mvcc
Table "public.demo_unique_mvcc"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
email | text | | not null |
payload | text | | not null |
marker | integer | | not null |
Indexes:
"demo_unique_mvcc_email_key" UNIQUE CONSTRAINT, btree (email)
"demo_unique_mvcc_marker_idx" btree (marker)
postgres=#
I also created another index on marker. This is deliberate. If I update only a non-indexed column, PostgreSQL may use HOT updates, so no new entries are needed in the indexes. I want a non-HOT update to show the general case, so I update an indexed column, marker, while keeping the same email. To prove my point, I've set FILLFACTOR to 10% so that HOT updates are possible.
I inserted one row:
postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
VALUES ('a@example.com', 'first version', 1)
;
INSERT 0 1
postgres=# SELECT ctid, xmin, xmax, *
FROM demo_unique_mvcc
;
ctid | xmin | xmax | id | email | payload | marker
-------+------+------+----+---------------+---------------+--------
(0,1) | 697 | 0 | 1 | a@example.com | first version | 1
(1 row)
The precise xmin may vary as it's your transaction identifier. What's crucial is the ctid. In this case, the visible row version is (0,1), which is the first tuple on the first page.
Now let’s look at the heap page:
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 0 | (0,1) | 2306 | 4
(1 row)
This tuple was inserted by committed transaction 697, has not been deleted or updated (xmax is invalid), contains at least one variable-length column, and stores 4 attributes.
I inspect the unique index. For a B-tree index, block 0 is the metapage, so the first leaf page is usually block 1 for a tiny index:
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(1 row)
One visible row. One index entry in block 1 of the index (ctid) addressing block 1 of the heap table (htid). Nothing surprising yet.
Multiple updates
I updated the row two times, but I did not change the unique key:
postgres=# UPDATE demo_unique_mvcc
SET payload = 'second version', marker = 2
WHERE email = 'a@example.com'
;
UPDATE 1
postgres=# UPDATE demo_unique_mvcc
SET payload = 'third version', marker = 3
WHERE email = 'a@example.com'
;
UPDATE 1
The email value did not change. Logically, there is still one row with email = 'a@example.com':
postgres=# SELECT ctid, xmin, xmax, *
FROM demo_unique_mvcc
;
ctid | xmin | xmax | id | email | payload | marker
-------+------+------+----+---------------+---------------+--------
(0,3) | 711 | 0 | 1 | a@example.com | third version | 3
(1 row)
Only one row is visible to my SQL query.
But the heap page tells a different physical story:
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 0 | (0,3) | 10498 | 4
(3 rows)
There are three heap tuple versions. The first is no longer current and has been superseded by transaction 710, which has set xmax. The second one is the transaction 710 change superseded by transaction 711. The third is the current version with xmax set to 0. Note that t_ctid points to the next version, so it can chain from old versions to new ones, except for the current version, which points to itself. Typically, here, an index entry pointing to (0,1) (line pointer lp=1 in block 0) can continue to (0,2) and then (0,3).
The old versions are not visible to my current query, but they are still physically present because I disabled autovacuum and have not vacuumed the table.
Now inspect the unique index:
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,2) | (0,2) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
3 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(3 rows)
This is the important point.
The unique index can physically hold multiple entries for the same logical key (data) because these entries point to different versions of heap tuples (htid).
Duplicate key violations
I tried to insert the same email into a new row:
postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
VALUES ('a@example.com', 'another logical row', 10)
;
ERROR: duplicate key value violates unique constraint "demo_unique_mvcc_email_key"
DETAIL: Key (email)=(a@example.com) already exists.
postgres=#
This fails. The B-tree found matching key entries in the index. Then PostgreSQL checked the heap tuple visibility of the referenced tuples. At least one conflicting tuple is visible, so this is a uniqueness violation. The index alone did not decide everything. The heap visibility check decides whether the duplicate index entry is a real conflict.
The duplicate tuple was inserted into the heap before the statement was aborted, and this is visible from the physical history:
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 0 | (0,3) | 10498 | 4
4 | 712 | 0 | (0,4) | 2050 | 4
(4 rows)
PostgreSQL does not determine visibility from xmin/xmax alone. The infomask bits tell whether the transaction status is already known (committed, aborted, invalid, updated, locked, etc.). When the bits are not set, PostgreSQL consults the transaction status and may later set hint bits in the tuple header:
postgres=# SELECT txid_status('744')
;
txid_status
-------------
aborted
(1 row)
postgres=# SELECT txid_status('742')
;
txid_status
-------------
committed
(1 row)
Nothing changed in the index. It was used to find the multiple versions, but a duplicate key was detected before updating it:
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,2) | (0,2) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
3 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(3 rows)
It is not visible here, but even if uniqueness is detected on the heap tuples, it is still part of the index access methods because concurrency control must synchronize transactions by locking the index leaf page, which is the only place where all transactions updating the same key must go, since heap tuples could be in different blocks. This explains why a unique index is required to enforce unique constraints, even though duplicate checking itself is done on the heap.
Heap-Only Tuples
I mentioned HOT updates. As I've set a low fill factor and still have lots of free space in the block (as we confirm with ctid, all updates are within the same block), an update of a single indexed column may not add a new entry:
postgres=# UPDATE demo_unique_mvcc
SET payload = 'fourth version' --, marker = 2
WHERE email = 'a@example.com'
;
UPDATE 1
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 713 | (0,5) | 8450 | 16388
4 | 712 | 0 | (0,4) | 2050 | 4
5 | 713 | 0 | (0,5) | 10242 | 32772
(5 rows)
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,1) | (0,1) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,2) | (0,2) | t | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
3 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(3 rows)
The t_infomask2 added HEAP_ONLY_TUPLE (32768) to the number of attributes (4), indicating it has no index entry of its own. The entry pointing to (0,3) will follow the HOT chain to find the other version within the same page.
Deferred unique constraint
One last test. We have seen that a duplicate key insert leaves a tuple in the heap but no new entry in the index because the duplicate is detected during index update. However, if the unique constraint checking is deferred, the entry will be stored, and the detection happens at commit:
postgres=# ALTER TABLE demo_unique_mvcc
DROP CONSTRAINT demo_unique_mvcc_email_key
;
postgres=# ALTER TABLE demo_unique_mvcc
ADD CONSTRAINT demo_unique_mvcc_email_key
UNIQUE (email) DEFERRABLE;
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(1 row)
postgres=# BEGIN;
BEGIN
postgres=# SET CONSTRAINTS demo_unique_mvcc_email_key DEFERRED
;
SET CONSTRAINTS
postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
VALUES ('a@example.com', 'a duplicate row', 10);
;
INSERT 0 1
postgres=*# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 713 | (0,5) | 9474 | 16388
4 | 712 | 0 | (0,4) | 2562 | 4
5 | 713 | 0 | (0,5) | 10498 | 32772
6 | 718 | 0 | (0,6) | 2050 | 4
(6 rows)
postgres=*# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,6) | (0,6) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(2 rows)
postgres=*# COMMIT;
ERROR: duplicate key value violates unique constraint "demo_unique_mvcc_email_key"
DETAIL: Key (email)=(a@example.com) already exists.
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
2 | (0,6) | (0,6) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(2 rows)
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | 697 | 710 | (0,2) | 1282 | 4
2 | 710 | 711 | (0,3) | 9474 | 4
3 | 711 | 713 | (0,5) | 9474 | 16388
4 | 712 | 0 | (0,4) | 2562 | 4
5 | 713 | 0 | (0,5) | 10498 | 32772
6 | 718 | 0 | (0,6) | 2050 | 4
(6 rows)
Like with many databases, unique constraint violations should be exceptional rather than part of the normal execution path. Do not use insert-first-then-update for an upsert use case, as it will increase table and maybe also index bloat.
Garbage collection
For the demo, I disabled it, but the auto-vacuum runs in the background and cleans up old versions. I do the equivalent with a manual VACUUM:
postgres=# vacuum;
VACUUM
postgres=# SELECT itemoffset, ctid, htid, dead,
data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
FROM bt_page_items('demo_unique_mvcc_email_key', 1)
ORDER BY itemoffset
;
itemoffset | ctid | htid | dead | data | encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
1 | (0,3) | (0,3) | f | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
(1 row)
postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
ORDER BY lp
;
lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
1 | | | | |
2 | | | | |
3 | | | | |
4 | | | | |
5 | 713 | 0 | (0,5) | 10498 | 32772
(5 rows)
Only the current heap tuple and index entry remain.
Conclusion
PostgreSQL unique indexes are not physically unique as Oracle practitioners may expect. A unique B-tree can have multiple entries with the same key because index entries identify tuple versions, not logical rows. Under MVCC, several versions of the same row coexist, each requiring an index entry.
Uniqueness isn't determined by the index key alone. PostgreSQL matches index entries to the heap and uses visibility information (xmin, xmax, transaction status, HOT chains) to determine whether two rows appear as duplicates in the same snapshot. This preserves MVCC while guaranteeing logical uniqueness. Raw pages viewed with PageInspect show duplicate index entries as normal, but duplicate visible rows are not. The unique index serves as a rendezvous point for concurrent transactions, with the final decision determined by heap tuple visibility.
Failed duplicate inserts and deferred constraints can leave behind invisible heap tuples or index entries. Uniqueness depends on the index, MVCC visibility, and heap metadata, not just the index.
Although this might seem like a performance weakness compared to block-level MVCC and using the B-tree key itself to detect duplicate key violations, PostgreSQL's design brings a major advantage: extensibility. New data types, operators, and even entirely new index access methods can be added without changing the core database engine.
PostgreSQL ships with multiple built-in index types:
- B-tree for equality, ranges, ordering, and unique constraints
- Hash for equality lookups
- GiST for geometric, range, and extensible indexing
- SP-GiST for partitioned search structures such as tries and quadtrees
- GIN and RUM for inverted indexes on arrays, JSON documents, and full-text search
- BRIN for very large naturally ordered tables
- Bloom for space-efficient multi-column membership tests
Extensions further expand the indexing landscape:
- BM25 indexes (ParadeDB, pg_textsearch) for relevance-ranked lexical search
- IVFFlat/HNSW (pg_vector), and DiskANN indexes for vector embeddings and similarity search
- Extended RUM indexes in DocumentDB to support MongoDB-style multi-key indexing
Oracle preserves uniqueness within its index structure, suitable for a fixed set of index types. In contrast, PostgreSQL separates index navigation from tuple visibility, enabling any index access method to conduct uniqueness checks independently of MVCC. An open-source, extensible system faces different trade-offs than proprietary commercial software: it needs to keep its code simple, well-organized, and with a clear separation of concerns to encourage community contributions, rather than prioritizing revenue-driven use cases.
Top comments (0)