View و Materialized View با REFRESH CONCURRENTLY

کوئری نام‌دار و کوئری ذخیره‌شده

View

view یک کوئری ذخیره‌شده است که مثل جدول صدا زده می‌شود. داده‌ای نگه نمی‌دارد و هر بار اجرا می‌شود؛ برنامه‌ریز کوئری view را در کوئری شما ادغام می‌کند، پس معمولاً هزینه‌ی اضافه ندارد.

CREATE VIEW report.order_summary AS
SELECT o.id, o.status, o.created_at, c.full_name, c.city,
       sum(oi.line_total_rial) AS total_rial,
       sum(oi.qty) AS pieces
FROM factory.orders o
JOIN factory.customers c ON c.id = o.customer_id
JOIN factory.order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.id;

GRANT SELECT ON report.order_summary TO bi_reader;

کاربر bi_reader حتی بدون دسترسی به جدول‌های factory می‌تواند view را بخواند، چون view به‌طور پیش‌فرض با مجوز صاحبش به جدول‌ها دسترسی می‌گیرد. اگر می‌خواهید مجوز و RLS کاربر خواننده بررسی شود، از نسخه‌ی 15 WITH (security_invoker = true) بگذارید.

view قابل به‌روزرسانی

viewی که فقط از یک جدول، بدون GROUP BY و DISTINCT و aggregate ساخته شده باشد، خودبه‌خود INSERT و UPDATE و DELETE می‌پذیرد. WITH CHECK OPTION جلوی نوشتن ردیفی را می‌گیرد که از دید view خارج شود:

CREATE VIEW factory.kashan_customers AS
SELECT * FROM factory.customers WHERE city = 'کاشان'
WITH CHECK OPTION;
-- INSERT با city = 'تهران' از طریق این view خطا می‌دهد

Materialized View

materialized view نتیجه را واقعاً روی دیسک ذخیره می‌کند؛ مناسب داشبوردی که کوئری سنگینش لازم نیست هر لحظه تازه باشد.

CREATE MATERIALIZED VIEW report.daily_sales AS
SELECT (o.created_at AT TIME ZONE 'Asia/Tehran')::date AS day,
       oi.design_code,
       sum(oi.qty) AS pieces,
       sum(oi.line_total_rial) AS revenue
FROM factory.orders o JOIN factory.order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1, 2
WITH DATA;

CREATE UNIQUE INDEX ON report.daily_sales (day, design_code);

REFRESH MATERIALIZED VIEW CONCURRENTLY report.daily_sales;

REFRESH معمولی قفل انحصاری می‌گیرد و در تمام مدت، خواندن از view مسدود است. نسخه‌ی CONCURRENTLY نتیجه‌ی جدید را جدا می‌سازد و فقط تفاوت‌ها را اعمال می‌کند؛ خواننده‌ها معطل نمی‌شوند. شرطش یک ایندکس UNIQUE روی ستون‌های ساده (بدون WHERE) است.

نکته‌هایی که کمتر کسی می‌داند

• SELECT * در تعریف view هنگام ساخت به فهرست ثابت ستون‌ها تبدیل می‌شود؛ ستونی که بعداً به جدول اضافه کنید در view ظاهر نمی‌شود.
• تا وقتی viewی به یک ستون وابسته است، ALTER COLUMN ... TYPE روی آن ستون خطا می‌دهد؛ باید view را DROP، ستون را تغییر و view را دوباره بسازید (همه داخل یک تراکنش).
• CREATE OR REPLACE VIEW فقط اجازه‌ی افزودن ستون در انتها را می‌دهد؛ تغییر نام یا نوع یا ترتیب ستون‌ها نیازمند DROP است.
• materialized view خودکار تازه نمی‌شود. زمان‌بندی را با cron سیستم یا اکستنشن pg_cron انجام دهید: SELECT cron.schedule('*/10 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY report.daily_sales').
• REFRESH CONCURRENTLY وقتی بخش بزرگی از داده عوض شده باشد از REFRESH معمولی کندتر است و bloat تولید می‌کند؛ برای نتیجه‌های کوچکی که کل‌شان عوض می‌شود، REFRESH ساده در ساعت کم‌بار بهتر است.