PostgreSQL: بهترین شیوه ها و نمایه سازی

(بخش: تکنولوژی و زیرساخت)

خلاصه ای کوتاه

PostgreSQL «هسته حقیقت» برای پول، KYC و سوابق قانونی قابل توجه در iGaming است. این تضمین ACID، SQL قدرتمند و قابلیت توسعه را فراهم می کند. برای مقاومت در برابر قله مسابقات و PSP webhooks، آنها حیاتی هستند: طرح صالح، شاخص ها، پارتیشن بندی، خودکار خلاء، تنظیم WAL و مشاهده. در زیر سازنده شیوه ها و قالب ها است.

1) معماری و SLO

نقش PostgreSQL: رهبر برای نوشتن + کپی برای خواندن ؛ برای صفحه نمایش داغ - کش/پیش بینی (نمایش Redis/materialized).
نمونه SLO: کیف پول p99 'INSERT/UPDATE' ≤ 25-40 میلی ثانیه ؛ خواندن تعادل p99 ≤ 10-15 میلی ثانیه ؛ تاخیر ماکت ≤ 2-5 ثانیه ؛ دسترسی ≥ 99 9%.
سیاست خواندن پس از نوشتن: صفحه نمایش کاربر پس از یک معامله از رهبر خوانده می شود و یا در انتظار تاخیر تکرار.

2) طراحی شماتیک

عادی سازی هسته پول (کیف پول، دفتر کل) + denormalization برای خواندن (CQRS/پیش بینی).
محدودیت های سختگیرانه: «NOT NULL»، «CHECK»، «UNIQUE»، FK با هدف «ON DELETE/UPDATE» (RESTRICT/SET NULL/NO ACTION).
نسخه بندی طرح: مهاجرت بالا/پایین، پرچم های ویژگی ؛ اجتناب از شکستن تغییر نام زمان گرم.
شناسه: 'BIGINT' + دنباله (یا ULID/UUIDv7 برای توزیع). برای درج های داغ - توالی ها در یک جدول جداگانه/حجم WAL.

3) نمایه سازی: چه، کجا و چگونه

3. 1 درخت بی (به طور پیش فرض)

هنگامی که: مسابقات دقیق، محدوده، انواع، 'JOIN' توسط FK.
الگوها: «WHERE player_id = ؟»، «ORDER BY created_at DESC LIMIT 100».

• شاخص های چند ستونی - مطابق با شرایط.
• پوشش: 'INCLUDE (...)' для اسکن فقط فهرست.
• شاخص فردی تحت "سفارش توسط... 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' برای trigrams، '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 (جغرافیایی/محدوده/امضا)

زمان: موقعیت جغرافیایی (IP-geo، radii)، فواصل، نزدیکترین همسایه.
کمتر در هسته پول استفاده می شود، مفید برای محدودیت های جغرافیایی/بازی مسئول.

3. 4 برین (بزرگ «ضمیمه» جداول در زمان)

زمان: میلیاردها خط، همبستگی طبیعی در طول زمان (سیاهههای مربوط به شرط بندی/رویداد).
مزایا: اندازه کوچک، خدمات ارزان.
منفی: درشت selectivity → با پارتیشن بندی ترکیب شود.

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

3. 5 هش

به ندرت مورد نیاز: برابری توسط یک ستون در غیاب محدوده ؛ اغلب B-Tree کافی است.

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

شاخص جزئی: زیر مجموعه های داغ را تسریع می کند (فعال، "وضعیت =" در انتظار ").
شاخص بیان: کلید پیش محاسبه ('lower (email)', '(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 شاخص ضد گلوله

«شاخص برای همه چیز»: بیش از شاخص کند می کند پایین نوشتن و VACUUM.
شاخص های تکراری (همان ستون مجموعه/سفارش).
فهرست به یک ستون با کاردینالیتی بسیار کم (به عنوان مثال، «وضعیت» با مقادیر 2) - انجام جزئی.

4) تقسیم بندی

چرا: کاهش نفخ، سرعت بخشیدن به VACUUM/اسکن، تسهیل حفظ/آرشیو.
طرح ها: RANGE بر اساس تاریخ (روز/هفته) برای سیاهههای مربوط به شرط بندی ؛ HASH توسط 'player _ id' برای جداول بزرگ کاربر ؛ ترکیب شده است.
تمرین چرخش: ایجاد احزاب آینده در پیش، 'ATTACH PARTITION'، آرشیو قدیمی - 'DETACH' + جنبش.

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) درج/به روز رسانی با حداقل تکه تکه شدن

