PostgreSQL MVCC: qator versiyalari, xmin/xmax va VACUUM

Qisqacha: MVCC (Multi-Version Concurrency Control) — Postgres qatorni joyida o'zgartirmaydi: har UPDATE qatorning yangi versiyasini yozadi, eskisi sahifada qoladi. Har versiyada ikki belgi bor: xmin (uni yaratgan tranzaksiya) va xmax (uni o'chirgan tranzaksiya). Har tranzaksiya o'z snapshot'iga qarab "qaysi versiya menga ko'rinadi" deb hal qiladi — shuning uchun o'quvchi yozuvchini kutmaydi. Hech kimga kerak bo'lmay qolgan eski versiyalarni VACUUM tozalaydi; tozalanmasa jadval shishadi (kanon: 9 MB → 53 MB).

Bu darsda

• xmin, xmax va ctid yashirin ustunlarini o'qiy olasiz.
• Nega Postgres'da UPDATE = "eskisini o'chirildi deb belgilash + yangisini qo'shish" ekanini ko'rsatasiz.
• Snapshot qaysi versiyani ko'rishini qanday hal qilishini tushuntirasiz.
• Dead tuple, VACUUM va bloat orasidagi bog'liqlikni o'lchov bilan bilasiz.
• Uzoq ochiq tranzaksiya nega xavfli ekanini va HOT update nima ekanini aytasiz.

Oldin bilishingiz kerak: Read phenomena va izolyatsiya darajalari, Tranzaksiya asoslari, UPDATE va DELETE xavfsiz.

1. Nega bu kerak?

Oldingi darsda ikki g'alati narsani ko'rdik. Birinchisi: B qatorni o'zgartirib, hali COMMIT qilmagan — A esa shu qatorni kutmasdan o'qidi va eski qiymatni oldi. Ikkinchisi: REPEATABLE READ'da A bazada allaqachon 50 turganda ham 100 ni ko'rishda davom etdi.

Qanday qilib bitta qatorning bir vaqtda ikki qiymati bo'ladi? Javob: Postgres'da bir vaqtda bitta qatorning bir nechta versiyasi saqlanadi. Har tranzaksiya o'ziga mos versiyani oladi.

Hayotiy o'xshatish: maktab devoridagi dars jadvali. Direktor jadvalni o'zgartirmoqchi. Eski varaqni yirtib, ustiga chizib o'tirmaydi — yangi varaqni yoniga ilib qo'yadi va eskisiga "11-oktabrdan amalda emas" deb yozadi. Ertalab kelgan o'quvchi sanaga qarab, o'ziga tegishli varaqni o'qiydi. Eski varaq hech kimga kerak bo'lmay qolganda farrosh uni olib tashlaydi. Postgres'da varaq — qator versiyasi, sana — tranzaksiya raqami, farrosh — VACUUM.

Bu bilim amalda nima beradi? Uch narsa:

• Nega jadval o'chirmasangiz ham kattalashadi (bloat).
• Nega "unutilgan" ochiq tranzaksiya butun bazani sekinlashtiradi.
• Nega ko'p UPDATE qilinadigan jadvalga indeks qo'shish qimmat.

2. Yashirin ustunlar: ctid, xmin, xmax

2.1 Har jadvalda ko'rinmas ustunlar bor

SELECT * ularni ko'rsatmaydi, lekin nomini yozsangiz chiqadi:

• ctid — versiyaning jismoniy manzili: (sahifa, joy). (0,1) — 0-sahifa, 1-joy. Sahifa — 8 KB lik blok; Postgres jadvalni diskda shunday bloklar ketma-ketligi sifatida saqlaydi.
• xmin — shu versiyani yaratgan tranzaksiya raqami (txid).
• xmax — shu versiyani o'chirgan (yoki yangilagan) tranzaksiya raqami. 0 — hech kim o'chirmagan.

Tranzaksiya raqami (transaction ID, txid) — har yozuvchi tranzaksiyaga beriladigan o'sib boruvchi son. Uni pg_current_xact_id() ko'rsatadi.

Pastdagi natijalarni biz ishga tushirdik: PostgreSQL 17.6, toza sinov bazasi. Raqamlar (885, 886) sizda boshqacha bo'ladi — ular bazada shu paytgacha nechta tranzaksiya bo'lganiga bog'liq. Muhimi — ular orasidagi bog'liqlik. Kanon (PostgreSQL 18.6) xuddi shu tajribani bajargan va bir xil manzara olgan (t50-mvcc.txt).

CREATE TABLE taom (id int PRIMARY KEY, nom text)
  WITH (autovacuum_enabled = off);
BEGIN;
SELECT pg_current_xact_id();
INSERT INTO taom VALUES (1, 'osh'), (2, 'manti');
COMMIT;
SELECT ctid, xmin, xmax, * FROM taom;

Natija:

 pg_current_xact_id
--------------------
                885
(1 row)

 ctid  | xmin | xmax | id |  nom
-------+------+------+----+-------
 (0,1) |  885 |    0 |  1 | osh
 (0,2) |  885 |    0 |  2 | manti
(2 rows)

Ikkala qatorni 885-tranzaksiya yaratgan (xmin = 885), hech kim o'chirmagan (xmax = 0). Ular 0-sahifaning 1- va 2-joylarida.

WITH (autovacuum_enabled = off) — tajriba uchun: fonda ishlaydigan avtomatik tozalovchini shu jadvalda o'chirdik, aks holda u eski versiyalarni biz ko'rmasdan tozalab qo'yishi mumkin. Haqiqiy jadvalda buni qilmang.

2.2 UPDATE — yangi versiya

Endi bitta qatorni o'zgartiramiz:

UPDATE taom SET nom = 'shashlik' WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM taom;

Natija:

 ctid  | xmin | xmax | id |   nom
-------+------+------+----+----------
 (0,2) |  885 |    0 |  2 | manti
 (0,3) |  886 |    0 |  1 | shashlik
(2 rows)

Uchta o'zgarish ko'rinadi:

1. id = 1 endi (0,3) da — yangi joyda. Qator ko'chmadi; yangi versiya yozildi.
2. Uning xmin i — 886: yangi versiyani UPDATE tranzaksiyasi yaratdi.
3. Qatorlar tartibi o'zgardi — ORDER BY siz tartib kafolatlanmagan (Relatsion model darsidagi qoida — mana sababi).

Eski osh qayerda? SELECT uni ko'rsatmaydi. Sahifani ichidan ko'radigan pageinspect kengaytmasi (extension) bilan qaraymiz. U Postgres bilan birga keladi, faqat superuser ishlata oladi:

CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('taom', 0));

Natija:

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  1 |    885 |    886 | (0,3)
  2 |    885 |      0 | (0,2)
  3 |    886 |      0 | (0,3)
(3 rows)

Sahifada uchta versiya. 1-joydagi osh hali turibdi: uni 885 yaratgan, 886 "o'chirgan" (t_xmax = 886), va u keyingi versiyaga ko'rsatadi (t_ctid = (0,3)). Bu — versiyalar zanjiri.

flowchart LR
  V1["(0,1) osh<br/>xmin 885, xmax 886"] -->|t_ctid| V3["(0,3) shashlik<br/>xmin 886, xmax 0"]
  V2["(0,2) manti<br/>xmin 885, xmax 0"]

Demak Postgres'da UPDATE = eski versiyani "o'chirilgan" deb belgilash + yangi versiyani qo'shish. DELETE esa faqat birinchi yarmi: xmax qo'yiladi, versiya joyida qoladi. Hech narsa darhol o'chmaydi.

Tekshirib ko'ring: Nega ctid ni qatorning doimiy identifikatori sifatida ishlatib bo'lmaydi (masalan, API'da ?ctid=(0,3))?

3. Kim qaysi versiyani ko'radi

3.1 O'quvchi yozuvchini kutmaydi

A qatorni o'zgartiradi, lekin COMMIT qilmaydi. Shu payt B o'qiydi. Ikki terminal (Read phenomena va izolyatsiya darajalari darsidagidek):

Transkriptda B ning javobi qachon chiqqaniga va xmax ustuniga qarang:

A=> BEGIN;
A   BEGIN
A=> UPDATE taom SET nom = 'chuchvara' WHERE id = 2;
A   UPDATE 1
B=> SELECT ctid, xmin, xmax, * FROM taom WHERE id = 2;
B    ctid  | xmin | xmax | id |  nom
B   -------+------+------+----+-------
B    (0,2) |  885 |  887 |  2 | manti
B   (1 row)
A=> ROLLBACK;
A   ROLLBACK
B=> SELECT ctid, xmin, xmax, * FROM taom WHERE id = 2;
B    ctid  | xmin | xmax | id |  nom
B   -------+------+------+----+-------
B    (0,2) |  885 |  887 |  2 | manti
B   (1 row)

B darhol javob oldi — kutmadi. U eski versiyani (manti) ko'rdi. Uning xmax i 887 — A uni "o'chirdim" deb belgilagan. Lekin 887 hali tugamagan, shuning uchun B uchun bu belgi kuchsiz: versiya hali tirik.

A ROLLBACK qildi. Qiziq tomoni: xmax = 887 qoldi. Postgres belgini o'chirib yurmaydi. U shunchaki biladiki, 887-tranzaksiya bekor qilingan — demak uning xmax i hisobga olinmaydi. Har tranzaksiyaning yakuni (commit yoki abort) alohida joyda — commit jurnalida (pg_xact) saqlanadi.

chuchvara versiyasi-chi? U ham sahifada yozilgan va endi abadiy "o'lik": uni yaratgan tranzaksiya bekor bo'lgan. Sahifada endi to'rt versiya — ROLLBACK ham iz qoldiradi.

3.2 Ko'rinish qoidasi (soddalashtirilgan)

Har versiya uchun tranzaksiya o'z snapshot'iga qarab ikki savol beradi:

1. xmin — mening snapshot'imga ko'ra tugallangan va commit qilinganmi? Yo'q bo'lsa — versiya men uchun hali tug'ilmagan (o'zim yaratgan bo'lsam — ko'raman).
2. xmax — bo'sh yoki bekor qilingan yoki hali menga ko'rinmaydigan tranzaksiyami? Ha — versiya tirik. Yo'q (o'chirish commit qilingan va menga ko'rinadi) — versiya men uchun o'lgan.

Ikkalasi ham "ha" bo'lsa — versiya ko'rinadi. Har qator uchun ko'pi bilan bitta versiya mos keladi.

Snapshot esa — "qaysi tranzaksiyalar men uchun tugagan" ro'yxati. Uni pg_current_snapshot() ko'rsatadi, xmin:xmax:faollar ko'rinishida:

• xmin dan kichik txid'lar — hammasi tugagan (commit yoki abort — commit jurnalidan qaraladi);
• xmax va undan katta — hali boshlanmagan, ko'rinmaydi;
• oradagi faollar ro'yxati — snapshot olingan paytda hali ishlayotganlar, ko'rinmaydi.

(Snapshot'dagi xmin/xmax va qator versiyasidagi xmin/xmax — boshqa-boshqa narsa, nomi bir xil xolos.)

Izolyatsiya darajalari aynan shu yerda farqlanadi: READ COMMITTED har so'rov boshida yangi snapshot oladi, REPEATABLE READ — tranzaksiyaning birinchi so'rovida bitta oladi va oxirigacha ishlatadi.

3.3 Snapshot eski versiyani "ushlab" turadi

B REPEATABLE READ tranzaksiyasini ochib, o'qiydi. A esa qatorni yana o'zgartiradi va tozalovchini qo'lda chaqiradi:

Transkriptda B ning ikkinchi SELECT iga va oxirgi jadvaldagi bo'sh qatorlarga qarang:

B=> SELECT pg_current_snapshot(), nom FROM taom WHERE id = 1;
B    pg_current_snapshot |   nom
B   ---------------------+----------
B    888:888:            | shashlik
B   (1 row)
A=> UPDATE taom SET nom = 'somsa' WHERE id = 1;
A   UPDATE 1
B=> SELECT nom FROM taom WHERE id = 1;
B      nom
B   ----------
B    shashlik
B   (1 row)
A=> VACUUM taom;
A   VACUUM
A=> SELECT lp, t_xmin, t_xmax, t_ctid
A=> FROM heap_page_items(get_raw_page('taom', 0));
A    lp | t_xmin | t_xmax | t_ctid
A   ----+--------+--------+--------
A     1 |        |        |
A     2 |    885 |    887 | (0,4)
A     3 |    886 |    888 | (0,5)
A     4 |        |        |
A     5 |    888 |      0 | (0,5)
A   (5 rows)

B'ning snapshot'i 888:888: — "887 gacha hammasi tugagan, 888 va keyingilar men uchun yo'q". A'ning UPDATEi aynan 888 bo'ldi — B uni ko'rmaydi va shashlik da qoladi.

VACUUM nimani tozaladi? 1-joy (osh) va 4-joy (bekor qilingan chuchvara) — bo'shadi. Ular hech kimga kerak emas. Lekin 3-joydagi shashlik (xmax = 888) qoldi: B hali uni o'qishi mumkin. VACUUM faqat hamma ochiq snapshot'lar uchun o'lik bo'lgan versiyani o'chiradi.

B yopilgandan keyin:

-- B
COMMIT;
-- A
VACUUM taom;
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('taom', 0));

Natija:

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  1 |        |        |
  2 |    885 |    887 | (0,4)
  3 |        |        |
  4 |        |        |
  5 |    888 |      0 | (0,5)
(5 rows)

Endi shashlik ham ketdi. Sahifada ikki tirik versiya: manti (2-joy) va somsa (5-joy). Bo'shagan joylar (1, 3, 4) keyingi yozuvlar uchun qayta ishlatiladi.

Kanon xuddi shuni sanoq bilan ko'rsatgan: B ochiq paytda VACUUM'dan keyin sahifada 3 versiya, B yopilgandan keyin — 2 (t50-mvcc.txt).

Diqqat: Bu darsning eng muhim amaliy xulosasi: uzoq ochiq tranzaksiya VACUUM'ni to'xtatadi — faqat o'z jadvalida emas, butun bazada. Kimdir BEGIN deb, psql oynasini ochiq qoldirib tushlikka ketsa, shu vaqt ichida bazadagi hamma o'lik versiyalar to'planadi. pg_stat_activity dagi idle in transaction holatidagi ulanishlar — birinchi gumondor (Tranzaksiya asoslari darsidagi pool.query('BEGIN') tuzog'i aynan shunday ulanish qoldiradi).

4. Dead tuple, VACUUM va bloat

4.1 Atamalar

• Dead tuple (o'lik versiya) — hech bir tranzaksiyaga endi ko'rinmaydigan versiya. Har UPDATE va DELETE bittadan yaratadi.
• VACUUM — o'lik versiyalar joyini "bo'sh" deb belgilaydi. Fayl kichraymaydi, lekin keyingi yozuvlar shu joyga tushadi. Jadvalni qulflamaydi — o'qish va yozish davom etadi.
• Autovacuum — VACUUM'ni fonda avtomatik chaqiradigan jarayon. Sukut bo'yicha yoqilgan, jadvalda o'lik versiyalar ma'lum chegaradan oshganda ishga tushadi.
• VACUUM FULL — jadvalni yangi faylga qayta yozadi, fayl kichrayadi. Narxi: ishlash vaqtida jadval to'liq qulflanadi (ACCESS EXCLUSIVE — o'qish ham kutadi).
• Bloat (shishish) — jadval faylidagi bo'sh yoki o'lik joy. Ma'lumot kam, fayl katta.

4.2 Kanon o'lchovi

Kanon 100 000 qatorli jadvalni (autovacuum o'chiq) besh marta to'liq yangiladi (t50-mvcc.txt, PostgreSQL 18.6):

6) boshida: { jadval: '9096 kB', n_live_tup: '100000', n_dead_tup: '0' }
6) 5 × UPDATE hammasi: { jadval: '53 MB', n_live_tup: '100000', n_dead_tup: '499916' }
6) VACUUM (joy qayta ishlatiladi, fayl kichraymaydi): { jadval: '53 MB', n_live_tup: '100000', n_dead_tup: '0' }
6) yana 1 UPDATE (VACUUM bo'shatgan joyga): { jadval: '53 MB', n_live_tup: '100000', n_dead_tup: '100000' }
6) VACUUM FULL (qayta yoziladi, ACCESS EXCLUSIVE): { jadval: '9096 kB', n_live_tup: '100000', n_dead_tup: '0' }

Grafikda xuddi shu raqamlar (9 096 kB ≈ 8.9 MB):

Nimaga qarash kerak:

1. Ma'lumot o'zgarmadi — har doim 100 000 tirik qator. Fayl esa 6 barobar o'sdi: har UPDATE yangi versiya yozdi.
2. Oddiy VACUUM faylni kichraytirmadi (53 MB), lekin o'lik versiyalarni 0 ga tushirdi.
3. Keyingi UPDATE fayl hajmini oshirmadi — yangi versiyalar bo'shagan joyga tushdi. VACUUM'ning asl foydasi shu: o'sishni to'xtatish.
4. Faqat VACUUM FULL faylni boshlang'ich hajmga qaytardi.

Shuning uchun ishlab chiqarishda strategiya: autovacuum o'z vaqtida ishlasin (o'sishni to'xtatadi); VACUUM FULL — faqat favqulodda va texnik oynada. Autovacuum sozlamalari, pg_repack kabi qulfsiz muqobillar — VACUUM, autovacuum va bloat darsida (27-qism).

4.3 Indekslar ham versiya ko'radi

Har yangi versiya yangi joyda (ctid). Indeks esa qatorni ctid orqali topadi. Demak oddiy holda har UPDATE har indeksga ham yangi yozuv qo'shadi — hatto indekslangan ustun o'zgarmagan bo'lsa ham. Jadvalda 5 ta indeks bo'lsa, bitta UPDATE — 1 ta jadval yozuvi va 5 ta indeks yozuvi.

Bu narxni kamaytiradigan optimallashtirish bor — HOT (Heap-Only Tuple). Agar o'zgargan ustunlarning hech biri indeksda bo'lmasa va yangi versiya o'sha sahifaga sig'sa, Postgres indeksga tegmaydi. Indeks eski joyga ko'rsatadi, u yerdan versiyalar zanjiri (t_ctid) bo'ylab yangisiga o'tiladi.

Kanon o'lchovi (t50-mvcc.txt): sahifada 10 % bo'sh joy qoldirilgan jadvalda (fillfactor = 90) indekssiz ustunni 10 000 marta yangiladi va indeksli id ni 10 000 marta. Natija: 20 000 UPDATE'dan 1 152 tasi HOT bo'ldi. id UPDATE'lari HOT bo'lishi mumkin emas edi (indeksli ustun). Indekssiz ustun yangilanishlarining esa faqat bir qismi HOT bo'ldi — sahifalardagi bo'sh joy tez tugadi.

7) HOT: { n_tup_upd: '20000', n_tup_hot_upd: '1152' } (soni — indekssiz ustun: HOT; id — indeksli: HOT emas)

Amaliy xulosa: tez-tez yangilanadigan ustunga (masalan, izohlar_soni, oxirgi_kirish) keraksiz indeks qo'ymang — u HOT'ni o'chiradi. Indekslarni Indeks nima: B-tree ichidan darsidan boshlab o'rganamiz.

Tekshirib ko'ring: 1 mln qatorli jadvalda DELETE FROM jadval WHERE yaratilgan < '2025-01-01' 900 000 qatorni o'chirdi. Fayl hajmi qanday o'zgaradi? VACUUM'dan keyin-chi?

5. Txid tugashi haqida bir so'z

Txid — 32 bitli son, taxminan 4 mlrd qiymat. Postgres ularni aylana bo'ylab ishlatadi va eski versiyalarni vaqti-vaqti bilan muzlatadi (freeze) — "bu versiya hammaga ko'rinadi" deb belgilaydi, aniq raqam endi kerak emas. Buni ham VACUUM qiladi. Agar VACUUM uzoq vaqt ishlamasa, baza o'zini himoya qilish uchun yangi yozuvlarni to'xtatishi mumkin (wraparound himoyasi).

Kanon bazasida (t50-mvcc.txt): hozirgi txid 2646, eng eski muzlatilmagan tranzaksiya "yoshi" — 1901. Kichik baza uchun xavf yo'q. Kuzatish va oldini olish — VACUUM, autovacuum va bloat darsida. Hozircha bitta qoida: autovacuum'ni o'chirmang.

6. Boshqa bazalar qanday qiladi

MVCC'ni deyarli hamma zamonaviy bazalar ishlatadi, lekin boshqacha:

Postgres yondashuvining afzalligi — ROLLBACK bir zumda (hech narsa qaytarilmaydi, tranzaksiya "bekor" deb belgilanadi xolos). Kamchiligi — VACUUM va bloat. Boshqa bazalar bu kursda sinalmagan — jadval hujjatlar bo'yicha.

7. Node qadami: kanon tajribasi, loyihaga kirmaydi

vazifalar sxemasiga bu darsda o'zgarish yo'q. Lekin MVCC bilimi sxemadagi ikki qarorni tushuntiradi:

• izohlar_soni (counter cache, Denormalizatsiya) — har izoh qo'shilganda vazifa qatorining yangi versiyasi yoziladi. Faol muhokamali vazifa ko'p o'lik versiya qoldiradi. Bu ustunda indeks yo'q — shuning uchun bu UPDATE'lar HOT bo'lishi mumkin.
• yangilangan_vaqt va versiya trigger'lari — har tahrir baribir yangi versiya, ikki ustunni yangilash qo'shimcha versiya yaratmaydi (bitta UPDATE — bitta yangi versiya).

Kanon skripti ikki pg.Client bilan yuqoridagi hamma qadamlarni bajargan; uning sahifa qismini ko'rsatadigan joyi:

await A.query("UPDATE t50 SET matn = 'o''zgardi' WHERE id = 1");
await A.query("CREATE EXTENSION IF NOT EXISTS pageinspect");
const { rows } = await A.query(
  `SELECT lp AS joy, t_xmin AS xmin, t_xmax AS xmax,
          t_ctid AS keyingi
   FROM heap_page_items(get_raw_page('t50', 0))`);
console.log(rows);

Kanon chiqishi:

[
  { joy: 1, xmin: '2623', xmax: '2624', keyingi: '(0,3)' },
  { joy: 2, xmin: '2623', xmax: '0', keyingi: '(0,2)' },
  { joy: 3, xmin: '2624', xmax: '0', keyingi: '(0,3)' }
]

Diqqat: pg xid turini satr sifatida qaytaradi ('2623') — bigint kabi (node-postgres asoslari).

8. Ko'p uchraydigan xatolar

8.1 "DELETE qildim — joy bo'shadi"

Fayl hajmi o'zgarmaydi; VACUUM'dan keyin ham joy faqat qayta ishlatish uchun bo'shaydi. Diskni tejash kerak bo'lsa — VACUUM FULL (qulf bilan) yoki bo'laklash.

8.2 Autovacuum'ni "tezlik uchun" o'chirish

Bloat, sekinlashuv va oxir-oqibat txid himoyasi. Autovacuum yoqilgan qoladi; katta jadvallar uchun uning chegaralari sozlanadi (27-qism).

8.3 Ochiq qolgan tranzaksiya

idle in transaction ulanish soatlab tursa, VACUUM hech narsani tozalay olmaydi. finally { client.release() }, COMMIT/ROLLBACK har yo'lda; serverda idle_in_transaction_session_timeout sozlamasi.

8.4 ctid ni identifikator sifatida saqlash

U UPDATE va VACUUM FULL'da o'zgaradi. Birlamchi kalit.

8.5 "UPDATE bitta ustunni o'zgartirdi — arzon"

Postgres'da butun qator yangi versiya bo'ladi, indekslar ham (HOT bo'lmasa) yangilanadi. Katta, kam o'zgaradigan matnni tez-tez yangilanadigan hisoblagichdan ajratish yoki keraksiz indeksni olib tashlash haqida o'ylang.

9. Mashqlar

1-mashq (oson): Zanjirni o'qing

heap_page_items shunday natija berdi:

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  1 |    922 |    923 | (0,2)
  2 |    923 |    924 | (0,3)
  3 |    924 |      0 | (0,3)

(Biz ishga tushirdik, PostgreSQL 17.6, autovacuum o'chiq jadval.) Nechta qator bor? Qaysi versiya hozirgi? 922, 923, 924 tranzaksiyalar nima qilgan? Hammasi commit qilingan.

2-mashq (o'rta): Versiyalarni sanang

Bitta qatorli jadval yarating (autovacuum_enabled = off), qatorni uch marta yangilang. heap_page_items bilan sahifada nechta versiya borligini sanang (WHERE t_xmin IS NOT NULL), VACUUM qiling va qayta sanang. Natija uch qator bo'lsin: oldin: N, keyin: N, jadvalda: <narx>. Eslatma: matnni ulash — || (Ifodalar va funksiyalar).

DROP TABLE IF EXISTS m50_narx;
CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE TABLE m50_narx (id int PRIMARY KEY, narx int)
  WITH (autovacuum_enabled = off);
INSERT INTO m50_narx VALUES (1, 35000);
UPDATE m50_narx SET narx = 36000 WHERE id = 1;
UPDATE m50_narx SET narx = 37000 WHERE id = 1;
UPDATE m50_narx SET narx = 38000 WHERE id = 1;
-- t_xmin bo'sh bo'lmagan joylar — saqlangan versiyalar
SELECT 'oldin: ' || count(*)
FROM heap_page_items(get_raw_page('m50_narx', 0))
WHERE t_xmin IS NOT NULL;
VACUUM m50_narx;
SELECT 'keyin: ' || count(*)
FROM heap_page_items(get_raw_page('m50_narx', 0))
WHERE t_xmin IS NOT NULL;
SELECT 'jadvalda: ' || narx FROM m50_narx;
DROP TABLE m50_narx;

oldin: 4
keyin: 1
jadvalda: 38000

3-mashq (qiyin): Bloat'ni o'lchang

10 000 qatorli jadval yarating (id, soni int, matn — 50 ta x), autovacuum o'chiq. Hajmni (pg_size_pretty(pg_relation_size(...))) besh nuqtada chiqaring: boshida, 3 marta to'liq UPDATE'dan keyin, VACUUM'dan keyin, yana bitta UPDATE'dan keyin, VACUUM FULL'dan keyin. Natijani kanon o'lchovi bilan solishtiring: nisbatlar mos keladimi? Eslatma: generate_series (Ifodalar va funksiyalar), repeat('x', 50) — satrni takrorlash.

DROP TABLE IF EXISTS m50_bloat;
CREATE TABLE m50_bloat (id int PRIMARY KEY, soni int, matn text)
  WITH (autovacuum_enabled = off);
INSERT INTO m50_bloat
SELECT g, 0, repeat('x', 50) FROM generate_series(1, 10000) g;
SELECT 'boshida: '
  || pg_size_pretty(pg_relation_size('m50_bloat'));
UPDATE m50_bloat SET soni = soni + 1;
UPDATE m50_bloat SET soni = soni + 1;
UPDATE m50_bloat SET soni = soni + 1;
SELECT '3 x UPDATE: '
  || pg_size_pretty(pg_relation_size('m50_bloat'));
VACUUM m50_bloat;
SELECT 'VACUUM: '
  || pg_size_pretty(pg_relation_size('m50_bloat'));
UPDATE m50_bloat SET soni = soni + 1;
SELECT 'yana UPDATE: '
  || pg_size_pretty(pg_relation_size('m50_bloat'));
VACUUM FULL m50_bloat;
SELECT 'VACUUM FULL: '
  || pg_size_pretty(pg_relation_size('m50_bloat'));
DROP TABLE m50_bloat;

boshida: 912 kB
3 x UPDATE: 3632 kB
VACUUM: 3632 kB
yana UPDATE: 3632 kB
VACUUM FULL: 912 kB

10. Real ishda

• Monitoring: pg_stat_user_tables dagi n_dead_tup, last_autovacuum — DBA'ning kundalik ko'rsatkichlari. Ko'p o'lik versiyali jadval — autovacuum yetishmayapti degani.
• "Baza nega sekinlashdi?" — tez-tez javob: soatlab ochiq qolgan tranzaksiya yoki replikatsiya sloti VACUUM'ni to'xtatgan (Monitoring va favqulodda vaziyatlar).
• Navbat jadvallari (ko'p INSERT/UPDATE/DELETE) — eng ko'p bloat beradigan jadvallar; SKIP LOCKED bilan navbat darsida bunga qaytamiz.
• Intervyu: "Postgres'da UPDATE qanday ishlaydi?", "VACUUM nima uchun kerak?", "MVCC o'quvchini nega bloklamaydi?" — middle/senior backend savollari.

Xulosa

• Postgres qatorni joyida o'zgartirmaydi: har UPDATE yangi versiya (xmin — yaratgan, xmax — o'chirgan tranzaksiya).
• Tranzaksiya snapshot'iga qarab har qatorning bitta mos versiyasini ko'radi — o'quvchi yozuvchini kutmaydi.
• ROLLBACK hech narsani qaytarmaydi — tranzaksiya "bekor" deb belgilanadi, uning versiyalari o'lik bo'lib qoladi.
• VACUUM o'lik versiyalar joyini qayta ishlatishga beradi; faylni faqat VACUUM FULL kichraytiradi (qulf bilan).
• Uzoq ochiq tranzaksiya butun bazada tozalashni to'xtatadi — eng ko'p uchraydigan bloat sababi.
• Indekslangan ustun o'zgarmasa va sahifada joy bo'lsa — HOT update indeksga tegmaydi.

Keyingi dars: Qulflar: row, table va advisory — MVCC o'quvchi va yozuvchini ajratadi, lekin ikki yozuvchi baribir to'qnashadi; ularni qanday boshqarishni ko'ramiz.

Manbalar

• PostgreSQL 18 hujjati: Concurrency Control — Introduction (MVCC).
• PostgreSQL 18 hujjati: System Columns.
• PostgreSQL 18 hujjati: Routine Vacuuming.
• PostgreSQL 18 hujjati: pageinspect.