BRIN Index في PostgreSQL: index بـ 12MB بدل 2.4GB لجدول 800 مليون صف

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

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

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

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

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

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

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

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

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

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