Kiến trúc lưu trữ

Chương này mô tả cách PostgreSQL giữ dữ liệu: từ data directory và tablespace, qua page 8 KB và tuple mang thông tin MVCC, đến WAL, checkpoint, rồi chuỗi sao lưu cho phép khôi phục theo thời điểm. Thứ tự các mục đi theo chương lưu trữ SQL Server để hai chương đặt cạnh nhau được. Ví dụ dùng cùng database bán hàng, viết theo quy ước của PostgreSQL: database banhang, tên bảng và cột dạng snake_case.

Mốc là PostgreSQL 17 trên Debian hoặc Ubuntu. Phần lớn nội dung cũng đúng cho PostgreSQL 16. Chỗ hành vi đổi theo phiên bản được ghi ngay tại câu đó. Đường dẫn theo quy ước của gói Debian: data directory /var/lib/postgresql/17/main, tệp cấu hình ở /etc/postgresql/17/main.

1. Bản đồ phân cấp #

Một instance PostgreSQL phục vụ một cluster: một data directory chứa mọi database, một luồng WAL chung, một bộ trạng thái giao dịch chung. SQL Server tách log theo từng database. PostgreSQL không tách. Hệ quả lớn nhất nằm ở sao lưu: base backup và khôi phục theo thời điểm áp cho cả cluster.

Nhánh dữ liệu đi từ cluster xuống tablespace, thư mục con theo database, relation, tệp segment, page và tuple. Tablespace thuộc cluster, không thuộc database: một tablespace chứa thư mục con cho mỗi database có đối tượng nằm trong đó. Nhánh nhật ký gồm pg_wal với các segment WAL 16 MB, và pg_xact giữ trạng thái commit của từng giao dịch.

flowchart TB
  CL["Cluster — /var/lib/postgresql/17/main"]
  DB["Database — biên giới kết nối và pg_dump"]
  TS["Tablespace — một thư mục, dùng chung cho cả cluster"]
  DIR["Thư mục con theo OID của database"]
  REL["Relation — bảng, partition, chỉ mục, bảng TOAST"]
  SEG["Tệp segment — tối đa 1 GB mỗi tệp"]
  PG["Page — 8 KB"]
  TUP["Tuple — một phiên bản của dòng"]
  WAL["pg_wal — segment WAL 16 MB, chung cả cluster"]
  XACT["pg_xact — trạng thái commit"]

  CL --> DB
  CL --> TS
  CL --> WAL
  CL --> XACT
  TS --> DIR
  DB -.-> DIR
  DIR --> REL
  REL --> SEG
  SEG --> PG
  PG --> TUP

Bố trí của banhang trong chương này. Các đường dẫn là volume tách nhau. Tablespace nằm trên cùng ổ vật lý với data directory không thêm băng thông. Máy production tên db01, máy khôi phục tên db-khoiphuc, cấu hình giống chương SQL Server: 8 core, 64 GB RAM.

2. Tệp vật lý #

Data directory #

Theo tài liệu, 200 triệu giao dịch tương ứng khoảng 50 MB pg_xact. Thư mục nhỏ, nhưng thiếu nó thì engine không biết tuple nào đã commit. Từ PostgreSQL 10, pg_xlog đổi tên thành pg_wal và pg_clog thành pg_xact, vì tên cũ có chữ "log" dễ bị coi là nhật ký xóa được. Không xóa và không dọn tay tệp nào trong hai thư mục này. Trên Debian, postgresql.conf và pg_hba.conf nằm ở /etc/postgresql/17/main. Base backup chỉ chép data directory và tablespace, nên không mang theo hai tệp đó. Giữ bản sao cấu hình riêng cùng lịch sao lưu.

Một bảng là những tệp nào #

Mỗi relation có số filenode, lấy trong pg_class.relfilenode. Tệp chính mang đúng số đó, các fork khác thêm hậu tố:

1 GB là 131.072 page. Kích thước segment chỉ đổi được lúc build PostgreSQL. Relation trong tablespace người dùng nằm ở pg_tblspc/<OID tablespace>/PG_17_<catversion>/<OID database>/<filenode>. Thư mục PG_17_<catversion> cho phép phiên bản sau dùng lại cùng thư mục tablespace mà không đụng nhau.

Filenode không cố định. TRUNCATE, REINDEX, CLUSTER, VACUUM FULL và một số dạng ALTER TABLE gán filenode mới, OID của bảng giữ nguyên. Đừng ghi cứng tên tệp vào script. Hỏi engine:

SELECT c.relname, pg_relation_filepath(c.oid) AS duong_dan,
       pg_size_pretty(pg_relation_size(c.oid))        AS main_fork,
       pg_size_pretty(pg_relation_size(c.oid, 'fsm')) AS fsm,
       pg_size_pretty(pg_relation_size(c.oid, 'vm'))  AS vm
FROM pg_class AS c
WHERE c.relname LIKE 'don\_hang\_%' AND c.relkind = 'r'
ORDER BY c.relname;

Kết quả minh họa, sau khi nạp đủ 10 triệu đơn và chạy VACUUM. OID là ví dụ. Cỡ fork main lấy từ phép tính ở mục 4.

Ba partition có dữ liệu đều dưới 1 GB nên mỗi cái chỉ có một tệp main. Chép một tệp relation sang cluster khác không phải là sao lưu: tệp đó cần catalog và pg_xact của chính cluster sinh ra nó để đọc đúng.

Tăng trưởng tệp #

PostgreSQL không có FILEGROWTH. Tệp của relation lớn dần khi cần thêm page, và sang tệp .1 khi chạm 1 GB. Không có bước nới để chỉnh. WAL không có VLF. pg_wal là một dãy segment 16 MB. Sau mỗi checkpoint, segment không còn cần cho crash recovery được tái sử dụng hoặc xóa. min_wal_size (mặc định 80 MB) là phần luôn giữ để tái sử dụng. max_wal_size (mặc định 1 GB) là ngưỡng mềm: WAL sắp vượt mức này thì checkpoint chạy sớm. pg_wal vẫn có thể vượt xa max_wal_size khi archive lỗi hoặc replication slot giữ WAL. Đếm segment, tương tự đếm VLF ở chương SQL Server (cần superuser hoặc vai trò pg_monitor):

SELECT count(*) AS so_segment, pg_size_pretty(sum(size)) AS dung_luong
FROM pg_ls_waldir();

3. Tablespace và partition #

Tablespace là chỗ gần nhất với filegroup, nhưng không giống hẳn:

Tạo tablespace, database và bảng đơn hàng #

Tablespace phụ thuộc metadata trong data directory chính. Mất ổ chứa một tablespace có thể làm cả cluster không đọc được hoặc không khởi động được. Thư mục phải có sẵn, rỗng, và thuộc user hệ điều hành postgres. Lệnh chạy trong psql với quyền superuser trên db01.

CREATE TABLESPACE ts_data    LOCATION '/srv/pg_ssd/ts_data';
CREATE TABLESPACE ts_archive LOCATION '/srv/pg_hdd/ts_archive';

CREATE DATABASE banhang;
ALTER DATABASE banhang SET timezone TO 'Asia/Ho_Chi_Minh';

ngay_tao dùng timestamptz(0), không dùng timestamp(0). Cả hai đều 8 byte. timestamptz lưu một thời điểm tuyệt đối và hiển thị theo timezone của phiên: 2026-10-02 11:58:00+07 và 2026-10-02 04:58:00+00 là cùng một giá trị. Không cần quy ước ngầm "cột này là giờ Việt Nam" khi API phía Azure gửi giờ UTC. recovery_target_time ở mục 10 cũng là timestamptz. Việt Nam không có giờ mùa hè nên +07 cố định. Mọi literal trong chương ghi rõ +07.

\c banhang

CREATE TABLE don_hang (
    don_hang_id   bigint         NOT NULL,
    ngay_tao      timestamptz(0) NOT NULL,
    khach_hang_id int            NOT NULL,
    trang_thai    smallint       NOT NULL,
    tong_tien     numeric(18, 2) NOT NULL,
    PRIMARY KEY (don_hang_id, ngay_tao)
) PARTITION BY RANGE (ngay_tao);

CREATE TABLE don_hang_truoc_2025 PARTITION OF don_hang
    FOR VALUES FROM (MINVALUE) TO ('2025-01-01 00:00:00+07')
    TABLESPACE ts_archive;
CREATE TABLE don_hang_2025 PARTITION OF don_hang
    FOR VALUES FROM ('2025-01-01 00:00:00+07') TO ('2026-01-01 00:00:00+07')
    TABLESPACE ts_archive;
CREATE TABLE don_hang_2026 PARTITION OF don_hang
    FOR VALUES FROM ('2026-01-01 00:00:00+07') TO ('2027-01-01 00:00:00+07')
    TABLESPACE ts_data;
CREATE TABLE don_hang_tu_2027 PARTITION OF don_hang
    FOR VALUES FROM ('2027-01-01 00:00:00+07') TO (MAXVALUE)
    TABLESPACE ts_data;

CREATE INDEX ix_don_hang_khach_hang ON don_hang (khach_hang_id, ngay_tao);

Cận dưới của range thuộc partition, cận trên thì không. Bốn vùng khớp với RANGE RIGHT của chương SQL Server. smallint thay cho tinyint vì PostgreSQL không có kiểu số nguyên 1 byte. Khóa chính phải chứa cột phân vùng, vì mỗi chỉ mục chỉ kiểm tra trùng trong partition của nó. Thứ tự cột thì tự do. Khóa chính không quyết định thứ tự vật lý (mục 5), nên (don_hang_id, ngay_tao) đặt cột hay tìm bằng dấu bằng lên trước, ngược với (NgayTao, DonHangId) của SQL Server. Tìm đơn 10042 theo don_hang_id dùng được chỉ mục này dù không biết ngay_tao.

Bảng cha không có tệp. Khóa chính và chỉ mục khai trên bảng cha thành chỉ mục riêng trên từng partition. Theo tài liệu, lệnh tạo chỉ mục không ghi TABLESPACE dùng default_tablespace, mặc định là tablespace của database, tức pg_default trên SSD hệ thống. Chuyển chỉ mục về cùng tablespace với partition. Khối dưới dùng \gexec của psql để chạy từng lệnh mà truy vấn sinh ra. Trên bảng rỗng, mỗi lệnh xong ngay. Kiểm tra kết quả bằng cột tablespace của pg_indexes.

SELECT format('ALTER INDEX %s SET TABLESPACE %s',
              i.indexrelid::regclass,
              CASE WHEN c.relname IN ('don_hang_truoc_2025', 'don_hang_2025')
                   THEN 'ts_archive' ELSE 'ts_data' END)
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indrelid
WHERE c.relname LIKE 'don\_hang\_%'
  AND c.relkind = 'r'
\gexec

Đơn hàng mới rơi vào đâu #

Câu sau chỉ đụng don_hang_2026. Literal có +07 được đổi thành hằng lúc phân tích câu lệnh, nên partition pruning loại các partition khác ngay khi lập kế hoạch. Kế hoạch in ra chỉ nhắc tới don_hang_2026.

EXPLAIN (COSTS OFF)
SELECT don_hang_id, tong_tien
FROM don_hang
WHERE ngay_tao >= '2026-10-01 00:00:00+07'
  AND ngay_tao <  '2026-10-03 00:00:00+07';

Khóa phần lịch sử #

PostgreSQL không có tablespace read-only. REVOKE quyền ghi trên partition cũng không đủ: truy vấn qua bảng cha chỉ kiểm tra quyền trên bảng cha, nên DELETE FROM don_hang vẫn xóa được dòng của partition đã bị REVOKE. Cách chặn ở mức dòng là trigger trên từng partition lịch sử. Trigger dòng chạy cả khi lệnh đi qua bảng cha.

CREATE FUNCTION chan_sua_luu_tru() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION 'partition % chỉ cho đọc', TG_TABLE_NAME;
END $$;

CREATE TRIGGER trg_chi_doc BEFORE INSERT OR UPDATE OR DELETE ON don_hang_truoc_2025
    FOR EACH ROW EXECUTE FUNCTION chan_sua_luu_tru();
CREATE TRIGGER trg_chi_doc BEFORE INSERT OR UPDATE OR DELETE ON don_hang_2025
    FOR EACH ROW EXECUTE FUNCTION chan_sua_luu_tru();

Đây là rào ở tầng ứng dụng: TRUNCATE không kích hoạt trigger dòng, và superuser tắt được trigger. Kịch bản xóa nhầm ở mục 10 giả định hai trigger này chưa được tạo, giống chương SQL Server giả định FG_ARCHIVE chưa read-only. Có trigger thì lệnh xóa lúc 12:07 thất bại và không có gì phải khôi phục.

4. Page và tuple #

Cấu trúc một page #

Hướng mọc ngược với SQL Server, nơi dòng mọc từ trên xuống và slot array mọc từ cuối page lên. Chỗ trống của page là khoảng giữa pd_lower (cuối dãy line pointer) và pd_upper (đầu vùng tuple). Địa chỉ của một tuple là ctid = (số page, số line pointer). Chỉ mục lưu ctid. Engine dời được tuple trong page khi dồn chỗ trống mà không làm hỏng chỉ mục, vì line pointer giữ nguyên số thứ tự.

Header của tuple #

Mỗi tuple của bảng mở đầu bằng header cố định 23 byte trên phần lớn máy: t_xmin (4 byte, giao dịch đã insert phiên bản này), t_xmax (4 byte, giao dịch đã xóa hoặc thay nó, 0 nếu chưa có), t_cid (4 byte, số thứ tự lệnh trong giao dịch), t_ctid (6 byte, ctid của chính nó hoặc của phiên bản mới hơn), t_infomask2 và t_infomask (2 byte mỗi trường: số cột, cờ có NULL, hint bit commit, cờ HOT), và t_hoff (1 byte, độ lệch tới phần dữ liệu).

Sau header là null bitmap, chỉ có khi dòng có ít nhất một NULL, một bit cho mỗi cột. Dữ liệu bắt đầu ở t_hoff, luôn là bội số của MAXALIGN. Trên máy 64 bit, MAXALIGN là 8, nên header 23 byte thực chất chiếm 24 byte. Bảng đến 8 cột có null bitmap 1 byte vừa lấp byte thứ 24. Mỗi cột còn được căn theo kiểu của nó: bigint và timestamptz căn 8, int căn 4, smallint căn 2. Theo tài liệu TOAST, giá trị độ dài thay đổi ngắn dùng header varlena 1 byte và không căn theo ranh giới nào.

Một dòng đơn hàng chiếm bao nhiêu byte #

tong_tien là numeric, độ dài thay đổi. Tài liệu mô tả: giá trị lưu không kèm số 0 thừa ở đầu và cuối; mỗi nhóm 4 chữ số thập phân tốn 2 byte, cộng 3 đến 8 byte overhead. Nhóm tính từ dấu thập phân. 1500000.00 tách thành nhóm 150 và 0000 trước dấu phẩy, 00 sau dấu phẩy. Bỏ các nhóm 0 ở cuối, còn một nhóm: 2 + 3 = 5 byte. 1750000.00 cũng 5 byte. 12345678.90 cần ba nhóm: 6 + 3 = 9 byte.

Dòng don_hang_id = 10042, không cột nào NULL:

Đơn dưới 100 triệu đồng cần tối đa ba nhóm chữ số, nên tong_tien từ 5 đến 9 byte và tuple từ 51 đến 55 byte. Làm tròn lên bội số 8, mọi tuple như vậy đều chiếm 56 byte. Con số 60 byte mỗi dòng đứng vững cho gần như mọi đơn của banhang. Thứ tự cột ảnh hưởng tới đệm. Nếu khach_hang_id int đứng trước ngay_tao, engine chèn 4 byte đệm để ngay_tao căn 8. Bảng mới nên đặt cột 8 byte trước, rồi 4 byte, 2 byte, cuối cùng là cột độ dài thay đổi.

Bao nhiêu dòng một page #

Phần dùng được của page là 8.192 − 24 = 8.168 byte. Bảng mặc định fillfactor 100, tức insert lấp đầy page. Số dòng tối đa = 8.168 / 60 = 136 dòng, dư 8.168 − 136 × 60 = 8 byte. Page đầy có pd_lower = 24 + 136 × 4 = 568 và pd_upper = 8.192 − 136 × 56 = 576. Ước lượng theo công thức, bảng vừa nạp, chưa có UPDATE hay DELETE, chưa tính chỉ mục. Số page là số dòng chia 136, làm tròn lên. Kích thước là số page × 8 KB.

Vì sao lớn hơn 37 byte của SQL Server #

Phần dữ liệu gần như bằng nhau. timestamptz lớn hơn datetime2(0) 2 byte, smallint lớn hơn tinyint 1 byte, nhưng numeric 5 byte nhỏ hơn decimal(18, 2) cố định 9 byte. Chênh lệch nằm ở overhead: 33 byte mỗi dòng so với 9. 1 triệu đơn chiếm khoảng 57 MB heap so với khoảng 36 MB clustered index, gấp khoảng 1,6 lần. Bảng có dòng rộng thì overhead cố định chiếm tỷ lệ nhỏ hơn.

Header 23 byte mang xmin, xmax và ctid. Nhờ đó engine quyết định phiên bản nào thấy được ngay trên page, không cần kho phiên bản riêng. SQL Server giữ phiên bản cũ trong version store của tempdb, hoặc persisted version store khi bật ADR, và chỉ thêm 14 byte thông tin phiên bản vào dòng khi database bật snapshot isolation hoặc READ_COMMITTED_SNAPSHOT. Hai cách quản lý phiên bản được so sánh trong chương transaction và khóa của SQL Server.

Kiểm tra trên máy thật #

SELECT pg_column_size(d.*)         AS byte_tuple,
       pg_column_size(d.tong_tien) AS byte_tong_tien
FROM don_hang AS d
WHERE d.don_hang_id = 10042
  AND d.ngay_tao = '2026-10-02 11:58:00+07';

Mong đợi byte_tuple 51 và byte_tong_tien 5. pg_column_size(d.*) tính cả header nhưng không tính phần làm tròn và line pointer. Số page thực tế của từng partition có trong pg_class.relpages, số ước lượng do VACUUM và ANALYZE cập nhật. Nhìn thẳng vào page bằng extension pageinspect, chỉ superuser gọi được. get_raw_page cần tên partition, vì bảng cha không có page. Page 0 của partition lịch sử, đã đầy và chưa từng bị sửa:

CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT lower, upper, special, pagesize
FROM page_header(get_raw_page('don_hang_truoc_2025', 0));

SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid, t_hoff
FROM heap_page_items(get_raw_page('don_hang_truoc_2025', 0))
ORDER BY lp
LIMIT 3;

Mong đợi lower 568, upper 576, special 8.192. Ba tuple đầu có lp_off 8.136, 8.080, 8.024, cách nhau đúng 56 byte. lp_len từ 51 đến 55 tùy tong_tien, t_hoff 24.

5. Không có clustered index #

SQL Server lưu bảng có clustered index ngay trong cây B-tree: page lá của chỉ mục chính là page dữ liệu. PostgreSQL không có cấu trúc này. Mỗi bảng là một heap, dòng nằm ở page nào còn chỗ. Mọi chỉ mục, kể cả khóa chính, là cấu trúc riêng mà mỗi entry trỏ tới một ctid.

Chỉ mục PostgreSQL không chứa thông tin hiển thị. Tìm qua chỉ mục vẫn phải đọc heap để xem tuple có thấy được không, trừ khi visibility map cho phép index-only scan (mục 6).

CLUSTER sắp lại heap theo một chỉ mục, nhưng chỉ một lần. Tài liệu ghi rõ: dòng thêm hoặc sửa sau đó không được đặt theo thứ tự chỉ mục. Lệnh giữ khóa ACCESS EXCLUSIVE suốt thời gian chạy, chặn cả đọc lẫn ghi, và cần chỗ trống ít nhất bằng bảng cộng các chỉ mục. Trên PostgreSQL 17, CLUSTER một bảng partition sắp từng partition và không chạy được trong transaction block. Chỗ dùng hợp lý là partition không còn đổi: don_hang_2025 sắp theo chỉ mục (khach_hang_id, ngay_tao) của nó một lần, báo cáo theo khách năm 2025 đọc ít page hơn mãi về sau. Tên chỉ mục của partition do engine tự đặt, lấy bằng SELECT indexrelid::regclass FROM pg_index WHERE indrelid = 'don_hang_2025'::regclass. Đơn mới của don_hang_2026 thường ghi vào cuối heap, nên thứ tự vật lý gần đúng theo ngay_tao mà không cần CLUSTER, cho tới khi VACUUM mở lại chỗ trống ở page cũ.

6. MVCC trên đĩa #

UPDATE ghi phiên bản mới #

UPDATE trong PostgreSQL ghi một tuple mới chứa toàn bộ dòng sau khi sửa, rồi đặt xmax của tuple cũ bằng ID giao dịch đang sửa. Tuple cũ vẫn nằm trên page. Giao dịch nào thấy phiên bản nào được quyết định bằng xmin, xmax, snapshot của giao dịch, và trạng thái commit trong pg_xact.

Đơn 10042 tạo lúc 11:58, tong_tien 1.500.000, nằm ở page 26470 của don_hang_2026. Lúc 12:04 ứng dụng sửa thành 1.750.000. ID giao dịch và số line pointer là minh họa.

t_ctid của tuple cũ trỏ sang tuple mới, tạo thành chuỗi phiên bản. Phiên khác đọc lúc 12:04 thấy xmax = 7341905 trên tuple cũ, nhưng giao dịch đó chưa commit nên vẫn trả về 1.500.000. Không ai bị chặn: đọc không chờ ghi, ghi không chờ đọc. Xem qua bảng cha:

SELECT tableoid::regclass AS partition, ctid, xmin, xmax, tong_tien
FROM don_hang
WHERE don_hang_id = 10042
  AND ngay_tao = '2026-10-02 11:58:00+07';

Truy vấn thường chỉ trả phiên bản thấy được, nên luôn ra một dòng. heap_page_items của pageinspect trả mọi tuple trên page bất kể hiển thị, nên thấy cả lp 12 lẫn lp 48.

HOT update và fillfactor #

Lần sửa lúc 12:04 không chạm chỉ mục, nhờ heap-only tuple (HOT). Theo tài liệu, HOT xảy ra khi UPDATE không đổi cột nào mà chỉ mục của bảng tham chiếu (chỉ mục BRIN không tính), và page chứa tuple cũ còn đủ chỗ cho tuple mới. tong_tien không nằm trong khóa chính hay ix_don_hang_khach_hang. Page 26470 là page cuối của partition, còn nhiều chỗ. Hai chỉ mục vẫn trỏ tới (26470,12). Engine tới lp 12 rồi đi theo t_ctid sang lp 48. Khi không snapshot nào còn cần tuple cũ, pruning xóa dữ liệu của nó và biến lp 12 thành redirect. Pruning chạy ngay trong lúc đọc page, kể cả bởi SELECT, không chờ VACUUM.

Lần sửa không phải HOT thì mọi chỉ mục đều nhận entry mới cho tuple mới, kể cả chỉ mục mà cột của nó không đổi. Tuple mới thường sang page khác vì page cũ hết chỗ. Chỉ mục một phần kiểu WHERE trang_thai = 1, tương ứng IX_DonHang_DangMo của chương kỹ thuật SQL Server, tham chiếu trang_thai trong điều kiện lọc. Có chỉ mục đó thì mọi lần đổi trạng thái đơn mất HOT. Đơn năm 2026 đi qua các trạng thái 1, 2, 3, 4 trong vài ngày. Khoảng 13.000 đơn mỗi ngày lấp một page 136 dòng trong khoảng 15 phút, nên lần đổi trạng thái sau đó chỉ còn HOT nếu pruning đã mở chỗ. fillfactor 90 chừa 10% page cho phiên bản mới. Đặt cho partition 2027 ngay từ đầu, vì tham số chỉ áp cho lần insert sau, không viết lại page đã có:

ALTER TABLE don_hang_tu_2027 SET (fillfactor = 90);

Với 90, một page nhận tối đa (8.192 − 819 − 24) / 60 ≈ 122 dòng mới, chừa 848 byte, đủ cho 14 phiên bản. 3,6 triệu đơn sẽ chiếm khoảng 29.509 page thay vì 26.471, thêm khoảng 11%. Đo trước và sau bằng tỷ lệ n_tup_hot_upd / n_tup_upd ở cuối mục này.

