Explain vacuum versus undo retention

Think of VACUUM in PostgreSQL and Undo Retention in Oracle as solving the same problem from opposite directions.

• PostgreSQL MVCC keeps old row versions in the table.
• Oracle keeps old row versions in Undo segments.

The challenge for both is:

"How long should old versions be kept for active transactions?"

---

PostgreSQL VACUUM

What happens after UPDATE?

UPDATE emp

SET salary = 10000

WHERE id = 1;

Internally:

Old tuple  -> Dead

New tuple  -> Live

Table becomes:

Row Version 1 (dead)

Row Version 2 (active)

The dead row is still present because some transaction might still need it.

VACUUM's Job

VACUUM:

1. Finds dead tuples.
2. Checks whether any transaction can still see them.
3. Removes them from future visibility.
4. Marks space reusable.

Before VACUUM

John(dead)

David(active)

After VACUUM

David(active)

---

Why VACUUM Cannot Remove Rows Immediately

Assume:

T1 starts at 10:00

T2 updates row at 10:01

T1 still sees the old row version.

If VACUUM deletes it:

T1 loses consistency

Therefore VACUUM waits until all interested transactions finish.

---

Problem: Table Bloat

If VACUUM cannot clean:

100 updates

=

100 dead versions

Table grows:

50 GB live data

150 GB dead tuples

This is called table bloat.

Common causes:

Long-running transactions

Replication slots

Idle-in-transaction sessions

---

Oracle Undo Retention

Oracle stores previous values in Undo segments.

Update:

UPDATE emp

SET salary = 10000

WHERE id = 1;

Results:

Table:

salary=10000

Undo:

salary=8000

---

Undo Retention Parameter

Oracle has:

UNDO_RETENTION = 3600

Meaning:

Try to keep undo data

for at least 3600 seconds

Not a guarantee in all configurations.

---

Why Oracle Needs Undo Retention

Suppose:

Query started at 10:00

Update occurs:

10:05

Oracle reconstructs old data using undo.

If undo gets overwritten before query finishes:

ORA-01555

snapshot too old

---

More Undo Retention

UNDO_RETENTION = 7200

Benefits:

✅ Long reports succeed

✅ Flashback queries succeed

✅ Long-running transactions remain consistent

Cost:

❌ Larger undo tablespace

---

Direct Comparison

Feature	PostgreSQL VACUUM	Oracle Undo Retention
Stores old versions where?	Table itself	Undo tablespace
Cleanup mechanism	VACUUM	Undo overwrite
What consumes space?	Dead tuples	Undo blocks
Long query impact	Prevents cleanup	Requires undo retention
Failure mode	Table/index bloat	ORA-01555
Space reclaimed by	VACUUM	Undo reuse
Admin tuning	autovacuum	undo_retention + undo size

---

Example: 1 Million Updates

PostgreSQL

1 million updates

Creates:

1 million dead tuples

Need:

VACUUM

otherwise:

Table size grows

Indexes grow

Performance drops

---

Oracle

1 million updates

Creates:

Undo records

Need:

Sufficient Undo Tablespace

otherwise:

ORA-01555

for long consistent reads.

---

DBA Perspective

PostgreSQL Monitoring

Check dead tuples:

SELECT relname,

       n_live_tup,

       n_dead_tup

FROM pg_stat_user_tables;

Check vacuum activity:

SELECT *

FROM pg_stat_progress_vacuum;

---

Oracle Monitoring

Check undo retention:

SHOW PARAMETER undo_retention;

Check undo statistics:

SELECT *

FROM v$undostat;

---

Interview Answer

VACUUM in PostgreSQL removes obsolete row versions created by MVCC after they are no longer needed by any transaction. Its goal is to prevent table and index bloat and reclaim space for reuse.

Undo Retention in Oracle controls how long old row images stored in Undo segments are preserved so Oracle can reconstruct historical versions for consistent reads and flashback operations.

In one sentence:

PostgreSQL keeps old versions in the table and later removes them using VACUUM, whereas Oracle keeps old versions in Undo and preserves them for a configured retention period before reusing the space.