MVCC Di Postgres

🔹 1. Apa Itu MVCC?

MVCC adalah teknik yang memungkinkan PostgreSQL mengizinkan concurrent access dengan menyimpan beberapa versi data. Setiap transaksi melihat snapshot data yang konsisten sesuai waktu mulai transaksi.

Tujuan Utama:

• Isolasi Transaksi: Transaksi tidak saling melihat perubahan yang belum di-commit.

• Non-Blocking Reads: Operasi baca tidak memblokir operasi tulis, dan sebaliknya.

• Konsistensi: Setiap transaksi bekerja dengan data yang stabil selama eksekusi.

---

🔹 2. Konsep Dasar MVCC

Metadata pada Tuple:

• xmin: Transaction ID (XID) yang membuat tuple (INSERT/UPDATE).

• xmax: XID yang menghapus/mengunci tuple (DELETE/UPDATE).

• ctid: Identifier fisik lokasi tuple di heap.

• cmin/cmax: Command ID dalam transaksi (untuk operasi dalam transaksi yang sama).

Snapshot:

• Definisi: Snapshot adalah representasi keadaan database pada suatu waktu tertentu.

• Komponen:

  • Daftar XID transaksi yang sedang aktif (in-progress) saat snapshot diambil.

  • XID tertinggi yang dianggap sudah commit (latest completed XID).

• Pembuatan:

  • Read Committed: Snapshot baru diambil setiap pernyataan SQL.

  • Repeatable Read/Serializable: Snapshot diambil saat transaksi dimulai.

---

🔹 3. Algoritma Visibilitas Tuple

PostgreSQL menggunakan kombinasi XID, snapshot, dan CLOG (Commit Log) untuk menentukan visibilitas tuple.

Langkah Pengecekan:

1. Cek xmin:

   • Jika xmin belum commit (berada dalam daftar in-progress XID), tuple tidak terlihat.

   • Jika xmin sudah commit dan lebih kecil dari XID transaksi, lanjut ke xmax.

2. Cek xmax:

   • Jika xmax tidak ada (0) atau belum commit, tuple terlihat.

   • Jika xmax sudah commit dan lebih kecil dari XID transaksi, tuple tidak terlihat (sudah dihapus).

Contoh:

-- Transaksi A (XID=100)
BEGIN;
UPDATE users SET name = 'Alice' WHERE id = 1; -- xmin=100, xmax=0 (baru)

-- Transaksi B (XID=101)
BEGIN;
SELECT * FROM users WHERE id = 1; -- Masih melihat versi lama (xmax=100)

---

🔹 4. Snapshot Isolation vs Isolation Level

Isolation Level dan MVCC:

Level Isolasi	Mekanisme MVCC
Read Committed	Snapshot baru setiap query. Lihat perubahan yang sudah di-commit.
Repeatable Read	Snapshot diambil saat transaksi dimulai. Data tidak berubah selama transaksi.
Serializable	Menggunakan predikat lock untuk mencegah serialization anomalies.

Repeatable Read Example:

-- Transaksi A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM users; -- Snapshot diambil di sini.

-- Transaksi B
UPDATE users SET name = 'Bob' WHERE id = 1;
COMMIT;

-- Transaksi A (masih melihat data sebelum B commit)
SELECT * FROM users;

---

🔹 5. Transaction ID Wraparound

Masalah:

• XID di PostgreSQL adalah 32-bit (hingga ~4 miliar transaksi).

• Jika XID mencapai batas, akan terjadi wraparound (XID 0 dianggap lebih baru dari XID 4 miliar).

Solusi: Freezing:

• Tuple dengan XID sangat lama ditandai sebagai frozen (dianggap selalu valid).

• Proses VACUUM FREEZE akan mengubah xmin menjadi FrozenTransactionId untuk mencegah wraparound.

Monitoring:

• Cek pg_database.datfrozenxid untuk melihat XID tertua yang belum dibekukan.

• Jika age(datfrozenxid) > 2 miliar, muncul warning transaction ID wraparound.