DELETE lúc 12:07 trên page #

Lệnh xóa nhầm ở mục 10 xóa 6.400.000 đơn trước năm 2026. Nó không giải phóng page nào. Nó đặt xmax trên 6,4 triệu tuple trong 47.059 page của hai partition lịch sử, và ghi WAL cho từng thay đổi. Sau khi commit, các tuple đó thành dead tuple: vẫn nằm trên đĩa, nhưng không lệnh SQL nào đọc lại được. Đường lấy lại dữ liệu vẫn là khôi phục theo thời điểm. Không có undo cũng có nghĩa ROLLBACK rẻ: giao dịch hủy chỉ được đánh dấu aborted trong pg_xact, tuple nó đã ghi thành dead tuple cho VACUUM dọn. SQL Server, khi chưa bật ADR, phải hoàn tác từng thay đổi lúc rollback.

VACUUM và VACUUM FULL #

VACUUM thường xóa dead tuple khỏi bảng và chỉ mục khi không giao dịch nào còn cần chúng, rồi ghi chỗ trống vào free space map để insert và update sau dùng lại. Nó chạy song song với SELECT, INSERT, UPDATE, DELETE. Nó không trả chỗ cho hệ điều hành, trừ khi các page cuối bảng trống hẳn và lấy được khóa độc quyền ngắn để cắt tệp. VACUUM FULL viết lại toàn bộ bảng sang tệp mới không còn chỗ trống, nên bảng nhỏ lại thật.

Autovacuum với partition 2026 #

Autovacuum chạy VACUUM trên một bảng khi số dead tuple vượt:

ngưỡng vacuum = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

Mặc định 50 và 0,2. Với don_hang_2026, reltuples = 3.600.000: ngưỡng = 50 + 0,2 × 3.600.000 = 720.050 dead tuple. Ước lượng thô: 13.000 đơn mỗi ngày, mỗi đơn đổi trạng thái khoảng 3 lần, tức khoảng 39.000 dead tuple mỗi ngày. Chạm ngưỡng sau 720.050 / 39.000 ≈ 18 ngày, chưa trừ phần pruning đã dọn. Trong 18 ngày đó heap phình và visibility map ít page được đánh dấu. Hạ hệ số cho riêng partition đang nóng:

ALTER TABLE don_hang_2026 SET (autovacuum_vacuum_scale_factor = 0.02);

Ngưỡng mới = 50 + 0,02 × 3.600.000 = 72.050, khoảng 2 ngày. Đặt tham số trên partition. Bảng partition không chứa tuple nên autovacuum không xử lý bảng cha, kể cả ANALYZE. Chạy ANALYZE don_hang theo lịch nếu truy vấn cần thống kê cấp bảng cha.

Bảng chỉ nhận insert cũng được vacuum theo ngưỡng riêng: autovacuum_vacuum_insert_threshold (1.000) + autovacuum_vacuum_insert_scale_factor (0,2) × reltuples, tức 721.000 dòng mới với partition 2026. Từ PostgreSQL 18, ngưỡng insert nhân thêm tỷ lệ phần bảng chưa freeze, và autovacuum_vacuum_max_threshold (mặc định 100.000.000) làm trần cho ngưỡng vacuum của bảng rất lớn. Sau 12:07, don_hang_truoc_2025 có 3.000.000 dead tuple, vượt xa ngưỡng 600.050 của nó, nên autovacuum sẽ dọn trong vài phút. Việc đó không cản khôi phục theo thời điểm, vì khôi phục dựng lại từ base backup và WAL, không đọc page hiện tại.

Visibility map, free space map, index-only scan #

Visibility map giữ hai bit cho mỗi page heap. Bit all-visible nghĩa là mọi tuple trên page thấy được với mọi giao dịch. Bit all-frozen nghĩa là mọi tuple đã freeze. Chỉ VACUUM bật các bit này. Mọi thao tác sửa page đều tắt chúng. Index-only scan dùng bit all-visible: page đã all-visible thì không cần đọc heap để kiểm tra hiển thị. Partition lịch sử sau VACUUM gần như toàn page all-visible. Báo cáo các ngày đặt hàng của khách 42 trong năm 2025 chỉ cần ngay_tao, nên đọc thẳng từ chỉ mục (khach_hang_id, ngay_tao). Trong EXPLAIN (ANALYZE), nút Index Only Scan với Heap Fetches gần 0 nghĩa là visibility map đang làm việc. Số lớn nghĩa là partition cần VACUUM. Free space map ghi mức chỗ trống của từng page để insert tìm page còn chỗ mà không quét bảng, phần việc PFS làm cho heap ở SQL Server. VACUUM cập nhật map này.

XID wraparound và freeze #

ID giao dịch dài 32 bit và được so sánh theo vòng tròn: với mỗi XID, khoảng hai tỷ giá trị là quá khứ và hai tỷ là tương lai. Tuple có xmin cũ quá khoảng hai tỷ giao dịch sẽ bỗng trông như ở tương lai và biến mất. VACUUM ngăn việc đó bằng cách freeze: đánh dấu tuple đủ cũ là thấy được với mọi giao dịch, không cần so xmin nữa. Khi age(relfrozenxid) của một bảng vượt autovacuum_freeze_max_age (mặc định 200 triệu), autovacuum chạy VACUUM chống wraparound trên bảng đó, kể cả khi autovacuum bị tắt. Ở mức 2.000 giao dịch mỗi giây, 200 triệu XID đến sau 100.000 giây, khoảng 28 giờ. Với banhang vài chục nghìn giao dịch mỗi ngày, mốc đó cách nhiều năm. Khi số XID còn lại quá ít, PostgreSQL cảnh báo, rồi từ chối cấp XID mới để bảo vệ dữ liệu. Partition lịch sử nên freeze một lần sau khi nạp. Page all-frozen được các lần VACUUM chống wraparound sau bỏ qua. Lần freeze đầu ghi lại mọi page của hai partition, chạy ngoài giờ cao điểm.

VACUUM (FREEZE, ANALYZE) don_hang_truoc_2025, don_hang_2025;

SELECT c.relname, age(c.relfrozenxid) AS tuoi_xid
FROM pg_class AS c
WHERE c.relkind = 'r'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 5;

Đo dead tuple và HOT #

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS pct_dead,
       n_tup_upd, n_tup_hot_upd, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE relname LIKE 'don\_hang%'
ORDER BY n_dead_tup DESC;

n_dead_tup là số ước lượng. last_autovacuum trống hoặc rất cũ trên partition có n_dead_tup cao nghĩa là autovacuum không theo kịp, hoặc một giao dịch mở lâu đang giữ dead tuple lại. Cần số chính xác thì dùng extension pgstattuple, chấp nhận nó đọc cả bảng.

7. Dữ liệu lớn: TOAST #

Một tuple phải nằm trong một page: tối đa 8.160 byte với page 8 KB, tức 8.192 trừ header page và một line pointer đã căn lề. Cột độ dài cố định không được đẩy ra ngoài, nên tổng của chúng phải nằm trong giới hạn này. Cột độ dài thay đổi (text, varchar, bytea, numeric, jsonb, …) đi qua TOAST.

TOAST bắt đầu khi tuple sắp ghi rộng hơn TOAST_TUPLE_THRESHOLD, thường khoảng 2 KB. Engine nén và/hoặc chuyển giá trị ra ngoài cho tới khi tuple ngắn hơn TOAST_TUPLE_TARGET, cũng khoảng 2 KB, hoặc không còn gì để làm. Giá trị chuyển ra ngoài được cắt thành chunk và ghi vào bảng TOAST riêng của bảng chính, với ba cột chunk_id, chunk_seq, chunk_data và chỉ mục duy nhất trên (chunk_id, chunk_seq). Tuple chính giữ một con trỏ 18 byte. Một giá trị tối đa 1 GB.

Nén mặc định là pglz. Từ PostgreSQL 14 có thêm lz4 nếu bản build có hỗ trợ. default_toast_compression chọn thuật toán cho giá trị mới, từng cột đổi bằng ALTER TABLE ... ALTER COLUMN ... SET COMPRESSION.

Hợp đồng dài và file PDF #

