Post-Mortem & Runbook Vận hành: Giải cứu Database 200 triệu dòng với PostgreSQL Declarative Partitioning

Vào 02:00 sáng một ngày cuối tuần, hệ thống cảnh báo ghi nhận đĩa cứng SSD của cụm Database chính chạm ngưỡng 98% dung lượng. Lệnh dọn dẹp log định kỳ:

DELETE FROM system_audit_logs WHERE created_at < NOW() - INTERVAL '90 days';

Thao tác tưởng chừng đơn giản này đã ném hệ thống vào thảm họa: câu lệnh DELETE chạy suốt 45 phút không xong, tạo ra hàng chục GB Write-Ahead Logging (WAL), làm nghẽn CPU I/O và khóa chặt toàn bộ các giao dịch ghi mới.

Nguyên nhân root cause: Bảng system_audit_logs đã phình to vượt ngưỡng 200 triệu bản ghi trên một cấu trúc bảng đơn khối (Monolithic Table).

Dưới đây là Báo cáo xử lý sự cố (Post-Mortem) và Sổ tay hướng dẫn chi tiết (Runbook) chuyển đổi sang kiến trúc PostgreSQL Declarative Partitioning mà không gây ngắt đoạn dịch vụ (Zero-Downtime Migration).

1. Phân tích nguyên nhân: Vì sao bảng lớn lại “kháng” lệnh DELETE và INDEX?

Trong hệ quản trị CSDL PostgreSQL, kiến trúc MVCC (Multi-Version Concurrency Control) xử lý lệnh DELETE không phải bằng cách xóa ngay dữ liệu khỏi đĩa cứng, mà là đánh dấu bản ghi đó là “dữ liệu chết” (Dead Tuples).

1. Thảm họa bloat đĩa cứng: Việc DELETE 50 triệu dòng tạo ra 50 triệu Dead Tuples. Tiến trình VACUUM sau đó phải mất hàng giờ để dọn dẹp, trong khi dung lượng đĩa cứng vật lý không hề được trả lại cho OS trừ khi chạy VACUUM FULL (vốn sẽ khóa hoàn toàn bảng).
2. Kích thước B-Tree phá vỡ RAM: Với bảng 200 triệu dòng, chỉ riêng file Index cho cột created_at và user_id đã chiếm tới 18GB. Khi dung lượng Index vượt quá shared_buffers của PostgreSQL, ổ cứng buộc phải swap dữ liệu liên tục, làm giảm số lượng phép tính IOPS xuống mức thảm hại.

2. Runbook Kỹ thuật: Chuyển đổi sang Partitioned Table không Downtime

Để khắc phục triệt để, chúng tôi áp dụng mô hình Range Partitioning theo Tháng kết hợp với kỹ thuật đổi tên bảng ngầm (Table Swapping).

[Bảng cũ đơn khối (200MB+)] ──► Rename thành system_audit_logs_legacy
                                              │
[Bảng cha mới (Partitioned)] ──► Tạo sẵn các phân vùng con theo Tháng
                                              │
           ┌──────────────────────────────────┴──────────────────────────────────┐
           ▼                                  ▼                                  ▼
[audit_logs_2026_09]                  [audit_logs_2026_10]                   [audit_logs_2026_11]

Bước 1: Khởi tạo bảng cha định tuyến và các phân vùng con

SQL

-- 1. Tạo bảng cha sử dụng thuộc tính PARTITION BY RANGE
CREATE TABLE system_audit_logs_new (
    id BIGSERIAL,
    tenant_id UUID NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    payload JSONB,
    PRIMARY KEY (id, created_at) -- Ràng buộc bắt buộc: PK phải chứa Partition Key
) PARTITION BY RANGE (created_at);

-- 2. Tạo các phân vùng con cho các tháng hiện tại và tương lai
CREATE TABLE audit_logs_2026_09 PARTITION OF system_audit_logs_new
    FOR VALUES FROM ('2026-09-01 00:00:00+07') TO ('2026-10-01 00:00:00+07');

CREATE TABLE audit_logs_2026_10 PARTITION OF system_audit_logs_new
    FOR VALUES FROM ('2026-10-01 00:00:00+07') TO ('2026-11-01 00:00:00+07');

CREATE TABLE audit_logs_2026_11 PARTITION OF system_audit_logs_new
    FOR VALUES FROM ('2026-11-01 00:00:00+07') TO ('2026-12-01 00:00:00+07');

-- 3. Tạo Index riêng trên từng phân vùng (Index siêu nhẹ, vừa vặn trong RAM)
CREATE INDEX idx_audit_2026_10_tenant ON audit_logs_2026_10 (tenant_id);

Bước 2: Tráo đổi tên bảng trong một Transaction duy nhất (< 5ms Lock)

SQL

BEGIN;
-- Đổi tên bảng cũ để ứng dụng không ghi tiếp vào đây
ALTER TABLE system_audit_logs RENAME TO system_audit_logs_legacy;

-- Đổi tên bảng phân vùng mới thành tên chính thức
ALTER TABLE system_audit_logs_new RENAME TO system_audit_logs;
COMMIT;
-- Lúc này mọi traffic ghi mới lập tức chảy vào phân vùng audit_logs_2026_10

Bước 3: Đồng bộ dữ liệu lịch sử ngầm (Background Backfill)

Dùng một script chạy ngầm để đọc dữ liệu từ system_audit_logs_legacy theo từng batch 5.000 dòng và chèn lại vào system_audit_logs (PostgreSQL sẽ tự động định tuyến bản ghi cũ về đúng bảng phân vùng tương ứng).

3. Sức mạnh tuyệt đối: Xóa 50 triệu bản ghi cũ trong 1 mili-giây

Sau khi đã phân vùng, việc dọn dẹp dữ liệu cũ quá hạn (Data Retention Policy) không còn dùng lệnh DELETE nữa. Chúng tôi sử dụng quy trình Detach & Drop:

SQL

-- 1. Tách phân vùng cũ ra khỏi cấu trúc định tuyến (Không tốn CPU)
ALTER TABLE system_audit_logs DETACH PARTITION audit_logs_2026_09;

-- 2. Xóa hẳn bảng dữ liệu riêng biệt khỏi ổ cứng
DROP TABLE audit_logs_2026_09;

Kết quả Benchmark: Lệnh DROP TABLE giải phóng 42GB đĩa cứng ngay lập tức trong 1.2 mili-giây, không phát sinh log WAL và không ảnh hưởng đến hiệu năng của các phân vùng khác.

4. Checklist quy chuẩn vận hành cho Database Administrator (DBA)

• [ ] Bắt buộc có Default Partition: Luôn khởi tạo CREATE TABLE audit_logs_default PARTITION OF system_audit_logs DEFAULT; để hứng các bản ghi có thời gian nằm ngoài dự kiến, tránh làm văng lỗi ứng dụng.
• [ ] Tự động hóa bằng pg_partman: Cài đặt extension pg_partman để tự động khởi tạo bảng con cho tháng mới trước 3 ngày và tự động Detach các phân vùng cũ theo chính sách lưu trữ.
• [ ] Kiểm tra Query Execution Plan: Bắt buộc chạy EXPLAIN ANALYZE cho các câu truy vấn chính để đảm bảo Query Planner hiển thị dòng Subquery Scan hoặc Seq Scan chỉ trên duy nhất 1 phân vùng con (Partition Pruning hoạt động thành công).

Chuyển đổi sang kiến trúc Phân vùng bảng là bước ngoặt quan trọng giúp hạ tầng dữ liệu của bạn chuyển từ trạng thái thụ động chống đỡ sang chủ động kiểm soát quy mô hàng trăm triệu bản ghi.