Sự Cố Production: Chỉ Một Index Đơn Giản Cũng Đủ Đánh Sập Hiệu Năng Ghi

Hãy tưởng tượng kịch bản sau: đội ngũ kỹ sư của bạn phát hiện một truy vấn trên trang quản trị đang chạy chậm vì phải quét toàn bộ bảng (sequential scan) trên bảng orders có tần suất ghi cao, chỉ để sắp xếp theo cột updated_at. Để tối ưu, một lập trình viên tạo pull request để bổ sung index:

CREATE INDEX CONCURRENTLY idx_orders_updated_at ON orders (updated_at);

Pull request được duyệt, triển khai lên hệ thống production, và thời gian phản hồi của truy vấn trang quản trị giảm từ 4 giây xuống còn 3 miligiây. Thắng lợi lớn? Chưa hẳn. Chỉ trong vòng hai giờ, mức sử dụng I/O đĩa trên nút PostgreSQL chính tăng vọt lên 100%, độ trễ nhân bản (replication lag) trên các máy chủ đọc tăng theo đường thẳng, và độ trễ trung bình của các API cập nhật tăng từ 8ms lên hơn 600ms. Các tiến trình Autovacuum phải hoạt động liên tục nhưng vẫn không thể theo kịp tốc độ phình bảng và phình index.

Nguyên nhân của sự cố vận hành này là gì? Việc thêm index mới đã vô tình phá hỏng một trong những cơ chế tối ưu nội bộ quan trọng nhất của PostgreSQL: cập nhật Heap-Only Tuple (HOT).

Bên Dưới Hộp Đen: PostgreSQL MVCC và Heap-Only Tuples

Để hiểu lý do xảy ra sự cố, chúng ta phải xem xét cách PostgreSQL triển khai cơ chế Đa phiên bản Điều khiển Đồng thời (MVCC). Khác với các storage engine như MySQL InnoDB—nơi thực hiện cập nhật ghi đè trực tiếp (in-place) và lưu phiên bản cũ vào UNDO log—PostgreSQL sẽ ghi một bản ghi mới (tuple) hoàn toàn vào tập tin dữ liệu heap mỗi khi có câu lệnh UPDATE diễn ra.

Trong luồng xử lý MVCC tiêu chuẩn:

  • PostgreSQL ghi tuple mới vào bất kỳ trang heap nào còn không gian trống.
  • Nó thêm các con trỏ index mới vào tất cả các index hiện có trên bảng đó, ngay cả khi các cột được đánh chỉ mục không hề bị thay đổi bởi câu lệnh cập nhật.

Nếu một bảng có 10 index, một thao tác cập nhật đơn lẻ sẽ tạo ra 1 lần ghi vào trang heap và 10 lần ghi riêng biệt trên 10 trang B-Tree index. Hiện tượng này gọi là khuếch đại ghi (write amplification), làm suy giảm nghiêm trọng hiệu năng ghi và đẩy nhanh quá trình phình index (index bloat).

Giải Pháp Heap-Only Tuples (HOT)

Để giải quyết vấn đề khuếch đại ghi, PostgreSQL 8.3 đã giới thiệu cơ chế tối ưu Heap-Only Tuple (HOT). Một câu lệnh cập nhật sẽ được thực hiện dưới dạng cập nhật HOT nếu đáp ứng đủ hai điều kiện bắt buộc:

  1. Không Cập Nhật Cột Có Index: Câu lệnh UPDATE không thay đổi bất kỳ cột nào có mặt trong bất kỳ index nào của bảng.
  2. Trang Heap Còn Đủ Dung Lượng: Trang heap hiện tại chứa tuple cũ phải còn đủ khoảng trống để chứa tuple phiên bản mới.

Khi cập nhật HOT diễn ra, PostgreSQL sẽ đặt phiên bản tuple mới trên chính trang heap của tuple cũ. Nó liên kết tuple cũ với tuple mới thông qua một chuỗi con trỏ dòng nội bộ (intra-page line pointer). Điều quan trọng nhất: không có bất kỳ con trỏ index nào bị sửa đổi hay thêm mới. Các index hiện tại vẫn trỏ tới tuple gốc, và PostgreSQL chỉ cần duyệt chuỗi HOT trong bộ nhớ để tìm ra phiên bản dữ liệu mới nhất.

Nguyên Nhân Gốc Rễ: Chuỗi HOT Bị Tắt

Trong các hệ thống có tần suất ghi cao, nhiều bảng được gắn trigger tự động hoặc logic ứng dụng nhằm cập nhật nhãn thời gian updated_at ở mỗi thao tác ghi:

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Trước khi thêm index idx_orders_updated_at, các câu lệnh cập nhật những trường như status hoặc total_amount đều đủ điều kiện là cập nhật HOT (giả sử trang còn đủ trống). Tuy nhiên, ngay khi index được tạo trên updated_at, mọi câu lệnh UPDATE đều làm thay đổi một cột đã được đánh index.

