VACUUM: chỗ trống tái sử dụng và snapshot còn giữ phiên bản cũ

Câu hỏi bài này trả lời: đã DELETE và chạy VACUUM, vì sao bảng còn lớn và phiên bản cũ chưa được dọn?

Cần biết trước: transaction và MVCC/isolation, cùng lab database. Lab chạy PostgreSQL 18.6 native qua socket riêng trên macOS arm64, Python 3.14.4 stdlib. Docker/Linux chưa kiểm. Fixture có MySQL nhưng bài chỉ đo PostgreSQL.

UPDATE tạo phiên bản mới; DELETE làm phiên bản cũ không còn visible với các snapshot mới. PostgreSQL chỉ được dọn khi phiên bản đó không còn cần cho transaction liên quan. VACUUM thường làm chỗ trống dùng lại được trong relation; ANALYZE thu thập thống kê cho planner. Hai việc có thể chạy riêng hoặc kết hợp. Routine vacuuming PG18 giải thích các mục đích này, gồm visibility map và chống transaction ID wraparound.

Dữ liệu và phép đo

Tạo thư mục trống, chép bốn file cách A của bài lab: lab-common.sh, lab-local.sh, seed-pg.sql, seed-mysql.sql. Lưu controller bên dưới thành vacuum.py.

Bảng wiki_lab.vacuum_demo có 4000 hàng giả, payload 160 ký tự và index trên qty. UPDATE toàn bảng đổi qty được index để tránh HOT update cho workload này; DELETE 1000 hàng. Reader A giữ snapshot REPEATABLE READ từ trước writer commit. Không có writer ngoài controller trong khi lấy số đo.

Tạm tắt autovacuum chỉ ở bảng disposable này để daemon không chen vào các bước; không tắt ở server hoặc bảng nghiệp vụ. Mỗi lượt reset tạo lại bảng, cleanup hủy database lab. Không dùng cấu hình này làm hướng dẫn vận hành.

Controller dùng extension pgstattuple đi kèm PostgreSQL. Nó cần extension đã được cài vào database và quyền đọc tương ứng; role lab có quyền quản trị trong database riêng. Không tự cấp quyền trên database thật.

Các cột output:

pgstattuple phân loại dead bằng HeapTupleSatisfiesDirty; dead theo metric này chưa đồng nghĩa VACUUM được phép xóa khi còn snapshot cũ. Scan không là ảnh chụp atomic nếu có writer đồng thời. Heap còn header/pointer/alignment nên live bytes + dead bytes + free không bằng toàn file. Cumulative statistics ghi rõ n_dead_tup là ước lượng; controller không khóa nó vào số dead vật lý chính xác.

Controller: marker sau SQL, khóa chờ có quan sát

Ba client chỉ dùng bảng lab. A giữ snapshot, B giữ khóa ShareUpdateExclusive tạm thời, C thử VACUUM. Controller chỉ release B sau khi thấy yêu cầu khóa của C trong pg_locks; không dùng sleep để đoán câu lệnh đã chạy. Deadline chống treo, poll chỉ chờ điều kiện quan sát được.

import json
import os
import queue
import signal
import subprocess
import threading
import time

CLIENT = ". ./lab-local.sh; lab_psql -At"
TABLE = "wiki_lab.vacuum_demo"
VACUUM = f"VACUUM (TRUNCATE false, INDEX_CLEANUP on) {TABLE};"


def query(sql):
    result = subprocess.run(["bash", "-c", CLIENT], input=sql, text=True,
                            capture_output=True, timeout=15, check=True)
    return result.stdout.strip()


class Session:
    def __init__(self):
        self.process = subprocess.Popen(
            ["bash", "-c", CLIENT], stdin=subprocess.PIPE, stdout=subprocess.PIPE,
            stderr=subprocess.PIPE, text=True, bufsize=1, start_new_session=True)
        self.lines = queue.Queue()
        self.reader = threading.Thread(target=self.read, daemon=True)
        self.reader.start()
        self.id = int(self.ask("SELECT pg_backend_pid();")[0])
        assert self.ask("SET statement_timeout='12s';") == []

    def read(self):
        for line in self.process.stdout:
            self.lines.put(line.strip())
        self.lines.put(None)

    def send(self, sql):
        self.process.stdin.write(sql + " SELECT 'barrier';\n")
        self.process.stdin.flush()

    def collect(self):
        values = []
        while True:
            value = self.lines.get(timeout=15)
            assert value is not None, "client kết thúc trước marker"
            if value == "barrier":
                return values
            values.append(value)

    def ask(self, sql):
        self.send(sql)
        return self.collect()

    def close(self):
        if self.process.poll() is None:
            os.killpg(self.process.pid, signal.SIGTERM)
            try:
                self.process.wait(timeout=3)
            except subprocess.TimeoutExpired:
                os.killpg(self.process.pid, signal.SIGKILL)
                self.process.wait(timeout=3)
        self.reader.join(timeout=2)


def measure(label):
    row = json.loads(query(f"""
SELECT row_to_json(m) FROM (
  SELECT (SELECT count(*) FROM {TABLE}) AS visible,
         p.tuple_count AS physical_live, p.dead_tuple_count AS dead,
         p.free_space AS free, pg_relation_size('{TABLE}') AS heap,
         pg_indexes_size('{TABLE}') AS index,
         pg_total_relation_size('{TABLE}') AS total,
         s.n_dead_tup AS estimated_dead, s.vacuum_count AS vacuum,
         s.analyze_count AS analyze
  FROM pgstattuple('{TABLE}'::regclass) p
  JOIN pg_stat_user_tables s ON s.relid='{TABLE}'::regclass
) m;
"""))
    print(label, " ".join(f"{key}={value}" for key, value in row.items()))
    return row


def run():
    query(f"""
DROP TABLE IF EXISTS {TABLE};
CREATE TABLE {TABLE} (
  id integer PRIMARY KEY, qty integer NOT NULL, payload text NOT NULL
) WITH (autovacuum_enabled=false);
CREATE INDEX vacuum_demo_qty ON {TABLE}(qty);
INSERT INTO {TABLE} SELECT g, 10, repeat('x',160) FROM generate_series(1,4000) g;
ANALYZE {TABLE};
""")
    before = measure("before")
    assert before["visible"] == before["physical_live"] == 4000
    assert before["dead"] == 0 and before["analyze"] == 1
    sessions = []
    try:
        for _ in range(3):
            sessions.append(Session())
        a, b, c = sessions
        assert a.ask("BEGIN ISOLATION LEVEL REPEATABLE READ;") == []
        assert a.ask(f"SELECT count(*) FROM {TABLE};") == ["4000"]
        assert b.ask("BEGIN;") == []
        assert b.ask(f"LOCK TABLE {TABLE} IN SHARE UPDATE EXCLUSIVE MODE;") == []
        c.send(VACUUM)
        deadline = time.monotonic() + 10
        while query(f"SELECT count(*) FROM pg_locks WHERE pid={c.id} "
                    f"AND relation='{TABLE}'::regclass "
                    "AND mode='ShareUpdateExclusiveLock' AND NOT granted;") != "1":
            assert time.monotonic() < deadline, "không quan sát được VACUUM chờ khóa"
            time.sleep(0.02)
        assert b.ask("COMMIT;") == []
        assert c.collect() == []
        print("lock requested=ShareUpdateExclusiveLock observed_wait=yes released=yes")

        query(f"UPDATE {TABLE} SET qty=20; DELETE FROM {TABLE} WHERE id<=1000;")
        changed = measure("changed")
        assert changed["visible"] == changed["physical_live"] == 3000
        assert changed["dead"] == 5000 and changed["heap"] > before["heap"]
        assert a.ask(f"SELECT count(*) FROM {TABLE} WHERE qty=10;") == ["4000"]
        query(f"ANALYZE {TABLE};")
        analyzed = measure("analyzed")
        assert analyzed["analyze"] == changed["analyze"] + 1
        assert analyzed["vacuum"] == changed["vacuum"]
        assert analyzed["dead"] == changed["dead"]

        query(VACUUM)
        held = measure("vacuum_held")
        assert held["dead"] == changed["dead"]
        assert held["vacuum"] == analyzed["vacuum"] + 1
        assert held["analyze"] == analyzed["analyze"]
        assert held["heap"] == changed["heap"]
        assert a.ask(f"SELECT count(*) FROM {TABLE} WHERE qty=10;") == ["4000"]
        assert a.ask("ROLLBACK;") == []
        query(VACUUM)
        reclaimed = measure("vacuum_released")
        assert reclaimed["dead"] == 0 and reclaimed["free"] > held["free"]
        assert reclaimed["heap"] == held["heap"]
        assert reclaimed["analyze"] == held["analyze"]
        query(f"INSERT INTO {TABLE} SELECT g,20,repeat('x',160) "
              "FROM generate_series(4001,5000) g;")
        refill = measure("refill")
        assert refill["visible"] == 4000 and refill["heap"] == reclaimed["heap"]
        assert refill["free"] < reclaimed["free"]
        print("snapshot old_rows=4000 new_rows=3000; dead held=5000 released=0")
        print("analyze_only=yes heap_unchanged_after_vacuum=yes refill_reused=yes")
    finally:
        for session in reversed(sessions):
            session.close()


query("CREATE EXTENSION IF NOT EXISTS pgstattuple;")
print("server", query("SHOW server_version;"))
print("autovacuum", query("SHOW autovacuum;"),
      "threshold", query("SHOW autovacuum_vacuum_threshold;"),
      "scale", query("SHOW autovacuum_vacuum_scale_factor;"),
      "maximum", query("SHOW autovacuum_vacuum_max_threshold;"))
run()
print("all checks passed; sessions closed")

Chạy từ fixture sạch

. ./lab-local.sh
lab_up
lab_seed
lab_whoami

. ./lab-local.sh
lab_target
python3 vacuum.py

Controller in số đo từng bước trước assertions. Không dùng KB từ thí nghiệm khác làm expected. Giá trị byte phụ thuộc row layout/version/page size; nếu assertion heap/refill không đúng trên môi trường mới, đọc số đo và xác định nguyên nhân trước khi kết luận.

lock requested=ShareUpdateExclusiveLock observed_wait=yes released=yes
snapshot old_rows=4000 new_rows=3000; dead held=5000 released=0
analyze_only=yes heap_unchanged_after_vacuum=yes refill_reused=yes
all checks passed; sessions closed

Đọc kết quả và chọn maintenance

Hai lượt trên môi trường đã nêu cho cùng số đo dưới đây (byte, không phải benchmark):

Vacuum counter ở Changed đã là 1 vì case khóa chạy một VACUUM trước workload. Trong lượt này estimated_dead lần lượt 0/5000/5000/5000/0/0, khớp số quét vật lý; không suy hai metric luôn bằng nhau trong hệ thống có ghi đồng thời.

Sau writer commit, client mới thấy 3000 hàng còn A vẫn thấy 4000 hàng qty=10. ANALYZE tăng analyze_count; số dead vật lý giữ nguyên khi snapshot còn cần chúng. Nó không thay VACUUM. Sau plain VACUUM khi A còn mở, dead vẫn 5000; VACUUM chạy xong không có nghĩa đã dọn mọi phiên bản.

Sau A rollback, VACUUM dọn dead về 0 và free tăng. Heap bytes giữ nguyên vì bài chủ động dùng TRUNCATE false; INSERT 1000 hàng tiếp theo dùng chỗ trống, không tăng heap trong workload đã kiểm. Đây là bằng chứng tái sử dụng heap, không chứng minh index/TOAST cũng giảm hay một tỷ lệ bloat phổ quát. Controller in riêng index/total; không gộp chúng với heap.

VACUUM PG18 cho biết plain VACUUM mặc định có thể truncate các page rỗng cuối file; bước đó cần AccessExclusive. Vì vậy “plain VACUUM không bao giờ trả dung lượng cho OS” cũng sai. Lab tắt truncate để tách việc reclaim trong heap khỏi việc cắt file.

Khóa chờ đã thấy là ShareUpdateExclusive, xung đột với cùng mode của B. Sau release, VACUUM hoàn thành dù A còn AccessShare từ SELECT. Không suy “VACUUM không bao giờ khóa/chặn”: DDL, maintenance khác và truncate có quan hệ xung đột riêng.

VACUUM FULL rewrite relation và cần AccessExclusive cùng dung lượng tạm cho bản mới. Autovacuum không tự chạy FULL. Bài không chạy FULL và không có số đo rút file/timing của nó; đây là lựa chọn cần maintenance window, không là phản xạ khi thấy n_dead_tup cao. ANALYZE PG18 mô tả thống kê phục vụ planner; ghép VACUUM (ANALYZE) khi cần cả hai mục đích.

Autovacuum và lỗi thường gặp

Theo tham số PG18, ngưỡng UPDATE/DELETE dựa threshold + scale_factor × ước lượng số tuple, có trần autovacuum_vacuum_max_threshold khi khác -1. Mặc định tài liệu là 50, 0.2 và 100000000. Có ngưỡng INSERT riêng, ngưỡng ANALYZE riêng và công việc chống wraparound; không áp một công thức cho mọi lý do chạy vacuum. Controller in giá trị server thật; bảng lab có override tắt daemon nên không đo thời điểm autovacuum kích hoạt.

• Giữ autovacuum trong vận hành; chỉnh theo tốc độ thay đổi, bảng, I/O và thời gian reclaim thực tế. Không lấy scale_factor của một case nhỏ làm giá trị tối ưu cho mọi bảng.
• Stats cập nhật có độ trễ và có thể được cache trong transaction; query đo ở đây dùng client mới. n_dead_tup không là bằng chứng chính xác dung lượng lãng phí hoặc số tuple có thể xóa ngay.
• VACUUM không chạy trong transaction block. Controller gọi nó ở client autocommit, không bên trong transaction A/B.
• Transaction dài cần snapshot có thể giữ horizon; kiểm xact_start/backend_xmin và tình trạng transaction, không chỉ session đang active. Không tự terminate transaction của người khác để đạt số đo.
• Slot replication/standby feedback cũng có thể giữ horizon nhưng chưa thử trong lab này. Chưa đo wraparound, index-only scan, throughput, parallel vacuum hoặc VACUUM FULL; không khẳng định kết quả hiệu năng từ số dead.
• pgstattuple quét bảng và cần quyền; chọn phạm vi/chi phí thích hợp, không coi full scan là telemetry miễn phí.

Reset, chạy lại và dọn lab

Controller tự DROP/CREATE đúng một bảng riêng khi mọi session lượt trước đã đóng, không cần xóa bảng seed khác. Chạy lại từ cùng fixture:

. ./lab-local.sh
lab_target
python3 vacuum.py

. ./lab-local.sh
lab_clean
echo cleaned

Học tiếp EXPLAIN ANALYZE và MVCC/isolation. Nguồn manual18 đọc ngày 2026-10-03; dữ liệu/controller là ví dụ riêng, các byte chỉ có ý nghĩa trong môi trường đã đo.