PostgreSQL: أفضل الممارسات والفهرسة

(القسم: التكنولوجيا والهياكل الأساسية)

موجز موجز

PostgreSQL هي «نواة الحقيقة» للمال و KYC والسجلات ذات الأهمية القانونية في iGaming. يوفر ضمانات ACID و SQL القوية وقابلية التوسع. لتحمل ذروة البطولات وخطافات الويب PSP، فهي مهمة: مخطط كفء، ومؤشرات، وتقسيم، وفراغ تلقائي، وضبط WAL وإمكانية الملاحظة. فيما يلي مبني الممارسات والقوالب.

1) الهندسة المعمارية و SLO

دور PostgreSQL: قائد للكتابة + نسخ طبق الأصل للقراءة ؛ للشاشات الساخنة - مخبأ/إسقاطات (Redis/مناظر ملموسة).
أمثلة SLO: p99 'INSERT/UPDATE' wallet ≤ 25-40 ms ؛ p99 قراءة الرصيد ≤ 10-15 مللي ثانية ؛ ونسخة طبق الأصل ≤ 2-5 ث ؛ ≥ 99. 9%.
سياسة القراءة بعد الكتابة: تتم قراءة شاشات المستخدم بعد المعاملة من القائد أو في انتظار تأخر النسخ.

2) التصميم التخطيطي

تطبيع جوهر المال (المحافظ، دفتر الأستاذ) + إزالة الطابع الطبيعي للقراءة (CQRS/الإسقاطات).
القيود الصارمة: "NOT NULL'،" CHECK "،" UNIQUE "، FK مع استهداف" ON DELETE/UPDATE "(RESTRICT/SET NULL/NO O AC).
إصدار المخطط: هجرات لأعلى/لأسفل، أعلام مميزة ؛ تجنب إعادة التسمية في الوقت الحار.
المعرفات: 'BIGINT' + التسلسلات (أو ULID/UUIDv7 للتوزيع). للإدخالات الساخنة - تسلسلات في ملعقة كبيرة منفصلة/حجم WAL.

3) الفهرسة: ماذا وأين وكيف

3. 1 B-Tree (افتراضي)

الزمان: المباريات الدقيقة، النطاقات، الأنواع، «الانضمام» بواسطة FK.
الأنماط: «WHERE player_id = ؟», «ORDER BY created_at DESC LIMED 100».

• فهارس متعددة الأعمدة - تطابق ترتيب الشروط.
• تغطية: «تضمين (...)» для الفهرس فقط.
• الفهرس الفردي تحت الترتيب حسب... DESC 'على «الأشرطة».

sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);

3. 2 GIN (JSONB، صفائف، نص كامل)

الزمان: بحث مفتاح/مسار JSONB، مصفوفات العلامات، FTS.
المتغيرات: 'gin _ trgm _ ops' للتريغرام،' jsonb _ path _ ops'/' jsonb _ ops'.
الممارسة: الحد بدقة من مجالات البحث والتفكير في الكاردينالية.

sql
CREATE INDEX idx_profile_jsonb_gin
ON player_profile USING GIN (data jsonb_path_ops);

-- Search by email/name with typos
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_player_trgm ON player USING GIN (email gin_trgm_ops);

3. 3 GiST (geo/ranges/signations)

الزمان: تحديد الموقع الجغرافي (IP-geo، radii)، الفترات، أقرب الجيران.
أقل استخدامًا في جوهر المال، ومفيد للقيود الجغرافية/اللعب المسؤول.

3. 4 BRIN (جداول «تذييل» كبيرة في الوقت المناسب)

الزمان: مليارات الخطوط، الارتباط الطبيعي بمرور الوقت (سجلات الرهان/الحدث).
الإيجابيات: حجم صغير، خدمة رخيصة.
السلبيات: → دمج الانتقائية الخشنة مع التقسيم.

sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);

3. 5 هاش

نادرا ما يلزم: المساواة بعمود واحد في غياب النطاقات ؛ في كثير من الأحيان تكفي B-Tree.