CREATE TABLE hop_dong (
    hop_dong_id int           NOT NULL,
    tom_tat     varchar(5000) NOT NULL,
    dieu_khoan  varchar(5000) NOT NULL,
    ban_scan    bytea,
    CONSTRAINT pk_hop_dong PRIMARY KEY (hop_dong_id) USING INDEX TABLESPACE ts_data
) TABLESPACE ts_data;

ALTER TABLE hop_dong ALTER COLUMN ban_scan SET STORAGE EXTERNAL;

varchar(5000) trong PostgreSQL giới hạn 5.000 ký tự, không phải 5.000 byte. Chữ tiếng Việt có dấu trong UTF-8 tốn 2 đến 3 byte mỗi ký tự. Ví dụ dưới giả định mỗi cột chữ khoảng 5.000 byte, giống chương SQL Server. ban_scan là PDF, vốn đã nén. EXTERNAL bỏ bước nén thử, tiết kiệm CPU mà không mất gì về dung lượng. SET STORAGE chỉ áp cho giá trị ghi sau đó.

Hợp đồng 15 có tom_tat và dieu_khoan khoảng 5.000 byte mỗi cột, ban_scan 2 MB. Lúc INSERT, engine xử lý cột lớn nhất trước. ban_scan ra bảng TOAST nguyên dạng, tuple chính giữ con trỏ 18 byte. Hai cột chữ được nén. Văn bản thường nén tốt, nên mỗi cột có thể còn nằm trong dòng ở dạng nén, hoặc cũng ra ngoài nếu tuple vẫn trên 2 KB. Kết quả tùy nội dung, xem bằng pg_column_size. Không có ngưỡng 8.060 byte với row-overflow riêng và LOB riêng như SQL Server: PostgreSQL dùng một cơ chế cho mọi giá trị độ dài thay đổi, kèm nén. Chunk dài tối đa khoảng 2.000 byte, chọn để bốn dòng chunk vừa một page. Trên bản build 64 bit thông thường, chunk đầy là 1.996 byte. PDF 2 MB = 2.097.152 byte, chia 1.996 ra 1.050,7. Vậy bảng TOAST có 1.051 dòng cho bản scan: 1.050 chunk đầy và một chunk cuối 1.352 byte. Bốn dòng một page cho 263 page, khoảng 2,05 MB, cộng vài page chỉ mục của bảng TOAST. SQL Server cần khoảng 256 page LOB_DATA cho cùng tệp. SELECT hop_dong_id, tom_tat FROM hop_dong WHERE hop_dong_id = 15 không đọc 1.051 dòng đó, vì giá trị TOAST chỉ được lấy khi cột thực sự cần. SELECT * thì có. Tài liệu còn ghi: UPDATE không đổi giá trị đã ở ngoài thì giữ nguyên con trỏ, nên sửa tom_tat không chép lại 2 MB của ban_scan dù phiên bản dòng mới được ghi.

Kiểm tra TOAST #

SELECT octet_length(tom_tat)  AS tom_tat_goc,  pg_column_size(tom_tat)  AS tom_tat_luu,
       octet_length(ban_scan) AS ban_scan_goc, pg_column_size(ban_scan) AS ban_scan_luu
FROM hop_dong
WHERE hop_dong_id = 15;

pg_column_size trả số byte đang lưu, sau nén. ban_scan_goc và ban_scan_luu cùng 2.097.152 vì cột không nén. tom_tat_luu nhỏ hơn tom_tat_goc khi văn bản nén được. Đếm chunk của đúng bản scan bằng pg_column_toast_chunk_id, có từ PostgreSQL 17. Đọc bảng TOAST cần quyền chủ bảng hoặc superuser.

SELECT reltoastrelid::regclass AS bang_toast
FROM pg_class
WHERE oid = 'hop_dong'::regclass \gset

SELECT count(*) AS so_chunk, max(length(chunk_data)) AS chunk_lon_nhat
FROM :bang_toast
WHERE chunk_id = (SELECT pg_column_toast_chunk_id(ban_scan)
                  FROM hop_dong WHERE hop_dong_id = 15);

Mong đợi so_chunk 1.051 và chunk_lon_nhat 1.996 trên bản build thông thường.

8. Đường ghi dữ liệu #

shared_buffers và page cache #

Page được đọc vào shared_buffers, bộ đệm dùng chung của mọi tiến trình. Sửa dòng là sửa page trong bộ đệm, page thành dirty. Khác SQL Server, PostgreSQL đọc ghi tệp qua page cache của hệ điều hành, nên một page có thể nằm ở cả hai nơi. shared_buffers mặc định thường là 128 MB. Tài liệu gợi ý điểm khởi đầu 25% RAM cho máy chỉ chạy database, và cho rằng trên 40% khó tốt hơn vì PostgreSQL vẫn dựa vào cache của hệ điều hành. Với 64 GB RAM, điểm khởi đầu là 16 GB. Phần còn lại hệ điều hành dùng làm cache tệp, khác với max server memory 56 GB của chương SQL Server.

WAL trước, page sau #

Quy tắc giống SQL Server: thay đổi trên tệp dữ liệu chỉ được ghi sau khi bản ghi WAL mô tả nó đã nằm trên đĩa. Mỗi page mang pd_lsn. Trước khi ghi một dirty page, engine flush WAL tới ít nhất pd_lsn đó. Thứ tự của một UPDATE đã commit:

1. Page nằm trong shared_buffers, hoặc được đọc lên.
2. Tuple mới được ghi, xmax của tuple cũ được đặt, bản ghi WAL vào WAL buffers, page thành dirty và nhận pd_lsn mới.
3. Lúc COMMIT, commit record vào WAL, và WAL được flush xuống pg_wal tới hết commit record.
4. pg_xact đánh dấu giao dịch là committed.
5. Client nhận kết quả thành công.
6. Sau đó checkpointer, background writer hoặc chính backend mới ghi dirty page xuống tệp dữ liệu.

Với synchronous_commit = on, mặc định, bước 3 chờ flush cục bộ, và chờ thêm standby đồng bộ nếu đã cấu hình.

Sửa đơn 10042, theo từng thời điểm #

BEGIN;

SELECT pg_current_xact_id();   -- minh họa: 7341905

UPDATE don_hang
SET tong_tien = 1750000.00
WHERE don_hang_id = 10042
  AND ngay_tao = '2026-10-02 11:58:00+07';

-- Chưa COMMIT. Page 26470 trong shared_buffers có hai tuple:
-- lp 12 (1.500.000, xmax = 7341905) và lp 48 (1.750.000, xmin = 7341905).
-- Bản ghi WAL đang ở WAL buffers. Tệp trên /srv/pg_ssd chưa có lp 48.
-- Phiên khác đọc thấy 1.500.000 vì 7341905 chưa commit.

COMMIT;

-- Commit record đã được flush xuống /srv/pg_wal. pg_xact ghi 7341905 là committed.
-- Phiên mới đọc thấy 1.750.000. Tệp dữ liệu có thể chưa có lp 48 cho tới checkpoint.

Ba cách mất điện ngay sau đó:

Hàng giữa là khác biệt chính với SQL Server, nơi recovery có pha undo đưa TongTien về 1.500.000. Ở đây không cần: tuple của giao dịch chưa commit vẫn nằm trên page nhưng không ai thấy, và VACUUM dọn sau như mọi dead tuple khác.

full_page_writes và WAL sau checkpoint #

Đĩa ghi theo sector, thường 512 byte. Mất điện giữa lúc ghi một page 8 KB có thể để lại page nửa cũ nửa mới, và bản ghi WAL chỉ mô tả thay đổi thì không sửa được page rách. Vì vậy, với full_page_writes = on (mặc định), lần sửa đầu tiên của mỗi page sau một checkpoint ghi cả ảnh page vào WAL. Recovery dùng ảnh đó làm điểm xuất phát cho page. Hệ quả: lượng WAL tăng vọt ngay sau mỗi checkpoint, rồi giảm khi các page nóng đã có ảnh trong chu kỳ. Ước lượng thô cho banhang, chỉ tính page heap:

• Đơn của khoảng 3 ngày gần nhất nhận phần lớn lần đổi trạng thái: 39.000 đơn / 136 ≈ 290 page nóng.
• checkpoint_timeout mặc định 5 phút: 288 checkpoint mỗi ngày × 290 ảnh page × 8 KB ≈ 652 MB WAL mỗi ngày chỉ cho ảnh page.
• checkpoint_timeout = 15min: 96 × 290 × 8 KB ≈ 218 MB.
• Lệnh DELETE lúc 12:07 chạm 47.059 page lần đầu trong chu kỳ: riêng ảnh page tới khoảng 47.059 × 8 KB ≈ 368 MB WAL, chưa kể bản ghi xóa cho từng dòng.

Checkpoint thưa hơn làm WAL nhỏ hơn, đổi lại recovery sau sự cố dài hơn vì phải redo nhiều WAL hơn. wal_compression nén ảnh page bằng pglz, hoặc lz4, zstd nếu bản build có hỗ trợ. Chỉ tắt full_page_writes khi hệ thống tệp bảo đảm không ghi rách page; tài liệu lấy ví dụ ZFS. Điểm khởi đầu cho banhang, không phải giá trị mặc định:

# /etc/postgresql/17/main/postgresql.conf
shared_buffers = 16GB          # 25% RAM, cần restart
checkpoint_timeout = 15min     # mặc định 5min
max_wal_size = 4GB             # mặc định 1GB

Đo lượng ảnh page và loại checkpoint. View pg_stat_checkpointer có từ PostgreSQL 17. Trên PostgreSQL 16, xem checkpoints_timed và checkpoints_req trong pg_stat_bgwriter.

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal FROM pg_stat_wal;

SELECT num_timed, num_requested, buffers_written FROM pg_stat_checkpointer;

num_requested tăng nhanh hơn num_timed nghĩa là WAL chạm max_wal_size trước khi hết checkpoint_timeout. Nâng max_wal_size.

Ai đẩy dữ liệu xuống đĩa #

Checkpoint chạy mỗi checkpoint_timeout, hoặc sớm hơn khi WAL sắp vượt max_wal_size.

Recovery lúc khởi động: chỉ có redo #

Sau sự cố, PostgreSQL đọc pg_control, lấy bản ghi checkpoint gần nhất, rồi redo từ vị trí redo ghi trong bản ghi đó tới hết WAL. Mọi thay đổi trước vị trí redo đã chắc chắn nằm trên tệp dữ liệu. Không có pha undo. Giao dịch không có commit record là aborted, tuple của nó vô hình. Thời gian recovery tỷ lệ với lượng WAL phải redo, nên checkpoint_timeout và max_wal_size là hai núm chỉnh RTO cho sự cố mất điện, giống TARGET_RECOVERY_TIME của SQL Server.

Commit không chờ WAL #

synchronous_commit = off cho COMMIT trả về trước khi WAL được flush. Nó là bản tương ứng của delayed durability trong chương kỹ thuật SQL Server. Rủi ro là mất các giao dịch cuối, không phải hỏng dữ liệu: tài liệu ghi cửa sổ rủi ro tối đa bằng ba lần wal_writer_delay, tức 600 ms với mặc định. fsync = off thì khác, có thể làm hỏng database tùy ý sau sự cố. Tham số đặt được cho từng giao dịch bằng SET LOCAL synchronous_commit = off ngay sau BEGIN, hợp với luồng ghi dày như nhật ký truy cập. Đơn đã thu tiền giữ on.

9. Mức phục hồi #

PostgreSQL không có recovery model theo database. Mức phục hồi do cấu hình cấp cluster quyết định: wal_level, archive_mode cùng archive_command hoặc archive_library, và lịch base backup.

Cùng sự cố lúc 14:00 ngày 2026-10-02, ba cách cấu hình cho ba kết cục:

wal_level = minimal bỏ thông tin cần cho archive và replica. Nó không đi cùng archive_mode hay base backup trực tuyến, chỉ hợp với máy nạp dữ liệu làm lại được. Lệnh archive chỉ chạy trên segment đã đầy 16 MB. archive_timeout ép chuyển segment sau một khoảng thời gian, nên giới hạn tuổi của phần WAL chưa archive. Đó là RPO khi mất cả máy. Tài liệu cảnh báo segment chuyển sớm vẫn dài 16 MB, nên archive_timeout quá ngắn làm phình kho archive, và coi mức khoảng một phút là hợp lý. banhang chọn 5 phút: tối đa 288 segment mỗi ngày, khoảng 4,5 GB nếu mọi segment đều bị ép chuyển. Cần RPO vài giây thì dùng pg_receivewal nhận WAL liên tục qua kết nối replication, hoặc một standby. RTO là thời gian chép lại toàn bộ cluster từ bản backup, cộng thời gian redo WAL từ điểm bắt đầu backup tới mốc cần về. Base backup 22:00 cộng WAL tới 12:06 nghĩa là redo hơn 14 giờ WAL. Incremental lúc 12:00 rút phần đó còn vài phút, giống vai trò differential ở SQL Server.

10. Sao lưu và khôi phục #

Logical và physical #

pg_dump cho một snapshot nhất quán mà không chặn đọc hay ghi. Nó không chứa role và tablespace, những thứ thuộc cluster; lấy chúng bằng pg_dumpall --globals-only.

Cấu hình WAL archive #

# /etc/postgresql/17/main/postgresql.conf trên db01
wal_level = replica            # mặc định, cần restart nếu đổi
archive_mode = on              # cần restart
archive_command = 'test ! -f /mnt/backup/wal/%f && cp %p /mnt/backup/wal/%f'
archive_timeout = 5min
summarize_wal = on             # PostgreSQL 17, cần cho incremental backup

%p là đường dẫn segment cần archive, %f là tên tệp. test ! -f từ chối ghi đè tệp đã có trong archive, điều tài liệu khuyên mọi lệnh archive phải làm. Lệnh phải trả mã 0 khi và chỉ khi thành công; engine chỉ tái sử dụng segment sau khi nhận mã 0. Dòng cp là ví dụ của tài liệu, không phải khuyến nghị: cp không fsync tệp đích. Từ PostgreSQL 15 có thể dùng archive_library thay cho archive_command.

Base backup và incremental #

Trên Debian, pg_hba.conf mặc định cho user hệ điều hành postgres kết nối replication qua socket cục bộ, nên lệnh dưới chạy thẳng trên db01. Chạy từ máy khác cần role có quyền REPLICATION và một dòng replication trong pg_hba.conf. Cluster có tablespace thì backup dạng plain cần -T cho từng tablespace. Không có nó, pg_basebackup ghi tablespace vào đúng đường dẫn gốc, vốn đang được cluster dùng.

# Chạy trên db01 bằng user postgres.
B=/mnt/backup/base/full_20261001_2200
I=/mnt/backup/base/incr_20261002_1200

# 22:00 ngày 2026-10-01: base backup đầy đủ
pg_basebackup -D "$B/data" -Fp -X stream -P \
  -T /srv/pg_ssd/ts_data="$B/ts_data" \
  -T /srv/pg_hdd/ts_archive="$B/ts_archive"

# 12:00 ngày 2026-10-02: incremental so với bản 22:00, PostgreSQL 17
pg_basebackup -D "$I/data" -Fp -X stream -P \
  --incremental="$B/data/backup_manifest" \
  -T /srv/pg_ssd/ts_data="$I/ts_data" \
  -T /srv/pg_hdd/ts_archive="$I/ts_archive"

-X stream, mặc định, mở thêm một kết nối để nhận WAL trong lúc backup chạy, nên bản backup tự đủ WAL để nhất quán. backup_manifest liệt kê mọi tệp kèm checksum và là đầu vào của --incremental. Mỗi base backup cũng ghi một tệp .backup vào WAL archive, đặt tên theo segment đầu tiên mà bản đó cần. Incremental chỉ chép block đổi kể từ bản tham chiếu. Server biết block nào đổi nhờ WAL summary trong pg_wal/summaries, do tiến trình WAL summarizer viết khi summarize_wal = on. Tham số phải bật trước khi bản full bắt đầu, và wal_summary_keep_time (mặc định 10 ngày) phải dài hơn khoảng cách giữa các bản. Partition lịch sử không đổi nên gần như không vào bản incremental, tương tự DCM bỏ qua filegroup read-only ở SQL Server. PostgreSQL không tự theo dõi bản incremental nào cần bản nào: xóa bản full 22:00 thì bản 12:00 vô dụng. Kiểm tra bản full bằng pg_verifybackup "$B/data".

Thứ tự khôi phục #

