دیتابیس ستونی و OLAP: چرا گزارش سالانه را نباید از دیتابیس اپلیکیشن گرفت

فرض کنید همان فروشگاه آنلاین مطلب قبلی را دارید. یک دیتابیس PostgreSQL پشت اپلیکیشن است و روزی چند صد هزار سفارش ثبت می‌کند. سه سال گذشته و جدول order_items حالا حدود یک میلیارد ردیف دارد.

آخر سال، تیم مالی یک گزارش می‌خواهد: «فروش هر دسته‌بندی کالا، به تفکیک ماه، برای کل سال گذشته». یک نفر کوئری را می‌نویسد و روی دیتابیس اصلی اجرا می‌کند:

SELECT
    date_trunc('month', o.created_at) AS month,
    p.category,
    sum(oi.quantity * oi.unit_price)  AS revenue
FROM order_items oi
JOIN orders   o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;

کوئری چهل دقیقه طول می‌کشد. در این چهل دقیقه دیسک سرور به سقف می‌رسد، cache دیتابیس از داده‌های قدیمی پر می‌شود و صفحه‌ی پرداخت سایت کند می‌شود. چند مشتری هم وسط خرید خطای timeout می‌گیرند.

ایراد از کوئری نیست. کوئری درست است و ایندکس‌ها هم سر جایشان هستند. مشکل این است که از دیتابیسی که برای یک نوع کار ساخته شده، کار کاملاً متفاوتی خواسته‌ایم.

دو نوع کار: OLTP و OLAP

کاری که اپلیکیشن هر روز با دیتابیس می‌کند اسم دارد: OLTP (Online Transaction Processing). مشتری سفارش ثبت می‌کند، موجودی انبار یکی کم می‌شود، وضعیت پرداخت عوض می‌شود، پروفایل کاربر خوانده می‌شود. ویژگی مشترک همه‌ی این‌ها این است که هر کوئری با چند ردیف سروکار دارد، ولی معمولاً همه‌ی ستون‌های آن ردیف‌ها را می‌خواهد. تعداد این کوئری‌ها زیاد است و هر کدام باید در چند میلی‌ثانیه تمام شود.

گزارش تیم مالی از جنس دیگری است: OLAP (Online Analytical Processing). این کوئری به میلیون‌ها یا میلیاردها ردیف سر می‌زند ولی از هر ردیف فقط دو سه ستون را لازم دارد، مثلاً تاریخ، دسته‌بندی و مبلغ. تعداد این کوئری‌ها کم است، هیچ‌کدام چیزی را تغییر نمی‌دهند و اگر به جای ده میلی‌ثانیه دو ثانیه طول بکشند هم کسی ناراحت نمی‌شود.

دیتابیس‌هایی مثل PostgreSQL و MySQL برای ستون OLTP این جدول طراحی شده‌اند و در آن کار عالی‌اند. برای ستون OLAP، دیتابیس دیگری لازم است و مهم‌ترین تفاوتش در یک تصمیم خیلی پایه‌ای است: داده روی دیسک به چه ترتیبی نوشته می‌شود.

داده روی دیسک چطور چیده می‌شود

فرض کنید جدول order_items این پنج ستون را دارد (در واقعیت بیشتر است):

ذخیره‌سازی ردیفی (row-oriented)

PostgreSQL داده را در صفحه‌های ۸ کیلوبایتی نگه می‌دارد و در هر صفحه ردیف‌ها را پشت سر هم می‌نویسد. یعنی همه‌ی ستون‌های یک ردیف کنار هم روی دیسک قرار می‌گیرند:

page 1: [1, 501, 87, 2, 120000] [2, 501, 12, 1, 45000] [3, 502, 87, 1, 120000] ...

برای OLTP این چیدمان ایده‌آل است. وقتی می‌خواهید سفارش شماره‌ی ۵۰۱ را نشان بدهید، ایندکس شما را مستقیم به صفحه‌ی درست می‌برد و با یک بار خواندن، همه‌ی ستون‌های ردیف را دارید. ثبت یک ردیف جدید هم یعنی نوشتن یک تکه‌ی پیوسته در انتهای یک صفحه.

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

ذخیره‌سازی ستونی (column-oriented)

دیتابیس ستونی جدول را برعکس می‌چیند. مقدارهای هر ستون جدا و پشت سر هم ذخیره می‌شوند، معمولاً هر ستون در فایل خودش:

id.bin:          1, 2, 3, ...
order_id.bin:    501, 501, 502, ...
product_id.bin:  87, 12, 87, ...
quantity.bin:    2, 1, 1, ...
unit_price.bin:  120000, 45000, 120000, ...

حالا کوئری‌ای که فقط quantity و unit_price را می‌خواهد، فقط همان دو فایل را می‌خواند. بقیه‌ی ستون‌ها اصلاً از دیسک بیرون نمی‌آیند.

چرا ستونی بودن این‌قدر فرق می‌کند

«فقط ستون‌های لازم را بخوان» شاید در نگاه اول بهینه‌سازی کوچکی به نظر برسد. در عمل چهار اثر روی هم جمع می‌شوند و نتیجه اختلاف ده تا صد برابری است.

۱. فقط ستون‌های لازم خوانده می‌شوند

یک حساب سرانگشتی: فرض کنید order_items یک میلیارد ردیف و سی ستون دارد و هر ردیف به طور متوسط ۲۰۰ بایت است. کل جدول حدود ۲۰۰ گیگابایت می‌شود. گزارش ما سه ستون لازم دارد که روی هم حدود ۲۰ بایت‌اند.

• دیتابیس ردیفی باید حدود ۲۰۰ گیگابایت را از دیسک بخواند.
• دیتابیس ستونی حدود ۲۰ گیگابایت را می‌خواند، و این قبل از فشرده‌سازی است.

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

۲. فشرده‌سازی خیلی بهتر کار می‌کند

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

• ستون category شاید فقط پنجاه مقدار متفاوت داشته باشد. به جای تکرار رشته‌ی «لوازم خانگی» در یک میلیارد ردیف، کافی است یک دیکشنری کوچک و یک عدد یک‌بایتی برای هر ردیف نگه داریم.
• ستون created_at اگر مرتب باشد، هر مقدار فقط چند ثانیه با قبلی فرق دارد. به جای هشت بایت برای هر timestamp، می‌شود فقط اختلاف با مقدار قبلی را نوشت (delta encoding) که در یکی دو بایت جا می‌شود.
• ستون quantity بیشتر وقت‌ها ۱ است و دنباله‌های طولانی از ۱ خیلی خوب فشرده می‌شوند.

نسبت فشرده‌سازی به داده بستگی دارد، ولی در داده‌ی واقعی نسبت‌های پنج تا ده برابری در دیتابیس‌های ستونی عجیب نیست. پس آن ۲۰ گیگابایت حساب بالا ممکن است روی دیسک فقط دو سه گیگابایت باشد. این هم هزینه‌ی ذخیره‌سازی را پایین می‌آورد و هم باز حجم خواندن را.

۳. پردازش برداری (vectorized execution)

وقتی مقدارهای یک ستون پشت سر هم در حافظه باشند، CPU می‌تواند آن‌ها را دسته‌ای پردازش کند. دیتابیس ردیفی معمولاً ردیف به ردیف جلو می‌رود: ردیف را بخوان، ستون را پیدا کن، شرط را چک کن، برو سراغ ردیف بعدی. دیتابیس ستونی مثل ClickHouse به جای یک مقدار، یک بلوک چند هزارتایی از یک ستون را برمی‌دارد و یک عملیات را روی کل بلوک اجرا می‌کند.

این روش با cache پردازنده سازگارتر است و اجازه می‌دهد از دستورهای SIMD استفاده شود که با یک دستور چند عدد را هم‌زمان جمع یا مقایسه می‌کنند. برای عملیاتی مثل sum روی میلیاردها عدد، این تفاوت بزرگی می‌سازد.

۴. رد شدن از بلوک‌هایی که به کار نمی‌آیند

دیتابیس‌های ستونی برای هر تکه از داده خلاصه‌ای مثل کمینه و بیشینه‌ی مقدارها نگه می‌دارند. اگر داده بر اساس تاریخ مرتب شده باشد و شما فقط سال ۲۰۲۵ را بخواهید، دیتابیس از روی همین خلاصه‌ها می‌فهمد کدام تکه‌ها اصلاً ردیفی از ۲۰۲۵ ندارند و سراغشان نمی‌رود. در بخش بعد می‌بینیم ClickHouse این کار را چطور انجام می‌دهد.

ClickHouse از نزدیک

ClickHouse یک دیتابیس ستونی متن‌باز است که اول در Yandex برای تحلیل ترافیک وب ساخته شد. دو چیز آن را برای ساختن data warehouse جذاب می‌کند: خیلی سریع است و SQL آن به SQL استاندارد نزدیک است، پس تیم لازم نیست زبان تازه‌ای یاد بگیرد.

همان گزارش فروش را در ClickHouse این‌طور مدل می‌کنیم. در دنیای تحلیل معمولاً جدول‌ها را denormalize می‌کنیم و اطلاعاتی مثل دسته‌بندی را مستقیم در جدول اصلی می‌گذاریم تا در هر کوئری join لازم نباشد:

CREATE TABLE sales
(
    order_id     UInt64,
    created_at   DateTime,
    customer_id  UInt64,
    product_id   UInt32,
    category     LowCardinality(String),
    city         LowCardinality(String),
    quantity     UInt16,
    unit_price   UInt64,
    revenue      UInt64 MATERIALIZED quantity * unit_price
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (category, created_at);

چند نکته در همین تعریف هست:

• MergeTree موتور اصلی ذخیره‌سازی ClickHouse است. تقریباً هر جدول جدی در ClickHouse از خانواده‌ی MergeTree است.
• ORDER BY مهم‌ترین تصمیم طراحی جدول است. داده روی دیسک به همین ترتیب مرتب می‌شود و کلید اصلی (primary key) هم به طور پیش‌فرض همین است. باید ستون‌هایی را اینجا بگذارید که بیشتر در WHERE کوئری‌ها می‌آیند.
• PARTITION BY toYYYYMM(created_at) داده‌ی هر ماه را جدا نگه می‌دارد. حذف داده‌ی یک ماه قدیمی با یک دستور DROP PARTITION انجام می‌شود، بدون اینکه چیزی بازنویسی شود.
• LowCardinality(String) همان فشرده‌سازی دیکشنری است که بالاتر گفتیم. برای ستون‌هایی که چند ده یا چند صد مقدار متفاوت دارند، هم حجم را کم می‌کند و هم کوئری را سریع‌تر.

ایندکس اسپارس، نه B-tree

ایندکس PostgreSQL از نوع B-tree است و برای هر ردیف یک ورودی دارد. این برای پیدا کردن یک ردیف خاص عالی است، ولی روی یک میلیارد ردیف، خود ایندکس هم ده‌ها گیگابایت می‌شود.

ClickHouse مسیر دیگری رفته. داده را به بلوک‌هایی به اسم granule تقسیم می‌کند که به طور پیش‌فرض هر کدام ۸۱۹۲ ردیف دارد و در کلید اصلی فقط اولین مقدار هر granule را نگه می‌دارد. برای یک میلیارد ردیف این یعنی حدود ۱۲۲ هزار ورودی که راحت در حافظه جا می‌شود.

وقتی کوئری با شرط category = 'لوازم خانگی' می‌آید، ClickHouse با جست‌وجوی دودویی روی همین ایندکس کوچک می‌فهمد کدام granuleها ممکن است این دسته را داشته باشند و فقط همان‌ها را می‌خواند. این ایندکس نمی‌تواند یک ردیف تکی را سریع پیدا کند، ولی برای کوئری‌ای که بازه‌ی بزرگی از داده را می‌خواهد دقیقاً همان چیزی است که لازم داریم.

نوشتن در قالب part و ادغام در پس‌زمینه

هر INSERT در ClickHouse یک part جدید روی دیسک می‌سازد: یک پوشه که برای هر ستون فایل مرتب و فشرده‌ی خودش را دارد. ClickHouse در پس‌زمینه partهای کوچک را با هم ادغام (merge) می‌کند و partهای بزرگ‌تر می‌سازد. اسم MergeTree هم از همین‌جا آمده.

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

همان گزارش، دوباره

SELECT
    toStartOfMonth(created_at) AS month,
    category,
    sum(revenue)               AS revenue
FROM sales
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
GROUP BY month, category
ORDER BY month, category;

کوئری تقریباً همان است، ولی ClickHouse فقط سه ستون created_at، category و revenue را می‌خواند، فقط partitionهای سال ۲۰۲۵ را باز می‌کند و روی داده‌ی فشرده به صورت برداری جمع می‌زند. روی سخت‌افزار معقول، کوئری‌ای که در PostgreSQL ده‌ها دقیقه طول می‌کشید معمولاً در ClickHouse به چند ثانیه یا کمتر می‌رسد. عدد دقیق به داده و سخت‌افزار بستگی دارد، پس قبل از تصمیم‌گیری با داده‌ی خودتان benchmark بگیرید.

هزینه‌ی ستونی بودن

اگر ستونی بودن این‌قدر خوب است، چرا همه‌چیز را در ClickHouse نگه نداریم؟ چون همان چیدمانی که پیمایش را سریع می‌کند، کارهای OLTP را سخت می‌کند.

خواندن یک ردیف کامل گران است. برای نشان دادن یک سفارش با سی ستون، دیتابیس ستونی باید از سی فایل جدا بخواند و در هر کدام یک granule کامل را باز کند. کاری که PostgreSQL با یک خواندن ایندکس انجام می‌دهد، اینجا ده‌ها برابر پرهزینه‌تر است.

به‌روزرسانی و حذف سنگین است. داده فشرده و مرتب است، پس عوض کردن یک مقدار یعنی بازنویسی بخشی از part. در ClickHouse دستور ALTER TABLE ... UPDATE یک mutation است که به صورت ناهمگام اجرا می‌شود و partهای درگیر را بازنویسی می‌کند. DELETE سبک (lightweight delete) هم هست که ردیف‌ها را فقط علامت می‌زند، ولی هیچ‌کدام برای تغییرات مکرر روی تک‌ردیف‌ها ساخته نشده‌اند. الگوی رایج این است که به جای update، نسخه‌ی جدید ردیف را درج کنید و با موتوری مثل ReplacingMergeTree بگذارید نسخه‌های قدیمی در merge حذف شوند.

درج‌های کوچک و زیاد مشکل‌ساز است. چون هر INSERT یک part می‌سازد، هزار درج تکی در ثانیه یعنی هزار part کوچک در ثانیه که merge پس‌زمینه به آن‌ها نمی‌رسد. باید درج‌ها را دسته‌ای کنید، مثلاً هر چند ثانیه چند ده هزار ردیف با هم، یا از قابلیت async_insert استفاده کنید که این دسته‌بندی را سمت سرور انجام می‌دهد.

تراکنش به معنای OLTP ندارید. اگر ثبت سفارش و کم کردن موجودی باید با هم موفق شوند یا با هم شکست بخورند، ClickHouse جای این کار نیست.

خلاصه اینکه ClickHouse جایگزین PostgreSQL نیست. کنارش می‌نشیند.

معماری رایج: هر دیتابیس سر کار خودش

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

       application
           │  (OLTP reads & writes)
           ▼
      PostgreSQL ──── CDC / ETL ────▶ ClickHouse ◀──── dashboards, reports, BI

اپلیکیشن فقط با PostgreSQL کار می‌کند. داده‌ها به یکی از این دو روش به ClickHouse منتقل می‌شوند:

• دسته‌ای (batch ETL): یک job شبانه یا ساعتی داده‌ی تازه را از PostgreSQL می‌خواند، شکلش را برای تحلیل عوض می‌کند و در ClickHouse می‌ریزد. ساده است و برای گزارش‌هایی که داده‌ی دیروز برایشان کافی است، کاملاً جواب می‌دهد.
• جریانی (CDC): ابزاری مثل Debezium تغییرات را از WAL پستگرس می‌خواند و در Kafka می‌گذارد و ClickHouse آن‌ها را مصرف می‌کند. داده با تأخیر چند ثانیه‌ای به انبار داده می‌رسد. پیچیده‌تر است، ولی داشبوردهای تقریباً زنده را ممکن می‌کند.

این چیدمان همان data warehouse است: یک دیتابیس جدا که داده‌ی سیستم‌های مختلف (سفارش‌ها، لاگ‌ها، داده‌ی بازاریابی، رویدادهای اپلیکیشن) در آن جمع می‌شود تا بشود رویش تحلیل کرد. جدا بودنش دو فایده‌ی هم‌وزن دارد. کوئری‌های تحلیلی سنگین دیگر به دیتابیس اپلیکیشن فشار نمی‌آورند، و داده می‌تواند با شکلی ذخیره شود که برای تحلیل مناسب است، نه برای تراکنش.

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

کی سراغ دیتابیس ستونی برویم

نشانه‌هایی که می‌گویند وقتش رسیده:

• کوئری‌های گزارش روی دیتابیس اصلی چند دقیقه طول می‌کشند و در همان زمان latency اپلیکیشن بالا می‌رود.
• گزارش‌ها روی بازه‌های بلند، مثل یک فصل یا یک سال، و روی جدول‌هایی با صدها میلیون ردیف یا بیشتر اجرا می‌شوند.
• برای فرار از کندی، دارید تعداد زیادی جدول خلاصه و materialized view دستی نگه می‌دارید.
• داده‌ی رویدادمحور (لاگ، کلیک، رویدادهای اپلیکیشن) دارید که فقط اضافه می‌شود و تقریباً هیچ‌وقت عوض نمی‌شود.
• تیم تحلیل داده می‌خواهد آزادانه کوئری‌های اکتشافی بنویسد، بدون اینکه نگران خواباندن سایت باشد.

و نشانه‌هایی که می‌گویند هنوز زود است:

• کل داده‌تان چند ده میلیون ردیف است. PostgreSQL با ایندکس درست، یک replica فقط‌خواندنی برای گزارش و شاید چند materialized view هنوز از پسش برمی‌آید.
• گزارش‌ها به داده‌ای نیاز دارند که مرتب update می‌شود و باید همیشه دقیقاً آخرین وضعیت را نشان بدهند.
• تیم ظرفیت نگه‌داری یک سیستم دیگر و pipeline انتقال داده را ندارد. سرویس‌های مدیریت‌شده‌ای مثل ClickHouse Cloud یا BigQuery بخشی از این هزینه را کم می‌کنند، ولی pipeline داده را همچنان شما باید بسازید و نگه دارید.

جمع‌بندی

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

پس سؤال درست این نیست که PostgreSQL بهتر است یا ClickHouse. سؤال این است که هر کوئری از کدام نوع است. کوئری‌های اپلیکیشن سراغ دیتابیس OLTP می‌روند و گزارش و تحلیل سراغ انبار داده‌ی ستونی.