Posted inDatabase

Làm chủ PostgreSQL Materialized View: Tăng tốc truy vấn phân tích và refresh đồng thời không gây khóa bảng

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Khởi động nhanh (5 phút): Cache truy vấn nặng đầu tiên của bạn

Việc quét hàng triệu dòng dữ liệu cho các dashboard phân tích thường xuyên làm tê liệt hiệu năng cơ sở dữ liệu. Khi một cổng thông tin nội bộ phải tính toán lại tổng khối lượng đơn hàng, giá trị vòng đời khách hàng và biên lợi nhuận gộp trong mỗi lần refresh, CPU của database nhanh chóng chạm ngưỡng 100%. Người dùng cuối chỉ còn biết chờ đợi trước màn hình trắng xóa.

Các SQL view tiêu chuẩn không thể giải quyết triệt để nút thắt cổ chai này. Một view tiêu chuẩn thực chất chỉ đóng vai trò như một bí danh (alias) lưu sẵn cho câu lệnh; PostgreSQL sẽ tính toán lại toàn bộ cây truy vấn ở mỗi lần gọi. Ngược lại, một materialized view chỉ thực thi truy vấn một lần duy nhất và ghi kết quả thô xuống ổ đĩa dưới dạng các physical heap page. Bạn đọc tập dữ liệu đã được tính toán sẵn này hệt như một bảng dữ liệu thông thường.

Hãy cùng chạy thử nghiệm trên một bảng giao dịch chuẩn:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Chèn một triệu giao dịch thử nghiệm
INSERT INTO orders (customer_id, amount, status, created_at)
SELECT
    (random() * 5000)::INT,
    (random() * 500 + 10)::NUMERIC(10, 2),
    (ARRAY['completed', 'pending', 'cancelled'])[floor(random() * 3 + 1)],
    NOW() - (random() * interval '90 days')
FROM generate_series(1, 1000000);

Việc chạy các hàm tổng hợp (aggregations) trên toàn bộ 1.000.000 dòng thường mất từ 400ms đến 900ms tùy thuộc vào tốc độ ổ đĩa và độ ấm của buffer cache. Chúng ta có thể loại bỏ hoàn toàn chi phí tải đó bằng cách lưu trữ các số liệu tổng hợp tính sẵn vào một materialized view:

CREATE MATERIALIZED VIEW mv_customer_sales_summary AS
SELECT
    customer_id,
    COUNT(id) AS total_orders,
    SUM(amount) AS total_spent,
    MAX(created_at) AS last_order_date
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

Giờ đây, việc truy vấn materialized view trở nên vô cùng đơn giản:

SELECT * FROM mv_customer_sales_summary WHERE total_spent > 5000;

Độ trễ truy vấn giảm ngay lập tức từ 680ms xuống chỉ còn 1.8ms. PostgreSQL hoàn toàn tránh được việc quét bảng orders có dung lượng 65MB và đọc thẳng từ bản snapshot đã tổng hợp sẵn chỉ vỏn vẹn 200KB.

Phân tích chuyên sâu: Vì sao Materialized View vượt trội hơn Application Caching

Các quản trị viên cơ sở dữ liệu thường tranh luận về việc nên cache các chỉ số tổng hợp trong Redis hay tính toán trước trực tiếp ngay trong PostgreSQL. Dù Redis mang lại tốc độ tra cứu key-value tính bằng micro giây, nó lại chuyển dồn độ phức tạp đáng kể sang tầng ứng dụng của bạn.

Vấn đề: Các công cụ báo cáo làm cạn kiệt Connection Pool của Database

Hãy hình dung một dashboard quản trị được 40 trưởng nhóm vận hành mở cùng lúc trong buổi họp đầu ngày. Mỗi lượt truy cập kích hoạt hàng loạt phép join nhiều bảng phức tạp giữa đơn hàng, nhật ký vận chuyển và bảng hoàn tiền. Khi sử dụng SQL view thông thường, PostgreSQL phải khởi tạo các parallel query worker cho từng request. Bộ nhớ nhanh chóng bị lấp đầy bởi các bảng băm (hash table), hàng đợi truy vấn bị ứ đọng và connection pool của client chạm ngưỡng cạn kiệt chỉ sau ít phút.