Điều này lập tức vô hiệu hóa cơ chế HOT. PostgreSQL buộc phải quay lại phương thức MVCC truyền thống, liên tục chèn các bản ghi index mới trên toàn bộ các cây B-Tree cho mỗi lần update. Kết quả là hiện tượng khuếch đại ghi diễn ra cực kỳ nghiêm trọng, index phình to nhanh chóng và kiệt quệ tài nguyên I/O.

Chẩn Đoán Sự Cố HOT Trên Hệ Thống Production

Bạn có thể kiểm tra xem cụm PostgreSQL của mình có đang bị mất tối ưu HOT hay không bằng cách truy vấn bảng thống kê hệ thống pg_stat_user_tables. Hãy chú ý tỷ lệ giữa tổng số lần update (n_tup_upd) và số lần update HOT thành công (n_tup_hot_upd):

SELECT 
    schemaname,
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    ROUND((n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0)) * 100, 2) AS hot_update_ratio
FROM 
    pg_stat_user_tables
WHERE 
    n_tup_upd > 1000
ORDER BY 
    n_tup_upd DESC;

Trong một hệ thống ghi nhiều hoạt động bình thường, chỉ số hot_update_ratio lý tưởng nên nằm trong khoảng 80% đến 95%. Nếu tỷ lệ này giảm xuống 0% trên một bảng nhận hàng trăm truy vấn update mỗi giây, cơ chế HOT đã hoàn toàn bị vô hiệu hóa.

Giải Pháp Khắc Phục Chuẩn Production

1. Điều Chỉnh Fillfactor Của Bảng

Dù không cập nhật cột có index, cập nhật HOT vẫn sẽ thất bại nếu trang heap bị lấp đầy 100%. Mặc định, PostgreSQL đặt fillfactor bằng 100 (lấp đầy trang hoàn toàn khi thực hiện INSERT). Giảm fillfactor sẽ để dành lại không gian trên mỗi trang cho các thao tác HOT update tương lai:

-- Đặt fillfactor = 80 để chừa lại 20% dung lượng trang cho HOT updates
ALTER TABLE orders SET (fillfactor = 80);

-- Lưu ý: Thay đổi fillfactor chỉ áp dụng cho các trang mới được tạo.
-- Để áp dụng cho dữ liệu cũ mà không khóa toàn bộ bảng, dùng pg_repack:
-- pg_repack --table=orders --dbname=production_db

2. Thay Thế Index Toàn Cục Bằng Index Một Phần (Partial Index)

Nếu các truy vấn tìm kiếm theo updated_at chỉ phục vụ các bản ghi đang xử lý, hãy thay thế index toàn cục bằng Partial Index:

-- Xóa index toàn cục đang làm hỏng cơ chế HOT
DROP INDEX CONCURRENTLY idx_orders_updated_at;

-- Tạo partial index chỉ tập trung vào các đơn hàng đang xử lý
CREATE INDEX CONCURRENTLY idx_orders_pending_updated_at 
ON orders (updated_at) 
WHERE status IN ('PENDING', 'PROCESSING');

Với partial index này, việc cập nhật các đơn hàng đã hoàn tất (status = 'COMPLETED') sẽ không còn làm thay đổi cột có index nữa, giúp PostgreSQL khôi phục lại cơ chế HOT update cho phần lớn dữ liệu lịch sử.

3. Tách Biệt Các Cột Cập Nhật Tần Suất Cao

Nếu bảng chứa các cột trạng thái thay đổi liên tục (như last_heartbeat_at, retry_count, status), hãy tách chúng sang một bảng trạng thái phụ có quan hệ 1:1 và hạn chế tối đa index trên bảng phụ này:

CREATE TABLE order_state (
    order_id BIGINT PRIMARY KEY REFERENCES orders(id),
    status VARCHAR(32) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
) WITH (fillfactor = 70);

Việc giữ cho bảng order_state nhẹ nhàng và không chứa các secondary index dư thừa giúp thao tác update luôn diễn ra nhanh chóng, nằm gọn trong trang heap và tối ưu tuyệt đối cho HOT.

Điểm Cốt Lõi

  • Cơ chế MVCC ghi ngoài vùng nhớ của PostgreSQL: Các bản cập nhật tiêu chuẩn phải ghi vào tất cả index trên bảng, gây khuếch đại ghi và phình dữ liệu nghiêm trọng.
  • Cập nhật HOT giúp bảo vệ hiệu năng: HOT giữ cho thao tác ghi chỉ nằm gói gọn trong một trang heap và không đụng đến index, miễn là không cột nào trong index bị sửa đổi và trang còn khoảng trống.
  • Cảnh giác với index trên cột updated_at: Việc tạo index trên cột timestamp tự động cập nhật sẽ vô hiệu hóa cơ chế HOT của toàn bộ câu lệnh write trên bảng đó.
  • Giám sát chỉ số thống kê định kỳ: Theo dõi tỷ lệ n_tup_hot_upd / n_tup_upd qua pg_stat_user_tables để bảo đảm các bảng có tần suất ghi cao duy trì tỷ lệ HOT trên 80%.
  • Áp dụng fillfactor và partial index: Điều chỉnh fillfactor từ 70–80 cho các bảng ghi nhiều và sử dụng partial index để giảm thiểu chi phí cập nhật index.