PostgreSQL MVCC, VACUUM và table bloat: vì sao database chậm dần sau vài tháng production

Nhiều team có một trải nghiệm rất giống nhau với PostgreSQL: hệ thống chạy ổn khi mới launch, query plan cũng đẹp, CPU và RAM chưa căng. Nhưng sau vài tháng production, cùng workload đó bắt đầu chậm dần. SELECT đọc nhiều block hơn, UPDATE nặng hơn, autovacuum xuất hiện trong dashboard nhiều hơn, index to lên nhanh bất thường, cache hit vẫn cao mà latency vẫn xấu đi.

Nếu chỉ nhìn ở tầng application, anh em rất dễ kết luận sai rằng vấn đề nằm ở ORM, thiếu index, hoặc “server yếu dần”. Thực tế, một nguyên nhân gốc rất phổ biến là MVCC tạo dead tuples, VACUUM không theo kịp, rồi table/index bloat tích lũy âm thầm theo thời gian.

Bài này không giải thích PostgreSQL kiểu nhập môn. Mục tiêu là nhìn MVCC, VACUUM và bloat dưới góc độ vận hành production cho backend engineer:

  • MVCC thực sự tạo ra overhead gì trên bảng và index
  • vì sao dead tuples khiến query chậm dù logic không đổi
  • autovacuum hoạt động theo ngưỡng nào và hay fail ở đâu
  • khi nào nên VACUUM, VACUUM ANALYZE, REINDEX, pg_repack
  • cách đọc các dấu hiệu như n_dead_tup, last_autovacuum, pg_stat_statements, table growth và WAL volume
  • checklist tuning để tránh rơi vào trạng thái “database chậm dần theo thời gian”

MVCC trong PostgreSQL: đọc không khóa mạnh, nhưng phải trả giá bằng phiên bản dòng

PostgreSQL dùng MVCC — Multi-Version Concurrency Control. Mỗi lần UPDATE một row, PostgreSQL không sửa đè row cũ ngay. Nó tạo ra một phiên bản row mới và để phiên bản cũ tồn tại cho tới khi không còn transaction nào cần nhìn thấy nó.

Điều này mang lại lợi ích rất lớn:

  • reader không phải block writer trong nhiều tình huống thông thường;
  • transaction có snapshot nhất quán;
  • concurrency tốt hơn so với kiểu lock-based thô.

Nhưng đổi lại, một UPDATE trong PostgreSQL về bản chất gần với mô hình delete old version + insert new version hơn là “ghi đè tại chỗ”. Nghĩa là mỗi workload có nhiều update/delete sẽ để lại phía sau:

  • dead tuples trong heap table;
  • entry cũ trong index;
  • metadata phải được dọn;
  • statistics cần cập nhật để planner không ra quyết định mù.

Đây là chỗ nhiều backend engineer đánh giá thấp chi phí thật sự của một bảng “cập nhật liên tục”. Một bảng orders 20 triệu dòng nhưng mỗi dòng chỉ ghi một lần không giống một bảng sessions 5 triệu dòng bị update heartbeat liên tục. Về mặt vận hành, bảng thứ hai có thể đau hơn nhiều.

Dead tuples không chỉ tốn disk, chúng làm tăng cost trên đường đọc

Có một hiểu nhầm phổ biến: “Dead tuples chỉ làm tốn ổ cứng, query vẫn dùng index thì chắc không sao.”

Không hẳn.

Khi dead tuples tích lũy, tác động xấu không chỉ ở storage:

1. Heap phải đi qua nhiều row version hơn

Ngay cả khi index tìm đúng range, PostgreSQL vẫn phải truy cập heap tuple và kiểm tra visibility theo snapshot. Nếu page chứa nhiều row chết hoặc row version cũ, lượng công việc trên đường đọc tăng lên.

2. Index cũng phình ra

UPDATE trên cột được index thường tạo entry mới trong index. Entry cũ không biến mất ngay. Kết quả là index bloat khiến:

  • tree sâu hơn;
  • nhiều page phải đọc hơn;
  • cache locality xấu hơn;
  • write amplification tăng.

3. Planner dễ ra quyết định tệ khi statistics cũ

Nếu ANALYZE không cập nhật đủ nhanh, planner có thể ước lượng cardinality sai. Từ đó nó chọn nested loop thay vì hash join, hoặc index scan thay vì bitmap scan, hoặc ngược lại. Nhiều incident “query plan tự nhiên xấu đi” thực ra là hỗn hợp giữa bloat và statistics stale.

4. HOT update không cứu được mọi case

