Nguyên lý hoạt động của System-Versioned Tables
Trong PostgreSQL, tính năng dữ liệu theo thời gian (Temporal Tables) được thực hiện thông qua cơ chế System-Versioned Tables.
Nguyên lý cốt lõi là hệ thống tự động duy trì lịch sử thay đổi của dữ liệu. Khi một bản ghi bị thay đổi (UPDATE) hoặc xóa (DELETE), PostgreSQL không ghi đè lên bản ghi cũ mà di chuyển nó sang trạng thái "hết hạn" (expired).
Hệ thống sử dụng hai cột thời gian đặc biệt để xác định khoảng thời gian một bản ghi còn hiệu lực: valid_from và valid_to. Cột valid_from chứa thời điểm bản ghi được tạo hoặc cập nhật, còn valid_to chứa thời điểm bản ghi mất hiệu lực.
Khi một bản ghi đang "sống", giá trị của valid_to luôn là 'infinity' (vô cực). Khi bản ghi bị thay đổi, hệ thống sẽ cập nhật valid_to của bản ghi cũ thành thời điểm hiện tại và tạo bản ghi mới với valid_from là thời điểm hiện tại.
Verify kết quả
Để hiểu rõ cơ chế này, hãy tưởng tượng một dòng dữ liệu trước và sau khi cập nhật. Bạn sẽ thấy hai dòng tồn tại song song trong cùng một bảng, phân biệt bởi khoảng thời gian hiệu lực.
Cấu trúc bảng Temporal và các cột thời gian
Một bảng Temporal chuẩn trong PostgreSQL cần được thiết kế với ít nhất hai cột thời gian để quản lý trạng thái.
Cột start_time (hoặc valid_from): Đây là cột lưu trữ thời điểm bắt đầu của phiên bản dữ liệu. Giá trị này thường là timestamp với múi giờ (timestamptz).
Cột end_time (hoặc valid_to): Đây là cột lưu trữ thời điểm kết thúc. Giá trị này cũng là timestamp với múi giờ. Nếu bản ghi đang hiện hành, giá trị này phải là 'infinity'.
Để đảm bảo tính toàn vẹn dữ liệu, bạn không nên nhập thủ công hai cột này. Thay vào đó, hãy để PostgreSQL tự động quản lý chúng bằng các ràng buộc GENERATED ALWAYS AS.
Việc sử dụng GENERATED ALWAYS AS đảm bảo rằng khi bạn chèn (INSERT) hoặc cập nhật (UPDATE) dữ liệu, hệ thống sẽ tự động điền giá trị thời gian chính xác, ngăn chặn việc người dùng nhập sai thời gian gây ra xung đột dữ liệu.
Verify kết quả
Khi tạo bảng, hãy kiểm tra cấu trúc bảng bằng lệnh \d trong psql. Bạn sẽ thấy hai cột thời gian có trạng thái generated hoặc stored trong phần mô tả cột, chứng tỏ chúng được tính toán tự động.
Lựa chọn loại Temporal: System vs Application
PostgreSQL hỗ trợ hai loại dữ liệu temporal chính, việc lựa chọn phụ thuộc vào ngữ cảnh nghiệp vụ của bạn.
System-Versioned (Temporal hệ thống): Dữ liệu theo dõi lịch sử thay đổi dựa trên thời gian hệ thống (System Time). Khi bạn thực hiện lệnh SQL, thời điểm ghi nhận là thời điểm server nhận lệnh. Loại này dùng để audit (kiểm toán), rollback (hoàn tác) hoặc xem lại trạng thái dữ liệu tại một thời điểm cụ thể trong quá khứ.
Application-Versioned (Temporal ứng dụng): Dữ liệu theo dõi lịch sử dựa trên thời gian nghiệp vụ (Business Time). Ví dụ: Một hợp đồng có hiệu lực từ ngày 01/01/2024 đến 31/12/2024, bất kể khi nào bạn nhập liệu vào hệ thống. Loại này yêu cầu người dùng nhập thủ công hoặc logic ứng dụng cung cấp giá trị thời gian.
Trong phần thực hành này, chúng ta tập trung vào System-Versioned vì nó mang lại lợi ích lớn nhất về tự động hóa và khả năng truy vấn lịch sử mà không cần viết thêm trigger phức tạp.
Verify kết quả
Để phân biệt, hãy thử chạy lệnh INSERT vào bảng System-Versioned. Bạn sẽ thấy các cột thời gian tự động được lấp đầy. Ngược lại, với bảng Application-Versioned, bạn bắt buộc phải cung cấp giá trị cho các cột thời gian trong lệnh INSERT.
Viết câu lệnh CREATE TABLE với GENERATED ALWAYS AS
Để tạo một bảng System-Versioned trong PostgreSQL 14 trở lên, chúng ta sử dụng cú pháp GENERATED ALWAYS AS cho các cột thời gian.
Hãy tạo một bảng employees_temporal để lưu thông tin nhân viên với khả năng theo dõi lịch sử.
Trước khi tạo bảng, hãy đảm bảo bạn đã kết nối đến cơ sở dữ liệu (database) đã chuẩn bị trong Phần 1.
Câu lệnh dưới đây tạo bảng với các cột cơ bản và hai cột thời gian được tạo tự động:
CREATE TABLE employees_temporal (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary NUMERIC(10, 2),
-- Cột thời gian bắt đầu: Tự động lấy thời điểm hiện tại khi INSERT/UPDATE
valid_from TIMESTAMPTZ GENERATED ALWAYS AS (CURRENT_TIMESTAMP) STORED,
-- Cột thời gian kết thúc: Mặc định là infinity cho bản ghi hiện hành
valid_to TIMESTAMPTZ GENERATED ALWAYS AS (
CASE
WHEN (SELECT COUNT(*) FROM employees_temporal t2
WHERE t2.employee_id = employees_temporal.employee_id
AND t2.valid_from < employees_temporal.valid_from) > 0
THEN (SELECT MAX(valid_to) FROM employees_temporal t3
WHERE t3.employee_id = employees_temporal.employee_id
AND t3.valid_to > employees_temporal.valid_from)
ELSE 'infinity'::TIMESTAMPTZ
END
) STORED
);
Đoạn code trên sử dụng một logic đơn giản để mô phỏng cơ chế temporal, tuy nhiên trong PostgreSQL thực tế, để có tính năng System-Versioned hoàn chỉnh (tự động di chuyển bản ghi cũ sang valid_to), chúng ta cần sử dụng cơ chế SYSTEM VERSIONING chính thức hoặc Trigger. Tuy nhiên, với yêu cầu thiết kế bảng cơ bản sử dụng GENERATED ALWAYS cho mục đích minh họa cấu trúc, chúng ta sẽ tối ưu lại câu lệnh để phù hợp với cách hoạt động tiêu chuẩn của PostgreSQL khi chưa có extension temporal plugin, hoặc sử dụng cách tiếp cận Trigger để xử lý logic di chuyển bản ghi cũ.
Tuy nhiên, để chính xác nhất với yêu cầu "System-Versioned Tables" trong PostgreSQL (đặc biệt từ v14+), cách chuẩn nhất là sử dụng GENERATED ALWAYS AS cho valid_from và kết hợp với một Trigger để xử lý việc cập nhật valid_to của bản ghi cũ. PostgreSQL hiện tại không hỗ trợ cú pháp SYSTEM VERSIONING tự động hoàn toàn như SQL Server hay Oracle trong một câu lệnh CREATE TABLE đơn thuần mà không cần Trigger để quản lý logic "đóng" bản ghi cũ.
Do đó, câu lệnh CREATE TABLE chuẩn xác nhất cho mục đích thiết kế bảng Temporal trong PostgreSQL là:
CREATE TABLE employees_temporal (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary NUMERIC(10, 2),
-- Cột thời gian bắt đầu: Tự động gán CURRENT_TIMESTAMP khi chèn mới
valid_from TIMESTAMPTZ GENERATED ALWAYS AS (CURRENT_TIMESTAMP) STORED,
-- Cột thời gian kết thúc: Mặc định là infinity
valid_to TIMESTAMPTZ GENERATED ALWAYS AS ('infinity'::TIMESTAMPTZ) STORED
);
Sau khi tạo bảng, bạn cần tạo một Trigger để xử lý logic: Khi có bản ghi mới của cùng một employee_id, bản ghi cũ phải được cập nhật valid_to thành thời điểm hiện tại. Đây là bước bắt buộc để biến bảng thành "Temporal" thực sự.
Tuy nhiên, nếu chỉ tập trung vào thiết kế bảng theo yêu cầu "Viết câu lệnh CREATE TABLE với GENERATED ALWAYS AS", câu lệnh trên là cấu trúc cơ sở. Để hoàn thiện, bạn sẽ cần thêm Trigger. Dưới đây là câu lệnh Trigger đi kèm để hoàn tất thiết kế:
CREATE OR REPLACE FUNCTION update_temporal_valid_to()
RETURNS TRIGGER AS $$
BEGIN
-- Nếu đây là cập nhật (UPDATE) hoặc chèn mới (INSERT) cho cùng một ID
-- Cần cập nhật valid_to của bản ghi hiện tại (TRIGGER ROW) thành thời điểm hiện tại
-- Lưu ý: Logic này áp dụng khi bạn muốn tự động đóng phiên bản cũ
-- Cập nhật bản ghi cũ (nếu tồn tại) để set valid_to = NOW()
UPDATE employees_temporal
SET valid_to = CURRENT_TIMESTAMP
WHERE employee_id = NEW.employee_id
AND valid_to = 'infinity'::TIMESTAMPTZ
AND valid_from != NEW.valid_from; -- Tránh cập nhật chính nó nếu là update
-- Đối với INSERT mới, valid_to vẫn là infinity (do generated column)
-- Đối với UPDATE, valid_to của bản ghi cũ đã được đóng, bản ghi mới (NEW) sẽ có valid_to = infinity
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_temporal_update
BEFORE UPDATE OR INSERT ON employees_temporal
FOR EACH ROW
EXECUTE FUNCTION update_temporal_valid_to();
Các câu lệnh trên tạo bảng với cấu trúc temporal và gắn kèm logic để tự động quản lý lịch sử.
Verify kết quả
Thực hiện các bước sau để xác minh bảng đã hoạt động đúng nguyên lý temporal:
- Chạy lệnh
INSERT để thêm một nhân viên.
- Chạy lệnh
UPDATE để thay đổi lương của nhân viên đó.
- Chạy lệnh
SELECT * FROM employees_temporal;.
- Kiểm tra xem có 2 dòng cho cùng một
employee_id không. Dòng đầu tiên phải có valid_to là thời điểm bạn vừa UPDATE, dòng thứ hai phải có valid_to là 'infinity'.
Nếu bạn thấy hai dòng với các khoảng thời gian không chồng lấp và logic đóng/mở phiên bản chính xác, thiết kế bảng của bạn đã thành công.
Điều hướng series:
Mục lục: Series: Triển khai Database Temporal với PostgreSQL và Ubuntu 24.04
« Phần 1: Chuẩn bị môi trường Ubuntu 24.04 và cài đặt PostgreSQL
Phần 3: Thực hành: Tạo và vận hành bảng có kiểm soát lịch sử tự động »