---

🔹 6. HOT (Heap Only Tuple) Updates

Optimisasi untuk Mengurangi Bloat:

• Jika UPDATE tidak mengubah kolom yang di-index, tuple baru ditempatkan di halaman heap yang sama.

• Manfaat:

  • Tidak perlu update index (ctid lama mengarah ke tuple baru).

  • Mengurangi fragmentasi dan bloat.

• Syarat:

  • Tidak ada perubahan kolom yang di-index.

  • Ada ruang kosong di halaman heap.

---

🔹 7. Write Conflict dan Locking

Konflik UPDATE:

• Dua transaksi mencoba UPDATE baris yang sama: transaksi kedua akan menunggu (atau gagal jika menggunakan NOWAIT).

• PostgreSQL menggunakan row-level locks (FOR UPDATE) untuk menangani ini.

Contoh Deadlock:

-- Transaksi A
UPDATE users SET name = 'A' WHERE id = 1;
UPDATE users SET name = 'B' WHERE id = 2; -- Menunggu Transaksi B

-- Transaksi B
UPDATE users SET name = 'B' WHERE id = 2;
UPDATE users SET name = 'A' WHERE id = 1; -- Deadlock!

---

🔹 8. VACUUM dan Autovacuum

Tugas VACUUM:

• Menghapus dead tuples (versi data yang tidak terlihat oleh transaksi manapun).

• Membekukan XID lama (freeze).

• Update statistik untuk query planner.

Autovacuum Tuning:

-- Contoh konfigurasi di postgresql.conf:
autovacuum_vacuum_scale_factor = 0.2 -- Jalankan VACUUM jika 20% baris mati.
autovacuum_vacuum_cost_limit = 2000 -- Batas beban autovacuum.

---

🔹 9. Impact of Long-Running Transactions

Transaksi yang lama terbuka akan:

• Mencegah VACUUM menghapus dead tuples yang dibuat sebelum transaksi dimulai.

• Menyebabkan table bloat dan penurunan performa.

• Solusi: Monitor menggunakan query:

  SELECT pid, query, xact_start FROM pg_stat_activity 
  WHERE state = 'idle in transaction' OR xact_start < NOW() - INTERVAL '1 hour';

---

🔹 10. PostgreSQL vs Sistem Lain

Perbandingan MVCC:

Sistem	Implementasi MVCC	Kelebihan
PostgreSQL	Heap-based, menyimpan semua versi di tabel	Fleksibel, mudah di-tune.
MySQL InnoDB	Menggunakan undo logs di tablespace	Bloat lebih terkontrol.
Oracle	Undo segments + Redo logs	Optimized untuk high concurrency.

---

🔹 11. Diagram Alur MVCC

Berikut ilustrasi alur operasi UPDATE dengan MVCC:

1. Baris Lama: (xmin=100, xmax=0)
2. Transaksi XID=200 melakukan UPDATE:
   - Tandai baris lama dengan xmax=200.
   - Tambahkan baris baru dengan xmin=200.
3. Transaksi XID=300 membaca:
   - Lihat baris lama (xmax=200 belum commit) atau baru (xmin=200) tergantung snapshot.

---

🔹 12. Kesimpulan dan Best Practices

Best Practices:

• Hindari transaksi panjang yang memblokir VACUUM.

• Monitor autovacuum dan sesuaikan parameter sesuai beban.

• Gunakan REPEATABLE READ jika membutuhkan snapshot stabil.

• Optimalkan UPDATE dengan HOT (hindari mengubah kolom terindeks).

Peringatan Umum:

• Bloat: Selalu pantau n_dead_tups di pg_stat_all_tables.

• Wraparound: Pastikan autovacuum berjalan lancar.

• Lock Contention: Gunakan pg_lock untuk lacak konflik.

---

Dengan memahami MVCC secara mendalam, Anda bisa mengoptimalkan performa PostgreSQL dan menghindari masalah umum seperti bloat, lock contention, dan transaction wraparound.