Materialized view trong PostgreSQL là một loại view đặc biệt lưu trữ dữ liệu kết quả của truy vấn view tại thời điểm tạo hoặc làm mới (refresh) view. Điều này giúp tăng tốc độ truy vấn vì dữ liệu không cần phải tính toán lại mỗi khi view được truy vấn, thay vào đó, nó đọc từ dữ liệu đã lưu trữ sẵn.
Materialized view ra đời nhằm giải quyết bài toán với idea không thể đơn giản hơn: caching. Nếu view là virtual table thì materialized view là physical table. Materialized view cần thêm không gian bộ nhớ để lưu trữ data là kết quả của query. Do đó với mỗi lần truy vấn, data sẽ được trả về ngay lập tức mà không thông qua quá trình query plan và query execution.
Tạo Materialized View
Để tạo một materialized view, bạn sử dụng lệnh CREATE MATERIALIZED VIEW. Dưới đây là một ví dụ:
CREATE MATERIALIZED VIEW sales_summary AS SELECT date_trunc('month', sale_date) AS month, SUM(amount) AS total_sales FROM sales GROUP BY month;Làm mới Materialized View
Dữ liệu trong materialized view không tự động cập nhật khi dữ liệu trong các bảng gốc thay đổi. Bạn cần làm mới (refresh) view để cập nhật dữ liệu. Bạn có thể làm điều này bằng lệnh REFRESH MATERIALIZED VIEW:
REFRESH MATERIALIZED VIEW sales_summary;
Bạn cũng có thể sử dụng tùy chọn CONCURRENTLY để làm mới view mà không khóa bảng, tuy nhiên view cần phải có một unique index để hỗ trợ việc này:
CREATE UNIQUE INDEX sales_summary_idx ON sales_summary (month); REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
Lợi ích và Hạn chế của Materialized View
Lợi ích:
- Tăng tốc độ truy vấn: Truy vấn trên materialized view thường nhanh hơn vì dữ liệu đã được tính toán sẵn, lưu trên đĩa cứng thay vì phải vào tính toán và export ra từ đầu như view thông thường.
- Giảm tải xử lý: Giảm tải xử lý trên hệ thống vì các truy vấn phức tạp được thực hiện trước và lưu trữ kết quả.
Hạn chế:
- Không tự động cập nhật: Cần phải làm mới thủ công hoặc đặt lịch làm mới định kỳ (crontab).
- Tiêu thụ không gian lưu trữ: Materialized view lưu trữ dữ liệu thực tế, do đó tiêu thụ không gian lưu trữ bổ sung.
- Khóa bảng khi làm mới: Làm mới view có thể gây ra Lock bảng, trừ khi sử dụng tùy chọn CONCURRENTLY.
Sử dụng Materialized View trong Thực tế
Dưới đây là một số kịch bản thực tế khi bạn có thể sử dụng materialized view:
- Báo cáo hàng tháng hoặc hàng quý: Khi bạn cần tổng hợp dữ liệu theo tháng hoặc quý và việc tính toán lại trên dữ liệu gốc quá tốn kém thời gian.
- Tổng hợp dữ liệu từ nhiều bảng: Khi bạn cần tổng hợp dữ liệu từ nhiều bảng và truy vấn trực tiếp trên các bảng này không hiệu quả.
- Truy vấn dữ liệu tĩnh: Khi bạn cần truy vấn dữ liệu mà không thay đổi thường xuyên, ví dụ: danh sách sản phẩm, thống kê năm trước.
Ví dụ Thực Tế
Giả sử bạn có một bảng orders và bạn muốn tạo một báo cáo hàng ngày về doanh thu:
CREATE TABLE orders ( id SERIAL PRIMARY KEY, order_date DATE, amount DECIMAL ); -- Tạo Materialized View CREATE MATERIALIZED VIEW daily_sales AS SELECT order_date, SUM(amount) AS total_sales FROM orders GROUP BY order_date;
-- Làm mới Materialized View hàng ngày bằng cách sử dụng cron job hoặc pgAgent REFRESH MATERIALIZED VIEW daily_sales;
Bạn có thể sử dụng crontab hoặc pgAgent để lên lịch tự động làm mới materialized view hàng ngày:
Sử dụng crontab:
crontab -e
Thêm lệnh sau để làm mới materialized view hàng ngày vào lúc 2 AM:
0 2 * * * psql -U postgres -d your_database -c "REFRESH MATERIALIZED VIEW daily_sales;"
Sử dụng pgAgent:
Nếu bạn sử dụng pgAgent, bạn có thể tạo một job để làm mới materialized view. Dưới đây là các bước cơ bản:
- Cài đặt pgAgent.
- Tạo một job trong pgAdmin hoặc bằng cách sử dụng SQL.
- Đặt lịch cho job để chạy hàng ngày.