Đã đăng vào khoảng 12 giờ trước 10 phút đọc

PostgreSQL Bài 43: Bảng tạm thời và Materialized Views: Lưu trữ kết quả truy vấn lớn (Caching)

Trong các hệ thống phân tích dữ liệu (OLAP) hoặc các ứng dụng hiển thị Dashboard tổng quan, có những câu truy vấn JOIN hàng chục bảng, GROUP BY hàng triệu dòng dữ liệu và phải mất từ vài chục giây đến vài phút để chạy xong.

Nếu mỗi lần người dùng tải lại trang web, cơ sở dữ liệu lại phải chạy lại câu query nặng nề đó từ đầu, CPU và ổ đĩa sẽ nhanh chóng bị vắt kiệt. Thay vì đẩy việc Caching lên tầng ứng dụng (dùng Redis hoặc Memcached), PostgreSQL cung cấp hai công cụ nguyên bản cực kỳ mạnh mẽ để lưu trữ trung gian kết quả truy vấn ngay tại tầng database: Bảng tạm thời (Temporary Tables) và Khung nhìn thực thể hóa (Materialized Views).

1. Bảng tạm thời (Temporary Tables)

1.1. Bản chất và Đặc điểm

Temporary Table (Bảng tạm) là một bảng vật lý được tạo ra, nhưng nó có vòng đời cực kỳ ngắn ngửi: chỉ tồn tại trong đúng một phiên làm việc (Session).

Ngay khi Client đóng kết nối (disconnect) hoặc Session kết thúc, PostgreSQL sẽ tự động xóa sạch (Drop) bảng tạm này cùng toàn bộ dữ liệu bên trong.
Tính cô lập tuyệt đối: Bảng tạm của Session A hoàn toàn tàng hình đối với Session B. Dù 2 Session cùng chạy lệnh CREATE TEMP TABLE my_temp_data, PostgreSQL vẫn tạo ra 2 bảng vật lý riêng biệt trong không gian nhớ riêng, không hề gây lỗi trùng tên.

1.2. Cú pháp và Thực chiến

Bảng tạm thường được dùng làm nơi lưu trữ trung gian (Staging) cho các quy trình ETL (Extract, Transform, Load) hoặc tính toán dữ liệu nhiều bước.

SQL

-- Tạo bảng tạm và nạp dữ liệu từ một câu SELECT phức tạp
CREATE TEMP TABLE temp_top_customers AS
SELECT customer_id, sum(amount) as total_spent
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 1000;

-- Sau đó, JOIN bảng tạm này với các bảng khác để tiếp tục xử lý
SELECT c.name, t.total_spent, c.email
FROM temp_top_customers t
JOIN customers c ON t.customer_id = c.id;

Mẹo tối ưu: Bảng tạm mặc định không có Index. Nếu bạn định JOIN bảng tạm này nhiều lần trong suốt Session, hãy tạo thêm Index cho nó để tăng tốc:

SQL

CREATE INDEX idx_temp_customer_id ON temp_top_customers(customer_id);

2. Views thông thường (Virtual Views) vs Materialized Views

Khác với bảng tạm chỉ sống tạm bợ trong Session, nếu bạn muốn lưu lại logic truy vấn cho tất cả mọi người cùng dùng vĩnh viễn, bạn dùng View. Nhưng hãy phân biệt rõ hai loại View:

2.1. View thông thường (`CREATE VIEW`)

View thông thường chỉ là một câu lệnh SQL được lưu lại (lưu logic, không lưu dữ liệu). Khi bạn gọi SELECT * FROM my_view, PostgreSQL dưới nền sẽ bê nguyên câu lệnh SQL gốc ra chạy lại từ đầu. Nếu câu query gốc tốn 10 giây, thì query vào View cũng tốn đúng 10 giây. Nó giúp code gọn gàng, nhưng không hề tăng tốc độ truy vấn.

2.2. Materialized View (`CREATE MATERIALIZED VIEW`)

Materialized View (MatView) là một bảng vật lý thực sự. Khi bạn tạo MatView, PostgreSQL sẽ chạy câu query gốc, lấy toàn bộ kết quả, và GHI CỨNG dữ liệu đó xuống ổ đĩa y hệt như một bảng thông thường. Khi bạn gọi SELECT * FROM my_mat_view, engine chỉ việc đọc dữ liệu đã được tính sẵn (Seq Scan trên cục dữ liệu nhỏ), thời gian phản hồi có thể giảm từ 10 giây xuống còn 5 mili-giây!

3. Tạo và Khai thác Materialized View

Giả sử chúng ta cần hiển thị Dashboard Doanh thu theo từng danh mục sản phẩm của toàn bộ lịch sử hệ thống (hàng chục triệu đơn hàng).

SQL

-- Tạo Materialized View để tính toán và lưu cứng kết quả
CREATE MATERIALIZED VIEW mv_category_revenue AS
SELECT 
    c.category_name,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS total_revenue,
    MAX(o.created_at) AS last_order_date
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN orders o ON p.id = o.product_id
GROUP BY c.category_name;

Ngay sau khi lệnh này chạy xong, dữ liệu tĩnh đã được lưu xuống đĩa. Bạn có thể truy vấn nó với tốc độ chớp nhoáng:

SQL

SELECT * FROM mv_category_revenue ORDER BY total_revenue DESC;

Tuyệt vời hơn, vì nó là một thực thể vật lý, bạn hoàn toàn có thể đánh Index trên Materialized View:

SQL

CREATE INDEX idx_mv_revenue ON mv_category_revenue(total_revenue DESC);

4. Vấn đề sống còn: Làm mới dữ liệu (Data Stale & Refresh)

Cái giá phải trả của việc "Lưu cứng" (Caching) là dữ liệu sẽ bị "Cũ" (Stale Data). Khi có thêm đơn hàng mới chèn vào bảng orders, dữ liệu trong mv_category_revenue không tự động thay đổi.

Để cập nhật số liệu mới nhất, bạn phải yêu cầu PostgreSQL tính toán lại bằng lệnh REFRESH. Và đây là lúc các hệ thống production thường gặp thảm họa nếu làm sai cách.

4.1. Lệnh Refresh mặc định (Gây gián đoạn dịch vụ)

SQL

-- CẢNH BÁO: LỆNH NÀY SẼ CHẶN TOÀN BỘ CÁC LỆNH ĐỌC (SELECT)
REFRESH MATERIALIZED VIEW mv_category_revenue;

Bản chất của lệnh này là PostgreSQL sẽ:

Chiếm giữ khóa cấp cao nhất AccessExclusiveLock (Đã học ở Bài 31).
Xóa sạch (Truncate) dữ liệu cũ của MatView.
Chạy lại query để đổ dữ liệu mới vào.

Trong suốt quá trình này (có thể mất vài phút), bất kỳ câu lệnh SELECT * FROM mv_category_revenue nào từ phía người dùng (Frontend) cũng sẽ bị treo (block) chờ đến khi Refresh xong. Điều này tạo ra trải nghiệm tồi tệ hoặc làm sập Connection Pool.

4.2. Giải pháp hoàn hảo: `REFRESH ... CONCURRENTLY`

Để vừa cập nhật dữ liệu ở background, vừa cho phép người dùng tiếp tục đọc dữ liệu cũ mà không bị treo, hãy dùng từ khóa CONCURRENTLY.

Điều kiện bắt buộc: MatView của bạn phải có ít nhất 1 Unique Index (Index duy nhất). PostgreSQL cần Unique Index để so sánh sự khác biệt (diff) giữa dữ liệu cũ và mới nhằm cập nhật ngầm.

Thực hành Refresh Zero-Downtime:

SQL

-- Bước 1: Tạo Unique Index trên MatView (Làm 1 lần duy nhất)
-- Ở ví dụ này, category_name là duy nhất cho mỗi dòng
CREATE UNIQUE INDEX idx_mv_cat_name ON mv_category_revenue(category_name);

-- Bước 2: Thiết lập Cronjob hoặc Scheduler gọi lệnh Refresh Concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_revenue;

Khi chạy CONCURRENTLY, PostgreSQL chỉ chiếm khóa ExclusiveLock (Cho phép lệnh SELECT diễn ra bình thường). Nó sẽ tính toán dữ liệu mới ra một vùng nhớ nháp, so sánh với dữ liệu cũ, và từ từ INSERT/UPDATE/DELETE các dòng có sự thay đổi. Trải nghiệm người dùng mượt mà 100%.

5. Giới hạn của Materialized View và Giải pháp thay thế (Incremental View Maintenance)

Dù có CONCURRENTLY, lệnh REFRESH vẫn phải chạy lại toàn bộ câu query gốc quét hàng triệu dòng chỉ để tìm ra vài dòng thay đổi trong ngày hôm nay. Nếu bảng dữ liệu lên tới hàng Terabyte, việc Refresh có thể tốn hàng giờ.

Kỳ vọng thực tế: Chúng ta mong muốn một cơ chế gọi là Incremental View Maintenance (IVM - Bảo trì Khung nhìn Tăng dần): Nếu chỉ có 1 đơn hàng giá 500k được chèn vào, hệ thống chỉ việc lấy tổng doanh thu cũ cộng thêm 500k, thay vì phải SUM lại từ đầu.

Hiện tại, PostgreSQL core chưa hỗ trợ IVM gốc. Nếu bài toán của bạn yêu cầu dữ liệu Dashboard phải là Real-time (Thời gian thực) và siêu lớn, bạn có các hướng giải quyết sau:

Sử dụng Trigger: Viết hàm Trigger gắn vào bảng orders. Mỗi khi có INSERT/UPDATE, hàm sẽ chủ động UPDATE giá trị tổng cộng vào một "Bảng thống kê" (Summary Table) tự tạo. Cách này hiệu quả nhưng code rất phức tạp và làm chậm tốc độ ghi của bảng orders.
Dùng TimescaleDB (Continuous Aggregates): Nếu dữ liệu là dạng Time-series, extension TimescaleDB cung cấp tính năng Continuous Aggregates giải quyết bài toán tính tổng lũy kế cực kỳ xuất sắc và hoàn toàn tự động.
Dùng extension pg_ivm: Một extension cộng đồng đang phát triển rất mạnh, cung cấp lệnh CREATE INCREMENTAL MATERIALIZED VIEW cho phép tự động tính toán bù trừ toán học khi có dòng dữ liệu thay đổi.

6. Tổng kết & Bài tiếp theo

Temporary Tables sống trong 1 Session duy nhất, tuyệt vời cho các bước tính toán trung gian (ETL) mà không sợ rác dữ liệu hay trùng lặp tên bảng.
Virtual View chỉ lưu câu lệnh SQL, không cải thiện hiệu năng.
Materialized View lưu cứng dữ liệu vật lý (như cơ chế Caching của database), biến một query nặng nề thành một cú Seq Scan hoặc Index Scan trong vài mili-giây.
Luôn luôn nhớ tạo Unique Index và sử dụng REFRESH MATERIALIZED VIEW CONCURRENTLY để cập nhật dữ liệu mà không làm sập các ứng dụng đang đọc.

Bài 44 xem tiếp: Common Table Expressions (CTE) và Truy vấn đệ quy (Recursive Queries) — chúng ta sẽ rời khỏi các khái niệm vật lý để bước vào thế giới sức mạnh của SQL. Tìm hiểu cú pháp WITH, cách bẻ nhỏ câu truy vấn lồng nhau phức tạp cho dễ đọc, và đặc biệt là cơ chế WITH RECURSIVE để duyệt cây phân cấp (Category đa cấp, Cây thư mục sơ đồ tổ chức) trực tiếp bằng SQL chỉ trong 1 câu lệnh!

PostgreSQL