Du hành thời gian trong SQL: Sử dụng Temporal Table trong MariaDB và PostgreSQL

Table of Contents

Cơn ác mộng lúc 2 giờ sáng: Dữ liệu đã đi đâu?

Lúc đó là 2 giờ 14 phút sáng. Điện thoại của tôi rung liên hồi trên tủ đầu giường với những cảnh báo từ hệ thống giám sát production. Một lập trình viên junior vừa chạy lệnh UPDATE trên bảng products nhưng quên mất mệnh đề WHERE. Chỉ trong chớp mắt, mọi mức giá trong danh mục sản phẩm của chúng tôi đều giảm xuống còn $0.00. Trong khi đó, một chương trình giảm giá lớn theo mùa sẽ bắt đầu chỉ sau đúng bốn giờ nữa.

Việc khôi phục hoàn toàn cơ sở dữ liệu 500GB sẽ mất ít nhất ba giờ, và point-in-time recovery (PITR) là một cơn đau đầu về mặt hậu cần liên quan đến các instance phụ và di chuyển dữ liệu. Tôi cần một cách để xem dữ liệu như nó vốn có từ năm phút trước, ngay tại production shell. Đây chính là lúc Temporal Tables—còn được gọi là System-Versioned Tables—trở thành “vị cứu tinh” thực sự.

Dù tôi đã sử dụng đủ loại từ MySQL đến MongoDB, MariaDB và PostgreSQL vẫn cung cấp những cách gọn gàng nhất để xử lý tiêu chuẩn SQL:2011 temporal. Hãy quên việc tạo ‘audit_logs’ thủ công hay các trigger lỏng lẻo đi. Những engine này có thể tự động theo dõi lịch sử của từng dòng dữ liệu, mang đến cho bạn một “cỗ máy thời gian” tích hợp sẵn cho dữ liệu của mình.

Logic: Tại sao sao lưu là chưa đủ

Các cơ sở dữ liệu tiêu chuẩn thường mang tính tạm thời; chúng chỉ quan tâm đến trạng thái “hiện tại”. Khi bạn cập nhật một dòng, giá trị trước đó sẽ bị ghi đè và mất đi. Temporal table thay đổi cuộc chơi bằng cách theo dõi “thời hạn hiệu lực” (period of validity) cho mọi bản ghi. Mỗi thay đổi đều được gán một timestamp, cho phép bạn truy vấn cơ sở dữ liệu tại bất kỳ thời điểm cụ thể nào trong lịch sử.

Tại sao điều này lại tốt hơn một audit log tiêu chuẩn? Dưới đây là ba lý do thực tế:

Điều tra Forensic tức thì: Bạn có thể xác định chính xác ai đã thay đổi địa chỉ giao hàng của khách hàng từ ba tuần trước mà không cần lục lọi các file log văn bản.
Khôi phục không gây gián đoạn (Zero-Downtime): Bạn có thể “hoàn tác” một bản cập nhật thảm khốc bằng cách lấy trạng thái trước đó trực tiếp thông qua một subquery.
Phân tích xu hướng chính xác: Bạn có thể so sánh mức độ biến động của kho hàng trong quý trước mà không cần xây dựng một data warehouse phức tạp.

Bắt đầu: Thiết lập và Cài đặt

Cách triển khai tùy thuộc vào engine bạn chọn. MariaDB và PostgreSQL vẫn cung cấp những cách tiếp cận khác nhau để quản lý dữ liệu lịch sử. MariaDB cung cấp cách tiếp cận “thiết lập một lần là xong” (set and forget) mang tính bản địa. PostgreSQL yêu cầu cấu hình thủ công nhiều hơn một chút nhưng lại mang lại sự linh hoạt cao hơn.

MariaDB: Native System Versioning

MariaDB đã tích hợp system versioning vào nhân từ phiên bản 10.3.4. Không cần cài đặt plugin hay quản lý các extension bổ sung. Nếu phiên bản của bạn là bản mới, bạn đã có sẵn các công cụ cần thiết.

PostgreSQL: Cách tiếp cận linh hoạt

PostgreSQL không có một nút “bật” duy nhất cho temporal tables. Hầu hết các đội ngũ, đặc biệt là những người sử dụng các dịch vụ quản lý như AWS RDS hoặc Google Cloud SQL, thường sử dụng kết hợp giữa history schema và trigger. Điều này giúp thiết lập của bạn có tính di động cao và tránh phụ thuộc vào các extension ngôn ngữ C cụ thể.

Nếu bạn đang làm việc trong môi trường local và muốn có extension chuyên dụng, bạn có thể cài đặt các gói phát triển server:

# Dành cho người dùng Debian/Ubuntu
sudo apt-get install postgresql-server-dev-all
# Xây dựng extension từ mã nguồn hoặc cài đặt qua trình quản lý gói

Cấu hình: Kích hoạt khả năng du hành thời gian

Thiết lập MariaDB

MariaDB làm cho việc này trở nên cực kỳ đơn giản. Bạn chỉ cần thêm WITH SYSTEM VERSIONING vào định nghĩa bảng của mình. Đây là một bảng products đã sẵn sàng cho môi trường production:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10, 2)
) WITH SYSTEM VERSIONING;

Cần thêm tính năng này vào một bảng đang hoạt động? Chỉ cần chạy lệnh alter:

ALTER TABLE products ADD SYSTEM VERSIONING;

Các cột ẩn của MariaDB—row_start và row_end—giờ đây sẽ quản lý vòng đời của mọi dòng dữ liệu ở phía sau. Chúng sẽ không làm rối kết quả SELECT * của bạn, nhưng chúng luôn ở đó.

Thiết lập PostgreSQL

Trong Postgres, chúng ta tạo một bảng history thứ hai để phản chiếu bảng chính. Cách tiếp cận này rất tốt cho hiệu năng vì nó giữ cho bảng “nóng” (hot table) của bạn luôn nhỏ gọn và chuyển dữ liệu cũ sang một vùng lưu trữ riêng biệt. Dưới đây là mô hình dựa trên trigger tiêu chuẩn:

-- Bảng chính cho dữ liệu hiện tại
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary INT,
    valid_from TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    valid_to TIMESTAMP WITH TIME ZONE DEFAULT '9999-12-31'
);

-- Bảng lịch sử
CREATE TABLE employees_history (LIKE employees);

-- Logic để lưu trữ các dòng cũ
CREATE OR REPLACE FUNCTION versioning_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO employees_history SELECT OLD.*;
        NEW.valid_from = CURRENT_TIMESTAMP;
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO employees_history SELECT OLD.*;
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_versioning
BEFORE UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION versioning_trigger();

Truy vấn quá khứ

Đây là lúc sự đầu tư của bạn mang lại thành quả. Hãy xem cách giải quyết cuộc khủng hoảng lúc 2 giờ sáng bằng cách nhìn lại quá khứ.

Cách làm với MariaDB

MariaDB sử dụng một cú pháp rất dễ đọc. Nếu lập trình viên làm hỏng cơ sở dữ liệu lúc 2:10 sáng, chúng ta có thể xem giá cả lúc 2:05 sáng chỉ với một dòng lệnh:

SELECT * FROM products 
FOR SYSTEM_TIME AS OF '2023-10-27 02:05:00';

Để xem mọi thay đổi giá của một mặt hàng cụ thể (ví dụ sản phẩm có ID 42) nhằm kiểm tra lỗi:

SELECT name, price, row_start, row_end 
FROM products FOR SYSTEM_TIME ALL 
WHERE id = 42 
ORDER BY row_start ASC;

Cách làm với PostgreSQL

Với thiết lập thủ công, chúng ta truy vấn cả bảng hiện tại và bảng lịch sử bằng lệnh UNION. Nó hơi dài dòng hơn một chút nhưng rất rõ ràng:

-- Lương của nhân viên 101 vào trưa ngày hôm qua là bao nhiêu?
SELECT * FROM employees_history 
WHERE id = 101 
AND '2023-10-26 12:00:00' BETWEEN valid_from AND valid_to
UNION ALL
SELECT * FROM employees 
WHERE id = 101 
AND '2023-10-26 12:00:00' BETWEEN valid_from AND valid_to;

Bảo trì: Quản lý sự tăng trưởng

Các bảng Temporal tăng trưởng rất nhanh. Mỗi lệnh UPDATE đều tạo ra một dòng mới trong lịch sử của bạn. Nếu bạn có một bảng với tần suất thay đổi cao, dung lượng lưu trữ của bạn sẽ biến mất nhanh chóng. Tôi đã từng thấy một bảng log được bật versioning ngốn sạch 1TB ổ cứng NVMe chỉ trong vòng bảy ngày.

Trong MariaDB, bạn có thể dọn dẹp dữ liệu cũ một cách dễ dàng:

-- Xóa lịch sử cũ hơn 30 ngày
DELETE FROM products FOR SYSTEM_TIME BEFORE (NOW() - INTERVAL 30 DAY);

Đối với PostgreSQL, hãy thiết lập một job pg_cron để di chuyển hoặc xóa các dòng khỏi bảng _history của bạn. Điều này giúp cơ sở dữ liệu chính của bạn luôn gọn nhẹ và các truy vấn luôn nhanh chóng.

Kết luận

Temporal tables không chỉ là một tính năng SQL xa lạ; chúng là gói bảo hiểm của bạn. MariaDB chiến thắng về sự đơn giản và tuân thủ tiêu chuẩn. PostgreSQL chiến thắng về khả năng tùy biến, cho phép bạn lưu trữ lịch sử trên các ổ đĩa rẻ hơn hoặc các tablespace khác nhau. Lần tới khi một script chạy sai, đừng hoảng loạn. Chỉ cần truy vấn quá khứ và tiếp tục đi ngủ.