3. 6 جزئية وتعبيرات

المؤشر الجزئي: يسرع المجموعات الفرعية الساخنة (النشطة، 'الحالة =' المعلقة ").
مؤشر التعبير: مفتاح precompute («أقل (بريد إلكتروني)»، (data ->> «psp _ tx»).

sql
CREATE INDEX idx_withdraw_pending ON withdrawals (player_id)
WHERE status = 'pending';

CREATE INDEX idx_tx_psp_tx ON tx ((data->>'psp_tx'));

3. 7 مؤشر أنتيباترن

«مؤشر كل شيء»: زيادة المؤشرات تبطئ الكتابة والفراغ.
الفهارس المكررة (نفس مجموعة العمود/الترتيب).
فهرس إلى عمود مع كاردينالية منخفضة جدا (على سبيل المثال، «الحالة» مع 2 قيم) - تفعل جزئية.

4) التقسيم

لماذا: تقليل الانتفاخ، وتسريع الفراغ/الفحوصات، وتسهيل الاحتفاظ/الأرشيف.
المخططات: النطاق حسب التاريخ (يوم/أسبوع) لسجلات الرهان ؛ HASH بواسطة «player _ id» لجداول المستخدم الكبيرة ؛ مجتمعة.
ممارسة التناوب: إنشاء أطراف مستقبلية مسبقًا، «التقسيم المرفق»، أرشيف للأطراف القديمة - «الانفصال» + الحركة.

sql
CREATE TABLE bets (
bet_id BIGINT PRIMARY KEY,
player_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
amount_cents BIGINT NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE bets_2025_11_05 PARTITION OF bets
FOR VALUES FROM ('2025-11-05') TO ('2025-11-06');

5) الإدخالات/التحديثات مع الحد الأدنى من التجزئة

تحديثات ساخنة: احتفظ بـ «عامل حشو» (على سبيل المثال 90) على جداول البيانات الساخنة لمساحة مجانية على الصفحة.
TOAST: نصوص/نصوص JSONB كبيرة - راقب الضغط ؛ تخزين الحقول الضخمة في طاولة منفصلة.
UPSERT: استخدم "في الصراع... قم بالتحديث بمنطق أحمق.

sql
INSERT INTO wallet (player_id, balance_cents, updated_at)
VALUES ($1, $2, now())
ON CONFLICT (player_id) DO UPDATE
SET balance_cents = wallet. balance_cents + EXCLUDED. balance_cents,
updated_at = now();

6) المعاملات والأقفال والمنافسة

مستويات العزل: «اقرأ ملتزمًا» لمعظم المسارات ؛ نقطة «قابلة للتكرار للقراءة »/« SERIALIZABLE» (تقارير، دفعات غير متصلة بالإنترنت). لا تمسكهم لفترة طويلة.
أنواع الأقفال: مستوى الصف (التشاؤم «للتحديث»)، مستوى الجدول (DDL)، الأقفال الاستشارية للطفرات الموزعة.
الجمود: معاملات قصيرة، ترتيب موحد للكيانات المستكملة، مهلة ('قفل _ مهلة'، 'بيان _ مهلة').
قوائم انتظار المهام: «SKIP LOCKED» لمجموعة العمل الموزعة.

sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;

7) فراغ السيارات والإحصاءات والانتفاخ

VACUUM/ANALYZE: احتفظ بالإحصاءات الحالية ('الافتراضي _ الإحصائيات _ الهدف')، ضبط AV على الجداول الساخنة (عتبة التشغيل أدناه).
احترس من اللف (العمر (txid) <2 مليار)، «facuum _ freeze _ min _ age».
التحكم في الانتفاخ: إعادة الدفع العادية/المجموعة على المؤشرات الثقيلة خارج أوقات الذروة ؛ التقسيم يقلل من حجم المشكلة.

sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);

8) الذاكرة ونقاط التفتيش و WAL

ذاكرة

"shared _ buffers': 20-25٪ ذاكرة الوصول العشوائي (يعتمد الملف الشخصي).
"work _ mem': عن طريق العملية! التشكيل بشكل متحفظ (على سبيل المثال، 8-64MB) وزيادة نقطة لأدوار الإبلاغ.
"mainformation _ work _ mem': فهارس كبيرة/استرداد (مهمة 512 ميجابايت-2 جيجابايت).

WAL ونقاط التفتيش

NVMe لـ WAL، مجلد واحد ؛ 'wal _ compression = on'.
«checpoint _ timeout' 10-15 دقيقة،» max _ wal _ size «عرضة للتغيير،» نقطة تفتيش _ اكتمال _ الهدف ≈ 0. 9`.
بالنسبة للقمم، تدرج الدفعات والالتزامات الجماعية.

9) التكرار وتحمل الخطأ

نسخة طبق الأصل للقائد: متزامنة مع أقرب (شبه مزامنة) لـ RPO≈0-30 ج ؛ غير متزامن للقراءات/التحليلات.
الترقية والمبارزة: Patroni/replica manager ؛ باستثناء «الزعيم المزدوج».
الاستعداد الساخن: «hot _ standby _ feeback» بعناية (نمو الانتفاخ)، أفضل - ترويض المعاملات الطويلة على النسخ المتماثلة.

10) النسخ الاحتياطية و PITRs

النسخ الاحتياطية الكاملة + الإضافية، خارج الموقع ؛ "archive _ command' لـ WAL.
PITR: تحقق من الاسترداد حتى النقطة الزمنية على المدرجات ؛ تنظيم RPO/RTO (محافظ - دقائق، سجلات - عشرات الدقائق).
تمارين DR (يوم اللعبة): فحص التعافي المنتظم.

11) JSONB ونموذج «الدائرة المرنة»

JSONB - ممتاز للسمات النادرة القراءة/المتغيرة (البيانات الوصفية KYC، بارامترات PSP).
التحقق بدقة من صحة الحقول المطلوبة في الأعمدة العلائقية ؛ JSONB - لـ «ذيل» الفروق الدقيقة.
الفهرسة: نقطة GIN حسب المسارات المستخدمة ؛ تجنب «جين عملاق واحد لكل شيء».

sql
-- partial GIN index only for documents with the desired key
CREATE INDEX idx_kyc_docnum ON kyc USING GIN ((data->'doc'->'number'))
WHERE data? 'doc';

12) النص الكامل والبحث الغامض

FTS المدمج: «متجه» + GIN ؛ للأخطاء المطبعية - "pg _ trgm'.
للبحث الصعب عن طريق السجلات/الألعاب - انطلق إلى محرك البحث (ES/OpenSearch)، وقم بتخزين الرابط/البيانات الوصفية في PG.

13) إمكانية الملاحظة والتنميط

pg_stat_statements: أعلى الاستفسارات البطيئة/المتكررة، التطبيع.
اشرح (تحليل، BUFFERS): اقرأ الخطط، ابحث عن مسح seq على المسارات الساخنة.
المقاييس: TPS، p95/p99، نقاط التفتيش، «النسخ المتماثل _ lag»، الجمود، bloat، حلقات AV، cache-hit ≥ 95٪.
التنبيهات: نمو التأخيرات، «الخمول في المعاملة»، مسح seq غير متوقع، عاصفة WAL.

14) مجمع الاتصال والاستفسارات المعدة

Puller (PgBouncer) في وضع «المعاملة» لحركة مرور الويب ؛ «جنس» - لمؤشرات طويلة/مكتب خلفي.
إعداد بيانات/بارامترات → خطط أقل تحليلا وثباتا.
الحد من الحد الأقصى لعدد النقاط الخلفية ('max _ connections' منخفض ؛ يتم الاستيلاء على المسبح بواسطة الصمام).

15) السلامة والامتثال

TLS في العبور، تشفير القرص، KMS/المفاتيح الأجنبية.
RBAC: الحد الأدنى من الحقوق، الفصل بين أدوار القراءة/الكتابة/الإدارة.
RLS (Row-Level Security) لسيناريوهات المستأجرين المتعددين.
PII: الإخفاء/التسلية، العمر الافتراضي.
مراجعة الحسابات: "pgaudit'/تحفيز التدقيق على الجداول الحرجة (المحفظة/دفتر الأستاذ).

