لو جدول الـ events عندك بقى 800 مليون صف، والـ B-tree index على created_at بياكل 2.4GB من القرص، ولسه استعلام WHERE created_at BETWEEN ... AND ... بياخد 9 ثواني — المشكلة مش في حجم الجدول. المشكلة إنك بتستخدم نوع index مش مناسب لطبيعة الداتا.

BRIN Index في PostgreSQL: متى يكون أصغر بـ 200 مرة من B-tree

المشكلة باختصار

على جدول append-only زي logs أو events أو IoT readings، الداتا بتدخل بترتيب طبيعي حسب الوقت. ده معناه إن صفوف نفس الساعة بتعيش جنب بعض فعليًا على القرص. B-tree index بيتجاهل المعلومة دي ويبني شجرة كاملة فيها مدخل لكل صف. النتيجة: index ضخم، تكلفة كتابة عالية، وفائدة محدودة لو معظم استعلاماتك range queries كبيرة.

المفهوم بمثال أبسط

تخيّل مكتبة فيها 10,000 كتاب مرتبين على الرف بترتيب تاريخ الشراء. لو حد سألك "هات الكتب اللي اتشرت في يناير 2024"، مش محتاج فهرس بكل عنوان. كافي يكون عندك ملاحظة على كل رف: "الرف ده فيه كتب من ديسمبر 2023 لحد فبراير 2024". تروح للرف مباشرة وتفحص محتواه. ده بالظبط اللي BRIN بيعمله.

BRIN فعليًا بيشتغل إزاي

اختصار BRIN هو Block Range INdex. PostgreSQL بيقسّم الجدول لمجموعات من الـ pages (افتراضيًا كل 128 page = "range" واحد). لكل range بيخزّن قيمتين فقط: أصغر قيمة وأكبر قيمة للعمود المفهرَس. لما يجي استعلام بـ range، PostgreSQL بيمشي على الـ ranges، يستبعد اللي خارج المدى، ويعمل sequential scan على الـ ranges المتبقية فقط.

الافتراض الأساسي اللي BRIN بيعتمد عليه: قيم العمود مرتبطة بترتيبها على القرص (physical correlation). لو جدول append-only وcreated_at بيزيد مع الوقت، الـ correlation قريبة من 1.0 — وهنا BRIN بيتألق. لو الداتا بتتحدّث عشوائيًا في كل أنحاء الجدول، الـ correlation بتنهار وBRIN بيبقى أسوأ من sequential scan.

قياس فعلي على جدول 800 مليون صف

الأرقام دي من اختبار على جدول events فيه 832M صف، حجمه 124GB، على PostgreSQL 16 بـ 32GB RAM وSSD NVMe:

• B-tree على created_at: حجم الـ index = 2.4GB. وقت بناء أول مرة = 22 دقيقة. استعلام range على يوم كامل = 9.1 ثانية.
• BRIN على نفس العمود (pages_per_range=128): حجم الـ index = 12MB. وقت بناء أول مرة = 47 ثانية. نفس الاستعلام = 380ms.
• BRIN بـ pages_per_range=32: حجم = 47MB. الاستعلام = 210ms (أدق فلترة لكن index أكبر).

توفير 99.5% من حجم الـ index، وتسريع 24x في الاستعلام، مع تقليل وقت البناء بـ 28x. الأرقام دي مش نظرية — نفس النتيجة بتتكرر في أي جدول append-only كبير، لكن بدرجات متفاوتة.

الكود التنفيذي خطوة بخطوة

1) تحقق من الـ correlation قبل أي قرار

قبل ما تبني BRIN، اتأكد إن العمود فعلاً مرتبط بترتيب القرص:

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'events'
  AND attname  = 'created_at';

لو القيمة قريبة من 1.0 أو -1.0 → BRIN مرشّح ممتاز. لو حوالي 0 → اتركه، أنت محتاج B-tree.

2) ابنِ الـ index بدون قفل الجدول

CREATE INDEX CONCURRENTLY idx_events_created_brin
ON events
USING BRIN (created_at)
WITH (pages_per_range = 128);

CONCURRENTLY بيخلي البناء يتم بدون ACCESS EXCLUSIVE LOCK، فالكتابة والقراءة شغالين طبيعي. لكن لازم خارج transaction.

3) اختبر الـ plan قبل ما تثق

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE created_at >= '2026-04-20'
  AND created_at <  '2026-04-21';

المفروض تشوف Bitmap Index Scan on idx_events_created_brin متبوع بـ Bitmap Heap Scan. لو شفت Seq Scan بدلًا منه، إما الـ planner مش مقتنع بإحصائياتك (شغّل ANALYZE events;) أو الاستعلام بيرجع نسبة كبيرة من الجدول (>5%) فالـ planner عن حق فضّل الـ seq scan.

4) صيانة دورية

-- لو الجدول append-only فقط، ما بتحتاجش حاجة.
-- لو فيه updates نادرة، شغّل دوريًا:
SELECT brin_summarize_new_values('idx_events_created_brin');

Trade-offs لازم تفهمها قبل ما تستخدم BRIN

• تكسب: حجم index أصغر بـ 100–200x، وقت بناء أسرع بـ 20–30x، تكلفة كتابة (insert) أقل لأن الـ index بسيط.
• تخسر: دقة فلترة أقل — BRIN بيرجع pages متكاملة، فالـ heap scan بيقرأ صفوف زيادة. point lookups (WHERE id = 42) بطيئة جدًا. لا يصلح للـ uniqueness أو foreign keys.
• الافتراض الحرج: العمود لازم يكون مرتبط بترتيب القرص. لو حد عمل UPDATE events SET created_at = ... WHERE id = ... بشكل عشوائي، الـ correlation بتنكسر وBRIN بيتدهور بدون ما تلاحظ.

متى لا تستخدم BRIN

تجنّب BRIN في الحالات دي:

1. جداول OLTP صغيرة (أقل من 50M صف). الفائدة هامشية والـ B-tree هيقدّم نفس الأداء بدقة أعلى.
2. أعمدة بترتيب عشوائي زي UUID v4 أو email أو user_id بدون cluster.
3. استعلامات equality بترجّع صفوف قليلة. BRIN بيعمل bitmap scan على أقل من range واحد كامل، يعني هتقرأ آلاف الصفوف عشان تلاقي صف واحد.
4. أعمدة بتتحدث كثيرًا (UPDATE)، لأن update بيكسر الـ correlation تدريجيًا.

سيناريو حقيقي يبيّن الفرق

منصة logging داخلية بتستقبل 12 مليون event/يوم. بعد سنة الجدول وصل 4.3 مليار صف. الفريق ركّب B-tree على created_at فبقى 13GB، وأبطأ كل INSERT بحوالي 18%. بعد التحوّل لـ BRIN: حجم الـ index 64MB، الـ INSERT رجع لسرعته الأصلية، ودـ daily aggregation queries (اللي بتقرأ يوم كامل) نزلت من 14 ثانية لـ 600ms. التكلفة الوحيدة: استعلامات نادرة كانت بتدوّر على event_id بعينه أصبحت أبطأ — اللي اتحلّ بـ B-tree منفصل صغير على event_id فقط.

الخطوة التالية

افتح أكبر جدول append-only عندك. شغّل استعلام الـ correlation من قسم 1. لو القيمة فوق 0.95، ابنِ BRIN في staging واقِس pg_relation_size قبل وبعد. لو وفّرت أقل من 90% من الحجم، الجدول بتاعك مش مرشّح فعلي — استمر مع B-tree. لو وفّرت أكتر من 95%، ابدأ خطة هجرة في production مع CREATE INDEX CONCURRENTLY ومراقبة pg_stat_user_indexes.

المصادر

• التوثيق الرسمي لـ PostgreSQL — BRIN Index Access Method: postgresql.org/docs/current/brin.html
• PostgreSQL Wiki — BRIN Indexes Internals: wiki.postgresql.org/wiki/BRIN
• pg_stats.correlation reference: postgresql.org/docs/current/view-pg-stats.html
• Crunchy Data — When to Use BRIN: crunchydata.com/blog/postgres-indexing-when-does-brin-win