PostgreSQL Materialized View: Dashboard nhanh và refresh an toàn

PostgreSQL materialized view lưu kết quả truy vấn thành dữ liệu vật lý để dashboard và báo cáo đọc nhanh hơn, nhưng nó không tự cập nhật khi bảng nguồn thay đổi. Vận hành production cần một freshness contract rõ ràng, unique index phù hợp cho concurrent refresh, cơ chế chống refresh chồng lấn, quyền truy cập tách biệt và telemetry chứng minh snapshot còn dùng được.

Bài viết bám theo tài liệu PostgreSQL hiện hành về materialized views, REFRESH MATERIALIZED VIEW và system view pg_matviews. Quyền thực thi và chi tiết lock phụ thuộc major version; luôn đối chiếu đúng phiên bản production.

1. View, materialized view và summary table khác nhau thế nào?

Materialized view phù hợp khi truy vấn nền đắt, tập kết quả vừa phải, stale data được chấp nhận và full recomputation nằm trong ngân sách. Nếu người dùng cần read-your-writes hoặc freshness vài giây trên dataset lớn, cần mô hình khác.

2. Định nghĩa freshness contract trước SQL

“Refresh mỗi 5 phút” chưa phải SLO. Job kéo dài 12 phút sẽ tạo backlog hoặc snapshot già hơn dự kiến. Contract nên nêu:

• Freshness: dữ liệu nguồn mới nhất được phép chậm bao lâu.
• Availability: reader có được tiếp tục đọc snapshot cũ khi refresh lỗi không.
• Completeness: nguồn late-arriving/correction được phản ánh từ lần nào.
• Latency: query dashboard phải đáp ứng trong ngưỡng nào.
• Failure policy: hiển thị badge stale, ngừng báo cáo hay fallback query trực tiếp.

Giao diện nên hiển thị “dữ liệu cập nhật lúc…” dựa trên metadata job thành công, không dựa vào thời gian trang được tải.

3. Ví dụ daily order summary

CREATE SCHEMA IF NOT EXISTS reporting;

CREATE MATERIALIZED VIEW reporting.daily_order_summary AS
SELECT
    tenant_id,
    (paid_at AT TIME ZONE 'UTC')::date AS report_date,
    currency,
    count(*) AS order_count,
    sum(total_amount) AS gross_amount,
    max(updated_at) AS source_high_watermark
FROM public.orders
WHERE status = 'paid'
GROUP BY
    tenant_id,
    (paid_at AT TIME ZONE 'UTC')::date,
    currency
WITH NO DATA;

WITH NO DATA tạo definition nhưng để view chưa populated và chưa query được. Cách này cho phép tạo index trước lần nạp đầu. Timezone được ghi rõ; nếu báo cáo theo ngày địa phương của từng tenant, schema phải lưu timezone và định nghĩa semantics cho DST thay vì ép mọi tenant vào UTC.

4. Unique index là hợp đồng của concurrent refresh

CREATE UNIQUE INDEX daily_order_summary_identity_idx
ON reporting.daily_order_summary
   (tenant_id, report_date, currency);

CREATE INDEX daily_order_summary_date_idx
ON reporting.daily_order_summary (report_date DESC);

Để dùng REFRESH ... CONCURRENTLY, PostgreSQL yêu cầu ít nhất một UNIQUE index:

• chỉ dùng tên cột, không phải expression;
• phủ toàn bộ row, không phải partial index có WHERE;
• thực sự xác định duy nhất một row kết quả.

Index thứ hai phục vụ query, không thay unique index phục vụ refresh. Chọn grain của materialized view trước khi chọn key; nếu GROUP BY không tạo row duy nhất theo key, concurrent refresh không có identity đúng.

5. Initial load rồi mới refresh concurrently

-- View đang WITH NO DATA: lần đầu không dùng CONCURRENTLY
REFRESH MATERIALIZED VIEW reporting.daily_order_summary;

-- Các lần sau, khi view đã populated và unique index hợp lệ
REFRESH MATERIALIZED VIEW CONCURRENTLY
  reporting.daily_order_summary;