به روز رسانی HOT: نگه داشتن «fillfactor» (به عنوان مثال 90) در صفحات گسترده داغ برای فضای آزاد در صفحه.
TOAST: JSONB بزرگ/متون - نگه داشتن چشم در تراکم ؛ زمینه های بزرگ را در یک جدول جداگانه ذخیره کنید.
UPSERT: استفاده از "در درگیری... DO UPDATE 'با منطق idempointent.

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) معاملات، قفل ها و رقابت

سطح انزوا: 'متعهد به خواندن' برای اکثر مسیرها ؛ 'تکرار خواندن '/' SERIALABLE' به صورت نقطه ای (گزارش ها، دسته های آفلاین). آنها را برای مدت طولانی نگه ندارید.
انواع قفل: سطح ردیف (بدبینی 'برای UPDATE')، سطح جدول (DDL)، قفل مشاوره برای mutexes توزیع شده است.
بن بست: معاملات کوتاه, منظور یکنواخت از به روز رسانی اشخاص, وقفه («lock _ timeout», «statement _ timeout»).
صفهای وظیفه: «SKIP LOCKED» برای استخر کار توزیع شده.

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

7) خلاء خودکار، آمار و نفخ

خلاء/تجزیه و تحلیل: نگه داشتن آمار فعلی ('default _ statistics _ target')، تنظیم AV در جداول داغ (آستانه ماشه زیر).
مراقب باشید برای wraparound (سن (txid) <2 میلیارد)، «vacuum _ freeze _ min _ age».
کنترل نفخ: به طور منظم reindex/CLUSTER در شاخص های سنگین خارج از اوج ؛ پارتیشن بندی مقیاس مشکل را کاهش می دهد.

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٪ RAM (وابسته به مشخصات).
'کار _ مم': با عملیات! پیکربندی محافظه کارانه (به عنوان مثال، 8-64MB) و افزایش نقطه برای نقش گزارش.
'maintenance _ work _ mem': شاخص های بزرگ/بازیابی (task 512MB-2GB).

WAL و ایست های بازرسی

NVMe برای WAL، تک حجم ؛ 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 دقیقه، 'max _ wal _ size' در معرض تغییر، 'checkpoint _ completion _ target ≈ 0. 9`.
برای قرار دادن قله - درج دسته ای و گروه مرتکب.

9) تکرار و تحمل خطا

ماکت رهبر: همزمان به نزدیکترین (نیمه همگام) برای RPO≈0 -30c ؛ ناهمزمان برای خواندن/تجزیه و تحلیل.

ارتقاء و شمشیربازی: مدیر Patroni/replica ؛ «رهبر دوگانه»

آماده به کار داغ: 'hot _ standby _ feedback' با دقت (رشد نفخ), بهتر - اهلی معاملات طولانی در کپی.

10) پشتیبان گیری و PITRs

پشتیبان گیری کامل + افزایشی، نسخه های خارج از سایت ؛ 'archive _ command' برای WAL.
PITR: بازیابی را به نقطه زمان در غرفه ها بررسی کنید. تنظیم RPO/RTO (کیف پول - دقیقه، سیاهههای مربوط - ده ها دقیقه).
تمرینات DR (روز بازی): بررسی بهبودی منظم.

11) JSONB و مدل «مدار انعطاف پذیر»

