PostgreSQL: מיטב האימונים והאינדקס

(סעיף: טכנולוגיה ותשתיות)

תקציר

PostgreSQL הוא ”גרעין האמת” עבור כסף, KYC ורשומות משמעותיות מבחינה משפטית ב-iGaming. הוא מספק ערבויות חומצה, SQL עוצמתיות, ואפשרויות הרחבה. כדי לעמוד בפסגות של טורנירי PSP, הם קריטיים: מזימה כשירה, מדדים, מחיצה, ואקום אוטומטי, כיוונון WAL ויכולת תצפית. להלן המבנה של מנהגים ותבניות.

1) ארכיטקטורה ו ־ SLO

תפקיד postgreSQL: מוביל לכתיבה + העתקים לקריאה; למסכים חמים - מטמון/תחזיות (צפיות רדיס/ממומשות).
דוגמאות: p99 'הכנס/עדכון' ארנק מצולם 25-40 ms; p99 קריאת שיווי משקל 10-15 ms; העתק lag מאגר 2-5 s; זמינות 99. 9%.
מדיניות קריאה לאחר כתיבה: מסכי משתמש לאחר עסקה נקראים מהמנהיג או ממתינים לשכפול.

2) עיצוב סכמטי

נורמליזציה של ליבת הכסף (ארנקים, פנקס) + דנורמליזציה לקריאה (CQRS/תחזיות).
הגבלות קפדניות: "NOT NULl', 'Check', 'ייחודי', 'FK' עם מטרה 'על מחיקה/עדכון' (הגבלה/הגדרה NULL/NO ACTION).
סכימה: מעלה/מטה נדודים, דגלים מאפיינים; להימנע משבירת שם זמן חם.
זיהוי: רצפי BIGINt' + (או ULID/UUIDv7 להפצה). לכניסות חמות - רצפים בנפח טאבלספייס/WAL נפרד.

3) אינדקס: מה, איפה ואיך

3. 1 B-Tree (ברירת מחדל)

כאשר: התאמות מדויקות, טווחים, מיני, 'להצטרף' על ידי FK.
תבניות: ”איפה player_id =?”, ”סדר על ידי created_at DESC גבול 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' עבור 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 (גיאו/רכסים/חתימות)

כאשר: geolocation (IP-geo, radii), מרווחים, קרוב לשכן.
פחות בשימוש בליבת הכסף, שימושי עבור Geo-הגבלות/משחק אחראי.

3. 4 ברין (גדול ”תוספתן” שולחנות בזמן)

כאשר: מיליארדי קווים, מתאם טבעי לאורך זמן (רישומי הימורים/אירועים).
מקצוענים: גודל קטן, שירות זול.
חסרונות: סלקטיביות גסה = להיות משולב עם מחיצה.

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 Antipatterns Index

אינדקס לכל דבר: עודף מדדים מאט את הכתיבה ואת ואקום.
שכפול אינדקסים (אותה עמודה/סדר).
אינדקס לטור עם קרדינליות נמוכה מאוד (לדוגמה, 'סטטוס' עם 2 ערכים) - עשה חלקי.

4) מחיצה

למה: להפחית נפח, להאיץ את VACUUM/סריקות, להקל על שימור/ארכיון.
מזימות: טווח אחר תאריך (יום/שבוע) עבור רישומי הימורים; HASH by "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) החדרות/עדכונים עם פיצול מינימלי

עדכונים חמים: שמור 'fillfactor' (למשל. 90) על גיליונות אלקטרוניים חמים עבור מרחב חופשי על הדף.
טוסט: JSONB/טקסטים גדולים - לפקוח עין על דחיפה; אחסן שדות מגושמים בשולחן נפרד.
השתמש בקונפליקט... האם לעדכן "עם היגיון אידמפוטנטי.

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' נקודה (דיווחים, צרור מחובר). אל תחזיק אותם זמן רב.
סוגים של מנעולים: שורה-רמה (פסימיות 'FOR עדכון'), טבלה-רמה (DDL), מנעולי ייעוץ למוטקסים מבוזרים.
פערים: העברות קצרות, סדר אחיד של ישויות מעדכנות, פסקי זמן (”lock _ timeout',” peaction _ timeout').
תורים: ”דלג נעול” למאגר עבודה מבוזר.

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

7) ואקום אוטומטי, סטטיסטיקה ונפיחות

