الفصل 14: عمليات قاعدة البيانات¶

الجمهور المستهدف: مهندسو DevOps، مهندسو المنصة

قاعدة بيانات Postgres في الإنتاج¶

يستخدم Cat-Scan خدمة Cloud SQL (إصدار Postgres 15) كقاعدة بيانات تشغيلية وحيدة. يتصل API عبر حاوية Cloud SQL Auth Proxy الجانبية على localhost:5432.

الجداول الرئيسية والحجم¶

الجدول	عدد الصفوف التقريبي	ما يخزّنه
`rtb_daily`	حوالي 84 مليون	الأداء اليومي لـ RTB لكل مشترٍ، وإبداع، وموقع جغرافي، إلخ.
`rtb_bidstream`	حوالي 21 مليون	تفصيل تدفق المزايدات حسب الناشر والموقع الجغرافي
`rtb_quality`	متغير	مقاييس الجودة (قابلية المشاهدة، سلامة العلامة التجارية)
`rtb_bid_filtering`	حوالي 188 ألف	أسباب وأحجام تصفية المزايدات
`pretargeting_configs`	صغير	لقطات تكوين الاستهداف المسبق
`creatives`	صغير	بيانات وصور مصغرة للإبداعات
`import_history`	صغير	سجلات استيراد CSV
`users`، `permissions`، `audit_log`	صغير	بيانات المصادقة والإدارة

الفهارس الحرجة¶

نمط الفهرس الأكثر حساسية للأداء هو:

CREATE INDEX idx_<table>_buyer_metric_date_desc
    ON <table> (buyer_account_id, metric_date DESC);

يوجد هذا الفهرس على جداول rtb_daily وrtb_bidstream وrtb_quality وrtb_bid_filtering. وهو يدعم استعلام حداثة البيانات والتحليلات المحصورة بالمشتري.

فهارس مهمة أخرى: - (metric_date, buyer_account_id): لفلاتر النطاق الزمني + المشتري - (metric_date, billing_id): للاستعلامات المحصورة بالفوترة - (row_hash) UNIQUE: إزالة التكرار عند الاستيراد

إزالة التكرار¶

يتم تجزئة كل صف مستورد (عمود row_hash). يمنع القيد الفريد على row_hash إدخال السجلات المكررة، مما يجعل إعادة الاستيراد آمنة.

نموذج الاتصال¶

يستخدم API اتصالات لكل طلب (بدون تجمّع اتصالات). ينشئ كل استعلام استدعاء psycopg.connect() جديدًا، مغلّفًا في run_in_executor للتوافق مع البرمجة غير المتزامنة.

async def pg_query(sql, params=()):
    loop = asyncio.get_event_loop()
    def _execute():
        with _get_connection() as conn:
            cursor = conn.execute(sql, params)
            return [dict(row) for row in cursor.fetchall()]
    return await loop.run_in_executor(None, _execute)

لأحمال العمل الإنتاجية، يُنصح بإضافة psycopg_pool إذا أصبح عبء الاتصال عنق زجاجة.

مهل الاستعلام¶

للاستعلامات المكلفة (مثل حداثة البيانات عبر الجداول الكبيرة)، يستخدم API وظيفة pg_query_with_timeout:

conn.execute(f"SET LOCAL statement_timeout = {timeout_ms}")
cursor = conn.execute(sql, params)

تفاصيل مهمة: - يحصر SET LOCAL المهلة في نطاق المعاملة الحالية ويُعاد تعيينها تلقائيًا عند انتهاء المعاملة (إيداع أو تراجع). - المهلة الافتراضية لحداثة البيانات: 30 ثانية. - قابلة للتكوين عبر متغير البيئة UPLOADS_DATA_FRESHNESS_QUERY_TIMEOUT_MS (الحد الأدنى 1000 مللي ثانية). - يتجنب SET LOCAL مشكلة المعاملة المُجهضة التي تحدث عند استخدام SET + RESET في كتلة try/finally (إذا أُلغي الاستعلام بسبب المهلة، تدخل المعاملة في حالة إجهاض، ويفشل RESET).

نمط استعلام حداثة البيانات¶

تحتاج نقطة نهاية حداثة البيانات إلى معرفة التواريخ التي تحتوي على بيانات لكل نوع تقرير. النمط عالي الأداء يستخدم generate_series + EXISTS:

SELECT d::date AS metric_date, 'bidsinauction' AS csv_type, 1 AS row_count
FROM generate_series(%s::date, CURRENT_DATE - 1, '1 day'::interval) AS d
WHERE EXISTS (
    SELECT 1 FROM rtb_daily
    WHERE metric_date = d::date AND buyer_account_id = %s
    LIMIT 1
)

يقوم هذا بعمليات بحث في الفهرس بعدد N (واحدة لكل يوم في النافذة) بدلاً من مسح ملايين الصفوف. لنافذة مدتها 14 يومًا: 14 عملية بحث بمعدل حوالي 0.1 مللي ثانية لكل منها مقابل مسح تسلسلي متوازٍ كامل يستغرق أكثر من 160 ثانية.

لماذا لا يعمل GROUP BY هنا: حتى مع 1 AS row_count (بدون COUNT)، يختار مُخطط الاستعلام مسحًا تسلسليًا عندما تكون مجموعة نتائج GROUP BY كبيرة نسبةً إلى الجدول. الفهرس (buyer_account_id, metric_date DESC) موجود لكن المُخطط يُقدّر أن مسح 84 مليون صف أرخص من إجراء 4.4 مليون قراءة في الفهرس.

دور BigQuery¶

يخزّن BigQuery البيانات الخام التفصيلية ويُشغّل مهام التحليل على دفعات. لا يُستخدم لاستعلامات API في الوقت الفعلي. النمط المتبع:

يتم تحميل بيانات CSV الخام إلى جداول BigQuery.
تقوم المهام الدفعية بتجميع البيانات.
تُكتب النتائج المجمّعة مسبقًا إلى Postgres.
يُقدّم API البيانات من Postgres.

الاحتفاظ بالبيانات¶

قابل للتكوين من /settings/retention. يتحكم في مدة الاحتفاظ بالبيانات التاريخية في Postgres قبل حذفها تلقائيًا.

مواضيع ذات صلة¶

نظرة عامة على البنية: موقع قاعدة البيانات في البنية العامة
استكشاف الأخطاء وإصلاحها: أنماط أعطال قاعدة البيانات
لمشتري الوسائط: استيراد البيانات يغطي شبكة حداثة البيانات من منظور المستخدم.