1. Truy vấn dữ liệu tại một thời điểm cụ thể (Point-in-Time Query)
Để xem trạng thái của dữ liệu tại một thời điểm lịch sử cụ thể, PostgreSQL sử dụng cú pháp AS OF kết hợp với từ khóa FOR SYSTEM_TIME.
Lệnh này sẽ tự động lọc và trả về các hàng dữ liệu có giá trị valid_from nhỏ hơn hoặc bằng thời điểm yêu cầu và valid_to lớn hơn thời điểm đó.
Ví dụ: Truy vấn danh sách nhân viên đang làm việc vào ngày 15/10/2023 lúc 14:00:00.
SELECT *
FROM employees
FOR SYSTEM_TIME AS OF '2023-10-15 14:00:00';
Kết quả mong đợi: Bảng trả về chỉ chứa các bản ghi có trạng thái "Active" tại đúng thời điểm đó. Nếu nhân viên nghỉ việc trước thời điểm này, họ sẽ không xuất hiện trong kết quả.
2. Truy vấn dữ liệu trong một khoảng thời gian (Range Query)
Sử dụng FROM ... TO để xem lịch sử thay đổi
Khi cần phân tích sự biến động của dữ liệu trong một khoảng thời gian nhất định, ta sử dụng cú pháp FROM ... TO.
Cú pháp này trả về tất cả các phiên bản của hàng dữ liệu đã tồn tại trong khoảng thời gian từ start_time đến end_time, bao gồm cả các bản ghi đã bị sửa đổi hoặc xóa trong khoảng đó.
Ví dụ: Xem toàn bộ lịch sử thay đổi của nhân viên có ID là 101 từ ngày 01/10/2023 đến 30/10/2023.
SELECT
employee_id,
first_name,
last_name,
department,
valid_from,
valid_to
FROM employees
FOR SYSTEM_TIME FROM '2023-10-01 00:00:00' TO '2023-10-30 23:59:59'
WHERE employee_id = 101;
Kết quả mong đợi: Xuất hiện nhiều dòng cho cùng một employee_id nếu nhân viên này đã thay đổi thông tin (ví dụ: chuyển phòng ban) nhiều lần trong khoảng thời gian trên. Mỗi dòng tương ứng với một phiên bản lịch sử.
Kết hợp điều kiện WHERE với cột thời gian
Bạn có thể kết hợp FOR SYSTEM_TIME với các điều kiện WHERE thông thường để lọc dữ liệu chính xác hơn.
Điều kiện WHERE được áp dụng sau khi PostgreSQL đã xác định phạm vi thời gian. Điều này giúp giảm thiểu dữ liệu cần quét.
Ví dụ: Tìm tất cả các nhân viên thuộc phòng "IT" đã từng tồn tại trong khoảng thời gian từ 01/01/2023 đến 31/12/2023.
SELECT DISTINCT
employee_id,
first_name,
last_name,
department
FROM employees
FOR SYSTEM_TIME FROM '2023-01-01 00:00:00' TO '2023-12-31 23:59:59'
WHERE department = 'IT';
Kết quả mong đợi: Danh sách các nhân viên duy nhất đã từng làm việc tại phòng IT trong năm 2023, ngay cả khi họ đã chuyển phòng hoặc nghỉ việc sau đó.
3. So sánh hiệu năng: Bảng thường vs Bảng Temporal
Thiết lập kịch bản kiểm tra
Để so sánh hiệu năng, chúng ta sẽ thực hiện truy vấn trên một bảng thường (không có temporal) và một bảng temporal đã được chuẩn bị từ các phần trước.
Chúng ta sẽ sử dụng câu lệnh EXPLAIN (ANALYZE, BUFFERS) để đo lường thời gian thực thi (Execution Time) và lượng bộ nhớ đệm (Buffers) tiêu thụ.
Trước tiên, hãy tạo một bảng thường để làm mẫu so sánh (nếu chưa có).
CREATE TABLE employees_normal AS
SELECT
employee_id,
first_name,
last_name,
department,
hire_date
FROM employees
WHERE valid_to = '2999-12-31 23:59:59';
CREATE INDEX idx_employees_normal_dept ON employees_normal(department);
Kết quả mong đợi: Bảng employees_normal được tạo thành công, chứa chỉ dữ liệu hiện tại. Chỉ mục trên cột department được tạo.
Thực hiện benchmark trên bảng thường
Thực hiện truy vấn lọc theo phòng ban trên bảng thường.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM employees_normal
WHERE department = 'IT';
Kết quả mong đợi:
- Execution Time: Rất thấp (dưới 1ms).
- Buffers: Chỉ đọc một vài trang dữ liệu vì chỉ có dữ liệu hiện tại.
- Plan: Sử dụng Index Scan trên idx_employees_normal_dept.
Thực hiện benchmark trên bảng Temporal
Thực hiện truy vấn tương tự nhưng sử dụng cú pháp temporal để lấy dữ liệu tại thời điểm hiện tại.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM employees
FOR SYSTEM_TIME AS OF NOW()
WHERE department = 'IT';
Kết quả mong đợi:
- Execution Time: Cao hơn bảng thường (có thể từ 5ms - 50ms tùy lượng dữ liệu lịch sử).
- Buffers: Tiêu thụ nhiều bộ nhớ hơn vì phải quét qua các bản ghi lịch sử để lọc ra bản ghi mới nhất.
- Plan: Có thể thấy Seq Scan hoặc Index Scan phức tạp hơn do phải join nội bộ với điều kiện thời gian valid_from <= NOW() AND valid_to > NOW().
4. Xác minh kết quả (Verification)
Để đảm bảo các truy vấn temporal hoạt động chính xác, hãy thực hiện các bước kiểm tra sau:
-
Kiểm tra tính nhất quán của dữ liệu: Chạy truy vấn
AS OF tại thời điểm hiện tại và so sánh với bảng thường. Kết quả phải giống hệt nhau.
SELECT COUNT(*) FROM employees_normal WHERE department = 'IT';
SELECT COUNT(*) FROM employees FOR SYSTEM_TIME AS OF NOW() WHERE department = 'IT';
Kết quả mong đợi: Hai lệnh trả về cùng một số lượng hàng.
-
Kiểm tra tính toàn vẹn của lịch sử: Chạy truy vấn
FROM ... TO bao phủ một khoảng thời gian mà bạn biết chắc đã có thay đổi dữ liệu (ví dụ: sau khi bạn thực hiện lệnh UPDATE trong Phần 3).
SELECT employee_id, department, valid_from, valid_to
FROM employees
FOR SYSTEM_TIME FROM '2023-01-01' TO '2023-12-31'
WHERE employee_id = 101
ORDER BY valid_from;
Kết quả mong đợi: Xuất hiện ít nhất 2 dòng cho cùng một employee_id nếu có thay đổi, với các khoảng thời gian valid_from và valid_to không chồng lấp nhau (trừ bản ghi cuối cùng có valid_to = '2999-12-31 23:59:59').
-
Kiểm tra hiệu năng: Quan sát
Execution Time trong kết quả của EXPLAIN. Nếu thời gian quá cao (>100ms với dữ liệu nhỏ), cần xem xét lại việc tạo chỉ mục cho các cột valid_from và valid_to (sẽ được hướng dẫn chi tiết trong Phần 5).
Điều hướng series:
Mục lục: Series: Triển khai Database Temporal với PostgreSQL và Ubuntu 24.04
« 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
Phần 5: Quản lý hiệu suất và tối ưu hóa chỉ mục cho dữ liệu temporal »