Theo tài liệu: (1) dừng server, chép pg_wal ra chỗ an toàn nếu còn đọc được vì nó có thể chứa segment chưa archive; (2) xóa nội dung cũ của data directory và thư mục tablespace, hoặc dùng máy mới; (3) đặt tệp backup vào chỗ, với incremental thì dùng pg_combinebackup ghép bản full và mọi bản incremental, từ cũ đến mới, thành một bản full tổng hợp; (4) chép segment chưa archive ở bước 1 vào pg_wal nếu cần về tới cuối; (5) đặt restore_command và mốc dừng trong postgresql.conf, tạo tệp rỗng recovery.signal; (6) khởi động server. Server đọc WAL từ archive tới mốc hoặc tới hết, rồi xóa recovery.signal. Từ PostgreSQL 12 không còn recovery.conf. Mốc dừng đặt bằng đúng một tham số recovery_target_*, như recovery_target_time hoặc recovery_target_lsn. Có mốc mà WAL hết trước khi tới mốc thì server dừng với lỗi FATAL.

Xóa nhầm đơn cũ lúc 12:07 #

Bối cảnh: base backup 22:00 ngày 2026-10-01, incremental chạy từ 12:00 đến 12:05 ngày 2026-10-02, WAL archive liên tục với archive_timeout = 5min. Lúc 12:07 một phiên chạy:

DELETE FROM don_hang
WHERE ngay_tao < '2026-01-01 00:00:00+07';

Lệnh xóa 6.400.000 đơn trước năm 2026 và đã commit. Người trực phát hiện lúc 12:10. Đơn năm 2026 vẫn tiếp tục được tạo trên db01. Khôi phục đè lên db01 về 12:06 sẽ xóa mọi giao dịch commit sau 12:06 trong mọi database của cluster, gồm đơn mới từ 12:06 đến lúc khôi phục xong. Đường đúng là dựng cluster ở db-khoiphuc về 12:06, rồi chép hai partition lịch sử về db01.

Bước 1, trên db01, lúc 12:10. Recovery cần WAL tới ít nhất bản ghi commit đầu tiên sau mốc. Segment đang ghi chưa đầy nên chưa được archive. Ép chuyển segment để mọi WAL tới 12:10 lên archive, rồi chờ. Việc này tương ứng tail-log backup, nhưng db01 vẫn chạy.

SELECT pg_walfile_name(pg_switch_wal()) AS segment_vua_dong;

-- Lặp lại tới khi last_archived_wal bằng hoặc sau segment_vua_dong.
SELECT last_archived_wal, last_archived_time, failed_count FROM pg_stat_archiver;

Bước 2, trên db-khoiphuc. Máy cài cùng PostgreSQL 17, chưa có cluster nào chạy, thấy /mnt/backup. Ghép bản full và incremental, đặt tablespace vào thư mục mới. Với pg_combinebackup, vế trái của -T là đường dẫn tablespace trong bản backup cuối cùng trên dòng lệnh, tức bản incremental. Bản backup không có postgresql.conf và pg_hba.conf vì Debian để chúng ở /etc, nên tạo cấu hình tối giản ngay trong data directory, rồi bật chế độ khôi phục:

# Chạy bằng user postgres.
B=/mnt/backup/base/full_20261001_2200
I=/mnt/backup/base/incr_20261002_1200
R=/srv/khoiphuc
BIN=/usr/lib/postgresql/17/bin

mkdir -p "$R"
$BIN/pg_combinebackup -o "$R/data" \
  -T "$I/ts_data=$R/ts_data" \
  -T "$I/ts_archive=$R/ts_archive" \
  "$B/data" "$I/data"
chmod 700 "$R/data"

cat > "$R/data/postgresql.conf" <<'EOF'
port = 5433
listen_addresses = 'localhost'
archive_mode = off
hot_standby = on
restore_command = 'cp /mnt/backup/wal/%f "%p"'
recovery_target_time = '2026-10-02 12:06:00+07'
recovery_target_action = 'pause'
EOF
echo 'local all postgres peer' > "$R/data/pg_hba.conf"
touch "$R/data/pg_ident.conf" "$R/data/recovery.signal"

$BIN/pg_ctl -D "$R/data" -l "$R/khoiphuc.log" start

archive_mode = off giữ máy khôi phục khỏi đẩy WAL của nó vào kho archive của production. postgresql.auto.conf đi theo bản backup và thắng postgresql.conf: nếu production từng đặt tham số archive bằng ALTER SYSTEM, xóa các dòng đó. max_connections, max_worker_processes, max_wal_senders, max_prepared_transactions, max_locks_per_transaction không được nhỏ hơn trên db01, nếu không recovery tạm dừng với cảnh báo thiếu tham số.

Recovery dừng trước giao dịch đầu tiên commit sau 12:06:00 +07, thường là một đơn mới tạo lúc 12:06 vài giây. Lệnh DELETE commit lúc 12:07, sau mốc, nên không có hiệu lực trong cluster khôi phục. Kể cả khi một phần bản ghi xóa của nó đã được redo, giao dịch không có commit record, nên theo đúng cơ chế ở mục 8, 6,4 triệu tuple vẫn thấy được. Log khi tới mốc, minh họa:

LOG:  recovery stopping before commit of transaction 7342291, time 2026-10-02 12:06:04.211+07
LOG:  pausing at the end of recovery
HINT:  Execute pg_wal_replay_resume() to promote.

pause để server ở chế độ chỉ đọc tại mốc, kiểm tra được trước khi quyết định:

-- psql -p 5433 -d banhang
SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state();
SELECT count(*) FROM don_hang WHERE ngay_tao < '2026-01-01 00:00:00+07';
SELECT tong_tien FROM don_hang
WHERE don_hang_id = 10042 AND ngay_tao = '2026-10-02 11:58:00+07';

SELECT pg_wal_replay_resume();

Mong đợi: t và paused; 6.400.000 đơn trước 2026; đơn 10042 là 1.750.000,00 vì đã commit lúc 12:04. pg_wal_replay_resume() kết thúc recovery, server mở ghi trên timeline mới. Nếu mốc chưa đúng, dừng server thay vì resume, đổi sang mốc muộn hơn và khởi động lại. Muốn về mốc sớm hơn thì phải ghép lại từ đầu.

Bước 3, chép hai partition về db01. -t với tên partition lấy đúng partition đó. Từ PostgreSQL 16, --table-and-children lấy một bảng cùng mọi partition của nó.

# Trên db-khoiphuc, user postgres.
mkdir -p /mnt/backup/khoiphuc
$BIN/pg_dump -p 5433 -d banhang -Fc --data-only \
  -t public.don_hang_truoc_2025 -t public.don_hang_2025 \
  -f /mnt/backup/khoiphuc/don_hang_truoc_2026.dump

Trên db01, hai partition chỉ còn dead tuple. Kiểm tra không còn dòng sống, rồi TRUNCATE để bỏ 47.059 page dead tuple ngay thay vì nạp 6,4 triệu dòng vào sau chúng. Nạp trong một giao dịch, rồi freeze và cập nhật thống kê:

# Trên db01, user postgres. Chỉ chạy TRUNCATE khi lệnh đầu trả 0.
psql -d banhang -c "SELECT count(*) FROM don_hang WHERE ngay_tao < '2026-01-01 00:00:00+07'"
psql -d banhang -c "TRUNCATE don_hang_truoc_2025, don_hang_2025"
pg_restore -d banhang --data-only --single-transaction \
  /mnt/backup/khoiphuc/don_hang_truoc_2026.dump
psql -d banhang -c "VACUUM (FREEZE, ANALYZE) don_hang_truoc_2025, don_hang_2025" \
                -c "ANALYZE don_hang"

Sau bước này, db01 có lại 6.400.000 đơn cũ, đơn 10042 vẫn 1.750.000,00, và mọi đơn tạo sau 12:07 còn nguyên. Nếu trigger chỉ đọc ở mục 3 đã tồn tại, tắt nó trong lúc nạp, vì COPY cũng kích hoạt trigger INSERT.

Bốn lỗi hay gặp với đúng bộ tệp này:

Hỏng ổ SSD, hoặc mất cả máy #

