Apa iku MVCC ing PostgreSQL?

Question

Accepted Answer

**MVCC** (Multi-Version Concurrency Control) yaiku carane PostgreSQL nangani akses bebarengan — tinimbang ngunci baris kanggo baca, iku tetep **macem-macem versi** saka baris, mula saben transaksi katon snapshot **konsisten** saka data. Keuntungan utama: **sing maca ora ngalangi sing nulis, lan sing nulis ora ngalangi sing maca**, mbukak concurrency dhuwur.

## Ide pokok: macem-macem versi baris

```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.
```

## Kauntungan gedhe: ora ana 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.
```

## Carane gawe (disederhanake)

```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 nglacak transaction IDs lan visibility baris supaya saben transaksi katon versi sing bener. Iki menehi **isolation** tanpa read locks.

## Biaya: dead tuples lan 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.
```

## Kanggo apa iki penting

MVCC yaiku dhasar kanggo paham perilaku concurrency lan performance PostgreSQL, mula iku ngewasa sing berharga kanggo makarya karo Postgres kanthi efektif.

Nilai inti sing MVCC warakno yaiku **concurrency dhuwur**: kanthi tetep macem-macem versi baris lan menehi saben transaksi snapshot konsisten, **sing maca ora ngalangi sing nulis lan sing nulis ora ngalangi sing maca** — iki kauntungan gedhe kanggo aplikasi nyata (laporan sing dowo-dowo ora ngalangi update, lan nulis ora ngalangi baca), mbukak concurrency luwih apik kanggo workload sing akeh baca lan campur tinimbang pendekatan berbasis kunci.

Mula iku PostgreSQL nangani akses bebarengan kanthi apik, lan paha MVCC njelaske karakteristik concurrency database (snapshot konsisten, isolation tanpa read locks, ora ana dirty reads).

Sang penting, paha MVCC uga njelaske **mengapa `VACUUM` penting ing Postgres**: MVCC nggawe versi baris sing ora ana ("dead tuples") nalika baris dipundhakke lan diilangi, sing kumpul minangka bloat, lan `VACUUM`/`AUTOVACUUM` yaiku mekanisme pembersih sing balekake ruang iki lan njaga database tetep sehat — mula MVCC lan VACUUM ana hubungan sing akrab, lan ngerti hubungan iki penting kanggo pemeliharaan lan performance database (nglirwakke vacuum ndadekake bloat lan performance nuruni, kangelan operasional nyata).
"). Amarga perilaku concurrency dasar ngaruhi carane database performa ing beban, lan amarga MVCC (model concurrency Postgres) njelaske rolas excellence read/write concurrency lan kabutuhan pemeliharaan VACUUM, paha MVCC yaiku ngewasa sing berharga kanggo makarya karo PostgreSQL — iku jelaske kauntungan concurrency database lan pemeliharaan sing dibutuhake, penting kanggo alasan performance lan ngopasi Postgres kanthi bener, lan topik umum sing ndudut paham carane Postgres nangani akses bebarengan.