CONCURRENTLY cho phép SELECT tiếp tục trong refresh, nhưng không có nghĩa không tốn tài nguyên hoặc nhanh hơn. Refresh không concurrent thường hoàn thành nhanh hơn và dùng ít overhead hơn, đổi lại có thể block reader. Chọn theo availability SLO, kích thước và workload đo được.

Ngay cả concurrent mode, chỉ một refresh được chạy tại một thời điểm trên cùng materialized view. Scheduler phải single-flight thay vì cứ xếp thêm job khi lần trước chưa xong.

6. Refresh thay toàn bộ kết quả, không phải incremental mặc định

PostgreSQL core chạy lại stored query và thay nội dung view; nó không tự đọc “chỉ row mới từ lần trước”. Với join/group lớn, refresh có thể:

• scan lượng lớn bảng/index nguồn;
• dùng CPU, memory, temporary files và I/O;
• tạo WAL và làm replica lag;
• tranh tài nguyên với OLTP và autovacuum;
• kéo dài qua nhiều chu kỳ schedule.

Nếu full refresh vượt ngân sách, cân nhắc summary table incremental, ETL/CDC, partitioned aggregate hoặc extension đã được đánh giá. Đừng gọi một trigger tự viết là “incremental materialized view” nếu chưa chứng minh idempotency, correction, late data và recovery.

7. Thiết kế query nền cho refresh

Tối ưu query definition như một batch workload:

• index base table theo join/filter thực tế;
• tránh function/cast làm mất index hoặc partition pruning ngoài ý muốn;
• đo EXPLAIN (ANALYZE, BUFFERS) trên dữ liệu representative;
• theo dõi temp spill, parallel workers và cache impact;
• loại cột không cần khỏi snapshot để giảm storage/index/WAL;
• định nghĩa rõ cách xử lý deleted, canceled và corrected rows.

Đừng nhúng ORDER BY và kỳ vọng row order được giữ sau refresh; query đọc vẫn phải có ORDER BY riêng.

8. Lập lịch không tạo refresh storm

Một scheduler production cần:

1. kiểm tra không có refresh cùng view đang chạy;
2. ghi started_at và attempt ID;
3. đặt timeout/statement timeout phù hợp;
4. refresh bằng role chuyên dụng;
5. chỉ ghi success/freshness watermark sau khi command thành công;
6. retry có backoff và không xếp vô hạn;
7. alert khi tuổi snapshot vượt SLO.

External scheduler có distributed lock hoặc PostgreSQL advisory lock có thể làm single-flight. Nếu dùng session-level advisory lock qua connection pool, phải bảo đảm unlock trên đúng session; transaction-level lock hoặc scheduler ownership thường dễ reasoning hơn.

9. Metadata freshness không nên đoán từ file hoặc reltime

pg_matviews cho biết owner, tablespace, có index hay không, trạng thái populated và definition, nhưng không lưu “lần refresh thành công cuối”. Tạo control table riêng:

CREATE TABLE reporting.refresh_state (
    object_name text PRIMARY KEY,
    last_started_at timestamptz,
    last_succeeded_at timestamptz,
    source_high_watermark timestamptz,
    duration_ms bigint,
    result_rows bigint,
    status text NOT NULL,
    error_code text
);

Job cập nhật row này theo kết quả thực tế. Không lưu message lỗi có secret/SQL parameter nhạy cảm. Nếu job crash giữa chừng, watchdog phải chuyển trạng thái stale/failed thay vì để “running” mãi.

10. Quyền và security boundary

Materialized view lưu dữ liệu vật lý. Query của consumer đọc snapshot, không chạy lại policy trên bảng nguồn cho từng user. Vì vậy không giả định Row-Level Security của base table tự động áp cho materialized view.

• Owner/refresh role riêng, không phải superuser.
• Grant SELECT qua role đọc tối thiểu.
• Tách materialized view theo security boundary hoặc đưa tenant key vào mọi query và policy phù hợp.
• Không snapshot secret/PII “vì query nguồn đã có quyền”.
• Đánh giá quyền MAINTAIN hoặc yêu cầu owner theo PostgreSQL version đang chạy.
• Vá PostgreSQL theo security release và không refresh object do role không tin cậy sở hữu.

11. Dependency và schema change

Materialized view phụ thuộc table, column, function, type và schema resolution trong definition. Rename/drop/type change có thể bị chặn hoặc làm deployment phức tạp. Function được gọi trong definition phải deterministic đủ cho mục tiêu báo cáo; thay logic function chỉ phản ánh sau refresh.

Inventory dependency trước migration và deploy theo expand/contract. Không drop/recreate trực tiếp object đang được dashboard truy cập nếu không chấp nhận downtime.

12. Đổi definition bằng shadow materialized view

1. Tạo daily_order_summary_v2 bằng definition mới WITH NO DATA.
2. Tạo unique index và query indexes.
3. Initial refresh và analyze.
4. Đối soát count, totals, sample tenants và query plan với v1.
5. Chuyển consumer qua stable normal view/API layer trong transaction ngắn.
6. Giữ v1 trong rollback window rồi mới drop.

Stable view như reporting.daily_order_summary_current giúp ứng dụng không phụ thuộc tên versioned object. Kiểm tra column types/names và grants khi đổi target.

13. High availability và replica

Refresh là write workload trên primary và được replicate qua WAL theo kiến trúc HA thông thường. Concurrent refresh có thể tạo WAL đáng kể. Theo dõi replica lag, slot retention và disk usage; đừng chạy nhiều materialized views lớn cùng giờ.

Nếu dashboard đọc replica, freshness thực tế bằng freshness của refresh cộng replica lag. Failover giữa refresh cần được test: scheduler không được chạy trên cả old và new primary, và trạng thái job phải nhận diện attempt bị gián đoạn.

14. Khi nào nên tách một view lớn thành nhiều view?

Một materialized view “mọi dashboard” tạo blast radius lớn và refresh lâu. Tách theo:

• freshness SLO khác nhau;
• security boundary khác nhau;
• nguồn dữ liệu/chi phí refresh khác nhau;
• grain và retention khác nhau.

Nhưng quá nhiều view cũng tạo scheduler/index/storage overhead. Đừng tách chỉ theo từng widget nếu chúng dùng cùng grain và refresh contract.

15. Monitoring production

Metric success không đủ nếu kết quả sai. Chạy data-quality assertion: total không âm, currency hợp lệ, tenant coverage, watermark tiến lên và đối soát mẫu với query nguồn.

Runbook khi refresh lỗi

1. Giữ snapshot cũ phục vụ reader nếu còn trong degraded tolerance.
2. Xác định lỗi lock, timeout, disk/temp space, permission, query plan hay source dependency.
3. Dừng scheduler tạo thêm attempt.
4. Kiểm tra primary load, WAL và replica lag trước retry.
5. Retry một lần có kiểm soát hoặc chuyển maintenance window.
6. Nếu definition/data sai, dùng shadow version; không vá trực tiếp row trong materialized view.
7. Cập nhật freshness badge/status page và incident timeline.

Checklist production

• Có freshness, availability và failure contract được consumer chấp nhận.
• Query definition được benchmark trên dữ liệu representative.
• Unique index cho concurrent refresh chỉ dùng cột và phủ mọi row.
• Initial refresh không concurrent đã hoàn tất trước lịch concurrent.
• Scheduler single-flight, có timeout, backoff và stale alert.
• Refresh role và read grants theo least privilege.
• RLS/security boundary được áp ở snapshot, không giả định kế thừa.
• Metadata lưu last success, watermark, duration, rows và status.
• WAL, temp, I/O, locks và replica lag nằm trong SLO.
• Đổi definition qua shadow object có đối soát và rollback.

Kết luận

Materialized view là công cụ mạnh để đổi freshness lấy tốc độ đọc, không phải cache tự động miễn phí. Production design tốt bắt đầu từ grain và freshness contract, tạo identity đúng cho concurrent refresh, kiểm soát single-flight, bảo vệ snapshot như dữ liệu thật và đo cả chất lượng lẫn tuổi dữ liệu. Khi full refresh còn nằm trong ngân sách, đây thường là bước đơn giản hơn nhiều so với dựng một pipeline CQRS hoặc cache phân tán.