View در دیتابیس: وقتی سه تیم یک عدد را سه‌جور حساب می‌کنند

فرض کنید در یک فروشگاه آنلاین متوسط کار می‌کنید. یک دیتابیس PostgreSQL دارید با جدول‌های آشنا: customers، orders، order_items، payments و refunds. اول کار فقط یک بک‌اند به این دیتابیس وصل است و همه‌چیز مرتب است.

بعد شرکت بزرگ می‌شود. پنل ادمین می‌خواهد «مجموع خرید هر مشتری» را نشان بدهد. تیم مالی برای گزارش ماهانه همین عدد را لازم دارد. تیم پشتیبانی هم می‌خواهد وقتی مشتری زنگ می‌زند، سابقه‌ی خریدش را ببیند. هر کدام از این‌ها کوئری خودش را می‌نویسد و تقریباً همه‌شان یک شکل دارند: customers را به orders و payments join می‌کنند و جمع می‌زنند.

چند ماه بعد، در جلسه‌ی آخر فصل، مدیر محصول از روی داشبورد می‌گوید درآمد فصل ۴۲ میلیارد تومان بوده. مدیر مالی از روی گزارش خودش می‌گوید ۳۸ میلیارد. هر دو هم درست می‌گویند، چون کوئری مالی سفارش‌های مرجوع‌شده را کم می‌کند و کوئری داشبورد نه. یک نفر هم در کوئری پشتیبانی سفارش‌های لغوشده را حساب کرده است.

این اولین مشکل است، ولی تنها مشکل نیست.

سه مشکل پشت یک کوئری تکراری

یکی: تعریف کسب‌وکار در چند جا پخش شده. «درآمد» یک مفهوم کسب‌وکاری است، نه یک ستون. اینکه سفارش لغوشده حساب شود یا نه، مرجوعی کم شود یا نه، هزینه‌ی ارسال جزو درآمد هست یا نیست، تصمیم‌هایی است که باید یک‌بار گرفته شود. وقتی همین منطق در سه سرویس با سه زبان مختلف کپی شده، هر تغییر در تعریف یعنی پیدا کردن و درست کردن همه‌ی کپی‌ها. همیشه هم یکی جا می‌ماند.

دو: دسترسی بیش از حد. تیم پشتیبانی برای دیدن سابقه‌ی خرید، به جدول customers دسترسی SELECT گرفته است. همان جدولی که کد ملی، آدرس کامل و شماره‌ی تلفن مشتری را دارد. کسی که فقط باید «سه سفارش آخر و وضعیتشان» را ببیند، الان می‌تواند با یک کوئری کل فهرست مشتری‌ها را خروجی بگیرد.

سه: اسکیما قفل شده. تیم بک‌اند می‌خواهد ستون full_name را به first_name و last_name بشکند. ولی نمی‌داند چند گزارش، اسکریپت و داشبورد مستقیم به این ستون وابسته‌اند. عملاً اسکیمای دیتابیس به API عمومی چند تیم تبدیل شده و هیچ‌کس جرئت نمی‌کند دستش بزند.

راه‌حل‌های اولی که به ذهن می‌رسد معمولاً از جنس اپلیکیشن است: یک کتابخانه‌ی مشترک، یا یک سرویس «گزارش» که بقیه از طریق API از آن داده بگیرند. این‌ها جواب می‌دهند، ولی برای مشکلی که اساساً در لایه‌ی داده است، هزینه‌شان زیاد است. ابزار ارزان‌تر و قدیمی‌تر خود دیتابیس است: view.

View چیست؟

view یک کوئری SELECT است که اسم دارد و در دیتابیس ذخیره شده. از بیرون شبیه جدول است، می‌شود از آن SELECT گرفت، با جدول‌های دیگر join کرد و روی آن دسترسی داد. ولی خودش داده‌ای نگه نمی‌دارد. هر بار که از آن می‌خوانید، کوئری پشتش اجرا می‌شود.

تعریف «درآمد خالص هر مشتری» را یک‌بار و در یک جا می‌نویسیم:

CREATE VIEW reporting.customer_revenue AS
SELECT
    c.id                                   AS customer_id,
    count(DISTINCT o.id)                   AS paid_orders,
    coalesce(sum(p.amount), 0)             AS gross_amount,
    coalesce(sum(r.amount), 0)             AS refunded_amount,
    coalesce(sum(p.amount), 0)
      - coalesce(sum(r.amount), 0)         AS net_revenue
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.id AND o.status NOT IN ('cancelled', 'failed')
LEFT JOIN payments p ON p.order_id = o.id AND p.status = 'captured'
LEFT JOIN refunds  r ON r.order_id = o.id
GROUP BY c.id;

حالا داشبورد، گزارش مالی و پنل پشتیبانی همه این را می‌خوانند:

SELECT customer_id, net_revenue
FROM reporting.customer_revenue
WHERE customer_id = 1024;

اگر فردا تیم مالی بگوید هزینه‌ی ارسال نباید جزو درآمد باشد، فقط همین view عوض می‌شود و عدد هر سه تیم با هم تغییر می‌کند. این همان چیزی است که در جلسه‌ی آخر فصل کم داشتیم.

این کوئری برای خوانایی ساده شده. در دیتای واقعی، join کردن هم‌زمان payments و refunds به یک سفارش می‌تواند ردیف‌ها را ضرب کند و جمع‌ها را بزرگ‌تر از واقعیت نشان بدهد. راه درستش این است که هر کدام را اول در یک subquery یا CTE جدا جمع بزنید و بعد join کنید. همین نکته دلیل خوبی است که این منطق فقط یک‌جا نوشته شود و یک‌بار درست شود.

view داده را کپی نمی‌کند

یک سوءتفاهم رایج این است که view نسخه‌ای از داده را جایی نگه می‌دارد. در PostgreSQL وقتی کوئری‌ای به یک view می‌رسد، planner تعریف view را جای اسمش می‌گذارد¹ و کل کوئری را با هم بهینه می‌کند. یعنی شرط WHERE customer_id = 1024 در مثال بالا معمولاً به داخل view هل داده می‌شود (predicate pushdown) و دیتابیس فقط سفارش‌های همان مشتری را می‌خواند، نه همه را.

نتیجه‌ی عملی‌اش دو چیز است:

• view معمولی نه سریع‌ترتان می‌کند و نه کندتر. سرعتش همان سرعت کوئری زیرین است، پس ایندکس‌ها باید روی جدول‌های اصلی باشند.
• داده همیشه تازه است. هر تغییری در orders بلافاصله در view دیده می‌شود.

البته pushdown همیشه ممکن نیست. اگر view تابع پنجره‌ای (window function)، DISTINCT ON یا LIMIT داشته باشد، planner گاهی نمی‌تواند شرط بیرونی را به داخل ببرد و مجبور است کل view را حساب کند و بعد فیلتر کند. برای همین قبل از اینکه یک view پرکاربرد را به تیم‌ها بدهید، EXPLAIN ANALYZE کوئری‌های معمول روی آن را ببینید.

view برای امنیت: فقط همان چیزی که لازم است

برگردیم به مشکل دوم. تیم پشتیبانی نباید به جدول customers دسترسی داشته باشد. به یک view دسترسی می‌گیرد که فقط ستون‌های لازم را دارد و شماره‌ی تلفن را هم نصفه نشان می‌دهد:

CREATE VIEW support.customer_summary AS
SELECT
    c.id,
    c.full_name,
    left(c.phone, 4) || '****' || right(c.phone, 3) AS phone_masked,
    cr.paid_orders,
    cr.net_revenue
FROM customers c
JOIN reporting.customer_revenue cr ON cr.customer_id = c.id;

REVOKE ALL ON customers FROM support_role;
GRANT SELECT ON support.customer_summary TO support_role;

این کار می‌کند چون در PostgreSQL view به‌طور پیش‌فرض با دسترسی صاحب view اجرا می‌شود، نه کسی که از آن می‌خواند. support_role به جدول customers دسترسی ندارد، ولی صاحب view دارد و view از طرف او جدول را می‌خواند.

این رفتار پیش‌فرض دو لبه دارد و باید آگاهانه انتخابش کنید:

• اگر روی جدول‌ها Row-Level Security² دارید (مثلاً هر tenant فقط ردیف‌های خودش را ببیند)، view با دسترسی صاحبش RLS را دور می‌زند. از نسخه‌ی ۱۵ به بعد می‌شود با WITH (security_invoker = true) کاری کرد که view با دسترسی خواننده اجرا شود و RLS رعایت شود.
• اگر view را برای پنهان کردن ردیف‌ها استفاده می‌کنید (مثلاً فقط سفارش‌های یک منطقه)، WITH (security_barrier = true) را بگذارید. بدون آن، کاربر می‌تواند یک تابع ارزان در WHERE بنویسد که planner قبل از فیلتر view اجرایش کند و از طریق پیغام خطا یا RAISE NOTICE مقدار ردیف‌های پنهان را بیرون بکشد.

view به‌عنوان قرارداد: اسکیما را آزاد کنید

مشکل سوم شاید مهم‌ترین کاربرد view در سیستم‌های بزرگ باشد. وقتی تیم‌ها به‌جای جدول‌ها به view وصل شوند، view نقش یک API را بازی می‌کند و جدول‌ها جزئیات پیاده‌سازی می‌شوند.

ستون full_name را می‌خواهیم بشکنیم. با view این کار بدون شکستن کسی انجام می‌شود:

-- ۱. ستون‌های جدید را اضافه و پر می‌کنیم
ALTER TABLE customers ADD COLUMN first_name text, ADD COLUMN last_name text;
UPDATE customers SET first_name = split_part(full_name, ' ', 1),
                     last_name  = substr(full_name, length(split_part(full_name, ' ', 1)) + 2);

-- ۲. view را طوری بازنویسی می‌کنیم که همان خروجی قبلی را بدهد
CREATE OR REPLACE VIEW support.customer_summary AS
SELECT
    c.id,
    c.first_name || ' ' || c.last_name AS full_name,
    ...

مصرف‌کننده‌ها هنوز full_name می‌بینند. بعداً، با خیال راحت، ستون قدیمی از جدول حذف می‌شود. اگر روزی قرارداد خود view باید عوض شود، یک نسخه‌ی جدید کنارش می‌سازید (مثلاً support.customer_summary_v2)، تیم‌ها را یکی‌یکی منتقل می‌کنید و نسخه‌ی قدیمی را آخر کار حذف می‌کنید. همان الگوی expand/contract³ که برای API‌های HTTP هم استفاده می‌شود.

وقتی view کند است: materialized view

view معمولی داده را همیشه تازه نشان می‌دهد، ولی هزینه‌اش این است که کوئری سنگین هر بار از اول اجرا شود. حالا فرض کنید داشبورد مدیریت می‌خواهد درآمد روزانه‌ی کل سال را روی نمودار نشان بدهد و هر بار که کسی صفحه را باز می‌کند، کوئری روی ده‌ها میلیون ردیف payments اجرا شود. این‌جا view معمولی کمکی نمی‌کند.

materialized view نتیجه‌ی کوئری را واقعاً روی دیسک ذخیره می‌کند. خواندن از آن مثل خواندن از یک جدول کوچک است، ولی داده‌اش تا وقتی خودتان refresh نکنید تازه نمی‌شود:

CREATE MATERIALIZED VIEW reporting.daily_revenue AS
SELECT
    date_trunc('day', p.captured_at)::date AS day,
    sum(p.amount)                          AS gross_amount,
    count(*)                               AS payments_count
FROM payments p
WHERE p.status = 'captured'
GROUP BY 1;

CREATE UNIQUE INDEX ON reporting.daily_revenue (day);

و برای تازه کردنش، مثلاً هر ده دقیقه با یک cron job یا pg_cron:

REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.daily_revenue;

کلمه‌ی CONCURRENTLY مهم است. بدون آن، REFRESH روی materialized view قفل انحصاری می‌گیرد و تا تمام شدنش هیچ‌کس نمی‌تواند از آن بخواند؛ یعنی داشبورد هر ده دقیقه چند ثانیه هنگ می‌کند. با CONCURRENTLY نتیجه‌ی جدید کنار نسخه‌ی قبلی ساخته می‌شود و فقط تفاوت‌ها اعمال می‌شود، ولی برای این کار یک ایندکس یکتا روی materialized view لازم است. همان خط CREATE UNIQUE INDEX بالا برای همین است.

چند چیز درباره‌ی materialized view را باید از اول بدانید:

• refresh کامل است، نه افزایشی. PostgreSQL در هر refresh کل کوئری را از اول اجرا می‌کند، حتی اگر فقط یک پرداخت جدید آمده باشد. اگر کوئری پشتش ده دقیقه طول بکشد، نمی‌توانید هر دقیقه refresh کنید.
• کهنگی داده را باید محصول بپذیرد. اینکه داشبورد تا ده دقیقه عقب باشد یک تصمیم فنی نیست، تصمیم محصول است. کنار نمودار بنویسید «آخرین به‌روزرسانی: ...» تا کسی عدد کهنه را با عدد زنده اشتباه نگیرد.
• هر دیتابیسی این را ندارد. MySQL اصلاً materialized view ندارد و معمولاً با یک جدول خلاصه و یک job زمان‌بندی‌شده شبیه‌سازی‌اش می‌کنند. SQL Server چیزی به اسم indexed view دارد که با هر نوشتن خودکار به‌روز می‌شود، ولی محدودیت‌های زیادی روی کوئری می‌گذارد. Oracle هم با materialized view log می‌تواند refresh افزایشی انجام بدهد.

اگر refresh کامل خیلی گران شد، قدم بعدی معمولاً یک جدول خلاصه است که خودتان به‌روزش می‌کنید: با trigger، با یک job که فقط ردیف‌های جدید را پردازش می‌کند، یا با CDC⁴ و یک پایپ‌لاین جریانی. این‌ها انعطاف بیشتری دارند، ولی منطقشان را هم خودتان باید درست نگه دارید. materialized view را تا وقتی جواب می‌دهد نگه دارید؛ ساده‌ترین گزینه‌ای است که کار می‌کند.

آیا می‌شود در view نوشت؟

بله، با شرط. در PostgreSQL viewهای ساده (یک جدول در FROM، بدون GROUP BY، DISTINCT، تابع تجمیعی و UNION) خودکار updatable هستند؛ یعنی INSERT و UPDATE روی آن‌ها به جدول زیرین منتقل می‌شود. این برای viewهایی که فقط ستون یا ردیف فیلتر می‌کنند کاربرد دارد:

CREATE VIEW ops.tehran_orders AS
SELECT id, customer_id, status, city
FROM orders
WHERE city = 'tehran'
WITH CHECK OPTION;

بدون WITH CHECK OPTION، کاربری که فقط به این view دسترسی دارد می‌تواند سفارشی با city = 'shiraz' از طریقش ثبت کند؛ ردیفی که بلافاصله بعد از نوشته شدن از دید خود view غیب می‌شود. WITH CHECK OPTION جلوی نوشتن ردیفی را که شرط view را نقض می‌کند می‌گیرد.

برای viewهای پیچیده‌تر هم می‌شود با INSTEAD OF trigger نوشتن را پیاده کرد، ولی تجربه‌ی من این است که معمولاً ارزشش را ندارد. view را برای خواندن نگه دارید و نوشتن را از مسیر اصلی اپلیکیشن انجام بدهید.

چطور در پروژه‌ی واقعی view بسازیم

این‌ها نکته‌هایی است که بیشترشان را از خراب شدن چیزی یاد گرفته‌ام:

۱. viewها را در migration نگه دارید، نه دستی در کنسول. view هم بخشی از اسکیماست. اگر کسی آن را مستقیم روی production بسازد، در محیط تست و روی لپ‌تاپ بقیه وجود ندارد و اولین migration بعدی ممکن است با آن تداخل پیدا کند. تعریف هر view را در فایل migration بنویسید و در code review مثل بقیه‌ی کد بررسی کنید.

۲. schema جدا بدهید. به‌جای اینکه viewها با جدول‌ها قاطی شوند و اسمشان با پیشوند v_ مشخص شود، برای هر مصرف‌کننده یک schema بسازید: reporting، support، analytics. هم معلوم است هر view مال کیست و هم دسترسی دادن ساده می‌شود؛ GRANT USAGE ON SCHEMA support TO support_role و تمام.

۳. هیچ‌وقت SELECT * در تعریف view ننویسید. در PostgreSQL وقتی view ساخته می‌شود، * همان لحظه به فهرست ستون‌ها باز می‌شود و ذخیره می‌شود. اگر بعداً ستونی به جدول اضافه کنید، در view ظاهر نمی‌شود. بدتر اینکه آدم‌ها فکر می‌کنند view «همه‌ی ستون‌ها» را دارد. ستون‌ها را صریح بنویسید تا قرارداد view هم صریح باشد.

۴. محدودیت CREATE OR REPLACE را بشناسید. با CREATE OR REPLACE VIEW فقط می‌شود ستون جدید به آخر فهرست اضافه کرد. عوض کردن اسم، نوع یا ترتیب ستون‌های موجود خطا می‌دهد و باید view را DROP کنید و دوباره بسازید. اگر viewهای دیگری به آن وابسته باشند، DROP هم بدون CASCADE جلو نمی‌رود و CASCADE آن‌ها را هم پاک می‌کند. این کار را داخل یک تراکنش و در migration انجام بدهید تا اگر وسطش خطا خورد چیزی نیمه‌کاره نماند.

