PostgreSQL VACUUM va autovacuum: bloat, VACUUM FULL va wraparound

Qisqacha: Har UPDATE va DELETE eski qator versiyasini "o'lik" (dead tuple) qilib qoldiradi. Autovacuum jadvaldagi o'lik versiyalar 50 + 0.2 × qatorlar dan oshganda ularni avtomatik tozalaydi — joy qayta ishlatiladi, lekin fayl kichraymaydi. Faylni faqat VACUUM FULL kichraytiradi, u esa jadvalni to'liq qulflaydi (kanonda oddiy SELECT 1.5 soniya kutdi). VACUUM yana bir hayotiy ishni qiladi — eski tranzaksiya raqamlarini "muzlatadi"; bu to'xtasa, baza wraparound himoyasi bilan yozishni to'xtatishi mumkin.

Bu darsda

• Autovacuum qachon ishga tushishini formuladan hisoblaysiz va jadval darajasida sozlaysiz.
• VACUUM (VERBOSE) chiqishini o'qiysiz.
• Bloat'ni pgstattuple va statistikadan o'lchaysiz, ikki manba nega farq qilishini tushuntirasiz.
• VACUUM FULL, REINDEX CONCURRENTLY va pg_repack ni qachon ishlatishni bilasiz.
• Transaction ID wraparound xavfini va uning kuzatuvini bilasiz.

Oldin bilishingiz kerak: MVCC: Postgres ichida versiyalar, Postgres arxitekturasi va asosiy sozlamalar, Extension'lar.

1. Nega bu kerak?

MVCC darsida ko'rdik: Postgres qatorni joyida o'zgartirmaydi, har UPDATE yangi versiya yozadi, eskisi sahifada qoladi. Kanon u yerda 100 000 qatorli jadvalni besh marta yangilab, 9 MB dan 53 MB ga o'stirgan edi. "Tozalovchi"ni o'shanda tanishtirgan edik — endi uni boshqarishni o'rganamiz.

O'xshatish: ko'p qavatli uydagi chiqindi. Har oila (tranzaksiya) chiqindi chiqaradi. Farrosh (autovacuum) muntazam yig'ib turadi — yo'laklar toza, lekin bino kichraymaydi. Farrosh kelmay qo'ysa — avval hid (sekinlashuv), keyin yo'lak to'ladi (bloat), oxirida uy boshqarmasi kirishni yopib qo'yadi (wraparound himoyasi).

Bu darsdagi so'rovlarni biz ishga tushirdik: PostgreSQL 17.6, yangi bo'sh baza. Kanon (PostgreSQL 18.6) xuddi shu tajribani bajargan (kanon o'lchovi, IlmHamroh, 2026-10); hajmlar va pgstattuple natijalari bir xil chiqdi, statistika hisoblagichlari va versiyaga xos qatorlar farq qiladi — ular alohida belgilangan.

2. Autovacuum qachon ishlaydi

2.1 Sozlamalar

SHOW autovacuum;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_vacuum_insert_scale_factor;

 autovacuum
------------
 on
(1 row)

 autovacuum_naptime
--------------------
 1min
(1 row)

 autovacuum_vacuum_threshold
-----------------------------
 50
(1 row)

 autovacuum_vacuum_scale_factor
--------------------------------
 0.2
(1 row)

 autovacuum_vacuum_insert_scale_factor
---------------------------------------
 0.2
(1 row)

• autovacuum_naptime — launcher har daqiqada bazalarni ko'zdan kechiradi.
• threshold + scale_factor — jadvalni tozalash chegarasi:

chegara = autovacuum_vacuum_threshold
        + autovacuum_vacuum_scale_factor × jadvaldagi qatorlar

O'lik versiyalar shundan oshsa — autovacuum ishchisi (worker) shu jadvalga keladi.

• insert_scale_factor (PG 13+) — faqat INSERT bo'ladigan jadvallar uchun (log, voqealar): u yerda o'lik versiya yo'q, lekin sahifalarni "hammaga ko'rinadi" va "muzlatilgan" deb belgilash kerak (pastda).

PostgreSQL 18 yana bitta sozlama qo'shgan — kanon chiqishi:

 autovacuum_vacuum_max_threshold
---------------------------------
 100000000
(1 row)

Bizning PG 17 da bu buyruq unrecognized configuration parameter "autovacuum_vacuum_max_threshold" beradi. Nega kerak: 1 mlrd qatorli jadvalda 0.2 × qatorlar — 200 mln o'lik versiya; shuncha kutish — juda katta bloat. PG 18 chegarani 100 mln bilan cheklaydi.

2.2 Tajriba jadvali

200 000 qatorli jadval, autovacuum o'chiq — tozalashni o'zimiz boshqarish uchun (haqiqiy jadvalda buni qilmang; MVCC darsidagi kabi):

CREATE EXTENSION pgstattuple;
CREATE TABLE t13 (id int PRIMARY KEY, matn text,
  bajarildi boolean DEFAULT false)
  WITH (autovacuum_enabled = false);
INSERT INTO t13 SELECT g, 'Vazifa ' || g, false
FROM generate_series(1, 200000) g;
VACUUM ANALYZE t13;
SELECT pg_size_pretty(pg_table_size('t13')) AS hajm;

CREATE EXTENSION
CREATE TABLE
INSERT 0 200000
VACUUM
   hajm
----------
 10224 kB
(1 row)

pgstattuple — jadval faylini to'liq o'qib, tirik, o'lik qatorlar va bo'sh joyni sanaydigan extension (Postgres bilan birga keladi). Endi ikki UPDATE — yarim va chorak qatorlar:

UPDATE t13 SET bajarildi = true WHERE id % 2 = 0;
UPDATE t13 SET matn = matn || ' (tahrir)' WHERE id % 4 = 0;
SELECT pg_size_pretty(pg_table_size('t13')) AS hajm,
       tuple_count, dead_tuple_count,
       round(dead_tuple_percent::numeric, 1) AS dead_foiz,
       round(free_percent::numeric, 1) AS bosh_foiz
FROM pgstattuple('t13');

UPDATE 100000
UPDATE 50000
 hajm  | tuple_count | dead_tuple_count | dead_foiz | bosh_foiz
-------+-------------+------------------+-----------+-----------
 18 MB |      200000 |            50000 |      11.4 |      25.7
(1 row)

Ma'lumot o'zgarmadi (200 000 qator), fayl 10 MB → 18 MB. Kanonda aynan shu (bo'sh joy 25.8 %).

2.3 Ikki manba, ikki raqam

150 000 ta UPDATE bo'ldi. Lekin pgstattuple faqat 50 000 o'lik versiya topdi. Kanon xuddi shu payt statistikadan ham so'ragan:

SELECT pg_size_pretty(pg_table_size('t13')) AS hajm,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables WHERE relname = 't13';

Kanon chiqishi (PostgreSQL 18.6):

 hajm  | n_live_tup | n_dead_tup
-------+------------+------------
 18 MB |     400000 |     100000
(1 row)

Uch xil raqam (150 000, 100 000, 50 000) — uchalasi ham "to'g'ri":

• 150 000 — yaratilgan o'lik versiyalar jami.
• pgstattuple 50 000 — hozir faylda haqiqatan turganlari. Qolganini HOT pruning allaqachon tozalagan: UPDATE sahifaga kirganda o'sha sahifadagi eski versiyalarni o'zi bo'shatadi (MVCC darsidagi HOT).
• n_dead_tup 100 000 — statistika hisoblagichi: taxminiy, kechikib yangilanadi. n_live_tup 400 000 ham noto'g'ri (haqiqatda 200 000). Bizning PG 17 da xuddi shu so'rov 200000 | 100000 berdi — hisoblagich versiya va vaqtga bog'liq.

Amaliy xulosa: monitoring uchun pg_stat_user_tables (arzon, taxminiy), aniq o'lchov uchun pgstattuple (butun faylni o'qiydi — katta jadvalda qimmat; pgstattuple_approx — tezroq taxmin).

2.4 Chegara

Bu jadval uchun autovacuum qachon ishlardi:

SELECT 50 + 0.2 * reltuples AS autovacuum_chegarasi
FROM pg_class WHERE relname = 't13';

 autovacuum_chegarasi
----------------------
                40050
(1 row)

reltuples — planner statistikasidagi qatorlar soni. 40 050 dan ko'p o'lik versiya — tozalash. Bizda 100 000 (hisoblagich bo'yicha) — autovacuum yoqilgan bo'lsa, allaqachon ishlagan bo'lardi.

Qaror qanday qabul qilinishi diagrammada:

flowchart TD
  L["autovacuum launcher<br/>har naptime (1 min)"] --> T{"n_dead_tup ><br/>50 + 0.2 × qatorlar?"}
  T -->|ha| W["worker: VACUUM"]
  T -->|yo'q| I{"INSERT'lar ><br/>chegara?"}
  I -->|ha| W
  I -->|yo'q| A{"XID yoshi ><br/>freeze_max_age?"}
  A -->|ha| F["majburiy VACUUM<br/>(wraparound)"]
  A -->|yo'q| K["kutish"]

Tekshirib ko'ring: 50 mln qatorli buyurtmalar jadvali. Sukut sozlamada autovacuum nechta o'lik versiyadan keyin ishlaydi? Bu yaxshimi?

3. VACUUM (VERBOSE) — ichini ko'ramiz

Qo'lda VACUUM, batafsil hisobot bilan. Kanon chiqishi (PostgreSQL 18.6; asosiy jadval qismi). Chiqish uzun — hammasini yodlash shart emas. Ikki qatorga qarang: tuples: (nechta tozalandi, nechtasi to'sildi) va pages: (fayl qisqardimi):

VACUUM (VERBOSE) t13;

INFO:  vacuuming "tajriba.public.t13"
INFO:  finished vacuuming "tajriba.public.t13": index scans: 1
pages: 0 removed, 2277 remain, 2277 scanned (100.00% of total), 0 eagerly scanned
tuples: 50000 removed, 200000 remain, 0 are dead but not yet removable
removable cutoff: 50000, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 2277 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan needed: 1910 pages from table (83.88% of total) had 149993 dead item identifiers removed
index "t13_pkey": pages: 1099 in total, 0 newly deleted, 0 currently deleted, 0 reusable
WAL usage: 5920 records, 0 full page images, 1046838 bytes, 0 buffers full
system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.03 s

Qatorma-qator eng muhimlari:

0 are dead but not yet removable — eng muhim diagnostika qatori. Agar u katta bo'lsa — kimdir uzun tranzaksiya ochib qo'ygan va VACUUM o'lik versiyalarni olib tashlay olmayapti (MVCC darsidagi ogohlantirish). Bizning PG 17 chiqishi deyarli bir xil, faqat 0 eagerly scanned (PG 18 yangiligi), visibility map va I/O timings qatorlari yo'q.

VACUUM'dan keyin:

VACUUM t13;
SELECT pg_size_pretty(pg_table_size('t13')) AS hajm,
       tuple_count, dead_tuple_count,
       round(free_percent::numeric, 1) AS bosh_foiz
FROM pgstattuple('t13');

VACUUM
 hajm  | tuple_count | dead_tuple_count | bosh_foiz
-------+-------------+------------------+-----------
 18 MB |      200000 |                0 |      38.6
(1 row)

O'lik versiyalar 0, fayl hamon 18 MB — 38.6 % bo'sh joy. Bu bo'sh joy yo'qolmadi — qayta ishlatiladi:

UPDATE t13 SET bajarildi = false WHERE id % 2 = 0;
SELECT pg_size_pretty(pg_table_size('t13')) AS qayta_ishlatildi;

UPDATE 100000
 qayta_ishlatildi
------------------
 18 MB
(1 row)

100 000 yangi versiya yozildi, fayl o'smadi. Autovacuum'ning asl vazifasi shu: faylni kichraytirish emas, o'sishni to'xtatish.

4. Bloat'dan qutulish

4.1 VACUUM FULL va uning qulfi

VACUUM FULL t13;
SELECT pg_size_pretty(pg_table_size('t13')) AS full_dan_keyin;

VACUUM
 full_dan_keyin
----------------
 10 MB
(1 row)

Fayl boshlang'ich hajmga qaytdi: VACUUM FULL jadvalni yangi faylga qayta yozadi (indekslarni ham). Narxi — AccessExclusiveLock: ishlash davomida jadvalga hech kim — hatto oddiy SELECT ham — tegolmaydi. Kanon buni Monitoring va favqulodda vaziyatlar tajribasida o'lchagan: 300 000 qatorli jadvalda VACUUM FULL 2 497 ms ishladi, shu paytda kelgan oddiy SELECT 1 459 ms kutdi, pg_locks da:

        mode         | granted
---------------------+---------
 AccessExclusiveLock | t
(1 row)

Kichik jadvalda 1.5 soniya. 50 GB jadvalda — daqiqalar yoki soatlar, butun ilova to'xtaydi. Shuning uchun VACUUM FULL — faqat texnik oynada (yoki hech qachon). Qo'shimcha talab: qayta yozish uchun jadval hajmicha bo'sh disk kerak.

Grafikda hajm qanday o'zgargani (yuqoridagi so'rovlar natijasi):

4.2 Indeks bloat va REINDEX CONCURRENTLY

Indekslar ham shishadi — va VACUUM ularni ham kichraytirmaydi. Indeksli ustunni yangilaymiz (HOT bo'lmaydi):

CREATE TABLE t13i (id int PRIMARY KEY, n int)
  WITH (autovacuum_enabled = false);
INSERT INTO t13i SELECT g, 0 FROM generate_series(1, 200000) g;
SELECT pg_size_pretty(pg_relation_size('t13i_pkey')) AS boshida;
UPDATE t13i SET id = id + 1000000;
VACUUM t13i;
SELECT pg_size_pretty(pg_relation_size('t13i_pkey'))
         AS yangilashdan_keyin;
REINDEX INDEX CONCURRENTLY t13i_pkey;
SELECT pg_size_pretty(pg_relation_size('t13i_pkey'))
         AS reindexdan_keyin;
DROP TABLE t13i;

CREATE TABLE
INSERT 0 200000
 boshida
---------
 4408 kB
(1 row)

UPDATE 200000
VACUUM
 yangilashdan_keyin
--------------------
 8792 kB
(1 row)

REINDEX
 reindexdan_keyin
------------------
 4408 kB
(1 row)

DROP TABLE

Indeks ikki barobar o'sdi va VACUUM'dan keyin ham shunday qoldi. REINDEX … CONCURRENTLY (PG 12+) indeksni yonida qayta quradi va almashtiradi — o'qish va yozish to'xtamaydi (CREATE INDEX CONCURRENTLY kabi, Partial, expression va covering indekslar). Tranzaksiya ichida ishlamaydi va oddiy REINDEX'dan sekinroq — lekin ilova ishlayveradi.

4.3 pg_repack — jadval uchun "CONCURRENTLY"

Jadvalni qulfsiz qayta yozish uchun Postgres'ning o'zida buyruq yo'q. pg_repack — tashqi extension + vosita: jadval nusxasini yonida quradi, o'zgarishlarni trigger bilan yig'adi va oxirida qisqa qulf bilan almashtiradi. Kanon image'ida yo'q — bu kursda sinalmagan, hujjat bo'yicha. Ko'p managed bazalarda mavjud. Muqobil va ko'pincha yaxshiroq yo'l — bloat'ga yo'l qo'ymaslik: autovacuum'ni to'g'ri sozlash va katta "tarix" jadvallarini bo'laklash (partitioning — jadvalni oylar kabi qismlarga bo'lib saqlash; 31-qismdagi Partitioning darsida, hozir bilish shart emas).

5. Autovacuum'ni sozlash

5.1 Jadval darajasida

Global sozlamani o'zgartirish — hamma jadvalga ta'sir qiladi. Ko'pincha muammo bitta-ikkita katta, ko'p yangilanadigan jadvalda. Ularga alohida:

ALTER TABLE t13 SET (autovacuum_enabled = true,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000);
SELECT relname, reloptions FROM pg_class WHERE relname = 't13';

ALTER TABLE
 relname |                                           reloptions
---------+------------------------------------------------------------------------------------------------
 t13     | {autovacuum_enabled=true,autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=1000}
(1 row)

Endi chegara 1000 + 0.01 × 200 000 = 3 000 — tez-tez, lekin kichik tozalashlar.

5.2 Boshqa tutqichlar

Hozir ishlayotgan VACUUM jarayonini pg_stat_progress_vacuum ko'rsatadi (qaysi bosqichda, nechta sahifa skanlangan).

Tekshirib ko'ring: Autovacuum "juda ko'p disk ishlatyapti" deb, kimdir uni global o'chirdi. Bir haftadan keyin nima bo'ladi?

6. Transaction ID wraparound

6.1 Muammo

Tranzaksiya raqami (XID) — 32 bitli: ~4.2 mlrd qiymat, aylana bo'ylab. Har versiya uchun "o'tmishmi yoki kelajakmi" savoli — raqamlar farqi bilan, ~2.1 mlrd (2³¹) oralig'ida hal qilinadi. Juda eski versiya to'satdan "kelajakdagi" bo'lib ko'rinmasligi uchun VACUUM uni muzlatadi (freeze) — "bu versiya hammaga ko'rinadi, raqami endi muhim emas" deb belgilaydi.

Bazaning eng eski muzlatilmagan XID "yoshi" — age(datfrozenxid). Kanon bazasi:

SELECT datname, age(datfrozenxid) AS yosh,
       round(100.0 * age(datfrozenxid) / 2147483648, 4) AS foiz
FROM pg_database ORDER BY 2 DESC;

Kanon chiqishi (qisqartirilgan):

  datname  | yosh  |  foiz
-----------+-------+--------
 postgres  | 49258 | 0.0023
 tajriba   | 49258 | 0.0023
 vazifalar | 49258 | 0.0023

0.0023 % — xavf yo'q. Bizning yangi klasterda yosh atigi 161 edi. Chegaralar:

• autovacuum_freeze_max_age (200 mln) — shundan oshsa, autovacuum jadvalni majburan tozalaydi (o'chirilgan bo'lsa ham).
• ~2.1 mlrd ga yaqinlashsa — Postgres avval ogohlantiradi (WARNING: database "…" must be vacuumed within … transactions), keyin yangi XID berishni to'xtatadi: baza faqat o'qish uchun, yozish yo'q. Tiklash — uzoq qo'lda VACUUM. Bu — eng og'ir Postgres avariyalaridan biri.

6.2 Freeze qo'lda

VACUUM (FREEZE) t13;
SELECT age(relfrozenxid) AS freezedan_keyin
FROM pg_class WHERE relname = 't13';

VACUUM
 freezedan_keyin
-----------------
               0
(1 row)

Jadval to'liq muzlatildi — yoshi 0. Real hayotda buni autovacuum o'zi qiladi; qo'lda — masalan, katta ma'lumot yuklagandan keyin (keyinroq hamma qator bir vaqtda muzlatilishi kerak bo'lib, kutilmagan "vacuum bo'roni" bo'lmasligi uchun).

6.3 Kim to'xtatadi

Wraparound sodir bo'lgan voqealarda deyarli har doim sabab bir xil — VACUUM ishlay olmagan:

• soatlab (kunlab) ochiq qolgan tranzaksiya yoki idle in transaction ulanish;
• tashlab ketilgan replikatsiya sloti yoki tayyorlangan (prepared) tranzaksiya;
• autovacuum juda sekin sozlangan, katta jadvalni tugata olmayapti.

Kuzatuv: age(datfrozenxid) ni monitoringga qo'shing va masalan 50 % da ogohlantirish bering (Monitoring va favqulodda vaziyatlar).

7. Kuzatuv so'rovi

Har kuni ko'rish arziydigan so'rov — kanon naqshi:

SELECT relname, n_live_tup, n_dead_tup,
       last_vacuum IS NOT NULL AS vacuum_bolgan,
       last_autovacuum IS NOT NULL AS autovacuum_bolgan,
       autovacuum_count
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

Eng ko'p o'lik versiyali jadvallar, ularni oxirgi marta kim (qo'lda yoki autovacuum) tozalagan. n_dead_tup doim katta, last_autovacuum esa eski bo'lsa — autovacuum shu jadvalga ulgurmayapti yoki nimadir uni to'sib turibdi.

8. Ko'p uchraydigan xatolar

8.1 Autovacuum'ni o'chirish

Bloat, eski statistika, wraparound. Yoqilgan; katta jadvallar uchun jadval darajasida tezlashtirish.

8.2 Bloat'ga qarshi muntazam VACUUM FULL

Har safar jadval qulflanadi. Autovacuum sozlash; kerak bo'lsa pg_repack yoki REINDEX CONCURRENTLY.

8.3 n_dead_tup ni aniq raqam deb bilish

Hisoblagich — taxmin. Aniq o'lchov — pgstattuple.

8.4 Uzun ochiq tranzaksiyalar

dead but not yet removable o'sadi. idle_in_transaction_session_timeout, kodda finally { client.release() }.

8.5 "DELETE qildim — disk bo'shaydi"

Fayl kichraymaydi. Ommaviy tozalashni partiyalab (Funksiyalar va PL/pgSQL darsidagi protsedura), katta tarix — bo'laklash.

9. Mashqlar

1-mashq (oson): Chegara kalkulyatori

pg_settings dan autovacuum_vacuum_threshold va autovacuum_vacuum_scale_factor ni oling va 10 000, 1 000 000 va 50 000 000 qatorli jadvallar uchun autovacuum chegarasini chiqaring (qatorlar: chegara). Ishora: unnest(ARRAY[…]) va current_setting(…)::numeric.

SELECT n || ': ' || round(
         current_setting('autovacuum_vacuum_threshold')::numeric
         + current_setting('autovacuum_vacuum_scale_factor')::numeric
           * n)
FROM unnest(ARRAY[10000, 1000000, 50000000]) AS n;

10000: 2050
1000000: 200050
50000000: 10000050

2-mashq (o'rta): Bloat'ni o'lchang

m13_eslatma (id int PRIMARY KEY, matn text) jadvalini autovacuum o'chiq yarating. Unga 50 000 qator qo'shing ('Eslatma ' || g), keyin hamma qatorni bir marta yangilang (matn = matn || '!'). pgstattuple bilan uch nuqtada tuple_count va dead_tuple_count ni chiqaring: UPDATE dan keyin, VACUUM dan keyin va VACUUM FULL dan keyin hajm bilan birga. Kengaytma: CREATE EXTENSION IF NOT EXISTS pgstattuple.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE TABLE m13_eslatma (id int PRIMARY KEY, matn text)
  WITH (autovacuum_enabled = false);
INSERT INTO m13_eslatma SELECT g, 'Eslatma ' || g
FROM generate_series(1, 50000) g;
UPDATE m13_eslatma SET matn = matn || '!';
SELECT 'update: ' || tuple_count || ' tirik, '
       || dead_tuple_count || ' olik, '
       || pg_size_pretty(pg_table_size('m13_eslatma'))
FROM pgstattuple('m13_eslatma');
VACUUM m13_eslatma;
SELECT 'vacuum: ' || tuple_count || ' tirik, '
       || dead_tuple_count || ' olik, '
       || pg_size_pretty(pg_table_size('m13_eslatma'))
FROM pgstattuple('m13_eslatma');
VACUUM FULL m13_eslatma;
SELECT 'full: ' || tuple_count || ' tirik, '
       || dead_tuple_count || ' olik, '
       || pg_size_pretty(pg_table_size('m13_eslatma'))
FROM pgstattuple('m13_eslatma');
DROP TABLE m13_eslatma;

update: 50000 tirik, 50000 olik, 5120 kB
vacuum: 50000 tirik, 0 olik, 5128 kB
full: 50000 tirik, 0 olik, 2560 kB

3-mashq (qiyin): Uzun tranzaksiya VACUUM'ni to'sadi

Ikki psql terminali kerak. A terminalda BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM m13_t; (ochiq qoldiring). B terminalda m13_t dagi hamma qatorni yangilang va VACUUM (VERBOSE) m13_t; qiling. tuples: qatoridagi … are dead but not yet removable qiymatini yozing. Keyin A da COMMIT, B da yana VACUUM (VERBOSE). Jadval: m13_t (id int), 1 000 qator, autovacuum o'chiq.

-- tayyorlash (istalgan terminal)
CREATE TABLE m13_t (id int) WITH (autovacuum_enabled = false);
INSERT INTO m13_t SELECT g FROM generate_series(1, 1000) g;
-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM m13_t;
-- B
UPDATE m13_t SET id = id + 1;
VACUUM (VERBOSE) m13_t;
-- A
COMMIT;
-- B
VACUUM (VERBOSE) m13_t;
DROP TABLE m13_t;

tuples: 0 removed, 2000 remain, 1000 are dead but not yet removable
tuples: 1000 removed, 1000 remain, 0 are dead but not yet removable

10. Loyiha qadami: kanon tajribasi, loyihaga kirmaydi

vazifalar sxemasiga o'zgarish yo'q — tajriba alohida tajriba bazasida, t13 jadvali oxirida o'chirildi. Loyiha uchun xulosalar:

• vazifalar jadvali tez-tez yangilanadi (izohlar_soni, tartib, versiya), lekin kichik — sukut autovacuum yetadi.
• vazifa_tarixi — faqat INSERT (audit trigger'i): unga insert_scale_factor ishlaydi; u katta o'sganda bo'laklash yoki eski yozuvlarni arxivlash rejalashtiriladi.
• Kanondagi katta to'plam (500 000 vazifa) o'lchovlari VACUUM ANALYZE dan keyin olingan — aks holda EXPLAIN raqamlari eski statistika bilan noto'g'ri chiqardi.

11. Real ishda

• "Disk to'lib qoldi" — ko'pincha bloat: navbat jadvali (SKIP LOCKED bilan navbat), sessiyalar, hisoblagichlar. Birinchi qadam — pg_stat_user_tables va pgstattuple.
• Wraparound avariyalari katta kompaniyalarda ham bo'lgan va soatlab to'xtashga olib kelgan — sabab deyarli doim kuzatuvsiz uzun tranzaksiya yoki sekin autovacuum.
• DBA intervyusi: "VACUUM va VACUUM FULL farqi?", "Autovacuum qachon ishlaydi?", "Wraparound nima?" — klassik savollar.

Xulosa

• O'lik versiyalar UPDATE/DELETE dan qoladi; autovacuum 50 + 0.2 × qatorlar dan keyin tozalaydi (PG 18 — yuqori chegara ham).
• VACUUM joyni qayta ishlatishga beradi, faylni kichraytirmaydi; VACUUM FULL kichraytiradi, lekin AccessExclusiveLock (kanonda SELECT 1.5 s kutdi).
• n_dead_tup — taxminiy hisoblagich, pgstattuple — aniq o'lchov; HOT pruning o'lik versiyalarning bir qismini oldindan tozalaydi.
• Indeks bloat — REINDEX CONCURRENTLY; jadval — pg_repack (sinalmagan) yoki yaxshisi oldini olish.
• Katta jadvallarga jadval darajasida autovacuum_vacuum_scale_factor.
• XID wraparound: VACUUM freeze qiladi; age(datfrozenxid) ni kuzating, uzun tranzaksiyalarga yo'l qo'ymang.

Keyingi dars: Backup va restore — pg_dump, pg_restore, PITR va "zaxira faqat tiklab ko'rilgan bo'lsa zaxira" qoidasi.

Manbalar

• PostgreSQL 18 hujjati: Routine Vacuuming (wraparound, freeze) — 2026-10-25 da ko'rildi.
• PostgreSQL 18 hujjati: Automatic Vacuuming (autovacuum_vacuum_max_threshold).
• PostgreSQL 18 hujjati: VACUUM, REINDEX, pgstattuple.
• pg_repack: reorg.github.io/pg_repack — sinalmagan.