14:00 cùng ngày, ổ /srv/pg_ssd hỏng. Data directory, /srv/pg_hdd và /srv/pg_wal vẫn đọc được. Không khôi phục riêng ts_data được, vì tablespace không sao lưu và khôi phục riêng. Làm đủ các bước ở trên cho cả cluster: chép /srv/pg_wal ra chỗ an toàn, ghép bản full và incremental vào đúng đường dẫn gốc với ổ SSD mới, dựng lại pg_wal thành symlink tới /srv/pg_wal như cũ, chép các segment chưa archive vào đó, tạo recovery.signal, không đặt mốc dừng. Recovery chạy tới hết WAL. Giao dịch đã commit tới sát 14:00 còn nguyên, đúng hàng thứ ba trong bảng ở mục 9.

Mất cả máy thì bản backup và WAL archive chỉ còn trên /mnt/backup. Khôi phục lên máy mới như trên, không có pg_wal cũ để chép. Recovery chạy tới segment cuối đã archive. Đơn commit sau thời điểm đó, tối đa khoảng 5 phút với archive_timeout = 5min, không còn ở đâu để lấy lại.

Tài liệu lưu ý: CREATE TABLESPACE được ghi vào WAL với đường dẫn tuyệt đối. Redo lệnh đó trên máy khác, hoặc vào data directory mới trên cùng máy, có thể ghi đè tablespace thật. Lấy base backup mới ngay sau khi tạo hoặc xóa tablespace.

Việc cần làm cùng lịch backup #

Lệnh cp trong archive_command đủ để hiểu cơ chế, không đủ cho production. pgBackRest và Barman là hai công cụ thường dùng: chúng archive WAL có kiểm tra, nén, giữ chuỗi full và incremental theo chính sách lưu giữ, và khôi phục bằng một lệnh. Cơ chế bên dưới vẫn là base backup và WAL archive như trên.

• Khôi phục thử lên db-khoiphuc theo lịch. Bản backup chưa khôi phục thành công thì chưa biết là dùng được. pg_verifybackup không thay cho việc này.
• Giữ bản backup và WAL archive trên máy khác, và một bản offline hoặc bất biến.
• Giữ /etc/postgresql/17/main và đầu ra pg_dumpall --globals-only cùng lịch backup.
• Cảnh báo khi base backup trễ lịch, khi failed_count tăng, hoặc khi last_archived_time cũ hơn ngưỡng RPO.

11. Cách các tầng này ăn khớp #

Lấy đúng lần COMMIT lúc 12:04 của đơn 10042, tong_tien từ 1.500.000 lên 1.750.000:

1. Tuple 51 byte, chiếm 56 byte cộng 4 byte line pointer, nằm ở page 26470 của heap don_hang_2026, trong tệp dưới /srv/pg_ssd/ts_data/PG_17_<catversion>/<OID banhang>/.
2. UPDATE ghi tuple mới ở lp 48 trên cùng page và đặt xmax cho lp 12. Không cột nào có chỉ mục bị đổi và page còn chỗ, nên đây là HOT: hai chỉ mục không nhận entry mới.
3. Bản ghi WAL, kèm ảnh cả page nếu đây là lần sửa đầu của page sau checkpoint, được flush xuống /srv/pg_wal trước khi COMMIT trả về. pg_xact ghi giao dịch là committed. Tệp trên /srv/pg_ssd có thể chưa đổi.
4. Incremental chạy từ 12:00 đến 12:05 nhận WAL qua -X stream tới lúc nó kết thúc, nên lần sửa nằm trong phần WAL đi kèm bản đó. Ghép bản 22:00 với bản 12:00 rồi redo tới điểm nhất quán là có 1.750.000.
5. Checkpoint sau đó ghi dirty page xuống tệp dữ liệu.
6. Segment WAL chứa lần sửa được archive sang /mnt/backup/wal khi đầy hoặc khi archive_timeout ép chuyển. Sau checkpoint kế tiếp và sau khi đã archive, segment được tái sử dụng.
7. Base backup 22:00 ngày 2026-10-01 là mốc gốc. Không có nó thì bản incremental và toàn bộ WAL archive không dựng được cluster nào.
8. Khi không snapshot nào còn cần lp 12, pruning hoặc VACUUM dọn dữ liệu của nó, lp 12 thành redirect sang lp 48.

Thiếu bước 6, pg_wal phình tới khi đầy và server dừng. Thiếu bước 8 trên diện rộng, heap phình và index-only scan quay về đọc heap.

12. Khung so sánh với SQL Server #

Bảng của chương SQL Server, giữ nguyên các hàng, thêm cột PostgreSQL:

Ngoài khung còn bốn khác biệt chi phối mọi quyết định ở trên: phiên bản cũ của dòng nằm ngay trong heap thay vì version store; recovery chỉ có redo; biên giới backup vật lý là cluster thay vì database; và không có clustered index.

13. Những chỗ hay hiểu sai #

• "Tablespace là filegroup của PostgreSQL." Tablespace là một thư mục thuộc cả cluster: không proportional fill, không read-only, không sao lưu hay khôi phục riêng.
• "UPDATE sửa dòng tại chỗ." Nó ghi phiên bản mới. Phiên bản cũ ở lại trên page cho tới pruning hoặc VACUUM.
• "DELETE trả chỗ ngay." Nó chỉ đặt xmax. VACUUM đánh dấu chỗ dùng lại được, và thường không trả chỗ cho hệ điều hành.
• "CLUSTER giữ bảng luôn sắp theo chỉ mục." Chỉ một lần. Dòng mới và dòng sửa không theo thứ tự đó.
• "SELECT không ghi gì." Đọc có thể đặt hint bit và chạy pruning, làm page thành dirty. Khi bật checksum, lần đặt hint bit đầu tiên sau checkpoint còn sinh ảnh page trong WAL.
• "numeric(18, 2) luôn chiếm 9 byte như decimal(18, 2)." numeric dài theo giá trị: 1.500.000,00 chiếm 5 byte trên đĩa.
• "Khôi phục banhang về 12:06 bằng pg_basebackup." Base backup và WAL là của cả cluster. Dựng ở máy khác rồi chép dữ liệu về. pg_dump lấy được một database nhưng không về được thời điểm tùy chọn.
• "Recovery hoàn tác giao dịch dở dang." Không có undo. Giao dịch không có commit record là aborted, tuple của nó vô hình.
• "synchronous_commit = off có thể làm hỏng database." Nó chỉ có thể mất các giao dịch cuối, tối đa khoảng ba lần wal_writer_delay. fsync = off mới có thể làm hỏng database.
• "Khôi phục cần recovery.conf." Bỏ từ PostgreSQL 12. Dùng tham số trong postgresql.conf và tệp recovery.signal.
• "Data checksum bật sẵn." initdb của PostgreSQL 17 chưa bật mặc định, phải thêm --data-checksums lúc tạo cluster. Từ PostgreSQL 18, initdb bật mặc định. Kiểm tra bằng SHOW data_checksums;.

Đọc tiếp #

• SQL Server — Kiến trúc lưu trữ: chương đối chiếu, cùng dàn ý và cùng các mốc 12:04, 12:07.
• SQL Server — Kỹ thuật thường dùng: READ_COMMITTED_SNAPSHOT, ADR và delayed durability, ba kỹ thuật có bản tương ứng gần với MVCC, recovery chỉ redo và synchronous_commit = off ở đây.
• SQL Server — Transaction, khóa và mức isolation: khóa và versioning phía SQL Server, đối chiếu với xmin và xmax ở mục 6.
• Azure SQL — Khác với SQL Server tự cài: nền tảng quản lý thay chuỗi backup, để so với việc tự giữ WAL archive ở đây.

Nguồn #

• Database File Layout, Database Page Layout, TOAST, Heap-Only Tuples (HOT), Visibility Map
• Routine Vacuuming, Automatic Vacuuming (PostgreSQL 17), Vacuuming (PostgreSQL 18)
• Reliability, WAL Configuration, WAL Internals, Asynchronous Commit
• Write Ahead Log, Resource Consumption, Client Connection Defaults
• Continuous Archiving and Point-in-Time Recovery, Hot Standby
• pg_basebackup, pg_combinebackup, pg_dump, initdb
• Tablespaces, Table Partitioning, Inheritance
• CREATE TABLE, CREATE INDEX, CLUSTER, Numeric Types
• Introduction to MVCC, System Administration Functions, The Cumulative Statistics System, pageinspect, PostgreSQL Limits