JSONB - عالی برای ویژگی های به ندرت خواندن/متغیر (ابرداده KYC، پارامترهای PSP).
به شدت فیلدهای مورد نیاز را در ستون های رابطه ای تأیید کنید. JSONB - برای «دم» تفاوت های ظریف.
نمایه سازی: نقطه GIN توسط مسیرهای استفاده شده ؛ اجتناب از «یک 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: 'tsvector' + GIN ؛ برای تایپ - 'pg _ trgm'.
برای جستجوی دشوار توسط سیاهههای مربوط/بازی ها - به موتور جستجو (ES/OpenSearch) بروید و لینک/ابرداده را در PG ذخیره کنید.

13) قابلیت مشاهده و پروفایل

pg_stat_statements: نمایش داده شد بالا آهسته/مکرر، عادی سازی.
توضیح (تجزیه و تحلیل، بافر): برنامه ها را بخوانید، به دنبال اسکن seq در آهنگ های داغ باشید.
معیارها: TPS، p95/p99، ایست بازرسی، 'replication _ lag'، بن بست، نفخ، حلقه های AV، ≥ کش ضربه 95٪.
هشدارها: رشد تاخیرها، «بیکار در معامله»، اسکن غیر منتظره، طوفان WAL.

14) استخر اتصال و نمایش داده شد آماده

کشنده (PgBouncer) در حالت «معامله» برای ترافیک وب ؛ 'session' - برای نشانگرهای طولانی/دفتر پشتی.
عبارات/پارامترسازی آماده → تجزیه کمتر، برنامه های پایدار.
محدود کردن حداکثر تعداد پایانه ها ('max _ connections' low; استخر توسط دریچه گرفته شده است).

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 تاریخچه شرط بندی (TPS بالا، بازیکن/زمان خواندن)

تقسیم بندی بر اساس روز/هفته، برین بر اساس زمان + B-Tree '(player_id، created_at DESC)'.
نگهداری از طریق پارتیشن DETACH → بایگانی به OLAP.

16. 3 PSP Webhooks (burstami، Retrai)

صف رویدادهای "raw" (اضافه کردن فقط)، پارتیشنهای زمانی، ایندکس جزئی روی "status =" در حال انتظار "

Idempotency توسط 'idempotency _ key '/' psp _ tx' (منحصر به فرد).

17) چک لیست پیاده سازی

1. SLO و سیاست خواندن پس از نوشتن را مرتکب شوید.
2. طراحی شاخص های کلیدی برای نمایش داده شد واقعی (مشخصات پرس و جو).
3. شامل پارتیشن بندی که در آن الگوهای حفظ/ضمیمه وجود دارد.
4. تنظیم AV/ANALYZE در جداول داغ، نگه داشتن چشم در نفخ/wraparound.
5. تنظیم حافظه، WAL و ایست بازرسی برای پنجره اوج.

6. قرار دادن استخر اتصال و 'pg _ stat _ statements' ؛ هشدارها را دریافت کنید

7. تکرار + DR + PITR - مورد نیاز ؛ تمرینها را انجام دهید

8. JSONB و GIN را فقط به مسیرهای مورد نیاز محدود کنید. استفاده از شاخص های جزئی

9. به حداقل رساندن مدت زمان معامله، استفاده از «SKIP LOCKED» برای صف کار.
10. حسابرسی/PII/رمزگذاری/مدل نقش - قبل از شروع پرداخت.

18) ضد گلوله

یک شاخص «جهانی» در همه چیز و بدون تجزیه و تحلیل پرس و جو.
معاملات طولانی «حلق آویز» («بیکار در معامله») → رشد مسدود کردن/نفخ.
تکیه بر کپی برای خواندن پس از نوشتن بدون تاخیر.
همه چیز را در JSONB «فقط در مورد» ذخیره کنید و کل سند را با یک GIN فهرست کنید.
کنترل صفر VACUUM/ANALYZE و بدون نظارت بر عقب/بازرسی.
مهاجرت دسته جمعی/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/حافظه، replica/DR و مشاهده پذیری نظم و انضباط. با نمایه ای از پرس و جو ها و SLO ها شروع کنید، شاخص هایی را برای الگوهای واقعی ایجاد کنید، جداول داغ را جدا کنید، بهداشت عملیاتی دقیق را روشن کنید - و پایه به طور قابل پیش بینی مسابقات و پرداخت های پیک را حفظ خواهد کرد.