Cad is ea MVCC i PostgreSQL?

Question

IT Interview Practice · Accepted Answer

**MVCC** (Multi-Version Concurrency Control) is how PostgreSQL handles concurrent access — instead of locking rows for reads, it keeps **multiple versions** of rows, so each transaction sees a consistent **snapshot** of the data. The key benefit: **readers don't block writers, and writers don't block readers**, enabling high concurrency.

## The core idea: multiple row versions

```text
When a row is UPDATED, Postgres doesn't overwrite it — it creates a NEW version and
marks the old one obsolete. Different transactions can see different versions.
→ Each transaction sees a consistent SNAPSHOT of the database as of its start
  (depending on isolation level), unaffected by others' uncommitted changes.
```

## The big benefit: no read locks

```text
Without MVCC (lock-based): readers and writers BLOCK each other
  → a long-running report could block writes; a write could block reads.
With MVCC:
  ✓ READERS DON'T BLOCK WRITERS — a SELECT sees an old version while an UPDATE proceeds
  ✓ WRITERS DON'T BLOCK READERS
  ✓ Each transaction sees a stable, consistent view (no dirty reads)
→ Much higher concurrency for read-heavy and mixed workloads.
```

## How it works (simplified)

```sql
-- Transaction A starts a long SELECT (sees a snapshot)
BEGIN; SELECT count(*) FROM orders;   -- sees data as of its snapshot

-- meanwhile Transaction B updates rows — creates new versions, doesn't block A
-- A still sees its consistent snapshot; B's changes are a new version
```

Postgres tracks transaction IDs and row visibility so each transaction sees the correct version. This provides **isolation** without read locks.

## The cost: dead tuples and VACUUM

```text
MVCC creates obsolete row versions ("dead tuples") that accumulate → bloat.
VACUUM (and AUTOVACUUM) reclaims this dead space and keeps the database healthy.
→ This is why VACUUM is important in Postgres — it's the cleanup for MVCC's versions.
```

## Cén fáth a bhfuil sé tábhachtach

MVCC is fundamental to understanding PostgreSQL's concurrency behavior and performance, so it's valuable knowledge for working with Postgres effectively.

The core value MVCC provides is **high concurrency**: by keeping multiple versions of rows and giving each transaction a consistent snapshot, **readers don't block writers and writers don't block readers** — which is a major advantage for real applications (a long-running report doesn't block updates, and writes don't block reads), enabling much better concurrency for read-heavy and mixed workloads than lock-based approaches.

This is why PostgreSQL handles concurrent access so well, and understanding MVCC explains the database's concurrency characteristics (consistent snapshots, isolation without read locks, no dirty reads).

Crucially, understanding MVCC also explains **why `VACUUM` is important in Postgres**: MVCC creates obsolete row versions ("dead tuples") as rows are updated and deleted, which accumulate as bloat, and `VACUUM`/`AUTOVACUUM` is the cleanup mechanism that reclaims this space and keeps the database healthy — so MVCC and VACUUM are intimately connected, and knowing this connection is important for database maintenance and performance (neglecting vacuum leads to bloat and degraded performance, a real operational concern).

Since concurrency behavior fundamentally affects how a database performs under load, and since MVCC (Postgres's concurrency model) explains both the excellent read/write concurrency and the need for VACUUM maintenance, understanding MVCC is valuable knowledge for working with PostgreSQL — it clarifies the database's concurrency advantages and the maintenance it requires, important for both reasoning about performance and operating Postgres correctly, and a common topic that demonstrates understanding of how Postgres handles concurrent access.

The core idea: multiple row versions

The big benefit: no read locks

How it works (simplified)

The cost: dead tuples and VACUUM

Cén fáth a bhfuil sé tábhachtach

Ceisteanna gaolmhara