16) نماذج نموذجية لـ iGaming

16. 1 محفظة ودفتر أستاذ (اتساق صارم)

Индексы: «المحفظة (player_id PK)»، «دفتر الأستاذ (player_id، ts DESC)» + «تشمل (delta_cents، السبب)».
تستكمل المعاملة الرصيد وتكتب إلى دفتر الأستاذ ؛ وتكرار شبه المزامنة ؛ ذاكرة التخزين المؤقت كإسقاط فقط.

16. 2 تاريخ الرهان (High TPS، Player/Time Reading)

التقسيم حسب اليوم/الأسبوع، BRIN حسب الوقت + B-Tree '(player_id، created_at DESC)'.
الاحتفاظ عبر «تقسيم الانفصال» → الأرشفة إلى OLAP.

16. 3 خطافات الويب PSP (بورستامي، ريتراي)

قائمة انتظار الأحداث "الخام" (الملحق فقط)، التقسيمات الزمنية، الفهرس الجزئي على 'status =' معلقة. "

الفراغ بواسطة 'idempotency _ key '/' psp _ tx' (UNIC).

17) قائمة التنفيذ المرجعية

1. ارتكب سياسة SLO والقراءة بعد الكتابة.
2. تصميم الفهارس الرئيسية للاستفسارات الحقيقية (ملف تعريف الاستعلام).
3. تشمل التقسيم حيث توجد أنماط الاحتفاظ/التذييل.
4. قم بإعداد AV/ANALYZE على الطاولات الساخنة، وراقب الانتفاخ/اللف.
5. ضبط الذاكرة، WAL ونقاط التفتيش لنوافذ الذروة.
6. ضع تجمع الاتصال و "pg _ stat _ commissions' ؛ احصل على تنبيهات.
7. التكرار + DR + PITR - مطلوب ؛ إجراء التدريبات.
8. قصر الخلية والشبكة على المسارات المطلوبة فقط ؛ استخدام الفهارس الجزئية.
9. تقليل مدة المعاملة، واستخدام «SKIP LOCKED» لقوائم انتظار العمل.
10. مراجعة الحسابات/دليل الأداء الموحد/التشفير/نموذج يحتذى به - قبل بدء الدفع.

18) أنتيباترن

مؤشر «عالمي» واحد على كل شيء ولا يوجد تحليل استفسار.
المعاملات الطويلة «المعلقة» («المعطلة في المعاملة») → نمو الحظر/التوقف.
اعتمد على نسخ طبق الأصل للقراءة بعد الكتابة دون تأخير.
قم بتخزين كل شيء في JSONB «فقط في حالة» وفهرسة المستند بأكمله باستخدام GIN واحد.
عدم وجود فراغ/تحليل للمراقبة وعدم رصد التأخيرات/نقاط التفتيش.
الهجرات الجماعية/DDL خلال ساعات الذروة.

19) مقتطفات مفيدة

خطة الاستعلام والمخزونات المؤقتة

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;

تنميط الاستعلام الثقيل

sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

تناوب الأطراف (فكرة)

sql
-- create a party for tomorrow
CREATE TABLE bets_2025_11_06 PARTITION OF bets
FOR VALUES FROM ('2025-11-06') TO ('2025-11-07');

موجز

PostgreSQL قادر على سحب «المال والحقيقة» على منصة iGaming مع زيادة خطية في الحمل - إذا كانت المؤشرات والحفلات والفراغ التلقائي و WAL/الذاكرة والنسخة المقلدة/DR وقابلية الملاحظة منضبطة. ابدأ بملف تعريف للاستفسارات و SLOs، وقم ببناء مؤشرات للأنماط الحقيقية، وعزل الطاولات الساخنة، وتشغيل النظافة التشغيلية الصارمة - وستحتفظ القاعدة بشكل متوقع بالبطولات وذروة المدفوعات.