Khái niệm view trong relational database chắc hẳn đã quá quen thuộc với anh em dev. View chỉ lưu trữ câu lệnh truy vấn, không lưu kết quả truy vấn và thực hiện truy vấn trên (các) bảng gốc mỗi khi view được truy cập.

Vậy nếu truy vấn của view là một truy vấn phức tạp cho thống kế, cần JOIN nhiều bảng, nhiều điều kiện FILTER, tập dữ liệu ở bảng gốc lớn, có nhiều phép tổng hợp như SUM, AVG… Thì các bạn có đoán được vấn đề gì sẽ xuất hiện không?

Nếu câu trả lời của các bạn là “hiệu suất của truy vấn” thì xin chúc mừng - đó là câu trả lời chính xác. View thông thường ẩn đi chi tiết của truy vấn, không lưu lại kết quả. Khi truy cập view thông thường, PostgreSQL sẽ thực hiện merge view query và user query rồi thực hiện trên bảng gốc (xem ảnh bên dưới).

Do đó, view thông thường chưa giải quyết được vấn đề hiệu suất truy vấn.

Các nhà phát triển database nhận ra vấn đề này, họ đã giải quyết bằng cách tạo ra materialized view.

Materialized view là gì? Materialized view giải quyết vấn đề hiệu suất ở trên như thế nào? … Tất cả những câu hỏi này sẽ được giải đáp trong bài viết ngày hôm nay.

Các bạn cùng mình tìm hiểu chi tiết về materialized view trong PostgreSQL, bao gồm:

• Cách hoạt động, lợi ích mang lại.
• Cơ chế làm mới (REFRESH).
• Những lưu ý và mẹo thực tế để tối ưu hiệu suất.

1. Materialized view trong PostgreSQL là gì?

Materialized view trong PostgreSQL là một bảng dữ chứa dữ liệu được tạo bằng cách kết hợp dữ liệu từ nhiều bảng hiện có để truy xuất dữ liệu nhanh hơn. Khác với view thông thường, dữ liệu trong materialized view không tự động cập nhật mỗi lần truy vấn mà cần được làm mới (REFRESH) thủ công hoặc định kỳ để đảm bảo dữ liệu mới nhất.

Chúng ta có thể thao tác với materialized view giống như một bảng thông thường.

Bảng so sánh với view thông thường

Nói cách khác:

• View là “virtual table” - bảng ảo, không chiếm không gian lưu trữ cho dữ liệu.

Materialized view là “physical table” - bảng vật lý, giống như cache ở tầng database.

Dựa trên các đặc điểm đã nêu, việc sử dụng materialized view mang lại nhiều lợi ích đáng kể trong các hệ thống cơ sở dữ liệu:

1. Đơn giản hóa truy vấn: Tương tự view thông thường, sau khi tạo materialized view, bạn có thể dễ dàng sử dụng lại mà không cần biết các chi tiết truy vấn phức tạp bên trong.
2. Giảm tải cho hệ thống và bảng dữ liệu gốc: Do dữ liệu của materialized view được tính toán trước từ dữ liệu bảng gốc sau đó được lưu trữ trong ổ đĩa khác vị trí của bảng gốc, khi truy cập materialized view sẽ lấy dữ liệu từ vùng nhớ này - không cần truy cập bảng gốc tính toán lại nữa.

Cải thiện hiệu suất truy vấn: Việc lưu sẵn kết quả truy vấn giúp giảm độ trễ phản hồi (latency) và tăng khả năng phục vụ đồng thời (throughput), từ đó cải thiện đáng kể hiệu suất khi có nhiều người dùng truy cập cùng lúc.

Từ những lợi ích trên, materialized view phù hợp trong các trường hợp sau:

• Truy vấn phức tạp, tốn nhiều tài nguyên nếu thực hiện thường xuyên.
• Truy vấn dùng cho báo cáo, phân tích dữ liệu cần tốc độ cao.
• Dữ liệu không thay đổi thường xuyên hoặc hiệu suất truy vấn nhanh là yếu tố quan trọng hơn.

Tuy nhiên, materialized view cũng có một vài đặc điểm cần lưu ý khi sử dụng

• Dữ liệu không được cập nhật real-time với bảng gốc.
• Tốn thêm dung lượng lưu trữ và chi phí bảo trì.

Do đó, tránh sử dụng materialized view trong các trường hợp:

• Hệ thống yêu cầu dữ liệu luôn chính xác theo thời gian thực (như giao dịch chứng khoán).
• Hệ thống nhỏ, tài nguyên hạn chế.
• Truy vấn đơn giản, dữ liệu ít.

2. Sử dụng Materialized view trong PostgreSQL

2.1 Chuẩn bị dữ liệu

Trước khi bắt đầu thực hành với materialized view, chúng ta cần tạo một số bảng mẫu để mô phỏng hệ thống đặt hàng sản phẩm. Hãy chạy đoạn SQL sau để khởi tạo dữ liệu cần thiết:

CREATE TABLE products (
    sku TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    sale_price NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    date         DATE NOT NULL,
    payment      TEXT NOT NULL,
    status       TEXT NOT NULL
);

CREATE TABLE product_orders (
    order_id INT NOT NULL,
    sku      TEXT NOT NULL,
    qty      INT NOT NULL,
    price    NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, sku),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (sku) REFERENCES products(sku)
);

Trong đó:

• products: Danh sách sản phẩm với thông tin giá bán và giá giảm giá.
• orders: Thông tin đơn hàng, khách hàng, ngày đặt, hình thức thanh toán và trạng thái đơn hàng.
• product_orders: Quan hệ nhiều-nhiều giữa đơn hàng và sản phẩm, bao gồm số lượng và giá tại thời điểm đặt hàng.

Sau khi đã tạo xong 3 bảng, chúng ta sử dụng đoạn script sau để tự động sinh dữ liệu dummy cho cả 3 bảng:

--Thêm 5000 sản phẩm vào bảng products
INSERT INTO products (sku, name, price, sale_price)
SELECT 
    'SKU' || LPAD(i::text, 5, '0'),
    'Product ' || i,
    ROUND((random() * 100 + 1)::numeric, 2),
    ROUND((random() * 90 + 1)::numeric, 2)
FROM generate_series(1, 5000) AS s(i);


--Thêm 5000 đơn hàng vào bảng orders
INSERT INTO orders (order_id, customer_id, date, payment, status)
SELECT 
    i,
    (random() * 500 + 1)::int,
    CURRENT_DATE - ((random() * 100)::int),
    (ARRAY['Credit Card', 'PayPal', 'Cash'])[floor(random() * 3) + 1],
    (ARRAY['Shipped', 'Staged', 'In Progress'])[floor(random() * 3) + 1]
FROM generate_series(1, 5000) AS s(i);


--Thêm khoảng 20000 bản ghi vào bảng product_orders
INSERT INTO product_orders (order_id, sku, qty, price)
SELECT 
    o.order_id,
    p.sku,
    (random() * 5 + 1)::INT,
    p.sale_price
FROM (
    SELECT order_id 
    FROM orders 
    ORDER BY random() 
) o
JOIN (
    SELECT sku, sale_price 
    FROM products 
    ORDER BY random() 
) p
ON TRUE
WHERE random() < 0.03 
LIMIT 20000;

2.2 Tạo Materialized view

PostgreSQL cho phép tạo materialized view thông qua cú pháp sau:

CREATE MATERIALIZED VIEW <view_name> AS
<query>
WITH [NO] DATA;

Trong đó:

• <view_name> : Tên của materialized view bạn muốn tạo.
• <query> : Câu truy vấn SQL dùng để tạo dữ liệu cho materialized view.
• WITH DATA (mặc định): PostgreSQL sẽ thực thi <query> và lưu kết quả vào materialized view ngay khi tạo.
•  WITH NO DATA: PostgreSQL chỉ tạo cấu trúc, nhưng chưa nạp dữ liệu ngay. 

*Lưu ý: Nếu tạo view với tùy chọn WITH NO DATA và truy cập materialized view ngay sau đó, PostgreSQL sẽ báo lỗi tương tự như:

Để giải quyết lỗi này, cần chạy lệnh REFRESH để nạp dữ liệu:

REFRESH MATERIALIZED VIEW <view_name>;

Chúng ta sẽ tìm hiểu kỹ hơn về lệnh REFRESH ở phần sau của bài viết.

Cuối cùng, tạo một materialized view để tổng hợp số lượng sản phẩm đã giao hàng thành công, sắp xếp giảm dần theo tổng số lượng sản phẩm:

CREATE MATERIALIZED VIEW recent_product_sales AS
SELECT
    p.sku,
    SUM(po.qty) AS total_quantity
FROM
    products p
    JOIN product_orders po ON p.sku = po.sku
    JOIN orders o ON po.order_id = o.order_id
WHERE
    o.status = 'Shipped'
GROUP BY
    p.sku
ORDER BY
    2 DESC;

Giải thích:

