Menu

View và materialized view trong PostgreSQL: tạo và refresh

View là một truy vấn được lưu lại, chạy mỗi lần bạn đọc nó. Materialized view lưu kết quả của truy vấn và chỉ thay đổi khi bạn chạy REFRESH MATERIALIZED VIEW.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

View là một truy vấn được lưu lại mà bạn đọc như một bảng: CREATE VIEW name AS SELECT .... Nó không lưu dữ liệu, nên mỗi lần đọc sẽ chạy lại truy vấn và luôn thấy các hàng hiện tại. Materialized view, CREATE MATERIALIZED VIEW name AS SELECT ..., lưu kết quả xuống đĩa và chỉ thay đổi khi bạn chạy REFRESH MATERIALIZED VIEW name.

Đơn hàng được chèn sau khi tạo view vẫn có trong kết quả (ben với 90), vì truy vấn của view chạy đúng vào lúc SELECT. View giúp đặt tên cho một phép join hay bộ lọc bạn hay lặp lại, ẩn các cột khỏi những người dùng không nên thấy chúng, và giữ cho truy vấn cũ vẫn chạy khi bảng thay đổi.

Thay đổi hoặc xóa một view

CREATE OR REPLACE VIEW thay truy vấn ngay tại chỗ. Truy vấn mới phải trả về cùng các cột, cùng tên và kiểu, theo cùng thứ tự; nó chỉ được thêm cột mới vào cuối. Với mọi thay đổi khác, hãy xóa view rồi tạo lại.

CREATE OR REPLACE VIEW paid_totals AS
  SELECT customer, sum(amount) AS total, count(*) AS orders
  FROM orders WHERE paid GROUP BY customer;

DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE;  -- also drops views built on it

Chèn và cập nhật thông qua view

Một view trên một bảng sẽ tự động cập nhật được nếu nó không có GROUP BY, DISTINCT, hàm tổng hợp, window function, LIMIT hay phép toán tập hợp. Khi đó INSERT, UPDATE và DELETE trên view sẽ thay đổi bảng. WITH CHECK OPTION từ chối những hàng mà view không thể hiển thị sau đó:

Cả hai thay đổi đều tới được products. Chèn (4, 'shelf', false) qua view sẽ báo lỗi new row violates check option for view "active_products", vì view sẽ không hiển thị hàng đó. Với một view không cập nhật được, một trigger INSTEAD OF có thể định nghĩa việc ghi sẽ làm gì.

Tạo và refresh một materialized view

Materialized view chạy truy vấn của nó một lần và lưu các hàng. Đọc nhanh như đọc một bảng, nhưng dữ liệu mới không xuất hiện cho tới khi bạn refresh:

Trước khi refresh, ana vẫn hiện 120; sau đó là 180. REFRESH chạy lại toàn bộ truy vấn và thay các hàng đã lưu. Hãy dùng materialized view cho những kết quả tốn kém để tính và có thể cũ vài phút hay vài giờ: dashboard, báo cáo, bản tóm tắt tìm kiếm.

CREATE MATERIALIZED VIEW ... WITH NO DATA tạo nó ở trạng thái rỗng, và việc đọc sẽ thất bại cho tới lần refresh đầu tiên.

Refresh mà không chặn việc đọc

Một lệnh REFRESH thông thường lock materialized view, nên các truy vấn đọc nó phải chờ tới khi refresh xong. REFRESH MATERIALIZED VIEW CONCURRENTLY dựng kết quả mới riêng rồi chỉ áp dụng phần khác biệt, nên việc đọc vẫn tiếp tục. Nó cần một index unique trên materialized view, trên tên cột thông thường và không có WHERE:

Không có index unique, lệnh refresh thất bại kèm gợi ý tạo một index. CONCURRENTLY có thể nhanh hơn refresh thông thường khi ít hàng thay đổi, và không dùng được trên materialized view chưa từng được nạp dữ liệu. Dù cách nào, mỗi lúc chỉ có một lần refresh chạy trên một materialized view. Các index khác hoạt động như trên bảng, nên hãy đánh index các cột mà truy vấn của bạn dùng để lọc.

Refresh theo lịch

PostgreSQL không tự refresh materialized view. Hãy chạy refresh từ một bộ lập lịch, như cron gọi psql, hoặc extension pg_cron ở nơi đã cài nó:

# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');

View so với materialized view

ViewMaterialized view
Lưu hàngkhôngcó
Dữ liệu mới nhấtluôn luôntính tới lần refresh gần nhất
Tốc độ đọcbằng truy vấnbằng một bảng
Indexkhông (dùng index của bảng)có
Ghi đượcview đơn giản thì cókhông

Tài liệu tham khảo CREATE MATERIALIZED VIEW liệt kê các tùy chọn còn lại, như tham số lưu trữ và tablespace.

Câu hỏi thường gặp

Materialized view trong PostgreSQL có tự cập nhật không?

Không. Nó giữ kết quả từ lần REFRESH MATERIALIZED VIEW gần nhất (hoặc từ lúc tạo) cho tới khi bạn refresh lại. Hãy lập lịch refresh bằng cron, extension pg_cron, hoặc từ ứng dụng của bạn.

View có nhanh hơn việc chạy truy vấn không?

Không. Một view thông thường không lưu dữ liệu; PostgreSQL triển khai nó vào truy vấn đọc nó, nên tốc độ bằng đúng việc viết truy vấn đó ra. Chỉ materialized view mới tiết kiệm được công tính toán, với cái giá là dữ liệu cũ.

Có thêm index cho materialized view được không?

Được. Materialized view được lưu như một bảng, nên CREATE INDEX dùng được trên nó. Một index unique cũng là thứ mà REFRESH MATERIALIZED VIEW CONCURRENTLY yêu cầu.

Làm sao liệt kê các view và materialized view?

Trong psql, \dv liệt kê view và \dm liệt kê materialized view. Bằng SQL, hãy truy vấn pg_views và pg_matviews.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu