Storage Internals & VacuumStorage Internals & Vacuum

Thuộc bộ kiến thức PostgreSQL DBA Roadmap.

Tổng quan

Tất cả các chủ đề trước trong series này coi “row” là một khái niệm logic — thứ bạn INSERT, SELECT, và suy luận bằng các quy tắc snapshot của MVCC (xem 09 — Transactions and Concurrency Control). Bài này đi xuống dưới lớp trừu tượng đó để nhìn vào thực tế vật lý: một database trở thành một thư mục trên disk như thế nào, một table trở thành một hoặc nhiều file ra sao, những file đó được cắt thành các page kích thước cố định thế nào, và một row bạn viết bằng SQL trở thành một “tuple” nằm bên trong một trong các page đó ra sao. Chúng ta cũng bàn đến kiến trúc process và memory đọc/ghi các page đó, Write-Ahead Log (WAL) — cơ chế đảm bảo mọi thay đổi đều durable và an toàn khi crash, checkpoint — thứ định kỳ đồng bộ trạng thái in-memory với disk, và — mối quan tâm vận hành mang tính đặc thù PostgreSQL nhất trong toàn bộ roadmap này — VACUUM và autovacuum, cơ chế dọn dẹp hậu quả của mô hình update kiểu append-only của MVCC và ngăn chặn một failure mode thực sự thảm khốc gọi là transaction ID wraparound.

Nếu chỉ rút ra một ý từ bài này, hãy nhớ điều này: thiết kế MVCC của PostgreSQL (đã trình bày ở mức logic trong 09) nghĩa là một UPDATE không bao giờ ghi đè row hiện có tại chỗ — nó ghi một version row hoàn toàn mới và để lại version cũ, đã “chết” nhưng vẫn còn tồn tại vật lý. Không có gì tự động xóa các version chết đó như một phần của bản thân UPDATE hay DELETE. VACUUM là process quay lại sau đó để thu hồi khoảng trống đó. Bỏ qua nó, tune nó quá bảo thủ, hoặc để nó tụt lại phía sau trên một table bận rộn, và bạn sẽ có table bloat, query chậm hơn, và — trong trường hợp xấu nhất — bị buộc phải shutdown về chế độ read-only để bảo vệ tính toàn vẹn dữ liệu. Hiểu storage internals là điều làm cho câu nói đó có ý nghĩa thay vì nghe như truyền miệng dân gian.

Bài này giả định bạn đã quen với từ vựng schema-object từ 03 — Data Types and Schema Objects và mô hình MVCC/snapshot từ 09 — Transactions and Concurrency Control. WAL cũng chính là cơ chế nền tảng cho streaming replication, được trình bày sâu ở 13 — Replication and High Availability; các view monitoring được nhắc tới xuyên suốt (pg_stat_user_tables, pg_stat_activity, các query bloat) được mở rộng thêm ở 16 — Monitoring, Logging and Diagnostics.

Kiến thức nền tảng

Từ database đến thư mục: cấu trúc file vật lý

Một PostgreSQL server đang chạy có một data directory (PGDATA, thường là kiểu /var/lib/postgresql/17/main) là gốc của mọi thứ trên disk. Bên trong:

PGDATA/
├── base/                  # một thư mục con cho mỗi database, đặt tên theo OID
│   ├── 1/                 # template1
│   ├── 13394/             # template0
│   └── 16correlated...    # các database của bạn, mỗi database một thư mục
├── global/                 # các table dùng chung toàn cluster (pg_database, pg_authid, ...)
├── pg_wal/                 # các segment Write-Ahead Log (mặc định 16MB mỗi segment)
├── pg_xact/                 # bitmap trạng thái commit của transaction (trước đây là pg_clog)
├── pg_multixact/            # trạng thái multixact, dùng cho shared row lock
├── pg_tblspc/                # symlink tới vị trí các tablespace
├── pg_stat/                  # dữ liệu thống kê được lưu bền vững
├── postgresql.conf
├── pg_hba.conf
└── PG_VERSION

base/<db-oid>/ chứa một thư mục con cho mỗi database. Bên trong thư mục của một database, mỗi table và index được biểu diễn trên disk bằng một relfilenode — tên file thường là một số nguyên đơn giản (ví dụ 16412), khác với OID của table (chúng bắt đầu bằng nhau nhưng có thể khác nhau sau các thao tác như VACUUM FULL, CLUSTER, hay TRUNCATE, vốn gán một relfilenode mới). Bạn có thể tìm file bên dưới một table bằng:

SELECT pg_relation_filepath('orders');
-- ví dụ: base/16correlated/16412

Segment: một table tách thành nhiều file như thế nào

Một file relfilenode duy nhất bị giới hạn ở mức 1 GB theo mặc định (RELSEG_SIZE, cố định lúc compile, trên thực tế gần như không bao giờ thay đổi). Khi segment đầu tiên của một table đầy, PostgreSQL tạo file thứ hai tên <relfilenode>.1, rồi <relfilenode>.2, và cứ thế:

16412        <- 1 GB đầu tiên
16412.1      <- 1 GB thứ hai
16412.2      <- 1 GB thứ ba

Việc chia segment này tồn tại chủ yếu để tương thích với các filesystem có giới hạn kích thước file và để giữ từng file dễ quản lý cho các công cụ backup; về mặt logic nó hoàn toàn trong suốt — PostgreSQL ghép các segment lại thành một không gian địa chỉ liên tục khi đọc table. Một table 50 GB, về mặt vật lý, là khoảng năm mươi file 1 GB cộng với số file mà các index của nó cần.

Bên cạnh file dữ liệu chính, một table thường có vài file đi kèm chung tiền tố relfilenode:

Hậu tố	Vai trò
(không có)	Fork dữ liệu chính — các heap page thực sự (tuple)
_fsm	Free Space Map — theo dõi mỗi page còn bao nhiêu chỗ trống, để việc insert có thể tìm page còn chỗ mà không cần quét toàn bộ table
_vm	Visibility Map — một bit (và, từ PG 9.6, thêm bit “all-frozen”) cho mỗi page, đánh dấu các page mà mọi tuple đều visible với mọi transaction; giúp index-only scan hoạt động và giúp VACUUM bỏ qua các page không cần dọn

Page (block): đơn vị I/O cơ bản

PostgreSQL không bao giờ đọc hay ghi thứ gì nhỏ hơn một page (còn gọi là block), mặc định là 8 KB (BLCKSZ, một hằng số lúc compile; thay đổi nó cần build tùy chỉnh, trên thực tế hiếm khi làm). Mỗi file relation chỉ đơn giản là một chuỗi các page kích thước cố định này, được đánh địa chỉ bằng số thứ tự page bắt đầu từ 0.