Sự đánh đổi: Caching Key-Value bên ngoài vs. Lưu trữ bền vững trong Engine

Các tầng cache bên ngoài như Redis xử lý việc tra cứu điểm (point lookup) rất tốt, nhưng việc lọc dữ liệu tổng hợp lại gây ra nhiều trở ngại về mặt kiến trúc. Điều gì sẽ xảy ra nếu một chuyên viên phân tích muốn lọc các tài khoản giá trị cao theo ngày đăng ký và sắp xếp theo tổng chi tiêu trọn đời? Với Redis, bạn buộc phải tải các khối JSON khổng lồ vào bộ nhớ ứng dụng hoặc duy trì các secondary index phức tạp.

Materialized view giải quyết bài toán này một cách gọn gàng. Chúng vừa giữ trọn sự linh hoạt của truy vấn quan hệ, vừa loại bỏ hoàn toàn các tác vụ tính toán tổng hợp lặp đi lặp lại.

Đánh Index trực tiếp trên dữ liệu đã tổng hợp

Do materialized view lưu trữ các dòng cụ thể trên đĩa, bạn có thể đánh index cho nó như bất kỳ bảng PostgreSQL tiêu chuẩn nào. Các view thông thường không hỗ trợ đánh index:

CREATE INDEX idx_mv_customer_spent 
ON mv_customer_sales_summary(total_spent DESC);

Sau khi được đánh index, việc truy xuất top 100 khách hàng chi tiêu nhiều nhất chỉ cần một Index Scan thay vì phải quét tuần tự (sequential scan) toàn bộ bảng. Trên một tập dữ liệu gồm 500.000 khách hàng đã tổng hợp, thao tác này rút ngắn thời gian truy vấn từ 45ms xuống chỉ còn 0.4ms.

Cập nhật Zero-Downtime với REFRESH CONCURRENTLY

Materialized view về bản chất là các bản snapshot tĩnh. Khi có các dòng mới được ghi vào bảng orders, dữ liệu trong view sẽ bị tụt hậu. Việc refresh lại view rất đơn giản:

REFRESH MATERIALIZED VIEW mv_customer_sales_summary;

Tuy nhiên, việc chạy lệnh mặc định này trong môi trường production đang hoạt động sẽ dẫn đến những sự cố nghiêm trọng.

Hệ quả khóa bảng của cơ chế Refresh mặc định

Lệnh REFRESH MATERIALIZED VIEW cơ bản sẽ chiếm giữ một khóa bảng cấp độ ACCESS EXCLUSIVE. Khóa này chặn mọi truy vấn SELECT gửi đến view cho tới khi quá trình tính toán lại hoàn tất. Nếu truy vấn của bạn mất 45 giây để quét các bảng gốc, tất cả request của người dùng đang xếp hàng chờ phía sau sẽ bị treo hoặc gặp lỗi HTTP 504 gateway timeout.

Giải pháp Zero-Downtime: Refresh đồng thời (Concurrent Refresh)

PostgreSQL cung cấp một giải pháp thay thế không gây nghẽn: REFRESH MATERIALIZED VIEW CONCURRENTLY. Thay vì khóa view, PostgreSQL sẽ tạo tập dữ liệu mới vào một bảng tạm thời. Sau đó, nó tính toán phần dữ liệu chênh lệch (diff) và áp dụng trực tiếp các thay đổi vào view hiện có bằng khóa EXCLUSIVE. Điều này cho phép các truy vấn SELECT đang hoạt động tiếp tục diễn ra bình thường mà không bị gián đoạn.

Để sử dụng chế độ này, PostgreSQL yêu cầu phải có một UNIQUE index không chứa mệnh đề WHERE (không thể là partial index) trên các cột có giá trị non-null:

-- Bước 1: Tạo unique index không dùng mệnh đề WHERE (non-partial)
CREATE UNIQUE INDEX idx_mv_customer_id_unique 
ON mv_customer_sales_summary(customer_id);

-- Bước 2: Refresh view mà không chặn các truy vấn đọc (reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;

Lượng truy cập gửi đến vẫn tiếp tục đọc bản snapshot cũ cho đến khi quá trình refresh hoàn thành. Khi phần diff được commit, các truy vấn mới sẽ lập tức thấy dữ liệu cập nhật mà không làm rớt giao dịch hay treo các worker thread.

Tự động hóa các tác vụ Refresh định kỳ

PostgreSQL không tích hợp sẵn bộ lập lịch tự động (scheduler). Các đội ngũ kỹ thuật thường xử lý cập nhật định kỳ thông qua các background worker bên ngoài, crontab hoặc extension pg_cron. Dưới đây là cấu hình cron chuẩn production:

# Refresh dữ liệu tóm tắt khách hàng mỗi 15 phút mà không chặn các truy vấn đọc
*/15 * * * * psql -U dbuser -d production_db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;" >> /var/log/pg_refresh.log 2>&1

Nếu bạn muốn lên lịch trực tiếp ngay bên trong database engine, hãy cài đặt pg_cron và lập lịch refresh bằng SQL nguyên bản:

SELECT cron.schedule('refresh_customer_sales',
    '*/15 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary'
);

Kinh nghiệm thực tế cho môi trường Production

  • Dự trù dung lượng ổ đĩa tăng tạm thời: Trong quá trình refresh concurrent, PostgreSQL sẽ dựng toàn bộ view mới ở background trước khi áp dụng các thay đổi (diff). Hãy đảm bảo dung lượng đĩa trống của bạn đủ chứa khoảng 2.5 lần kích thước thông thường của view trong suốt quá trình thao tác.
  • Cân đối giữa tốc độ và nhu cầu tránh khóa bảng: Refresh concurrent mất nhiều thời gian hơn khoảng 1.5 đến 2 lần để hoàn tất và tạo ra nhiều write I/O hơn so với refresh tiêu chuẩn. Nếu bạn làm mới dữ liệu lịch sử trong khung giờ bảo trì thấp điểm khi không có traffic đọc, hãy chạy refresh tiêu chuẩn để hoàn thành nhanh hơn.
  • Cẩn trọng với cạm bẫy của WITH NO DATA: Bạn có thể thêm WITH NO DATA trong quá trình migration schema để tránh bị lock triển khai kéo dài. Tuy nhiên, PostgreSQL sẽ đánh dấu một view chưa được nạp dữ liệu là không thể đọc. Hơn nữa, bạn không thể chạy refresh concurrent trên một view chưa có dữ liệu; lần chạy đầu tiên bắt buộc phải là refresh tiêu chuẩn:
-- Triển khai tức thì trong migration script (không nạp sẵn dữ liệu)
CREATE MATERIALIZED VIEW mv_customer_sales_summary AS
SELECT
    customer_id,
    COUNT(id) AS total_orders,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
WITH NO DATA;

-- Bắt buộc nạp dữ liệu lần đầu (refresh tiêu chuẩn, không dùng CONCURRENTLY):
REFRESH MATERIALIZED VIEW mv_customer_sales_summary;

-- Các lần cập nhật tiếp theo có thể an toàn dùng CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_customer_id_unique ON mv_customer_sales_summary(customer_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_sales_summary;
  • Kiểm tra Execution Plan với EXPLAIN (ANALYZE, BUFFERS): Luôn đo lường hiệu năng của truy vấn trước và sau khi tối ưu. Hãy kiểm tra số lượng Buffers: shared hit để đảm bảo PostgreSQL đang phân phối dữ liệu trực tiếp từ shared buffer thay vì phải thực hiện quét tuần tự (sequential heap scan) qua hàng triệu dòng dữ liệu gốc.
Share:
Tags: