Menene MVCC a PostgreSQL?

Question

IT Interview Practice · Accepted Answer

**MVCC** (Multi-Version Concurrency Control) shi ne yadda PostgreSQL ke shakatawa da samun ajiyar aiki a gida — maimakon kumburin layuka don karantawa, yana adana **multi-versions" na layuka, saboda haka kowane ciniki yana ganin **snapshot** mai haske na bayanai. Babbar amfani: **marewa ba sa katse masu rubutawa, da masu rubutawa ba sa katse marewa**, yana ba da damar gaida ciniki.

## Shafin mahimmanci: multi-versions na layuka

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

## Babbar amfani: babu kumburin karantawa

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

## Yadda yake aiki (saukaita)

```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 yana bukatar ciniki IDs da jerin layuka saboda haka kowane ciniki ya ga daidai na version. Wannan yana ba da **karkita" babu kumburin karantawa.

## Tsada: dead tuples da 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.
```

## Me yasa ya zama mahimma

MVCC shine mahimmanci don fahimtar PostgreSQL concurrency halayen da aiki, saboda shi shine mahimmancin ilimi don aiki da Postgres daidai.

Asaliyas amfani MVCC ke ba dashi shi ne **high concurrency**: ta adana multi-versions na layuka da kuma ba da gida ciniki mai daidai snapshot, **marewa ba sa katse masu rubutawa da masu rubutawa ba sa katse marewa** — wanda babbar amfani ne para aiki na gaske (dogon lokaci rahoton bai katse buguwar baje-bajen, kuma rubutuwan ba sa katse karantawa), yana ba da damar kaida ciniki ta gaske don karantowa-heavy da gauraye aiki fiye da lock-based hanyoyi.

Wannan shine dalilin da PostgreSQL ke shakatawa da samun ajiyar aiki sannu-sannu, da fahimtar MVCC yana bayyana database concurrency characteristics (daidai snapshots, karkita without read locks, babu dirty reads).

Mahimmanci, fahimtar MVCC kuma yana bayyana **me yasa `VACUUM` ya zama mahimma a Postgres**: MVCC yana aiki obsolete row versions ("dead tuples") yayin da layuka ke buguwar da soke, wanda ya tsaya kamar bloat, da `VACUUM`/`AUTOVACUUM` shine tsarin tsara da ke sako this space da kuma kiyaye database kirji — saboda haka MVCC da VACUUM su kalibi, da sanin wannan jira mahimmanci ne don database maintenance da aiki (tsaya vacuum yana ba da bloat da tsarrin aiki, ainihi operational concern).

Tun da concurrency halayen fundamentally ya kawo yadda database ke aiki under load, da tun da MVCC (Postgres's concurrency model) yana bayyana duka kyakkyawan read/write concurrency da bukatuwar VACUUM maintenance, fahimtar MVCC mahimmancin ilimi ne don aiki da PostgreSQL — yana bayyana database's concurrency advantages da maintenance ya bukata, mahimmanci don duka tunani game da aiki da aiki Postgres daidai, da common topic wanda yana nuna fahimta ta yadda Postgres ke shakatawa da samun ajiyar aiki.

Shafin mahimmanci: multi-versions na layuka

Babbar amfani: babu kumburin karantawa

Yadda yake aiki (saukaita)

Tsada: dead tuples da VACUUM

Me yasa ya zama mahimma

Tambayoyi masu alaƙa