Một heap page (loại lưu row của table) có cấu trúc nội bộ như sau:

+----------------------------------------------------+
| PageHeaderData (24 bytes)                           |
+----------------------------------------------------+
| Mảng ItemId (line pointer, mỗi cái 4 bytes)          |  <- mọc dần xuống dưới
| ...                                                  |
+----------------------------------------------------+
|                  khoảng trống                        |
+----------------------------------------------------+
| ...                                                  |
| Dữ liệu tuple (các row version thực sự)              |  <- mọc dần lên trên
+----------------------------------------------------+
| Special space (dùng cho index, rỗng với heap)        |
+----------------------------------------------------+

Mảng line pointer (ItemIdData) mọc dần từ đầu page; dữ liệu tuple thực sự được đóng gói dồn từ cuối page lên. Mỗi line pointer là một slot nhỏ, kích thước cố định, chứa offset và length trỏ tới bytes tuple thực sự ở nơi khác trong page — sự gián tiếp này chính là thứ cho phép một page được tổ chức lại (tuple di chuyển, nén gọn) mà không đổi địa chỉ nhìn thấy từ bên ngoài của tuple, vì mọi thứ bên ngoài page tham chiếu tới một cặp (số page, số line pointer), không phải một byte offset thô. Cặp đó là TID (tuple identifier), địa chỉ vật lý của một row version, hiển thị qua cột hệ thống ẩn ctid:

SELECT ctid, * FROM orders WHERE order_id = 42;
--   ctid
-- --------
--  (3,7)      -- page 3, line pointer 7

Tuple: row trên disk

Một tuple là một version vật lý của một row. Mỗi tuple mang theo một header (HeapTupleHeaderData, tối thiểu 23 bytes) bên cạnh dữ liệu cột thực sự:

Trường	Vai trò
t_xmin	Transaction ID (XID) đã insert version tuple này
t_xmax	XID đã xóa/update-away version tuple này (0 nếu vẫn còn sống)
t_ctid	Trỏ tới version tuple tiếp theo nếu tuple này đã bị update (tạo thành một chuỗi update); tự trỏ vào chính nó nếu đây là version hiện hành
t_infomask / t_infomask2	Các bit flag: xmin/xmax đã commit hay chưa, tuple có NULL không, có giá trị TOASTed không, v.v.
null bitmap	Cột nào là NULL, để cột NULL không tốn chỗ lưu trữ ngoài một bit

Đây chính là cơ chế vật lý đứng sau các quy tắc visibility của MVCC được mô tả ở mức logic trong 09 — Transactions and Concurrency Control: một transaction với snapshot XID S coi một tuple là visible nếu t_xmin đã commit trước khi S bắt đầu và (t_xmax chưa được set, hoặc transaction của t_xmax chưa commit, hoặc commit sau khi S bắt đầu). Không tuple nào từng bị “thay đổi” tại chỗ để phản ánh view của một transaction khác — visibility được tính bằng cách so sánh các trường này với snapshot của người đọc.

TOAST: khi một row không vừa trong một page

Vì một tuple duy nhất phải vừa trong một page 8 KB (còn phải chừa chỗ cho các tuple khác — một page chứa nhiều row), PostgreSQL không thể lưu, ví dụ, một giá trị text 50 KB inline. TOAST (The Oversized-Attribute Storage Technique) tự động nén và/hoặc chuyển các giá trị cột lớn ra một TOAST table riêng, chỉ để lại một con trỏ nhỏ trong tuple chính. Điều này trong suốt với SQL — bạn SELECT cột đó và nhận lại đầy đủ giá trị — nhưng nó giải thích tại sao các row rất rộng với cột text/jsonb/bytea lớn không đơn giản bị lỗi khi insert, và tại sao các table như vậy có một table đi kèm pg_toast.pg_toast_<oid> mà bạn sẽ thấy khi kiểm tra pg_class.

Khái niệm chính

Kiến trúc process: postmaster, backend, và background worker

PostgreSQL là một hệ thống đa process (không phải đa thread). Sơ đồ dạng text của kiến trúc này:

postmaster (process giám sát, PID trong postmaster.pid)
│
├── backend process  ── một cái cho mỗi kết nối client (fork() khi connect)
├── backend process
├── backend process  ── ... (hoặc một pooler như PgBouncer đứng trước để giới hạn số này)
│
├── background writer (bgwriter)      ── rải dần các dirty page trong shared_buffers ra disk
├── checkpointer                       ── thực hiện checkpoint (từ PG 9.2, tách ra khỏi bgwriter)
├── WAL writer                          ── định kỳ flush WAL buffer ra pg_wal/
├── autovacuum launcher                 ── quyết định table nào cần vacuum/analyze, spawn worker
│    └── autovacuum worker(s)           ── một hoặc nhiều, thực sự chạy VACUUM/ANALYZE
├── stats collector / stats subsystem   ── tổng hợp các bộ đếm hoạt động pg_stat_*
├── logical replication launcher        ── quản lý logical replication worker, nếu có dùng
└── WAL sender(s)                       ── stream WAL tới replica, nếu có cấu hình replication

postmaster là process đầu tiên được khởi động và không bao giờ tự xử lý một query nào — nó lắng nghe trên TCP/Unix socket, và với mỗi kết nối tới, nó fork ra một backend process riêng đảm nhận session đó trong suốt vòng đời của nó. Đây là lý do vì sao kết nối PostgreSQL tương đối tốn kém để thiết lập (fork toàn bộ process, không phải thread nhẹ) và vì sao connection pooling (PgBouncer, pgbouncer, hoặc pooling tích hợp sẵn ở một số managed service) lại quan trọng đến vậy khi số kết nối biến động cao — điều này được bàn từ góc độ phục vụ query ở 10 — Query Planning and Performance Tuning.

Shared memory: shared_buffers và WAL buffer

Tất cả backend process chia sẻ một vùng memory được cấp phát lúc khởi động, kích thước chủ yếu được quyết định bởi hai setting:

• shared_buffers — cache dùng chung của các page 8 KB, giữ các page table và index được dùng gần đây trong memory để việc truy cập lặp lại không phải chạm disk. Thường được tune ở khoảng 25% RAM của hệ thống làm điểm khởi đầu (cao hơn không phải lúc nào cũng tốt hơn, vì OS page cache cũng cache lại các file này một cách dư thừa — PostgreSQL cố tình dựa vào double-buffering giữa shared_buffers của riêng nó và OS cache theo thiết kế).
• wal_buffers — một vùng nhỏ hơn giữ các WAL record đã được sinh ra nhưng chưa flush ra file WAL trên disk. Mặc định là một phần của shared_buffers (tự động tune từ PG 9.1), hiếm khi cần chỉnh tay trừ khi workload ghi rất nặng.

SHOW shared_buffers;
SHOW wal_buffers;
SELECT pg_size_pretty(setting::bigint * 8192) FROM pg_settings WHERE name = 'shared_buffers';

Một page mà backend cần được tìm trước trong shared_buffers; nếu miss, nó được đọc từ disk (qua OS, vốn có thể đã cache sẵn) vào một slot của shared_buffers. Một page bị sửa trong memory được đánh dấu dirty và không nhất thiết được ghi lại xuống disk ngay lập tức — đó là việc của background writer và checkpointer, sẽ bàn tiếp theo.

Write-Ahead Log (WAL)

Quy tắc durability cốt lõi

WAL là cơ chế durability quan trọng nhất trong PostgreSQL, và quy tắc rất đơn giản: trước khi bất kỳ thay đổi nào lên một data page được phép chạm tới disk, một record mô tả thay đổi đó phải được ghi (và, lúc transaction commit, flush/fsync) vào WAL trước. Bản thân các data page — các heap page và index page thực sự trong shared_buffers — có thể vẫn dirty trong memory rất lâu và được flush ra disk sau, một cách lười biếng, bởi background writer hoặc checkpoint. Thứ không thể trì hoãn là WAL record: PostgreSQL sẽ không báo cho client rằng transaction đã commit cho đến khi WAL record tương ứng đã nằm bền vững trên disk.

Thứ tự này chính xác là ý nghĩa của “write-ahead”, và nó là thứ làm cho crash recovery khả thi. Nếu server crash (mất điện, bị OOM kill, kernel panic) tại một thời điểm bất kỳ, một số dirty data page sẽ chưa kịp ghi xuống disk, nhưng thay đổi của mọi transaction đã commit sẽ tồn tại trong WAL. Khi restart, PostgreSQL thực hiện crash recovery: nó replay các WAL record bắt đầu từ checkpoint gần nhất trở đi, áp dụng lại từng thay đổi cho đến khi chạm điểm log thực sự kết thúc, tái tạo chính xác trạng thái đã tồn tại tại thời điểm crash — không hơn không kém. WAL record của các transaction chưa commit cũng có mặt, nhưng chúng được nhận diện là chưa commit và hiệu lực của chúng không được coi là hợp lệ bởi các reader sau này (nhất quán với các quy tắc visibility của MVCC từ 09).

Các file WAL nằm trong pg_wal/ dưới dạng các segment 16 MB (wal_segment_size, chỉ có thể set lúc initdb) với tên hex kiểu 000000010000000000000001. Bạn có thể kiểm tra vị trí WAL hiện tại:

SELECT pg_current_wal_lsn();
--   pg_current_wal_lsn
-- ---------------------
--  0/3A2F118

LSN (Log Sequence Number) là một offset byte tăng dần đơn điệu vào trong luồng WAL logic — mỗi WAL record đều có một LSN, và đó là thước đo cơ bản “replica này đã bắt kịp tới đâu” được dùng xuyên suốt trong monitoring replication.

Tại sao WAL cũng là nền tảng của replication

Vì WAL là một log đầy đủ, có thứ tự, ghi lại mọi thay đổi vật lý lên mọi data page, việc gửi log đó sang một server khác và replay nó ở đó tạo ra một bản sao chính xác của database, liên tục, với một độ trễ nhỏ. Đây chính là streaming replication: process WAL receiver của một replica hỏi process WAL sender của primary về các WAL record sau một LSN cho trước, và áp dụng chúng khi chúng đến. Không có “log replication” riêng biệt nào cả — đó chính là WAL dùng cho crash recovery, chỉ là được stream thêm qua mạng. Mục đích kép này (durability cục bộ và replication từ xa) là lý do vì sao cấu hình WAL (wal_level, max_wal_senders, replication slot) quan trọng ngay cả trên một setup một server dự định thêm replica sau này; cơ chế đầy đủ ở 13 — Replication and High Availability.

wal_level và các knob durability

wal_level = replica          # minimal | replica | logical — WAL record mang bao nhiêu thông tin
fsync = on                    # không bao giờ tắt trong production — đây CHÍNH LÀ đảm bảo durability
synchronous_commit = on       # on | off | local | remote_write | remote_apply
full_page_writes = on         # bảo vệ trước torn page khi OS/hardware crash

synchronous_commit = off là một toggle nổi tiếng “nhanh hơn nhưng kém durable hơn một chút”: WAL record vẫn được ghi và áp dụng theo đúng thứ tự, nhưng commit của client không đợi WAL flush xuống disk, chấp nhận rủi ro mất vài trăm mili-giây transaction gần nhất nếu crash (nhưng không bao giờ gây corruption hay inconsistency — các đảm bảo về thứ tự WAL không bị ảnh hưởng). Đây là một knob tuning hợp lý cho các workload có thể chấp nhận mất một số ít commit rất gần đây để đổi lấy độ trễ commit thấp hơn; không bao giờ được nhầm lẫn với việc tắt fsync, vốn không an toàn và có thể gây corruption thực sự, chứ không chỉ mất commit gần đây.

Checkpoint & background writer

Checkpoint làm gì

Một checkpoint là thao tác tại một thời điểm cụ thể, flush toàn bộ page đang dirty trong shared_buffers xuống disk rồi ghi một WAL record đặc biệt đánh dấu vị trí của checkpoint đó. Mục đích của nó hoàn toàn xoay quanh việc giới hạn thời gian crash recovery: một khi checkpoint hoàn tất, PostgreSQL biết mọi data page đều nhất quán với toàn bộ WAL tính đến LSN của checkpoint đó, nên ở lần crash tiếp theo, recovery chỉ cần replay WAL được sinh ra sau checkpoint hoàn tất gần nhất — không phải toàn bộ lịch sử WAL từ lúc server khởi động. Các segment WAL hoàn toàn nằm trước checkpoint cũ nhất còn cần cho recovery trở nên đủ điều kiện để tái sử dụng hoặc xóa (tùy thuộc vào replication slot và wal_keep_size giữ lại một phần).

CHECKPOINT;  -- ép một checkpoint ngay lập tức (hiếm khi cần làm thủ công; hữu ích trước khi shutdown có kiểm soát hoặc trước backup)

SELECT * FROM pg_stat_bgwriter;
--  checkpoints_timed | checkpoints_req | ... | buffers_checkpoint | buffers_clean | buffers_backend

checkpoints_timed đếm các checkpoint được kích hoạt bởi timer; checkpoints_req đếm các checkpoint bị kích hoạt sớm vì đã tích lũy quá nhiều WAL. Một hệ thống khỏe mạnh thường có đa số là checkpoints_timed; số checkpoints_req cao báo hiệu max_wal_size quá nhỏ so với lượng ghi, buộc checkpoint xảy ra thường xuyên hơn dự kiến.

Đánh đổi giữa checkpoint thường xuyên và không thường xuyên

	Checkpoint thường xuyên	Checkpoint không thường xuyên
Thời gian crash recovery	Ngắn hơn — ít WAL phải replay	Dài hơn — nhiều WAL phải replay
Kiểu I/O	Mượt hơn, nhưng tổng overhead checkpoint nhiều hơn	I/O tăng vọt lớn hơn khi xảy ra (nhiều dirty page bị flush cùng lúc)
Dung lượng WAL trên disk giữa các checkpoint	Thấp hơn	Cao hơn — phải giữ lại nhiều segment WAL hơn
Overhead full-page-write	Cao hơn — lần sửa đầu tiên của một page sau mỗi checkpoint phải log lại toàn bộ page	Thấp hơn — ít full-page-write này hơn theo thời gian

Hai setting chi phối:

checkpoint_timeout = 5min     # thời gian tối đa giữa các checkpoint tự động
max_wal_size = 1GB            # ngưỡng mềm cho lượng WAL giữa các checkpoint; vượt ngưỡng sẽ kích checkpoint sớm
checkpoint_completion_target = 0.9   # rải I/O của checkpoint ra phần này của checkpoint_timeout

Các default hiện đại (max_wal_size = 1GB là default ngay khi cài đặt nhưng gần như luôn quá nhỏ cho lượng ghi production thực tế) khiến checkpoint kích hoạt thường xuyên hơn nhiều so với chỉ dùng timer 5 phút, đơn giản vì lượng WAL. Tăng max_wal_size lên vài GB (hoặc hàng chục GB trên hệ thống ghi nặng) làm mượt I/O với cái giá là thời gian replay crash-recovery tiềm năng dài hơn và nhiều dung lượng disk dành cho WAL hơn. checkpoint_completion_target gần 1.0 rải các ghi của checkpoint ra gần như toàn bộ khoảng thời gian cho đến checkpoint tiếp theo, tránh một cú tăng vọt I/O đột ngột ngay khi checkpoint bắt đầu.

Vai trò của background writer

background writer (bgwriter) tồn tại để giảm kích thước của cú tăng vọt “flush mọi thứ dirty” mà một checkpoint sẽ gây ra nếu tự nó làm hoàn toàn một mình. Giữa các checkpoint, nó định kỳ quét một phần của shared_buffers và ghi ra một số dirty page chưa được đụng tới gần đây, để đến lúc checkpoint tới, ít page còn dirty cần flush cùng lúc hơn. Nó cố tình bảo thủ (bgwriter_lru_maxpages, bgwriter_delay) — việc của nó là làm mượt, không phải dọn dẹp quyết liệt, vì ghi một page quá sớm (trước khi một backend đã sửa xong nhiều lần) lãng phí I/O.

SELECT buffers_clean, buffers_backend, buffers_checkpoint FROM pg_stat_bgwriter;

buffers_clean (do bgwriter ghi) so với buffers_backend (một backend process bị buộc phải tự ghi dirty page của nó vì cần một buffer mà không có buffer nào sạch) là một tín hiệu sức khỏe hữu ích — buffers_backend cao so với các con số khác gợi ý shared_buffers đang chịu áp lực hoặc bgwriter không theo kịp, và backend đang phải làm việc ghi mà lẽ ra bgwriter hoặc checkpointer đã làm giúp.

Hệ quả ở tầng storage của MVCC: dead tuple

09 — Transactions and Concurrency Control giải thích MVCC từ góc độ visibility/isolation: reader không bao giờ chặn writer, writer không bao giờ chặn reader, vì mỗi transaction thấy một snapshot nhất quán. Cơ chế ở tầng storage làm điều này khả thi chính là các trường trong header tuple đã mô tả ở trên (t_xmin/t_xmax) — và nó có một cái giá vật lý trực tiếp.

Khi bạn chạy UPDATE orders SET amount = 150 WHERE order_id = 42, PostgreSQL không sửa bytes của tuple hiện có tại chỗ. Nó:

1. Ghi một tuple hoàn toàn mới với các giá trị đã update và một t_xmin mới (XID của transaction đang update).
2. Đặt t_xmax trên tuple version cũ thành XID của transaction đang update, đánh dấu nó đã bị thay thế (nhưng không xóa).
3. Liên kết t_ctid của tuple cũ để trỏ tới tuple version mới.

Tuple cũ giờ là một dead tuple: không còn visible với bất kỳ transaction nào bắt đầu sau khi update commit, nhưng vẫn chiếm chỗ vật lý trong page của nó, vì các transaction đã bắt đầu trước khi update xảy ra (và vẫn giữ một snapshot cũ hơn) có thể vẫn cần thấy nó một cách hợp lệ. Một DELETE cũng tạo ra dead tuple — nó không xóa row, chỉ đặt t_xmax và để tuple nguyên tại chỗ, chết kể từ thời điểm đó (một khi không snapshot nào còn cần thấy nó nữa).

Hệ quả: mỗi UPDATE và mỗi DELETE để lại lãng phí. Một table nhận một luồng update liên tục sẽ tích lũy dead tuple liên tục. Nếu không xử lý, các dead tuple này:

• Chiếm dung lượng disk không tự co lại.
• Làm chậm sequential scan và cả index scan, vì storage engine vẫn phải lấy và bỏ qua các page chứa dead tuple trước khi tới được dữ liệu sống (và các entry index trỏ tới dead tuple cũng là gánh nặng thừa cho index scan, cho tới khi được dọn).
• Ngăn không cho khoảng trống cũ được tái sử dụng cho insert/update mới cho đến khi được thu hồi.

Đây là vấn đề mà VACUUM tồn tại để giải quyết.

VACUUM

VACUUM thực sự làm gì

Chạy VACUUM trên một table (hoặc toàn bộ database nếu không có tham số) thực hiện ba việc riêng biệt:

1. Thu hồi khoảng trống từ dead tuple. Nó quét các page của table, xác định các tuple có transaction t_xmax đã commit và không còn visible với bất kỳ snapshot hiện tại/có thể có nào (không còn ai cần thấy chúng nữa), và đánh dấu khoảng trống đó là trống bên trong các page hiện có của table. Khoảng trống này trở nên khả dụng cho hoạt động INSERT/UPDATE tương lai trong chính table đó, được theo dõi qua Free Space Map (_fsm). Điều quan trọng là, VACUUM thường không thu nhỏ file trên disk hay trả khoảng trống về cho hệ điều hành trong trường hợp chung — kích thước file của table trên disk thường giữ nguyên hoặc chỉ co lại nếu các page hoàn toàn rỗng tình cờ nằm ở cuối file (trong trường hợp đó, VACUUM thường có thể truncate chúng).
2. Cập nhật visibility map. Các page mà mọi tuple còn lại đều được biết là visible với mọi transaction sẽ được set bit tương ứng trong fork _vm. Điều này mang lại hai lợi ích: một index-only scan có thể trả lời một query chỉ dùng index, bỏ qua hoàn toàn việc phải ghé heap page, nếu visibility map xác nhận heap page tương ứng là all-visible; và các lần chạy VACUUM tương lai có thể bỏ qua các page đã all-visible và chưa bị sửa từ đó — khiến việc vacuum định kỳ trên một table hầu như tĩnh gần như miễn phí.
3. Ngăn transaction ID wraparound bằng cách freeze các tuple cũ (xem bên dưới) — có lẽ là việc quan trọng nhất về mặt vận hành trong ba việc, vì lơ là nó có thể buộc phải có một outage khẩn cấp toàn database.

VACUUM orders;                 -- thu hồi khoảng trống, không rewrite file
VACUUM (VERBOSE) orders;       -- hiện tiến trình và số liệu chi tiết
VACUUM (ANALYZE) orders;       -- đồng thời cập nhật thống kê planner trong cùng một lượt
VACUUM (VERBOSE, ANALYZE) orders;

VACUUM (không có FULL) chỉ lấy một lock nhẹ (SHARE UPDATE EXCLUSIVE) cho phép đọc và ghi đồng thời vào table — đây chính là lý do vì sao chạy nó định kỳ là an toàn, và vì sao autovacuum có thể chạy liên tục trong production mà không chặn traffic của ứng dụng.

VACUUM FULL: thu hồi khoảng trống về cho OS

VACUUM FULL dùng một cách tiếp cận về cơ bản khác: nó rewrite toàn bộ table vào một file hoàn toàn mới, đóng gói chặt các tuple sống không còn khoảng trống chết, sau đó thay thế file mới vào và bỏ file cũ, thu nhỏ file một cách vật lý và thực sự trả lại khoảng trống đã giải phóng cho hệ điều hành.

VACUUM FULL orders;

Cái giá phải trả là một lock ACCESS EXCLUSIVE trong suốt thời gian rewrite — không đọc, không ghi, không gì có thể chạm vào table cho đến khi hoàn tất. Trên một table lớn điều này có thể có nghĩa là vài phút đến vài giờ table bị chặn hoàn toàn, đây là lý do VACUUM FULL không phải thứ bạn chạy định kỳ hay dùng một cách tùy tiện trên một table production đang sống; nó được dành cho các table đã bloat nghiêm trọng và nơi việc thu hồi dung lượng disk xứng đáng với một cửa sổ bảo trì đã lên lịch (hoặc cho các table bạn biết là an toàn để lock trong thời gian ngắn, như các table tham chiếu nhỏ).

Freeze và transaction ID wraparound

Transaction ID (XID) của PostgreSQL là số nguyên 32-bit. Các so sánh visibility (t_xmin/t_xmax so với một snapshot) được thực hiện bằng số học wraparound modulo-2^32, coi không gian XID như một vòng tròn: “trong quá khứ” và “trong tương lai” là tương đối so với một cửa sổ di chuyển khoảng 2 tỷ transaction ở mỗi hướng. Điều này hoạt động tốt miễn là không tuple nào có t_xmin bị tụt lại quá xa so với bộ đếm XID hiện tại đến mức số học wraparound bắt đầu hiểu nhầm nó là “trong tương lai” so với một transaction mới — điều này sẽ khiến một row cũ, hợp lệ, đã commit đột nhiên và âm thầm trở nên invisible, một lỗi correctness kiểu mất dữ liệu thực sự.

VACUUM ngăn chặn điều này bằng cách freeze các tuple cũ: một khi transaction insert của một tuple đủ cũ (cũ hơn vacuum_freeze_min_age transaction trở về trước, và nó đã commit và visible với mọi người), VACUUM rewrite t_xmin của nó thành một giá trị sentinel đặc biệt gọi là FrozenXID, luôn được coi là “trong quá khứ” bất kể số học wraparound — đánh dấu vĩnh viễn tuple là visible với mọi transaction tương lai mà không phụ thuộc gì vào vị trí của XID gốc nữa.

SELECT relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'orders';
--  relfrozenxid |  age
-- --------------+-------
--     123456789 | 45000

age(relfrozenxid) cho biết bao nhiêu transaction đã trôi qua kể từ XID chưa freeze cũ nhất của table — con số quan trọng cho an toàn wraparound. PostgreSQL cũng theo dõi giá trị toàn cluster:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;

Nếu một table (hoặc toàn cluster) không được vacuum đủ lâu đến mức tuổi này tiến gần autovacuum_freeze_max_age (mặc định 200 triệu), autovacuum bị buộc phải chạy một freeze vacuum quyết liệt, không thể hủy, bất kể các setting khác. Nếu tuổi tiến gần khoảng 2 tỷ (vacuum_failsafe_age, và về mặt lịch sử là bức tường cứng ở khoảng 2^31), PostgreSQL trước tiên sẽ kích hoạt các cảnh báo ngày càng khẩn cấp trong log, và nếu thực sự bị lờ đi, database sẽ từ chối cấp phát transaction ID mới hoàn toàn — thực chất tự ép mình vào chế độ single-user, gần như read-only khẩn cấp để ngăn corruption. Đây là kịch bản outage thảm khốc nhất, hoàn toàn có thể phòng tránh được, trong vận hành PostgreSQL, và nó luôn luôn do vacuum đã bị tắt, bị hỏng, hoặc không thể hoàn tất trên một table nào đó trong thời gian rất dài (một transaction bị treo idle lâu chặn việc dọn dẹp là nguyên nhân gốc rễ kinh điển — xem phần tiếp theo, và hướng dẫn monitoring ở 16 — Monitoring, Logging and Diagnostics).

Điều gì chặn vacuum không cho làm việc

Nguyên nhân phổ biến nhất trong thực tế khiến một table không được vacuum đúng cách không phải là thiếu một setting autovacuum — mà là thứ gì đó giữ lại vô thời hạn “biên giới transaction cũ nhất có thể vẫn cần thấy row cũ”:

• Một transaction chạy lâu (một ứng dụng mở transaction rồi quên commit/rollback, hoặc một người ở prompt psql đang giữa BEGIN) khiến VACUUM không thể coi bất kỳ tuple nào mới hơn snapshot của transaction đó là dead, bất kể nó thực sự đã bị thay thế từ bao lâu.
• Một replication slot bị bỏ quên (xem 13 — Replication and High Availability) giữ lại WAL và, với logical slot, có thể giữ lại vacuum horizon tương tự.
• Session idle in transaction là thủ phạm kinh điển cần tìm:

SELECT pid, state, xact_start, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Autovacuum

Autovacuum quyết định khi nào chạy như thế nào

Autovacuum là cơ chế nền tích hợp sẵn (launcher + các worker process trong sơ đồ process ở trên) chạy VACUUM và ANALYZE tự động, không cần DBA lên lịch cron job. Nó quyết định một table có cần vacuum hay không bằng một công thức ngưỡng dựa trên số row đã thay đổi kể từ lần vacuum trước:

ngưỡng vacuum = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
ngưỡng analyze = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

Các default:

autovacuum_vacuum_threshold = 50           # số dead row cơ bản trước khi cân nhắc vacuum
autovacuum_vacuum_scale_factor = 0.2       # + 20% số row ước tính của table
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1      # + 10% số row ước tính của table
autovacuum_vacuum_cost_delay = 2ms         # độ trễ điều tiết giữa các đợt làm việc bị giới hạn cost
autovacuum_vacuum_cost_limit = -1          # -1 nghĩa là "dùng vacuum_cost_limit" (mặc định 200)
autovacuum_naptime = 1min                  # tần suất launcher kiểm tra xem table nào đủ điều kiện

Với một table 10 triệu row, scale factor mặc định nghĩa là autovacuum đợi khoảng 2 triệu dead tuple (20% cộng ngưỡng cố định nhỏ) trước khi thậm chí cân nhắc vacuum table đó. Trên một table OLTP bận rộn nhận hàng nghìn update mỗi phút, ngưỡng đó có thể mất khá lâu để đạt tới so với tốc độ bloat tích lũy, và đến lúc autovacuum kích hoạt, có thể đã có bloat đáng kể, và bản thân việc vacuum có thể mất nhiều thời gian và cạnh tranh I/O với traffic bình thường.

Tại sao default lại quá bảo thủ với các table bận rộn

Default dựa trên scale factor được thiết kế để hoạt động tương đối ổn trên một dải kích thước table rất lớn mà không cần cấu hình, nhưng nó scale kém ở cả hai đầu: trên các table rất lớn, một ngưỡng dead-tuple cố định 20% đại diện cho một con số tuyệt đối khổng lồ dead row (và bloat tương ứng nghiêm trọng) trước khi vacuum thậm chí kích hoạt; trên các table rất nhỏ, cực kỳ “hot” (một table hàng đợi, một table counter), cùng tỷ lệ phần trăm đó có thể tương ứng với quá ít row để đáng bận tâm, nhưng autovacuum_vacuum_threshold = 50 cố định kết hợp với autovacuum_naptime = 1min vẫn có thể có nghĩa là độ trễ đáng kể giữa các lần vacuum trên một table có tốc độ thay đổi mỗi giây rất cao.

Cách khắc phục tiêu chuẩn là tune per-table qua storage parameter, ghi đè default toàn cluster cho các table hot cụ thể:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- vacuum chỉ sau 1% dead tuple thay vì 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000       -- để autovacuum của table này làm việc nhanh hơn (ít bị throttle hơn)
);

Điều này bảo autovacuum đối xử với orders quyết liệt hơn nhiều so với default toàn cluster, phù hợp với một table ghi nhiều nơi để dead tuple tích lũy tới 20% của một số lượng row có thể khổng lồ sẽ đồng nghĩa với hàng gigabyte bloat và các lượt vacuum chạy lâu. Ngược lại, với các table rất lớn, chủ yếu đọc, thay đổi chậm, default (hoặc thậm chí setting thoải mái hơn) thường ổn, vì có ít thứ cần dọn và chạy vacuum ít thường xuyên hơn tránh I/O không cần thiết.

Cũng đáng để tăng số autovacuum worker chạy song song trên các server có nhiều table đang được ghi tích cực:

autovacuum_max_workers = 3     # mặc định; tăng lên trên các server có nhiều table hot để chúng không phải xếp hàng chờ nhau

Autovacuum và ANALYZE

Autovacuum cũng chạy ANALYZE theo lịch riêng của nó (được điều chỉnh bởi các ngưỡng autovacuum_analyze_* tách biệt), làm mới thống kê planner trong pg_statistic mà query planner phụ thuộc vào để ước tính cardinality (xem 10 — Query Planning and Performance Tuning). Một table được vacuum thường xuyên nhưng không bao giờ được analyze vẫn có thể tạo ra query plan tệ vì planner đang làm việc dựa trên ước tính số row và phân phối đã cũ — hai công việc này liên quan nhưng khác nhau, và VACUUM (ANALYZE) tablename (hoặc để autovacuum làm cả hai) giữ cả hai đều khỏe mạnh cùng nhau.

Bloat của table và index

Bloat là triệu chứng có thể quan sát được, tích lũy, của việc dead tuple không được thu hồi đủ nhanh (hoặc không bao giờ): một table hay index chiếm dung lượng disk vật lý nhiều hơn đáng kể so với mức mà chỉ riêng dữ liệu sống cần, vì các page đầy “lỗ hổng” để lại bởi dead tuple mà autovacuum chưa bắt kịp, hoặc mà VACUUM thường đã giải phóng bên trong table nhưng chưa bao giờ trả về cho OS. Index cũng bloat, và thường tệ hơn cả table — một entry B-tree index trỏ tới một heap tuple đã chết không tự động được nén gọn lại chỉ vì heap tuple đó đã chết; nó cần lượt vacuum riêng của chính nó, và các workload UPDATE nặng trên các cột được index có thể tạo ra các index cực kỳ phân mảnh theo thời gian.

Phát hiện bloat

Không có một hàm pg_table_bloat() tích hợp sẵn duy nhất, nhưng vài cách tiếp cận tiêu chuẩn được dùng trong thực tế:

-- Ước lượng nhanh: so sánh kích thước thực tế với ước tính dựa trên số row sống
SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Kiểm tra hoạt động vacuum/autovacuum gần nhất của mỗi table — một table hiếm khi hoặc chưa bao giờ được vacuum là dấu hiệu cảnh báo
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST;

Để có ước tính chính xác hơn, ở mức page, extension cộng đồng pgstattuple kiểm tra nội dung page thực sự:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');
--  table_len | tuple_count | tuple_len | dead_tuple_count | dead_tuple_len | free_space | free_percent

pg_stattuple_approx('orders') cho ước tính nhanh hơn, dựa trên mẫu, cho các table lớn nơi việc quét chính xác sẽ quá chậm để chạy định kỳ. Nhiều team cũng dùng “bloat estimation query” nổi tiếng trong cộng đồng (dựa trên heuristic từ pg_class/pg_stats, được lưu truyền rộng rãi là “the bloat query”) hoặc các công cụ bên thứ ba như pgstattuple, báo cáo bloat riêng của pg_repack, hoặc các tích hợp monitoring (pganalyze, check_postgres) theo dõi điều này liên tục — xem 16 — Monitoring, Logging and Diagnostics để tích hợp việc này vào monitoring liên tục thay vì kiểm tra một lần.

Cách khắc phục

Với một table đã bloat, có hai cách khắc phục tiêu chuẩn, đánh đổi mức độ nghiêm trọng của lock lấy sự đơn giản vận hành:

• VACUUM FULL — đơn giản nhất để chạy, thu hồi khoảng trống triệt để nhất, nhưng yêu cầu lock ACCESS EXCLUSIVE trong suốt thời gian, khiến nó không phù hợp cho các table lớn trên hệ thống không thể chịu downtime.
• pg_repack — một extension/công cụ CLI được dùng rộng rãi, đạt được cùng kết quả vật lý (một table gọn gàng, không bloat) mà không cần lock độc quyền kéo dài: nó xây dựng một bản sao mới, sạch của table trong nền trong khi bản gốc vẫn hoàn toàn khả dụng cho đọc và ghi, theo dõi các thay đổi đồng thời qua một log, và chỉ lấy một lock độc quyền ngắn ở cuối cùng để hoán đổi các table. Điều này khiến nó trở thành công cụ tiêu chuẩn để de-bloat các table production lớn, luôn hoạt động.

# cách dùng pg_repack (cần cài extension pg_repack và CREATE EXTENSION pg_repack;)
pg_repack --table=orders -d mydb

Để phòng ngừa liên tục thay vì khắc phục một lần, giải pháp bền vững luôn là tune autovacuum quyết liệt hơn trên các table hot cụ thể (như đã trình bày ở trên), chứ không phải liên tục chạy VACUUM FULL/pg_repack như một giải pháp vá lỗi lặp lại cho autovacuum bị tune sai.

Bảng so sánh: VACUUM vs. VACUUM FULL vs. autovacuum

Khía cạnh	VACUUM (thường)	VACUUM FULL	Autovacuum
Lock lấy	SHARE UPDATE EXCLUSIVE — cho phép đọc/ghi đồng thời	ACCESS EXCLUSIVE — chặn mọi đọc và ghi	Giống VACUUM thường (SHARE UPDATE EXCLUSIVE)
Trả khoảng trống về OS	Không (thường) — khoảng trống được giải phóng trong file hiện có, tái sử dụng nội bộ	Có — table được rewrite vật lý và thu nhỏ	Không — giống VACUUM thường
Rewrite file table	Không	Có, hoàn toàn, thành file mới	Không
Cập nhật visibility map	Có	Có (file mới bắt đầu sạch)	Có
Ngăn XID wraparound	Có	Có	Có — đây là việc quan trọng nhất
Chạy tự động	Không — thủ công hoặc script	Không — luôn thủ công	Có — kích hoạt bởi ngưỡng dead-tuple
Kích hoạt điển hình	Bảo trì ad hoc, trước khi biết trước có bulk-delete cần dọn, job đã script	Bloat hiện có nghiêm trọng, cần lấy lại dung lượng disk, có thể chịu cửa sổ bảo trì	Sức khỏe table liên tục, đang diễn ra
Phù hợp nhất cho	Dọn dẹp định kỳ, an toàn để chạy bất cứ lúc nào	Table đã bloat sẵn, table nhỏ/ít truy cập, bảo trì một lần	Cơ chế mặc định, liên tục cho về cơ bản mọi table

Best Practices

• Không bao giờ tắt autovacuum toàn cluster (autovacuum = off) trong production; nếu bắt buộc phải tắt tạm thời trên một table để làm bulk operation, bật lại ngay sau đó và theo sau bằng một VACUUM (ANALYZE) thủ công.
• Tune autovacuum_vacuum_scale_factor và autovacuum_vacuum_cost_limit per-table cho các table OLTP ghi nhiều thay vì dựa vào default toàn cluster — default được thiết kế cho trường hợp chung, không phải cho các table bận rộn nhất của bạn.
• Theo dõi n_dead_tup, last_autovacuum, và age(relfrozenxid) từ pg_stat_user_tables/pg_class định kỳ (đưa việc này vào alerting mô tả ở 16 — Monitoring, Logging and Diagnostics) thay vì chỉ phát hiện bloat hay rủi ro wraparound sau khi đã có triệu chứng (query chậm, vacuum khẩn cấp đột ngột, hoặc tệ hơn, một outage bị buộc phải xảy ra).
• Chủ động truy tìm và kill các session idle in transaction và các replication slot bị bỏ quên — đây là nguyên nhân phổ biến nhất trong thực tế khiến vacuum không thể tiến triển dù đã được cấu hình đúng.
• Dùng pg_repack thay vì VACUUM FULL để de-bloat bất kỳ table nào phải giữ khả dụng liên tục; dành VACUUM FULL cho các table nhỏ hoặc các cửa sổ bảo trì thực sự.
• Định kích thước max_wal_size theo lượng ghi thực tế của bạn thay vì giữ nguyên default nhỏ — một giá trị quá nhỏ buộc các sự kiện checkpoints_req xảy ra thường xuyên ngoài ý muốn và ảnh hưởng đến throughput ghi; kiểm chứng bằng pg_stat_bgwriter.
• Không bao giờ tắt fsync trong production để “cải thiện hiệu năng” — đây không phải một đánh đổi durability/hiệu năng như synchronous_commit, nó có nguy cơ gây corruption dữ liệu thực sự, không chỉ mất commit gần đây.
• Coi rủi ro transaction ID wraparound là một SLA vận hành cứng, không phải một điều tò mò — cảnh báo từ rất sớm trước khi age(datfrozenxid) tiến gần autovacuum_freeze_max_age, vì failure mode ở cuối con đường là một outage toàn bộ database, không phải chỉ suy giảm hiệu năng.

Tài liệu tham khảo

• PostgreSQL Documentation — Database Physical Storage
• PostgreSQL Documentation — Reliability and the Write-Ahead Log
• PostgreSQL Documentation — Routine Vacuuming
• PostgreSQL Documentation — Preventing Transaction ID Wraparound Failures
• PostgreSQL Documentation — WAL Configuration (checkpoints, wal_level)
• PostgreSQL Documentation — The Statistics Collector / pg_stat views
• Hironobu Suzuki, The Internals of PostgreSQL — Chapter 6: Storage Management, Chapter 9: Concurrency Control (Vacuum)
• pg_repack — GitHub / documentation

PGDATA/
├── base/                  # one subdirectory per database, named by OID
│   ├── 1/                 # template1
│   ├── 13394/             # template0
│   └── 16correlated...    # your databases, one folder each
├── global/                 # cluster-wide tables (pg_database, pg_authid, ...)
├── pg_wal/                 # Write-Ahead Log segments (16MB each by default)
├── pg_xact/                 # transaction commit-status bitmap (formerly pg_clog)
├── pg_multixact/            # multixact status, for shared row locks
├── pg_tblspc/                # symlinks to tablespace locations
├── pg_stat/                  # permanent statistics data
├── postgresql.conf
├── pg_hba.conf
└── PG_VERSION

SELECT pg_relation_filepath('orders');
-- e.g. base/16correlated/16412

16412        <- first 1 GB
16412.1      <- second 1 GB
16412.2      <- third 1 GB

+----------------------------------------------------+
| PageHeaderData (24 bytes)                           |
+----------------------------------------------------+
| ItemId array (line pointers, 4 bytes each)           |  <- grows downward
| ...                                                  |
+----------------------------------------------------+
|                    free space                        |
+----------------------------------------------------+
| ...                                                  |
| Tuple data (actual row versions)                     |  <- grows upward
+----------------------------------------------------+
| Special space (used by indexes, empty for heap)      |
+----------------------------------------------------+

SELECT ctid, * FROM orders WHERE order_id = 42;
--   ctid
-- --------
--  (3,7)      -- page 3, line pointer 7

postmaster (the supervisor process, PID in postmaster.pid)
│
├── backend process  ── one per client connection (fork()'d on connect)
├── backend process
├── backend process  ── ... (or a pooler like PgBouncer sits in front to limit these)
│
├── background writer (bgwriter)      ── trickles dirty shared_buffers pages to disk
├── checkpointer                       ── performs checkpoints (since PG 9.2, split out of bgwriter)
├── WAL writer                          ── flushes WAL buffers to pg_wal/ periodically
├── autovacuum launcher                 ── decides which tables need vacuum/analyze, spawns workers
│    └── autovacuum worker(s)           ── one or more, actually run VACUUM/ANALYZE
├── stats collector / stats subsystem   ── aggregates pg_stat_* activity counters
├── logical replication launcher        ── manages logical replication workers, if used
└── WAL sender(s)                       ── stream WAL to replicas, if replication is configured

SHOW shared_buffers;
SHOW wal_buffers;
SELECT pg_size_pretty(setting::bigint * 8192) FROM pg_settings WHERE name = 'shared_buffers';

SELECT pg_current_wal_lsn();
--   pg_current_wal_lsn
-- ---------------------
--  0/3A2F118

wal_level = replica          # minimal | replica | logical — how much info WAL records carry
fsync = on                    # never disable in production — this IS the durability guarantee
synchronous_commit = on       # on | off | local | remote_write | remote_apply
full_page_writes = on         # guards against torn pages on OS/hardware crash

CHECKPOINT;  -- force an immediate checkpoint (rarely needed manually; useful before a controlled shutdown or backup)

SELECT * FROM pg_stat_bgwriter;
--  checkpoints_timed | checkpoints_req | ... | buffers_checkpoint | buffers_clean | buffers_backend

checkpoint_timeout = 5min     # max time between automatic checkpoints
max_wal_size = 1GB            # soft cap on WAL volume between checkpoints; exceeding it triggers an early checkpoint
checkpoint_completion_target = 0.9   # spread checkpoint I/O over this fraction of checkpoint_timeout

SELECT buffers_clean, buffers_backend, buffers_checkpoint FROM pg_stat_bgwriter;

VACUUM orders;                 -- reclaim space, don't rewrite the file
VACUUM (VERBOSE) orders;       -- show detailed progress and stats
VACUUM (ANALYZE) orders;       -- also update planner statistics in the same pass
VACUUM (VERBOSE, ANALYZE) orders;

VACUUM FULL orders;

SELECT relfrozenxid, age(relfrozenxid) FROM pg_class WHERE relname = 'orders';
--  relfrozenxid |  age
-- --------------+-------
--     123456789 | 45000

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC;

SELECT pid, state, xact_start, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
analyze threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

autovacuum_vacuum_threshold = 50           # base number of dead rows before considering vacuum
autovacuum_vacuum_scale_factor = 0.2       # + 20% of the table's estimated row count
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1      # + 10% of the table's estimated row count
autovacuum_vacuum_cost_delay = 2ms         # throttling delay between cost-limited work bursts
autovacuum_vacuum_cost_limit = -1          # -1 means "use vacuum_cost_limit" (default 200)
autovacuum_naptime = 1min                  # how often the launcher checks whether any table qualifies

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- vacuum after just 1% dead tuples instead of 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000       -- let this table's autovacuum work faster (less throttling)
);

autovacuum_max_workers = 3     # default; raise on servers with many hot tables so they don't queue behind each other

-- Quick heuristic: compare actual size to an estimate based on live row count
SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Check last autovacuum/vacuum activity per table — a table that's rarely or never vacuumed is a red flag
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST;

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');
--  table_len | tuple_count | tuple_len | dead_tuple_count | dead_tuple_len | free_space | free_percent

# pg_repack usage (requires the pg_repack extension installed and CREATE EXTENSION pg_repack;)
pg_repack --table=orders -d mydb