۵. view روی view روی view نسازید. یک لایه view روی جدول‌ها خوب است، دو لایه قابل تحمل. از آن بیشتر، کسی که کوئری کندی را دیباگ می‌کند باید پنج تعریف تودرتو را باز کند تا بفهمد دیتابیس واقعاً چه می‌کند، و planner هم با کوئری‌های خیلی بزرگ همیشه تصمیم خوبی نمی‌گیرد. اگر به این عمق رسیدید، احتمالاً وقت یک materialized view یا جدول خلاصه است.

۶. view کار سنگین را پنهان می‌کند؛ این را مستند کنید. SELECT * FROM reporting.customer_revenue بی‌خطر به نظر می‌رسد، ولی بدون WHERE روی کل جدول سفارش‌ها GROUP BY می‌زند. یک COMMENT ON VIEW بنویسید که بگوید view برای چه ساخته شده، معمولاً با چه فیلتری خوانده می‌شود و چه کسی صاحبش است:

COMMENT ON VIEW reporting.customer_revenue IS
  'تعریف رسمی درآمد خالص هر مشتری. همیشه با فیلتر customer_id بخوانید. مالک: تیم داده';

۷. نقش مالک view را حساب‌شده انتخاب کنید. چون view به‌طور پیش‌فرض با دسترسی صاحبش اجرا می‌شود، اگر صاحبش superuser باشد عملاً هر کسی که به view دسترسی دارد با دسترسی superuser آن جدول‌ها را می‌خواند. یک نقش مخصوص با کمترین دسترسی لازم بسازید و viewها را به او بدهید.

۸. برای materialized view، refresh را پایش کنید. job refresh که بی‌صدا شکست بخورد، داشبوردی می‌سازد که هفته‌ها عدد کهنه نشان می‌دهد و هیچ‌کس نمی‌فهمد. زمان آخرین refresh موفق را جایی ثبت کنید و اگر از حدی عقب افتاد هشدار بدهید.

کجا view جواب نیست

view ابزار لایه‌ی دیتابیس است و همان محدودیت‌ها را دارد.

• در معماری میکروسرویس با دیتابیس جدا برای هر سرویس، view نمی‌تواند داده‌ی دو دیتابیس را کنار هم بگذارد. با foreign data wrapper می‌شود، ولی آن وقت دو سرویس دوباره از طریق دیتابیس به هم گره خورده‌اند؛ همان چیزی که قرار بود از آن فرار کنید. این‌جا جای event، API یا یک data warehouse است.
• منطق کسب‌وکاری که مدام عوض می‌شود در view سخت‌تر از کد تست و deploy می‌شود. «درآمد خالص» که سالی یک‌بار تعریفش عوض می‌شود جایش در view خوب است؛ قانون تخفیف که هر هفته عوض می‌شود، نه.
• ORMها معمولاً view را مثل جدول می‌بینند و ممکن است سعی کنند در آن بنویسند یا migration خودکارشان بخواهد آن را به جدول تبدیل کند. مدل مربوط به view را در ORM صریحاً read-only علامت بزنید.
• برای گزارش‌گیری سنگین تحلیلی روی سال‌ها داده، دیتابیس عملیاتی (OLTP) جای درستی نیست، با view یا بدون آن. در این مقیاس داده را به یک انبار داده یا دیتابیس ستونی مثل ClickHouse ببرید.

جمع‌بندی

برگردیم به جلسه‌ی آخر فصل. مشکل ما کمبود ابزار نبود. یک تعریف کسب‌وکاری صاحب نداشت و در سه جا سه‌جور پیاده شده بود. view به آن تعریف یک اسم، یک صاحب و یک محل می‌دهد. در کنارش دسترسی تیم‌ها را به همان چیزی که لازم دارند محدود می‌کند و اسکیما را از وابستگی مستقیم مصرف‌کننده‌ها آزاد می‌کند. materialized view هم وقتی کمک می‌کند که همان تعریف برای هر بار اجرا زیادی گران باشد و چند دقیقه کهنگی داده مشکلی درست نکند.

اگر در مصاحبه‌ی طراحی سیستم یا در پروژه‌ی خودتان دیدید چند مصرف‌کننده یک join تکراری را هر کدام جدا می‌نویسند، قبل از اینکه سرویس یا کتابخانه‌ی جدیدی طراحی کنید، از خودتان بپرسید آیا یک view با تعریف درست کافی نیست. خیلی وقت‌ها کافی است.