• View tổng hợp tổng số lượng (total_quantity) các sản phẩm (p.sku) được giao thành công (o.status = 'Shipped').
• Các bảng products, product_orders và orders được liên kết để lấy dữ liệu đầy đủ.
• Kết quả được nhóm theo mã sản phẩm và sắp xếp giảm dần theo tổng số lượng.
• Nếu không chỉ định, PostgreSQL mặc định tạo materialized view với tùy chọn WITH DATA, nghĩa là sẽ tự động nạp dữ liệu ngay sau khi tạo.

2.3 So sánh materialized view và truy vấn SELECT thuần

Ở phần này, chúng ta sẽ so sánh sự khác biệt giữa việc sử dụng materialized view và việc chạy trực tiếp truy vấn SELECT tổng hợp phức tạp.

Đầu tiên, hãy chạy câu lệnh sau để xem chi tiết kế hoạch thực thi và thời gian thực hiện truy vấn SELECT thuần:

EXPLAIN ANALYSE 
SELECT
    p.sku,
    SUM(po.qty) AS total_quantity
FROM
    products p
    JOIN product_orders po ON p.sku = po.sku
    JOIN orders o ON po.order_id = o.order_id
WHERE
    o.status = 'Shipped'
GROUP BY
    p.sku
ORDER BY
    2 DESC;

Execution plan cho thấy chi phí thực thi truy vấn tập trung chủ yếu vào việc JOIN giữa ba bảng products, orders và product_orders. Thứ tự thực hiện truy vấn như sau:

1. Lọc các record ở bảng orders có status = ‘Shipped’ - dòng 14 đến 16
2. JOIN các record lọc được ở bảng orders với các record ở bảng product_orders theo điều kiện JOIN product_orders.order_id = orders.order_id - dòng 9 đến 12
3. Tiếp tục JOIN với các record bảng products theo điều kiện JOIN product_orders.sku = products.sku - dòng 7 đến 8
4. Tổng hợp dữ liệu theo product.sku và sắp xếp kết quả giảm dần theo tổng số lượng - dòng 2 đến 5

Estimated Cost: 1083.34...1095.84 (đơn vị cost của PostgreSQL, thể hiện chi phí ước lượng cho truy vấn)

Execution Time: 7.164 ms

Tiếp theo, chạy lệnh EXPLAIN ANALYSE trên materialized view “recent_product_sales”

EXPLAIN ANALYSE 
SELECT 
* 
FROM 
recent_product_sales;

Execution plan đơn giản đi rất nhiều. Thay vì phải FILTER, JOIN, AGGREGATE như truy vấn trên, bây giờ chúng ta chỉ cần sequence scan materialized view “recent_product_sales” để lấy được dữ liệu tương đương

Estimated Cost: 0.00..60.65 giảm ~95%

Execution Time: 0.237 ms giảm ~96%

Bảng so sánh hiệu suất của ví dụ trên

Materialized view “recent_product_sales” có hiệu suất tốt hơn do chỉ thực hiện truy vấn một lần duy nhất khi tạo (hoặc khi REFRESH). Kết quả được lưu trữ vật lý trên ổ đĩa. Do đó:

• Khi thực hiện truy cập materialized view, PostgreSQL không cần tính toán lại truy vấn JOIN ba bảng ở trên nữa.
• PostgreSQL chỉ cần thực hiện sequence scan materialized view “recent_product_sales”, tương tự như đọc từ bảng đã có sẵn dữ liệu.

Hiệu suất có thể tăng hơn nữa nếu kết hợp materialized view với index. 

2.4 Refresh materialized view

Materialized view không tự động lấy dữ liệu mới nhất từ các bảng gốc sau mỗi thay đổi.

Để cập nhật dữ liệu mới nhất vào materialized view, ta sử dụng lệnh REFRESH. Điều này buộc PostgreSQL chạy lại toàn bộ truy vấn gốc của materialized view và ghi đè kết quả cũ.

Có nhiều chiến lược để chạy lệnh REFRESH tùy theo trường hợp sử dụng.

2.4.1 Manual Refresh (blocking)

Cách đơn giản nhất để cập nhật dữ liệu mới nhất cho materialized view là chạy thủ công lệnh sau:

REFRESH MATERIALIZED VIEW <tên_view>;

Lệnh này sẽ thực thi lại truy vấn được dùng để tạo materialized view, thay thế dữ liệu cũ bằng kết quả mới, và rebuild index nếu có. Đây là cách phù hợp với các khối lượng công việc không yêu cầu dữ liệu theo thời gian thực, nhưng vẫn cần được cập nhật định kỳ.

Tuy nhiên, trong quá trình làm mới, PostgreSQL sẽ sử dụng exclusive lock trên toàn bộ materialized view. Các truy vấn đến materialized view trong thời gian này sẽ bị lock cho đến khi quá trình làm mới hoàn tất. Điều này có thể ảnh hưởng đến hệ thống, đặc biệt khi dữ liệu lớn hoặc đang chạy trên môi trường production.

Chiến lược REFRESH tiếp theo sẽ giải quyết được vấn đề exclusive lock khi làm mới dữ liệu.

2.4.2 Concurrent refresh (non-blocking)

Đối với môi trường production, nơi tính khả dụng của hệ thống là ưu tiên hàng đầu, việc làm mới đồng thời (concurrent refresh) là một giải pháp rất hữu ích. PostgreSQL sẽ cập nhật dữ liệu trong materialized view mà không sử dụng exclusive lock - tức là vẫn cho phép các truy vấn đọc diễn ra đồng thời.

Câu lệnh thực hiện như sau:

REFRESH MATERIALIZED VIEW CONCURRENTLY <tên_view>;

Trong quá trình làm mới đồng thời, PostgreSQL tạo một bảng tạm thời để lưu trữ kết quả mới của truy vấn. Kết quả truy vấn cũ và mới sẽ được so sánh và những thay đổi sau đó sẽ được áp dụng cho materialized view ban đầu bằng các lệnh UPDATE hoặc INSERT. Do đó các bản ghi cần UPDATE/INSERT vẫn sẽ bị lock trong quá trình sử dụng chiến lược làm mới này.

Tuy nhiên, để sử dụng CONCURRENTLY, materialized view bắt buộc phải có một unique index. Unique index giúp phát hiện sự khác nhau của dữ liệu trong materialized view và bảng tạm thời khi thực hiện REFRESH, từ đó sẽ chỉ thực hiện UPDATE/INSERT trên các bản ghi có sự thay đổi - đồng thời giúp concurrent refresh không lock truy vấn tới materialized view trong quá trình thực hiện làm mới dữ liệu.

Cơ chế này đặc biệt phù hợp với các hệ thống có khối lượng dữ liệu lớn hoặc tần suất truy cập cao, downtime thấp và tính sẵn sàng cao là yếu tố quan trọng.

2.4.3 Automated refresh

Cả hai chiến lược REFRESH trên đều phải thực hiện thủ công. Chúng ta cần một chiến lược tự động REFRESH vừa giảm chi phí thủ công vừa đảm bảo dữ liệu được cập nhập theo nhu cầu sử dụng.

Có hai phương pháp phổ biến để tự động làm mới:

1. CRON jobs hoặc pg_cron lên lịch các khoảng thời gian làm mới dữ liệu.

• Ưu điểm: Đơn giản, nhiều tùy chọn lập lịch.
• Nhược điểm: Phải chọn mốc thời gian làm mới phù hợp, tốn thêm chi phí quản lý.

2. Triggers làm mới materialized view mỗi khi dữ liệu thay đổi.

• Ưu điểm: Đảm bảo view được đồng bộ với bảng gốc.
• Nhược điểm: Phức tạp, có thể gây vấn đề hiệu suất nếu bảng gốc lớn hoặc được cập nhật thường xuyên.

3. Performance Optimization Tips

Để sử dụng materialized view hiệu quả, hãy chú ý những tips sau

3.1 Có kế hoạch làm mới dữ liệu

Việc lên kế hoạch chiến lược để làm mới materialized view là yếu tố then chốt nhằm duy trì hiệu suất hệ thống ở mức tối ưu.

• Chọn thời điểm thích hợp: Nên lập lịch làm mới vào những khung giờ thấp điểm, tránh ảnh hưởng đến hiệu suất ứng dụng. Điều này đặc biệt quan trọng với các materialized view lớn hoặc tốn thời gian xử lý.
• Cân nhắc tần suất cập nhật: Tần suất cập nhật dữ liệu ở các bảng gốc ảnh hưởng trực tiếp đến mức độ cần làm mới materialized view. Với các bảng thay đổi thường xuyên (đơn hàng, giao dịch…), có thể cần làm mới mỗi giờ. Ngược lại, với các bảng ít thay đổi, chỉ cần làm mới hàng ngày, hàng tuần hoặc hàng tháng.

3.2 Sử dụng index cho materialized view

Một lợi thế lớn của materialized view so với view thông thường là có thể tạo index trên materialized view như trên một bảng bình thường. Điều này đặc biệt hữu ích khi materialized view chứa lượng dữ liệu lớn hoặc được sử dụng thường xuyên trong các truy vấn có điều kiện lọc hoặc sắp xếp.

Bảng so sánh sự khác nhau giữa index trên bảng thông thường và materialized view

Ở phần Concurrent refresh cũng đã đề cập: bắt buộc phải có unique index trên materialized view để sử dụng được chiến lược REFRESH này. 

Cấu trúc sau sẽ giúp bạn thêm unique index cho materialized view:

CREATE UNIQUE INDEX <tên_index> ON <tên_view>(<tên_cột>);

Đây là câu lệnh để thêm unique index trên cột sku cho materialized view “recent_product_sales” chúng ta đã thực hành ở phần trước:

CREATE UNIQUE INDEX unique_sku_recent_product_sales ON recent_product_sales(sku);

Khi thực hiện REFRESH materialized view, các index trên materialized view sẽ được cập nhật lại, điều này có thể làm tăng thời gian thực thi và ảnh hưởng đến hiệu suất, đặc biệt với các view lớn hoặc nhiều index. Vì vậy, chỉ nên tạo index khi thực sự cần thiết, và cần lựa chọn thời điểm REFRESH phù hợp để tránh ảnh hưởng đến hệ thống.

3.3 Tận dụng Parallel Queries

PostgreSQL hỗ trợ xử lý song song (parallel execution) cho các truy vấn, bao gồm cả việc làm mới materialized view. Khi được cấu hình phù hợp, PostgreSQL có thể chia nhỏ công việc làm mới thành các phần nhỏ và xử lý chúng đồng thời trên nhiều lõi CPU, giúp giảm đáng kể thời gian làm mới, đặc biệt với các materialized view lớn.

Để bật thực thi truy vấn song song, bạn có thể sử dụng lệnh sau:

SET max_parallel_workers_per_gather = 4;

Bạn cần thận trọng khi thiết lập giá trị cho tham số max_parallel_workers_per_gather. Nếu đặt giá trị quá cao, có thể dẫn đến quá tải CPU. Ngược lại, nếu đặt quá thấp thì khả năng tăng hiệu suất sẽ bị hạn chế. Do đó, nên tăng dần một cách hợp lý và theo dõi hiệu suất thực tế để điều chỉnh cho phù hợp. Ngoài ra, giá trị của max_parallel_workers_per_gather cũng bị giới hạn bởi tham số max_parallel_workers.

3.4 Monitoring Materialized View

Để theo dõi tình trạng của các materialized view trong cơ sở dữ liệu, bạn có thể sử dụng truy vấn sau:

SELECT 
    schemaname,
    matviewname,
    matviewowner, 
	hasindexes,
    ispopulated,
    definition
FROM 
    pg_matviews;

Thông qua truy vấn này, bạn có thể xác định được materialized view nào chưa có dữ liệu (cần được REFRESH), kiểm tra đã có index hỗ trợ truy vấn chưa … Từ đó đánh giá truy vấn gốc để tối ưu nếu cần.

4. Tổng kết

Materialized view là một công cụ mạnh mẽ trong PostgreSQL, đặc biệt hữu ích cho các truy vấn phức tạp hoặc báo cáo dữ liệu yêu cầu hiệu năng cao. Khác với view thông thường, materialized view lưu trữ kết quả truy vấn trên đĩa, chúng giúp giảm thời gian xử lý truy vấn lặp đi lặp lại và tối ưu hiệu suất hệ thống.

Tuy nhiên, việc sử dụng materialized view cũng đòi hỏi người dùng phải hiểu rõ:

• Các trường hợp nên và không nên sử dụng materialized view.
• Cách làm mới dữ liệu (thủ công và tự động).
• Tối ưu hóa hiệu suất thông qua index hoặc truy vấn song song.
• Giám sát tình trạng để đảm bảo dữ liệu luôn cập nhật và có hiệu suất tốt.

Khi được sử dụng đúng cách, materialized view không chỉ nâng cao hiệu quả truy vấn mà còn góp phần quan trọng trong việc xây dựng hệ thống phân tích dữ liệu và báo cáo linh hoạt, mạnh mẽ.

Hy vọng qua bài viết ngày hôm nay, mọi sẽ hiểu hơn về materialized view từ đó áp dụng hiệu quả vào các dự án của bản thân.

Reference:

https://www.crunchydata.com/blog/indexing-materialized-views-in-postgres 

https://dev.to/mrpercival/how-to-use-materialized-views-49dk 

https://www.tigerdata.com/blog/materialized-views-the-timescale-way