Dead tuple và index bloat — PostgreSQL chậm dần thế nào, MySQL có bị không

Hệ thống PostgreSQL chạy tốt lúc mới lên, vài tháng sau query chậm dần dù số dòng "thật" không tăng mấy. Nguyên nhân hay gặp nhất: dead tuple (bản dòng cũ đã chết nhưng chưa dọn) và index bloat (index phình to vì chứa entry chết và khoảng trống). Bài này đi từ cơ chế bên dưới, đo bằng số thật, rồi so với kiến trúc InnoDB của MySQL — MySQL cũng bị, nhưng ở chỗ khác.

Thuật ngữ dùng trong bài
MVCC (multi-version concurrency control — giữ nhiều bản của một dòng để người đọc không phải chờ người ghi) · tuple (một bản vật lý của dòng trong file bảng) · dead tuple (bản cũ không transaction nào còn cần nhìn thấy) · heap (file chứa dữ liệu bảng của PostgreSQL, không sắp xếp) · bloat (phần dung lượng bị chiếm bởi rác và chỗ trống, không phải dữ liệu sống) · xmin horizon (mốc transaction cũ nhất còn đang mở — VACUUM chỉ dọn được rác chết trước mốc này) · HOT update (heap-only tuple — update không cần ghi entry mới vào index) · clustered index (InnoDB lưu chính dòng dữ liệu bên trong B-tree của primary key) · undo log (nhật ký giá trị cũ, InnoDB dùng để rollback và dựng lại bản cũ) · purge (luồng nền của InnoDB dọn undo và index record đã đánh dấu xoá).

Kiểm chứng: tài liệu lấy qua Context7 (PostgreSQL 18: routine-vacuuming, storage-hot, btree bottom-up deletion, sql-vacuum; MySQL 8.0 refman: innodb-multi-versioning, innodb-purge-configuration). Mọi số liệu trong bài là output thật, chạy trên PostgreSQL 18.4 (lab postgresql-learning) và MySQL 8.4.11 (container tạm, đã xoá sau khi test). Bảng test 200.000 dòng, chạy trong RAM nên thời gian (ms) chỉ để so sánh tương đối — con số đáng tin là số page (buffers) và dung lượng.

Trả lời 30 giây (khi interviewer hỏi thẳng)

PostgreSQL làm UPDATE bằng cách ghi bản mới của dòng ở chỗ khác và để bản cũ lại trong bảng. Mỗi bản mới thường cần thêm entry vào mọi index. Bản cũ chỉ được dọn khi VACUUM chạy và không còn transaction nào cần nó. Nên nếu update nhiều mà VACUUM không theo kịp — hoặc bị một transaction dài chặn — thì bảng và index đầy rác: seq scan đọc nhiều page hơn, index scan đi qua nhiều entry chết, cache chứa rác, index-only scan mất tác dụng. VACUUM thường chỉ đánh dấu chỗ trống để tái dùng, không trả dung lượng về OS.
MySQL InnoDB update tại chỗ trong clustered index và đẩy bản cũ sang undo log, nên bảng chính ít phình hơn. Nhưng nó vẫn bị: secondary index dùng delete-mark + insert, undo log phình khi có transaction dài, và người đọc snapshot cũ phải dựng lại dòng qua chuỗi undo — chậm hàng chục lần. Hai bên đều có rác, chỉ khác rác nằm ở đâu và ai trả giá.

1. Nó là gì / chạy thế nào (PostgreSQL)

1.1. UPDATE trong PostgreSQL = INSERT bản mới + đánh dấu bản cũ

PostgreSQL không sửa dòng tại chỗ. Mỗi tuple có 2 cột ẩn xmin (transaction tạo ra nó) và xmax (transaction xoá/thay thế nó). Khi UPDATE:

1. Ghi một tuple mới vào heap (cùng page nếu còn chỗ, không thì page khác).
2. Đặt xmax của tuple cũ = transaction hiện tại. Tuple cũ vẫn nằm nguyên đó.
3. Thêm entry mới vào mọi index của bảng trỏ tới tuple mới — kể cả index trên cột không hề đổi giá trị (trừ khi được HOT, xem 1.4).

DELETE chỉ làm bước 2. Sau khi transaction commit và không còn ai cần nhìn thấy bản cũ, bản cũ thành dead tuple. Nó vẫn chiếm chỗ trong heap, và entry index trỏ tới nó vẫn còn trong index.

flowchart LR
  subgraph IDX["Index trên email (không đổi giá trị)"]
    E1["entry email=a → (page 0, slot 1)"]
    E2["entry email=a → (page 7, slot 3)"]
  end
  subgraph HEAP["Heap (file bảng)"]
    T1["page 0 slot 1: status=1, xmax=500 (bản cũ — sắp chết)"]
    T2["page 7 slot 3: status=2, xmin=500 (bản mới)"]
  end
  E1 --> T1
  E2 --> T2
  T1 -. "UPDATE status=2 (tx 500)" .-> T2

Chú ý: cột email không đổi, nhưng index email vẫn phải có 2 entry cho cùng một dòng logic. Index B-tree không biết về MVCC — với nó, mỗi tuple là một đối tượng riêng (tài liệu PG 18, mục B-tree bottom-up deletion nói rõ điều này).

1.2. Đo thật: 5 lần UPDATE toàn bảng

Bảng 200.000 dòng, 3 index (pkey, status, email), tắt autovacuum để thấy rõ, rồi UPDATE tmp_bloat SET status = status + 1 5 lần:

CREATE TABLE tmp_bloat (id bigint PRIMARY KEY, status int NOT NULL, email text NOT NULL, note text)
  WITH (autovacuum_enabled = off);
INSERT INTO tmp_bloat SELECT g, g % 5, 'user' || g || '@x.com', 'n' FROM generate_series(1,200000) g;
CREATE INDEX tmp_bloat_status ON tmp_bloat(status);
CREATE INDEX tmp_bloat_email  ON tmp_bloat(email);
VACUUM ANALYZE tmp_bloat;
-- 5 lần:
UPDATE tmp_bloat SET status = status + 1;

 n_live_tup | n_dead_tup | n_tup_upd | n_tup_hot_upd
------------+------------+-----------+---------------
     200000 |    1000000 |   1000000 |             0

1 triệu dead tuple cho 200.000 dòng sống. Index pkey và email — cột không hề đổi — vẫn phình ×2–3. Chúng không phình ×6 như heap nhờ bottom-up index deletion (xem 1.4) tự dọn bớt một phần.

Một chi tiết hay bị hỏi: pgstattuple lúc này chỉ đếm dead_tuple_count = 200040 và free_percent = 62.1, trong khi n_dead_tup = 1000000. Lý do: page pruning (dọn trong page, chạy lúc đọc/ghi page) đã xoá phần dữ liệu của các bản cũ hơn, nhưng để lại line pointer (con trỏ slot 4 byte) ở trạng thái LP_DEAD, vì index vẫn còn trỏ vào đó. Chỉ VACUUM mới gỡ được entry index rồi giải phóng line pointer — log VACUUM ở dưới ghi đúng 1000000 dead item identifiers removed.

1.3. VACUUM làm gì — và không làm gì

sequenceDiagram
  participant V as VACUUM / autovacuum
  participant H as Heap
  participant I as Mọi index
  participant VM as Visibility map + FSM
  V->>V: tính removable cutoff (xmin horizon)
  V->>H: pha 1 — quét page, gom TID của tuple chết TRƯỚC cutoff
  V->>I: pha 2 — quét TOÀN BỘ từng index, xoá entry trỏ tới TID chết
  V->>H: pha 3 — đánh dấu line pointer là trống (tái dùng được)
  V->>VM: ghi chỗ trống vào FSM, bật cờ all-visible cho page sạch
  V->>H: nếu các page CUỐI file rỗng hết thì cắt bớt file (truncate)
  Note over V,H: Page rỗng ở giữa file KHÔNG được trả về OS

Chạy VACUUM (VERBOSE) tmp_bloat trên bảng vừa phình:

tuples: 172 removed, 200000 remain, 0 are dead but not yet removable
visibility map: 8822 pages set all-visible, 7347 pages set all-frozen (0 were all-visible)
index scan needed: 7352 pages from table (83.34% of total) had 1000000 dead item identifiers removed
index "tmp_bloat_pkey": pages: 1647 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "tmp_bloat_status": pages: 964 in total, 788 newly deleted, 788 currently deleted, 0 reusable
index "tmp_bloat_email": pages: 1764 in total, 0 newly deleted, 0 currently deleted, 0 reusable

Và dung lượng sau VACUUM:

VACUUM thường không làm file nhỏ lại. Nó chỉ biến rác thành chỗ trống để INSERT/UPDATE sau dùng lại. Nếu bảng đạt trạng thái cân bằng (rác sinh ra ≈ rác được dọn) thì không sao. Nếu đã phình một lần (batch update, VACUUM bị chặn lâu) thì dung lượng đó ở lại cho tới khi rebuild (VACUUM FULL, pg_repack, REINDEX CONCURRENTLY).

1.4. Cơ chế tự vệ: HOT update và bottom-up index deletion

HOT (heap-only tuple) — theo tài liệu PG 18, update được HOT khi đủ cả hai điều kiện:

1. Update không đổi cột nào có trong index (trừ index kiểu summarizing như BRIN).
2. Page chứa bản cũ còn đủ chỗ cho bản mới.

Khi HOT: không ghi entry index mới (index vẫn trỏ vào slot gốc, slot gốc thành "redirect" tới bản mới), và bản cũ ở giữa chuỗi có thể bị dọn ngay lúc đọc page — không cần đợi VACUUM.

Đo thật: 2 bảng 200.000 dòng, 100 lượt, mỗi lượt 1 transaction update 1% dòng rải đều (giống OLTP), chỉ đổi cột note không có index:

fillfactor=90 chừa 10% mỗi page cho bản mới nên tỉ lệ HOT cao hơn. Nhưng không bao giờ đạt 100%, và chỉ cần thêm 1 index lên cột hay update là HOT về 0 cho các update đó.

Bottom-up index deletion (có từ PG 14): khi một leaf page của B-tree sắp phải tách đôi (page split) vì chứa nhiều phiên bản của cùng dòng, PG thử xoá các entry chắc chắn đã chết trong page đó trước. Nó giúp index trên cột không đổi phình chậm hơn — đó là lý do email chỉ ×2 chứ không ×6. Nhưng tài liệu nói rõ: nó không thay được VACUUM, và không bảo đảm dọn hết.

1.5. Cái gì chặn VACUUM: xmin horizon

VACUUM chỉ xoá tuple chết trước transaction cũ nhất còn có thể nhìn thấy nó. Một transaction REPEATABLE READ mở từ 10:00 thì mọi bản cũ sinh ra sau 10:00 đều phải giữ lại — dù bảng đó transaction kia không hề đọc.

-- Session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM tmp_hold; (giữ 15s)
-- Session B: UPDATE tmp_hold SET v = v + 1;  (100.000 dòng)  rồi VACUUM (VERBOSE) tmp_hold;
tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable

-- Sau khi session A COMMIT, VACUUM lại:
tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable

flowchart TD
  H["xmin horizon của cả cluster"] --> V["VACUUM chỉ dọn rác cũ hơn mốc này"]
  A["Transaction chạy lâu (report, migration)"] --> H
  B["Session 'idle in transaction' (app quên commit)"] --> H
  C["Replication slot bỏ rơi (có xmin)"] --> H
  D["hot_standby_feedback=on + query dài trên replica"] --> H
  E["Prepared transaction (2PC) bị treo"] --> H

Điểm nguy hiểm: một transaction bị treo chặn dọn rác của mọi bảng trong cả database (với catalog thì cả cluster). Autovacuum vẫn chạy, vẫn tốn CPU/IO, nhưng không dọn được gì.

1.6. Rác làm chậm hệ thống bằng những đường nào

2. MySQL InnoDB: kiến trúc khác, có bị tương tự không?

2.1. InnoDB lưu bản cũ ở đâu

Theo tài liệu MySQL (innodb-multi-versioning), InnoDB xử lý 2 loại index khác nhau:

• Clustered index (chính là bảng): update tại chỗ. Giá trị cũ được chép sang undo log, dòng có cột ẩn DB_ROLL_PTR trỏ vào bản cũ trong undo. Bảng chính gần như không phình vì update.
• Secondary index: không có cột ẩn đó, không update tại chỗ. Đổi giá trị cột index = delete-mark record cũ + insert record mới — giống PostgreSQL. Nhưng secondary index chỉ bị động tới khi cột của nó đổi (khác PG, nơi mọi index đều bị ghi khi update không-HOT).
• Purge: luồng nền đọc history list (danh sách undo đã commit), khi không còn read view nào cần thì xoá undo và xoá hẳn record đã delete-mark trong index. Đây là "VACUUM" của InnoDB, chạy tự động, liên tục.

flowchart LR
  subgraph CI["Clustered index (bảng)"]
    R["id=7: status=3 (bản MỚI, sửa tại chỗ)"]
  end
  subgraph UNDO["Undo log (history list)"]
    U1["status=2 (tx 900)"]
    U2["status=1 (tx 850)"]
  end
  subgraph SI["Secondary index k_status"]
    S1["status=1, id=7 (delete-marked)"]
    S2["status=2, id=7 (delete-marked)"]
    S3["status=3, id=7"]
  end
  R -- "DB_ROLL_PTR" --> U1 --> U2
  P["Purge thread"] -. "xoá khi không read view nào cần" .-> UNDO
  P -. "xoá record delete-marked" .-> SI

2.2. Đo thật trên MySQL 8.4.11 — cùng bảng, cùng thao tác

CREATE TABLE tmp_bloat (id bigint PRIMARY KEY, status int NOT NULL, email varchar(64) NOT NULL,
  note varchar(10), KEY k_status(status), KEY k_email(email)) ENGINE=InnoDB;
-- 200.000 dòng, rồi 5 lần: UPDATE tmp_bloat SET status = status + 1;
-- dung lượng: mysql.innodb_index_stats (stat_name='size', số page 16KB đã cấp phát)
--             information_schema.INNODB_TABLESPACES.FILE_SIZE

Đọc bảng:

• Bảng chính không phình (10.5 MB đứng yên) — khác hẳn PG heap ×6.
• Index trên cột không đổi không bị đụng (k_email đứng yên) — PG thì ×2.
• Index trên cột bị đổi vẫn phình (k_status ×2,4 rồi ×5,5 khi purge bị chặn) — purge đã xoá record nhưng page đã tách thì không tự gộp lại hết.
• Rác chuyển sang undo tablespace (128 MB cho bảng 36 MB).
• OPTIMIZE TABLE trên InnoDB = recreate + analyze (output ghi Table does not support optimize, doing recreate + analyze instead) — tương đương VACUUM FULL, nhưng là online DDL.

2.3. Ai trả giá cho bản cũ: số đo đọc snapshot cũ

Session A mở START TRANSACTION WITH CONSISTENT SNAPSHOT, session B update toàn bảng 5 lần, rồi cả hai cùng đọc:

• MySQL: reader cũ phải đi theo DB_ROLL_PTR vào undo, dựng lại từng dòng qua 5 phiên bản. Covering index cũng không cứu được: tài liệu MySQL ghi rõ khi secondary record bị delete-mark hoặc mới hơn read view, InnoDB phải tra clustered index và undo. Reader mới thì nhanh vì bản mới nằm ngay trong bảng.
• PostgreSQL: không có bước dựng lại — bản cũ nằm sẵn trong heap, đọc thẳng. Nhưng mọi reader, cũ hay mới, đều phải quét qua heap đã phình (8114 page thay vì ~1470).

Tóm lại: PostgreSQL bắt mọi người trả giá cho rác (heap + mọi index phình). InnoDB dồn giá cho transaction dài (reader cũ chậm, undo phình) và index có cột bị đổi. Cả hai đều bị chặn dọn rác bởi transaction dài.

2.4. Bảng so sánh

3. Lợi ích

Chủ đề này là một vấn đề, nên "lợi ích" ở đây là: vì sao PostgreSQL chấp nhận thiết kế sinh rác, và được gì khi kiểm soát tốt rác.

• Reader không chặn writer, writer không chặn reader — cả hai DB đều được nhờ MVCC.
• Rollback tức thì trong PG. Transaction update 50 triệu dòng rồi rollback: PG chỉ đánh dấu abort, InnoDB phải chạy undo ngược lại có thể mất ngang thời gian đã chạy.
• Đọc snapshot cũ rẻ trong PG (63 ms vs 5552 ms ở số đo 2.3) — tốt cho report, pg_dump, transaction REPEATABLE READ dài vừa phải.
• Crash recovery của PG không cần pha undo — không có undo để áp lại.
• Khi kiểm soát tốt (autovacuum đủ nhanh, không có tx treo, HOT cao): bảng đạt trạng thái cân bằng, dung lượng ổn định, query không chậm dần theo thời gian.

4. Tác hại / hạn chế

• Write amplification (một thay đổi logic ghi ra nhiều byte vật lý) trong PG: update 1 cột có index trên bảng 8 index = 1 tuple mới + 8 entry index + WAL cho tất cả. Đây là lý do chính Uber viết bài công khai năm 2016 khi chuyển một phần hệ thống từ PG sang MySQL.
• Chậm dần âm thầm: không có lỗi, không có alert mặc định. Query p99 tăng từ từ qua nhiều tuần.
• Dung lượng không tự co lại: sau một lần phình, VACUUM không trả lại. Lấy lại cần rebuild — tốn IO, cần gấp đôi dung lượng tạm thời.
• Một tx treo làm hỏng cả database: không riêng bảng nó đọc.
• Autovacuum cũng tốn tài nguyên: pha quét index đọc toàn bộ từng index mỗi lần — bảng 10 index lớn thì một vòng autovacuum có thể chạy hàng giờ.
• MySQL: undo phình (có thể lên hàng chục GB), reader cũ chậm, history list lớn làm mọi query chậm vì phải lọc qua record delete-marked; rollback transaction lớn cực chậm.

5. Trade-off

5.1. Kiến trúc: append (PG) vs in-place + undo (InnoDB)

Trong phỏng vấn: không chọn DB chỉ vì bloat. Chọn DB theo tính năng, đội ngũ, hệ sinh thái; rồi thiết kế schema và vận hành để tránh điểm yếu của DB đã chọn.

5.2. Chọn cách xử lý bloat trong PostgreSQL

pg_repack chưa verify trong lab này (extension không cài sẵn trong image postgres:18-alpine). Các dòng khác đã chạy thật.

6. Khó khăn khi vận hành thực tế

6.1. Ngưỡng autovacuum mặc định quá lỏng cho bảng lớn

Autovacuum chạy VACUUM một bảng khi: n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples (PG 18 thêm trần autovacuum_vacuum_max_threshold). Giá trị trên lab:

autovacuum_vacuum_scale_factor  = 0.2
autovacuum_vacuum_threshold     = 50
autovacuum_vacuum_max_threshold = 100000000
autovacuum_naptime              = 1min
idle_in_transaction_session_timeout = 0      -- tắt: session treo bao lâu cũng được

Bảng 50 triệu dòng phải có ~10 triệu dead tuple mới được VACUUM — quá muộn. Chỉnh theo bảng:

ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000, fillfactor = 90);
-- kiểm tra:
SELECT reloptions FROM pg_class WHERE relname = 'jobs';
--  {autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=1000,fillfactor=90}

6.2. Query theo dõi (đã chạy trên PG 18.4)

-- Bảng nào nhiều rác, HOT bao nhiêu %, lần cuối autovacuum
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),1) AS dead_pct,
       round(100.0*n_tup_hot_upd/nullif(n_tup_upd,0),1)          AS hot_pct,
       last_autovacuum, autovacuum_count, total_autovacuum_time    -- cột *_time có từ PG 18
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- Ai đang giữ xmin horizon (chặn VACUUM)
SELECT 'backend' AS src, pid::text AS id, age(backend_xmin) AS xmin_age, state, now()-xact_start AS tx_age
  FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name::text, age(xmin), CASE WHEN active THEN 'active' ELSE 'inactive' END, NULL
  FROM pg_replication_slots WHERE xmin IS NOT NULL
UNION ALL
SELECT 'prepared', gid, age(transaction), 'prepared', now()-prepared FROM pg_prepared_xacts
ORDER BY xmin_age DESC;

-- Autovacuum đang làm tới đâu
SELECT relid::regclass, phase, heap_blks_scanned, heap_blks_total,
       index_vacuum_count, indexes_processed, indexes_total
FROM pg_stat_progress_vacuum;

-- Độ đầy của index (cần extension pgstattuple, đọc toàn bộ index — đừng chạy dày đặc trên prod)
SELECT leaf_pages, avg_leaf_density, deleted_pages FROM pgstatindex('tmp_bloat_email');

MySQL tương ứng (đã chạy trên 8.4.11):

-- Transaction đang mở lâu nhất (giữ read view, chặn purge)
SELECT trx_id, trx_started, trx_isolation_level,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s
FROM information_schema.INNODB_TRX ORDER BY trx_started;

-- History list length (đếm theo undo log của transaction, KHÔNG phải theo số dòng)
SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME = 'trx_rseg_history_len';
-- hoặc: SHOW ENGINE INNODB STATUS → dòng "History list length"

-- Dung lượng undo tablespace
SELECT NAME, FILE_SIZE/1024/1024 AS mb FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE 'innodb_undo%';

6.3. Alert nên có

• Transaction mở > 5 phút (trừ job đã biết), session idle in transaction > 1 phút.
• age(backend_xmin) hoặc slot xmin tăng liên tục.
• dead_pct > 20% ở bảng nóng; last_autovacuum quá lâu với bảng update nhiều.
• Replication slot inactive.
• MySQL: history list length tăng đều không giảm; dung lượng undo tăng.

6.4. Con người và chi phí

• Dev thường không biết DB::beginTransaction() rồi gọi HTTP ra ngoài 30 giây là đang chặn VACUUM cả database. Cần đưa vào code review: không gọi I/O ngoài trong transaction.
• Thêm index "cho nhanh" có giá: có thể làm mất HOT của update đang chạy mỗi giây. Review index mới phải hỏi "cột này có bị update thường xuyên không".
• Rebuild lấy lại dung lượng cần gấp đôi disk tạm thời và sinh nhiều WAL → replica lag. Chạy ngoài giờ cao điểm, theo dõi lag.
• Lúc 3h sáng: disk sắp đầy vì bloat → đừng chạy VACUUM FULL (cần thêm disk bằng kích thước bảng mới, và khoá bảng). Trước tiên tìm và kill thứ giữ xmin, để autovacuum dọn cho rác không tăng thêm, rồi tăng disk.

7. Case thực tế có thể gặp + cách xử lý

Các case dưới là ví dụ minh hoạ dựng từ cơ chế đã đo ở trên; số liệu quy mô (triệu dòng, GB) là ước lượng, không phải đo từ hệ thống thật.

Case 1: Bảng jobs / outbox chậm dần dù chỉ có vài nghìn dòng chờ

Triệu chứng: bảng jobs (Laravel database queue) hoặc email_outbox chỉ có ~2.000 dòng đang chờ, nhưng query lấy job SELECT ... WHERE queue = ? ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED từ 1 ms lên 200 ms. Bảng insert rồi delete liên tục cả triệu dòng/ngày.

Lý do: mỗi job xong là một dead tuple, và entry index của nó vẫn nằm ở đầu index — đúng chỗ query quét. Query phải đi qua hàng trăm nghìn entry chết trước khi gặp dòng sống. Ngưỡng autovacuum tính theo reltuples nhỏ nên hay chạy, nhưng nếu có tx dài thì vẫn không dọn được.

1. Chạy query "ai giữ xmin" ở 6.2. Có tx treo → xử lý trước (Case 2).
2. ALTER TABLE jobs SET (autovacuum_vacuum_threshold = 1000, autovacuum_vacuum_scale_factor = 0.0); — bảng queue nên VACUUM theo số tuyệt đối.
3. Kiểm tra EXPLAIN (ANALYZE, BUFFERS) trước/sau: số buffers phải giảm mạnh.
4. Index đã phình: REINDEX INDEX CONCURRENTLY.
5. Khối lượng rất lớn: đổi sang Redis queue, hoặc partition theo ngày rồi DROP partition cũ (xem bài gửi mail at-least-once).

Case 2: Toàn hệ thống chậm dần sau deploy, không query nào "mới"

Triệu chứng: sau khi deploy tính năng xuất báo cáo, p99 mọi API tăng dần trong vài giờ. n_dead_tup tăng ở nhiều bảng không liên quan. Log autovacuum có N are dead but not yet removable.

1. Query "ai giữ xmin": thấy một session idle in transaction, tx_age 3 giờ. Nguyên nhân hay gặp: code mở transaction, rồi gọi API ngoài / đợi queue / exception bị nuốt mà không rollback.
2. Cắt ngay: SELECT pg_terminate_backend(<pid>); (xác nhận với chủ tính năng trước nếu là job quan trọng).
3. Chặn tái diễn: đặt idle_in_transaction_session_timeout = '60s' (theo role app), và statement_timeout hợp lý.
4. Report dài: chuyển sang replica. Nếu replica bật hot_standby_feedback=on thì query dài trên replica vẫn giữ xmin của primary — phải chọn: bật (không bị cancel query, nhưng bloat primary) hay tắt (không bloat, nhưng query dài trên replica có thể bị cancel do xung đột).
5. Rác đã sinh: autovacuum sẽ tự dọn khi horizon tiến lên. Nếu bảng đã phình mạnh → xử lý như Case 3.

Case 3: Backfill 50 triệu dòng làm disk tăng gấp đôi

Triệu chứng: migration UPDATE orders SET new_col = ... một lệnh cho cả bảng. Bảng 40 GB thành 80 GB, mọi index cũng phình, replica lag vì WAL khổng lồ.

1. Phòng trước: backfill theo batch (vd 10.000 dòng/transaction, theo khoảng id), nghỉ giữa các batch, để autovacuum dọn và bản mới tái dùng chỗ của bản cũ. Bảng chỉ phình khoảng 1 batch thay vì ×2 (xem Migration PostgreSQL zero downtime).
2. Đã lỡ phình: chạy VACUUM (VERBOSE) orders để biến rác thành chỗ trống — dừng phình thêm.
3. Lấy lại dung lượng index: REINDEX INDEX CONCURRENTLY từng index một (cần disk trống ≥ kích thước index đó).
4. Lấy lại dung lượng heap: pg_repack ngoài giờ cao điểm; hoặc chấp nhận để đó nếu bảng sẽ tăng trưởng lấp lại chỗ trống.

Case 4 (MySQL): report 2 tiếng làm mọi query chậm và undo 50 GB

Triệu chứng: một job export chạy REPEATABLE READ 2 tiếng trên primary. History list length lên hàng triệu, undo tablespace phình, query đọc bình thường cũng chậm, chính job export chậm dần (như số đo ×29 ở 2.3).

1. INNODB_TRX tìm tx cũ nhất → KILL <processlist_id>. Nếu tx đó đã ghi nhiều thì kill = rollback dài, có thể mất ngang thời gian đã chạy — cân nhắc để nó chạy xong.
2. Chuyển export sang replica: khác PG, read view trên replica MySQL không chặn purge của source (replica nhận binlog, không chia sẻ undo).
3. Export theo từng khoảng khoá chính, mỗi khoảng một transaction ngắn, thay vì một snapshot dài.
4. Undo truncate (innodb_undo_log_truncate=ON mặc định) sẽ co undo tablespace khi vượt innodb_max_undo_log_size (mặc định 1 GB) và purge đã xong — chưa verify cơ chế co lại trong lab này.

Case 5: Replication slot bỏ rơi

Tắt một consumer CDC (Debezium) hoặc replica nhưng không xoá slot. Slot giữ WAL (disk đầy) và nếu có xmin thì giữ luôn horizon (bloat toàn database).

1. SELECT slot_name, active, xmin, catalog_xmin, restart_lsn FROM pg_replication_slots;
2. Slot inactive không còn dùng → SELECT pg_drop_replication_slot('ten_slot');
3. Phòng: đặt max_slot_wal_keep_size, alert slot inactive.

Case 5 viết theo tài liệu; lab chưa dựng slot có xmin nên chưa verify phần slot giữ xmin horizon.

8. Bẫy hay gặp

