Postgres mvcc (Multi-Version Concurrency Control )vs undo in Oracle for consistency internal view

Both PostgreSQL and Oracle provide read consistency (consistent reads), but they implement it very differently.

Aspect	PostgreSQL (MVCC)	Oracle (Undo-based Consistency)
Old row versions	Stored directly in the table (tuple versions)	Stored in Undo/Rollback segments
UPDATE	Creates a new row version	Updates row in place and saves old image in Undo
Read consistency	Reader chooses visible tuple version based on transaction snapshot	Reader reconstructs old version from Undo if needed
VACUUM/cleanup	VACUUM removes dead tuples	Undo retention manages old versions
Disk usage	Table can grow due to multiple versions	Undo tablespace grows
Snapshot storage	Snapshot contains Transaction IDs (XIDs)	SCN (System Change Number) based

---

PostgreSQL MVCC

Suppose table contains:

ID  NAME

1   John

Transaction T1:

BEGIN;

UPDATE emp SET name='David' WHERE id=1;

What happens internally:

Old Tuple:

ID=1 NAME=John xmin=100 xmax=101

New Tuple:

ID=1 NAME=David xmin=101 xmax=NULL

PostgreSQL does not overwrite the row.

Instead:

+------------------+

| John (old row)   |

+------------------+

         |

         v

+------------------+

| David (new row)  |

+------------------+

A transaction started before T1 sees:

John

A transaction started after commit sees:

David

No undo lookup is required because both versions exist in the table.

Advantage

Reads are very fast:

Reader → Table Version → Done

No rollback segment access.

Disadvantage

Dead tuples accumulate:

John -> dead

David -> active

Requires:

VACUUM

to reclaim space.

---

Oracle Undo-Based Consistency

Same update:

UPDATE emp SET name='David' WHERE id=1;

Oracle performs:

1. Update row in table block.
2. Store previous image (John) in Undo segment.

TABLE BLOCK

------------

ID=1 David

UNDO SEGMENT

------------

Before Image = John

If another transaction started earlier wants the row:

Reader Snapshot SCN = 500

Current Row SCN = 550

Oracle detects:

Current row is newer than reader snapshot

Then it uses Undo:

Current Row + Undo Records

      ↓

Reconstruct old version

      ↓

John

Advantage

Table does not contain multiple row versions.

Disadvantage

Consistent reads may require:

Table Block

   +

Undo Block

   +

Undo Block

extra I/O.

---

Example Timeline

PostgreSQL

T1 starts

T1 updates John -> David

Table:

John (old version)

David (new version)

Reader R1 started before update:

Reads John

Reader R2 started after commit:

Reads David

Both versions coexist.

---

Oracle

T1 updates John -> David

Table:

David

Undo:

John

Reader before update:

Uses Undo

Returns John

Reader after update:

Returns David

---

Why Oracle Can Throw "Snapshot Too Old"

Oracle relies on Undo.

If long query needs an old version:

SCN = 1000

and Undo has already been overwritten:

ORA-01555: snapshot too old

This occurs because Oracle lost the history required to reconstruct the row.

---

Why PostgreSQL Usually Doesn't Have This Problem

PostgreSQL keeps old row versions in the table itself.

Long-running transaction:

Transaction started at 10 AM

Old tuples cannot be vacuumed until that transaction ends.

Result:

No snapshot too old error

but:

Table bloat increases

VACUUM delayed

Trade-off:

Oracle -> Undo growth problem

PostgreSQL -> Table bloat problem

---

Architecture View

PostgreSQL

Table

 ├── Version A

 ├── Version B

 └── Version C

Reader

   ↓

Chooses visible version

Oracle

Table

 └── Current Version

Undo

 ├── Previous Version A

 ├── Previous Version B

 └── Previous Version C

Reader

   ↓

Reconstructs required version

---

Interview Answer (Short)

PostgreSQL MVCC stores multiple row versions directly in the table and uses transaction snapshots (XIDs) to determine visibility. Updates create new tuples, and old tuples are later removed by VACUUM.

Oracle stores only the latest row version in the table and keeps old versions in Undo segments. Consistent reads are generated by reconstructing older versions using Undo records and SCNs.

In one line:

PostgreSQL achieves consistency by storing multiple row versions in the data table, while Oracle achieves consistency by reconstructing older versions from Undo segments when required.