PostgreSQL có HOT update (Heap-Only Tuple) để giảm tác động index khi update các cột không tham gia index. Đây là tối ưu rất đáng giá, nhưng nó không phải chiếc đũa thần:

  • chỉ hoạt động khi update không đụng cột indexed;
  • cần còn chỗ trên page;
  • workload thực tế thường vẫn có nhiều update phá vỡ điều kiện HOT.

Nói ngắn gọn: nếu một bảng nóng bị update/delete thường xuyên mà vacuum/statistics không theo kịp, độ chậm sẽ tích lũy theo thời gian, không phải theo kiểu cliff ngay lập tức mà theo kiểu “sao tháng này mọi thứ nặng hơn tháng trước”.

VACUUM thực sự làm gì, và tại sao nó không giải phóng file ngay như nhiều người nghĩ

VACUUM trong PostgreSQL có hai vai trò lớn:

  1. đánh dấu space của dead tuples là có thể tái sử dụng;
  2. cập nhật visibility map, hỗ trợ index-only scan và tránh full freeze chậm trễ về sau.

Điểm quan trọng: VACUUM thường không trả dung lượng file về hệ điều hành ngay. Nó làm cho không gian bên trong table/index có thể được tái sử dụng cho insert/update tiếp theo.

Vì vậy có hai câu chuyện khác nhau:

  • logical reclaim: database tái sử dụng space nội bộ;
  • physical shrink: file trên disk nhỏ lại.

Muốn shrink vật lý mạnh tay thường phải dùng:

  • VACUUM FULL — lock nặng, rewrite table;
  • CLUSTER trong vài bài toán đặc thù;
  • pg_repack để giảm lock hơn trong nhiều case production;
  • REINDEX hoặc REINDEX CONCURRENTLY nếu index bloat là chính.

Đây là lý do câu “đã vacuum rồi mà table vẫn to” hoàn toàn có thể đúng mà không có gì bất thường.

Autovacuum: cứu tinh mặc định, nhưng không phải luôn đủ cho workload nóng

Phần lớn production PostgreSQL dựa vào autovacuum. Đây là cơ chế background tự chạy VACUUM/ANALYZE theo ngưỡng để tránh database mục ruỗng dần.

Mặc định, autovacuum kích hoạt vacuum cho một table khi số dead tuples vượt công thức gần như:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

Và analyze theo công thức tương tự với threshold/scale factor riêng.

Vấn đề là mặc định này hợp với workload vừa phải, nhưng dễ kém hiệu quả ở hai cực:

Bảng rất lớn

Nếu table có 100 triệu row và autovacuum_vacuum_scale_factor = 0.2, hệ thống có thể chờ tới hàng chục triệu dead tuples mới kích hoạt vacuum. Với bảng nóng, lúc đó đã quá muộn.

Bảng nhỏ nhưng cực nóng

Một bảng chỉ vài trăm nghìn row nhưng update hàng nghìn lần mỗi giây có thể tạo churn lớn hơn nhiều so với kích thước logic của nó. Nếu cost delay cao hoặc worker không đủ, autovacuum cứ chạy sau lưng workload chứ không đuổi kịp.

Transaction sống quá lâu

Đây là killer rất hay bị bỏ qua. Nếu có transaction mở quá lâu — ví dụ report query, logical replication slot lag, session quên commit, hoặc transaction idle in transaction — VACUUM có thể dọn ít hơn mong đợi vì vẫn phải giữ row version cũ cho snapshot đó.

Kết quả là anh thấy autovacuum chạy nhưng n_dead_tup không giảm đủ nhanh.

Dấu hiệu production cho thấy MVCC/VACUUM/bloat đang là thủ phạm

Không nên đoán bằng cảm giác. Có một số tín hiệu thực chiến đáng theo dõi cùng nhau.

1. n_dead_tup tăng kéo dài ở các bảng nóng

Trong pg_stat_user_tables, nếu vài bảng liên tục có n_dead_tup cao và không quay đầu sau các chu kỳ autovacuum, đó là dấu hiệu cần điều tra.

Nhưng đừng chỉ nhìn số tuyệt đối. Hãy nhìn theo:

  • tỷ lệ so với n_live_tup;
  • tốc độ tăng theo giờ/ngày;
  • tương quan với latency và WAL volume.

2. last_autovacuum quá xa hoặc autovacuum_count thấp bất thường

Nếu bảng nóng mà last_autovacuum rất cũ, có thể worker không đủ, table-level reloptions chưa đúng, hoặc autovacuum đang bị chặn bởi long transaction.

3. Query cùng filter nhưng số block read/hit tăng dần

Khi dùng EXPLAIN (ANALYZE, BUFFERS) hoặc đọc thống kê từ pg_stat_statements, nếu cùng query pattern mà shared/local block usage tăng dần theo thời gian, hãy nghĩ tới bloat trước khi đổ hết cho business logic.

4. Size bảng và index tăng nhanh hơn tốc độ tăng dữ liệu logic

Nếu row count chỉ tăng 10% nhưng total relation size tăng 40–60%, đó là tín hiệu không lành. Với bảng update-heavy, điều này rất thường gặp.

5. Autovacuum xuất hiện nhiều nhưng CPU/IO vẫn căng

Autovacuum chạy liên tục không có nghĩa vấn đề đã được giải quyết. Nó có thể đang ở trạng thái “đuổi theo mà không bắt kịp”. Trong case này, tăng worker hoặc giảm cost delay đôi khi cần thiết, nhưng phải làm có kiểm soát để không giết workload foreground.

Bloat không chỉ ở table, index mới là thứ hay làm query xuống cấp rõ nhất

Rất nhiều team tập trung vào table size mà quên index bloat.

Index bloat nguy hiểm vì nó ảnh hưởng trực tiếp đến nhiều query path vốn trông vẫn “có index đầy đủ”. Các dấu hiệu gồm:

  • index size tăng rất nhanh so với row count;
  • index scan latency tăng;
  • cache churn do index lớn vượt working set trước đây;
  • write path nặng hơn vì maintenance trên index phình to.

Nhất là với workload có nhiều update trên cột indexed, hoặc delete/insert liên tục theo batch, index bloat có thể là phần đắt nhất.

Khi đó VACUUM không phải lúc nào cũng đủ để index quay về hình dạng gọn gàng. Cần cân nhắc:

  • REINDEX INDEX CONCURRENTLY cho index riêng lẻ;
  • REINDEX TABLE CONCURRENTLY khi nhiều index cùng phình;
  • pg_repack nếu muốn rewrite lại table/index với lock ngắn hơn.

Điểm cần nhớ: REINDEX giải quyết cấu trúc index, không giải quyết gốc churn nếu application pattern vẫn tệ và autovacuum/statistics vẫn sai.

Case rất hay gặp: bảng queue hoặc jobs update liên tục

Một anti-pattern vận hành phổ biến là dùng PostgreSQL làm hàng đợi đơn giản:

  • worker poll job theo trạng thái;
  • update status, attempt_count, locked_by, locked_at liên tục;
  • delete hoặc archive muộn;
  • index trên status, run_at, priority bị churn nặng.

Bảng logic có thể chỉ vài trăm nghìn job active, nhưng mỗi job bị update nhiều vòng. Đây là công thức hoàn hảo cho bloat nếu không thiết kế cẩn thận.

Những hướng giảm áp lực thường hiệu quả hơn so với chỉ tuning database:

  • giảm số lần update heartbeat;
  • chuyển phần ephemeral state sang memory/Redis nếu hợp lý;
  • partition theo thời gian hoặc trạng thái;
  • tách archive table;
  • tránh index quá nhiều trên cột biến động mạnh.

Tức là đôi khi gốc vấn đề không nằm ở autovacuum_vacuum_cost_limit, mà nằm ở mô hình ghi của application.

VACUUM, VACUUM ANALYZE, ANALYZE, REINDEX, pg_repack: dùng gì khi nào?

Đây là chỗ dễ bị lạm dụng nhất.

ANALYZE

Dùng khi statistics stale làm planner ước lượng sai, nhưng chưa chắc table bloat nghiêm trọng. Có thể chạy riêng lẻ trên bảng nóng sau các đợt ingest/update lớn.

VACUUM

Dùng để dọn dead tuples, cập nhật visibility map, hỗ trợ reuse space. Đây là hoạt động maintenance chuẩn.

VACUUM ANALYZE

Hợp lý khi muốn vừa dọn dead tuples vừa làm mới statistics. Hay dùng sau batch operation lớn, migration dữ liệu, backfill hoặc khi vừa thấy query plan xấu đi sau churn mạnh.

REINDEX [CONCURRENTLY]

Dùng khi nghi index bloat là thành phần chính hoặc index corruption/logical inefficiency cần rebuild. CONCURRENTLY giảm tác động lock nhưng tốn thời gian hơn.

VACUUM FULL

Chỉ nên dùng rất cẩn thận vì nó rewrite table và đòi lock mạnh. Với production traffic thật, đây thường không phải lựa chọn đầu tiên.

pg_repack

Rất đáng giá trong nhiều môi trường production vì có thể reclaim bloat theo cách ít disruptive hơn VACUUM FULL. Nhưng nó cũng là một thao tác rewrite lớn, cần tính disk headroom, WAL impact và cửa sổ vận hành.

Long transaction là kẻ phá hoại âm thầm của autovacuum

Nếu phải chọn một thứ để dạy mọi backend engineer về PostgreSQL production, tôi sẽ chọn câu này: đừng để transaction sống lâu vô nghĩa.

Các thủ phạm quen thuộc:

  • job nền mở transaction rồi làm quá nhiều I/O ngoài database trước khi commit;
  • admin console hoặc BI query chạy snapshot lâu;
  • connection pool giữ session ở trạng thái idle in transaction;
  • logical replication slot lag;
  • migration script batch quá lớn trong một transaction.

Hệ quả:

  • old row versions phải giữ lại lâu hơn;
  • autovacuum không xóa được nhiều dead tuples;
  • freeze age tăng;
  • table/index bloat nặng dần;
  • nguy cơ wraparound vacuum khẩn cấp tăng về lâu dài.

Nếu production có bloat khó hiểu, hãy kiểm tra transaction age song song với autovacuum metrics. Đây là bước nhiều team bỏ qua nên sửa mãi không trúng gốc.

Tuning autovacuum: nên tinh chỉnh theo table, không chỉ global

Một lỗi vận hành khác là chỉnh global parameter quá mạnh rồi hy vọng mọi bảng đều ổn.

Thực tế, PostgreSQL cho phép set table-level autovacuum reloptions, và đây thường là vũ khí tốt nhất cho bảng nóng.

Ví dụ về tư duy tuning:

  • bảng lớn nhưng mostly append-only: scale factor có thể giữ vừa phải;
  • bảng nhỏ nhưng update-heavy: giảm scale factor mạnh hơn;
  • bảng rất quan trọng cho latency: tăng tần suất analyze;
  • bảng queue/jobs: có thể cần threshold nhỏ hơn rõ rệt.

Các hướng tuning phổ biến:

  • giảm autovacuum_vacuum_scale_factor cho hot tables;
  • giảm autovacuum_analyze_scale_factor để planner học workload sớm hơn;
  • tăng autovacuum_max_workers nếu nhiều bảng nóng cùng lúc;
  • tăng autovacuum_vacuum_cost_limit hoặc giảm cost delay khi maintenance luôn đuối;
  • tăng maintenance_work_mem có chọn lọc theo bài toán vận hành.

Nhưng tuning phải đi cùng giám sát vì đẩy autovacuum quá agressive cũng có giá:

  • IO contention tăng;
  • foreground query bị cạnh tranh tài nguyên;
  • WAL và replication lag có thể tệ hơn trong một số cửa sổ.

Đây là bài toán cân bằng, không có preset thần kỳ dùng cho mọi hệ.

Checklist điều tra khi PostgreSQL “chậm dần theo thời gian”

Khi gặp triệu chứng này, tôi thường đi theo thứ tự sau thay vì nhảy ngay vào thêm index:

1. Khoanh vùng bảng/query nóng

Từ pg_stat_statements, APM, dashboard latency hoặc slow query log, xác định:

  • query nào xuống cấp rõ nhất;
  • bảng nào bị chạm nhiều nhất;
  • read path hay write path đau hơn.

2. So size logic với size vật lý

Đọc table size, index size, row count, growth trend. Nếu kích thước vật lý tăng nhanh hơn nhiều so với dữ liệu logic, nghi bloat.

3. Kiểm tra pg_stat_user_tables

Đặc biệt chú ý:

  • n_live_tup
  • n_dead_tup
  • last_vacuum
  • last_autovacuum
  • last_analyze
  • last_autoanalyze
  • vacuum_count
  • autovacuum_count

4. Kiểm tra long-running transaction và idle in transaction

Nếu bước này bỏ qua, rất dễ chữa ngọn.

5. So query plan hiện tại với plan trước đó

Dùng EXPLAIN (ANALYZE, BUFFERS) trên query representative, đọc số block và row estimate. Nếu estimate lệch mạnh, statistics là nghi phạm lớn. Nếu block usage quá cao so với selectivity, bloat hoặc data locality là nghi phạm.

6. Đánh giá index bloat trước khi rebuild đại trà

Không phải index to nào cũng cần reindex ngay. Hãy ưu tiên index gắn với query latency quan trọng và có dấu hiệu phình bất thường.

7. Chỉnh application write pattern nếu cần

Nếu root cause là job table update mỗi giây hoặc workflow delete/insert không hợp lý, chỉ tuning PostgreSQL sẽ không bền.

Những anti-pattern khiến production PostgreSQL xuống cấp rất nhanh