VACUUM/ANALIZE: שמור על הסטטיסטיקה הנוכחית ('ברירת מחדל _ סטטיסטיקה _ מטרה'), כוון AV על טבלאות חמות (סף ההדק להלן).
היזהרו מ ־ waraparound (גיל (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 (תלוי פרופיל).
'Work _ Mem': על ידי פעולה! הגדר בצורה שמרנית (לדוגמה, 8-64MB) והגביר את החוכמה לדווח על תפקידים.
אינדקסים גדולים/התאוששות (משימה 512MB-2GB).

WAL ומחסומים

NVMe עבור WAL, כרך אחד; 'wal _ דחיסה = על ".
'checkpoint _ time out' 10-15 min ',' max _ wal _ size 'בכפוף לשינוי,' checkpoint _ inspection _ tart 0. 9`.
לכניסת פסגות - הכנסותיה של הקבוצה והתחייבויות קבוצתיות.

9) שכפול וסובלנות פגומה

Leader reception: synchronous to the הקרוב ביותר (חצי-סינכרון) עבור RPO weather 0-30c; אסינכרוני לקריאה/אנליטיקה.
קידום וסיוף: פטרוני/מנהל העתק; לא כולל ”מנהיג כפול”.
המתנה חמה: ”hot _ standby _ feedback” בזהירות (צמיחה נפוחה), טוב יותר - לאלף עסקאות ארוכות על העתקים.

10) גיבויים ו ־ PITRs

גיבוי מלא + אינקרמנטלי, העתקים מחוץ לאתר; 'Archive _ פקודה' עבור WAL.
בדוק התאוששות לנקודת הזמן על היציעים; לווסת RPO/RTO (ארנקים - דקות, יומנים - עשרות דקות).
תרגילים: בדיקת התאוששות רגילה.

11) JSONB ומודל ”המעגל הגמיש”

JSONB - מצוין לקריאה/תכונות משתנות נדירות (KYC metadata, פרמטרים PSP).
בהחלט לאמת שדות דרושים בעמודות התייחסות; JSONB - עבור ”זנב” של ניואנסים.
אינדקס: נקודה ג 'ין על ידי נתיבים בשימוש; להימנע מ ”ג 'ין אחד ענק לכל דבר”.

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), ולאחסן את הקישור/metadata ב ־ PG.

13) יכולת תצפית ואפיון

pg_stat_statements: שאילתות איטיות/תכופות, נורמליזציה.
קרא תוכניות, חפש סריקה על מסלולים חמים.
Metrics: TPS, p95/p99, נקודות ביקורת, ”שכפול _ לג”, בלוקים, נפח, לולאות AV, מטמון-להיט - 95%.
התראות: גידול בפיגומים, ”סרק בעסקה”, סריקה בלתי צפויה, סערת WAL.

14) בריכת חיבור והכנת שאילתות

Puller (FigBoundscer) במצב 'transfaction' עבור תעבורת אינטרנט; 'Session' - לקורסים ארוכים/משרד אחורי.
הצהרות מוכנות/פרמטריזציה = פחות ניתוחים, תוכניות יציבות.
הגבל את המספר המקסימלי של אחוריים (”max _ contracts” low; על הבריכה משתלט השסתום).

15) בטיחות וציות

TLS במעבר, הצפנת דיסק, מפתחות KMS/זרים.
זכויות מינימליות, הפרדת תפקידי קריאה/כתיבה/אדמיניסטרציה.
(RLS (Row-Level Security עבור תרחישים רב-דיירים.
מיסוך/זהות בדויה, חיי מדף.
ביקורת: ”pgaudit ”/audit מפעיל על שולחנות קריטיים (ארנק/ספר חשבונות).

16) תבניות אופייניות ל ־ iGaming

16. ארנק 1 וספר חשבונות (עקביות קפדנית)

'ארנק (player_id PK)', 'ספר חשבונות (player_id, ts DESC)' + 'CALL (delta_cents, סיבה).
העסקה מעדכנת את היתרה וכותבת לספר החשבונות; שכפול חצי סינכרון; מטמון כהשלכה בלבד.

16. 2 היסטוריית ההימורים (TPS גבוה, קריאה בשחקן/זמן)

מחיצה ביום/שבוע, BRIN bY + B-Tree '(player_id, created_at DESC)'.
שימור באמצעות 'ניתוק מחיצה' לארכיון OLAP.

16. 3 webhooks PSP (בורסטמי, רטריי)

תור של אירועים ”גולמיים” (append-only), מחיצות זמן, אינדקס חלקי על status = ”תלוי ועומד”

idempotency by ”idempotency _ key ”/” psp _ tx” (ייחודי).

17) רשימת מימושים

1. לבצע את מדיניות ה-SLO ולקרוא אחרי כתיבה.
2. עיצוב אינדקסי מפתח לשאילתות אמיתיות (שאילתות פרופיל).
3. כולל חלוקה היכן שיש תבניות שימור/תוספתן.
4. הגדרת AV/ANALYZE על שולחנות חמים, לפקוח עין על נפח/צינור.
5. זיכרון מנגינה, WAL ומחסומים לחלונות שיא.
6. שים את מאגר החיבור ו "pg _ stat _ adventions'; קבל התראות.
7. שכפול + DR + PITR - נדרש; תרגילי התנהגות.
8. הגבל את JSONB ו-GIN רק לנתיבים הנדרשים; השתמש באינדקסים חלקיים.
9. למזער את משך ההעברה, להשתמש ”דלג נעול” לתור עבודה.
10. ביקורת חשבונות/PII/הצפנה/מודל לחיקוי - לפני תחילת התשלומים.

18) תרופות אנטי ־ פטריות

אינדקס ”אוניברסלי” אחד על כל דבר ואין ניתוח שאילתות.
עסקאות ”תלייה” ארוכות (”סרק בעסקה”).
להסתמך על העתקים לקריאה לאחר כתיבה ללא פיגור.
לאחסן הכל ב ־ JSONB ”ליתר ביטחון” ולמדוד את כל המסמך ב ־ GIN אחד.
אפס בקרה ואקום/אנליזה ולא ניטור של lags/נקודות ביקורת.
נדידה המונית/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, לבנות אינדקסים לדפוסים אמיתיים, לבודד שולחנות חמים, להפעיל היגיינה מבצעית קפדנית - והבסיס צפוי לשמור על טורנירים ותשלומי שיא.