1. Nghĩ VACUUM làm nhỏ file. Không — chỉ truncate page rỗng ở cuối file (test: 69 MB giữ nguyên).
2. Chạy VACUUM FULL trên prod giờ cao điểm. ACCESS EXCLUSIVE lock chặn cả SELECT; cần thêm disk bằng bảng mới.
3. Tắt autovacuum vì "nó làm chậm". Rác dồn lại, và cuối cùng PG vẫn buộc chạy anti-wraparound VACUUM vào lúc tệ nhất.
4. Thêm index lên cột update thường xuyên (updated_at, status, last_seen_at) → mất HOT, mọi index bị ghi thêm.
5. Tin n_dead_tup là số chính xác. Đó là ước lượng thống kê; muốn chính xác dùng pgstattuple (đọc cả bảng — tốn IO).
6. Nhìn pgstattuple dead_tuple_count thấp rồi yên tâm. Test ở 1.2: chỉ 200.040 nhưng có 1 triệu line pointer chết và index vẫn đầy entry chết.
7. Kill tx treo xong nghĩ bảng tự nhỏ lại. Autovacuum dọn rác thành chỗ trống, dung lượng vẫn ở đó.
8. MySQL: đọc history list length như số dòng. Nó đếm undo log theo transaction — 5 UPDATE × 200.000 dòng hiện trx_rseg_history_len chỉ 7–17.
9. MySQL: nghĩ covering index luôn tránh được tra bảng. Record delete-marked hoặc mới hơn read view buộc tra clustered index + undo (×74 ở test).
10. MySQL: kill một tx ghi lớn để "cứu". Rollback có thể dài hơn cứ để nó chạy xong.
11. Đổi fillfactor rồi chờ hiệu quả ngay. Chỉ áp cho page mới ghi; page cũ phải rebuild mới có chỗ trống.

9. Câu hỏi phụ interviewer hay hỏi tiếp

Q: Vì sao PostgreSQL cần VACUUM mà MySQL không?
PG giữ bản cũ ngay trong bảng nên cần một quá trình riêng dọn bảng và mọi index. InnoDB đẩy bản cũ sang undo và có purge thread dọn liên tục — tức là MySQL cũng có "VACUUM", chỉ là chạy ngầm và dọn chỗ khác. Cả hai đều bị transaction dài chặn.

Q: Vì sao VACUUM không trả dung lượng về OS?
Nó chỉ đánh dấu chỗ trống trong page để tái dùng; dời dòng giữa các page sẽ đổi TID, phải cập nhật mọi index và cần khoá mạnh — đó là việc của VACUUM FULL. VACUUM thường chỉ cắt các page rỗng ở cuối file.

Q: HOT update là gì, khi nào không xảy ra?
Update không đổi cột có index và page còn chỗ → không ghi index mới. Không xảy ra khi đổi cột có index, hoặc page đầy (hạ fillfactor để chừa chỗ).

Q: Sao thêm một index có thể làm UPDATE chậm hơn ở chỗ khác?
Nếu index nằm trên cột hay bị update, những update trước đây được HOT giờ phải ghi vào tất cả index của bảng, sinh thêm WAL và rác index.

Q: VACUUM FULL, pg_repack, REINDEX CONCURRENTLY khác nhau thế nào?
VACUUM FULL rebuild heap + index nhưng khoá cả đọc. pg_repack làm tương tự bằng bảng tạm + trigger, chỉ khoá ngắn đầu/cuối. REINDEX CONCURRENTLY chỉ rebuild index, không chặn ghi.

Q: Transaction ID wraparound liên quan gì?
XID là số 32-bit. VACUUM còn có việc "freeze" tuple cũ để XID quay vòng không làm dữ liệu cũ biến thành "tương lai". Nếu VACUUM bị chặn quá lâu, PG sẽ cảnh báo rồi ngừng nhận transaction mới để bảo vệ dữ liệu. Cùng nguyên nhân gốc với bloat: tx dài, slot bỏ rơi.

Q: hot_standby_feedback nên bật hay tắt?
Bật: query dài trên replica không bị cancel, đổi lại primary bị bloat. Tắt: primary sạch, nhưng query dài trên replica có thể bị cancel. Report dài nên đi replica riêng tắt feedback và có max_standby_streaming_delay lớn, chấp nhận lag.

Q: MySQL có bị bloat vì UUID v4 làm primary key không?
Có, nhưng là dạng khác: insert ngẫu nhiên vào clustered index gây page split, page chỉ đầy ~50–70%. Không phải rác MVCC mà là phân mảnh. Dùng UUID v7 / khoá tăng dần để giảm.

Q: Làm sao biết bảng bị bloat mà không dùng extension?
So pg_relation_size với ước lượng reltuples × độ rộng dòng trung bình (các script "bloat estimate" phổ biến làm vậy), cộng n_dead_tup. Chỉ là ước lượng; muốn chính xác dùng pgstattuple / pgstatindex.