Update timestamp vô nghĩa trên bảng cực lớn

Ví dụ mỗi lần đọc hoặc poll lại update last_seen_at trên một bảng lớn có index liên quan. Đó là cách tự tạo bloat liên tục cho rất ít giá trị nghiệp vụ.

Index mọi thứ “để sau này có thể query”

Mỗi index thêm vào làm write path nặng hơn và tăng bề mặt bloat. Index phải có lý do rõ dựa trên access pattern thật.

Batch delete khổng lồ không có chiến lược

Xóa một lượng lớn row trong một transaction rồi bỏ đó sẽ tạo dead tuples lớn, spike WAL và gây maintenance debt. Partitioning hoặc purge theo batch nhỏ thường tốt hơn.

Chỉ nhìn CPU mà không nhìn heap/index health

Nhiều dashboard application-centric làm team thấy CPU database chỉ 40% nên tưởng PostgreSQL “còn khỏe”. Nhưng latency có thể đang xấu vì block churn, planner estimate sai và table/index đã phình quá mức tối ưu.

Một góc nhìn quan trọng: bloat là vấn đề của cả database lẫn thiết kế ứng dụng

VACUUM là cơ chế cực tốt, nhưng nó không phải cái cớ để application ghi bừa rồi mong database tự dọn hết. Production bền hơn khi team coi MVCC như một phần của design constraint.

Ví dụ, khi thiết kế một bảng trạng thái nóng, nên tự hỏi:

  • có cần update row liên tục không;
  • có thể append event rồi materialize trạng thái ở nơi khác không;
  • có thể partition để giới hạn working set không;
  • có thể tránh index trên trường thay đổi liên tục không;
  • có thể archive hoặc TTL data cũ theo lô không.

Đây là kiểu câu hỏi nối backend engineering với data engineering. Và nó gần với vận hành thật hơn nhiều so với các bài “PostgreSQL nhanh vì open source và mạnh”.

Kết luận

PostgreSQL không “tự nhiên chậm dần”. Thường sẽ có một cơ chế cụ thể phía sau: MVCC tạo row version, workload tạo dead tuples, autovacuum không theo kịp, statistics stale, index phình ra, rồi planner và IO path bắt đầu trả giá.

Nếu team hiểu được mối liên hệ giữa MVCC → dead tuples → vacuum/analyze → bloat → plan quality → query latency, rất nhiều incident sẽ được bắt sớm hơn nhiều.

Điều đáng mừng là đây không phải vấn đề mơ hồ. Nó đo được, điều tra được và sửa được. Nhưng cách sửa hiếm khi chỉ là chạy một lệnh maintenance. Hầu như luôn cần phối hợp giữa:

  • quan sát đúng metrics;
  • tuning autovacuum theo bảng;
  • xử lý transaction sống lâu;
  • bảo trì index/table có chọn lọc;
  • và quan trọng nhất, chỉnh lại write pattern của application khi nó đang chống lại cơ chế MVCC.

Một PostgreSQL production khỏe không chỉ là có index và connection pool. Nó là một hệ mà team hiểu cái giá của mỗi UPDATE lặp đi lặp lại theo thời gian.

---

  • Index trong PostgreSQL → /postgresql-index-query-performance/

    • - Lý do: nối từ index design sang index bloat và read amplification.

  • PostgreSQL Connection Pooling với PgBouncer → /postgresql-connection-pooling-pgbouncer-production/

    • - Lý do: phân biệt bottleneck ở connection layer với bottleneck do heap/index health.

  • Keyset Pagination vs Offset Pagination → /keyset-pagination-vs-offset-pagination/

    • - Lý do: pagination query trên bảng lớn sẽ xuống cấp nhanh hơn khi bloat tăng.

  • Database Migration Zero Downtime → /database-migration-zero-downtime/

    • - Lý do: migration/backfill dễ tạo churn, WAL spike và statistics stale.

  • Deploy Backend lên Production Checklist → /deploy-backend-len-production-checklist/

    • - Lý do: thêm góc database maintenance vào checklist release và hậu kiểm.

Social distribution angle

  • Hook: PostgreSQL hiếm khi chậm đi vì “server yếu”. Nó thường chậm dần vì MVCC sinh dead tuples, autovacuum không theo kịp và index/table bloat tích lũy theo thời gian.
  • Post angle: bài này giúp backend engineer nối được chuỗi nguyên nhân từ UPDATE tưởng như vô hại tới latency tăng sau vài tháng production.
  • CTA mềm: nếu team đang thấy query cũ chậm dần dù traffic không tăng tương ứng, đây là checklist nên đọc trước khi đổ lỗi cho ORM hay nâng server.