PostgreSQL: सर्वोत्तम अभ्यास और अनुक्रमण

(धारा: प्रौद्योगिकी और बुनियादी ढांचा)

संक्षिप्त सारांश

PostgreSQL पैसे, KYC और iGaming में कानूनी रूप से महत्वपूर्ण रिकॉर्ड के लिए "सत्य की कर्नेल" है। यह ACID गारंटी, शक्तिशाली SQL और विस्तार प्रदान करता है। टूर्नामेंट और पीएसपी वेबहूक की चोटियों का सामना करने के लिए, वे महत्वपूर्ण हैं: सक्षम योजना, सूचकांक, विभाजन, ऑटो-वैक्यूम, ट्यूनिंग डब्ल्यूएएल और अवलोकन। नीचे प्रथाओं और टेम्पलेट का निर्माता है।

1) वास्तुकला और एसएलओ

PostgreSQL भूमिका: पढ़ ने के लिए लिखने के लिए नेता + प्रतिकृतियां; हॉट स्क्रीन के लिए - कैश/अनुमान (Redis/भौतिक दृश्य)।

SLO उदाहरण: p99 'INSERT/UPDATE' बटुआ ≤ 25-40 ms; p99 बैलेंस रीडिंग ≤ 10-15 एमएस; प्रतिकृति अंतराल ≤ 2-5 एस; उपलब्धता ≥ 99। 9%.

रीड-आफ्टर-राइट पॉलिसी: लेनदेन के बाद उपयोगकर्ता स्क्रीन को नेता से पढ़ा जाता है या प्रतिकृति अंतराल की प्रतीक्षा की जाती है।

2) योजनाबद्ध डिजाइन

पैसे के मूल का सामान्यीकरण (पर्स, खाता) + पढ़ ने के लिए विमुद्रीकरण (CQRS/अनुमानों)।

सख्त प्रतिबंध: 'NOT NULL', 'CHECK', 'UNGENT', FK को लक्षित 'ON DELETE/UPDATE' (CONCENT/SET NULL/NO C)।

स्कीमा वर्शनिंग: अप/डाउन माइग्रेशन, फ्लैग्स की सुविधा; गर्म समय का नाम बदलने से बचें।

पहचानकर्ता: 'BIGINT' + अनुक्रम (या ULID/UUIDv7 वितरण के लिए)। गर्म आवेषण के लिए - एक अलग चम्मच/WAL वॉल्यूम में अनुक्रम।

3) अनुक्रमण: क्या, कहाँ और कैसे

3. 1 बी-ट्री (डिफ़ॉल्ट)

कब: FK द्वारा सटीक मैच, रेंज, प्रकार, 'JUN'।

पैटर्न: 'WHE player_id =?', 'ऑर्डर बाय created_at DESC LIMITE 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।

वेरिएंट: trigrams के लिए '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 (भू/रेंज/हस्ताक्षर)

कब: जियोलोकेशन (आईपी-जियो, रेडी), अंतराल, निकटतम-पड़ोसी।

पैसे के मूल में कम उपयोग किया जाता है, भू-प्रतिबंध/जिम्मेदार खेल के लिए उपयोगी है।

3. 4 ब्रिन (समय में बड़ी "परिशिष्ट" तालिकाएं)

कब: अरबों लाइनें, समय के साथ प्राकृतिक सहसंबंध (सट्टेबाजी/घटना लॉग)।

पेशेवरों: छोटा आकार, सस्ती सेवा।

विपक्ष: मोटे चयनात्मकता को विभाजन के साथ जोड़ा जाए।

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

3. 5 हैश

शायद ही कभी आवश्यकता होती है: सीमाओं की अनुपस्थिति में एक स्तंभ द्वारा समानता; अधिक बार बी-ट्री पर्याप्त है।

3. 6 आंशिक और अभिव्यक्ति

आंशिक सूचकांक: गर्म उप-सेट (सक्रिय, 'स्थिति =' लंबित ") को त्वरित करता है।

अभिव्यक्ति सूची: प्रीकॉम्प्यूट कुंजी ('लोअर (ईमेल)', '(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) विभाजन

क्यों: ब्लोट को कम करें, VECUUM/स्कैन को गति दें, प्रतिधारण/संग्रह की सुविधा दें।

योजनाएं: सट्टेबाजी लॉग के लिए तारीख (दिन/सप्ताह) द्वारा रेंज; बड़े उपयोगकर्ता तालिकाओं के लिए 'player _ id' द्वारा HASH; संयुक्त।

रोटेशन अभ्यास: भविष्य की पार्टियों को अग्रिम में बनाएं, 'ATTACH PARTION', पुराने लोगों का संग्रह - '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) न्यूनतम विखंडन के साथ सम्मिलन/अद्यतन

हॉट अपडेट: 'फिलफैक्टर' रखें (जैसे। पृष्ठ पर मुफ्त स्थान के लिए गर्म स्प्रेडशीट पर 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) लेन-देन, ताले और प्रतियोगिता

अलगाव का स्तर: अधिकांश रास्तों के लिए 'READ DOUP 'REPEATABLE READ '/' SERIALIZABLE' पॉइंटवाइज़ (रिपोर्ट, ऑफ़ लाइन बैच)। उन्हें लंबे समय तक मत पकड़ो।

ताले के प्रकार: पंक्ति-स्तर (निराशावाद 'अद्यतन के लिए'), टेबल-स्तर (डीडीएल), वितरित म्यूटेक्स के लिए सलाहकार ताले।

डेडलॉक: लघु लेनदेन, अद्यतन संस्थाओं का समान क्रम, टाइमआउट ('लॉक _ टाइमआउट', 'स्टेटमेंट _ टाइमआउट')।

कार्य कतारें: वितरित कार्य पूल के लिए 'SKIP LOCKED'।

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

7) ऑटो वैक्यूम, आंकड़े और ब्लोट

VECUUM/ANALIZE: वर्तमान आँकड़े रखें ('default _ statics _ targe'), गर्म तालिकाओं पर AV ट्यून करें (नीचे ट्रिगर सीमा)।

रैपराउंड के लिए देखें (उम्र (txid) <2 बिलियन), 'वैक्यूम _ freeze _ min _ age'.

ब्लोट नियंत्रण: भारी ऑफ-पीक सूचकांकों पर नियमित बारहसिंगा/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

मेमोरी

'शेयर _ बफर्स': 20-25% रैम (प्रोफ़ाइल निर्भर)।

'वर्क _ मेम': ऑपरेशन द्वारा! रूढ़िवादी रूप से कॉन्फ़िगर करें (उदाहरण के लिए, 8-64MB) और रिपोर्टिंग भूमिकाओं के लिए पॉइंटवाइ

'maintenance _ work _ mem': बड़े सूचकांक/वसूली (कार्य 512MB-2GB)।

WAL और चौकियों

WAL के लिए NVMe, एकल वॉल्यूम; 'wal _ compression = ऑन'।

'चेकपॉइंट _ टाइमआउट' 10-15 मिनट, 'मैक्स _ वाल _ साइज़' परिवर्तन के अधीन, 'चेकपॉइंट _ कम्प्लीशन _ लक्ष्य ≈ 0। 9`.

चोटियों को सम्मिलित करने के लिए - बैच आवेषण और समूह कमिट।

9) प्रतिकृति और दोष सहिष्णुता

नेता प्रतिकृति: RPO≈0 -30c के लिए निकटतम (अर्ध-सिंक) के लिए समकालिक; पढ़ ने/एनालिटिक्स के लिए अतुल्यकालिक।

पदोन्नति और बाड़ लगाना: पैट्रोनी/प्रतिकृति प्रबंधक; "दोहरे नेता" को छोड़ कर।

हॉट स्टैंडबाय: 'हॉट _ स्टैंडबाय _ फीडबैक' सावधानीपूर्वक (ब्लोट ग्रोथ), प्रतिकृतियों पर बेहतर - लंबे लेनदेन।

10) बैकअप और पीआईटीआर

पूर्ण + वृद्धिशील बैकअप, ऑफसाइट प्रतियां; WAL के लिए 'archive _ command'।

PITR: स्टैंड पर समय बिंदु पर वसूली की जांच करें; RPO/RTO (पर्स - मिनट, लॉग - दसियों मिनट) को विनियमित करें।

डीआर (खेल दिवस) अभ्यास: नियमित वसूली की जाँच।

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) पूर्ण पाठ और फजी खोज

बिल्ट-इन एफटीएस: 'tsvector' + GIN; टाइपोस के लिए - 'pg _ trgm'।

लॉग/गेम द्वारा एक कठिन खोज के लिए - खोज इंजन (ईएस/ओपनसर्च) पर ले जाएं, और पीजी में लिंक/मेटाडेटा संग्रहीत करें।

13) अवलोकन और प्रोफाइलिंग

pg_stat_statements: शीर्ष धीमी/लगातार प्रश्न, सामान्यीकरण।

EXPLEVE (ANALIZE, BUFERS): योजनाओं को पढ़ें, गर्म पटरियों पर seq स

मेट्रिक्स: TPS, p95/p99, चौकियां, 'प्रतिकृति _ लैग', गतिरोध, ब्लोट, एवी लूप, कैश-हिट ≥ 95%।

अलर्ट: लैग्स की वृद्धि, "लेनदेन में निष्क्रिय", अप्रत्याशित seq स्कैन, WAL तूफान।

14) कनेक्शन पूल और तैयार प्रश्न

वेब ट्रैफिक के लिए 'लेनदेन' मोड में पुलर (PgBouncer); 'सेशन' - लंबे कर्सर/बैक ऑफिस के लिए।

तैयार बयान/पैरामेटराइजेशन - कम पार्सिंग, स्थिर योजनाएं।

बैकेंड की अधिकतम संख्या सीमित करें ('max _ connections' कम; पूल को वाल्व द्वारा लिया जाता है)।

15) सुरक्षा और अनुपालन

टीएलएस पारगमन, डिस्क एनक्रिप्शन, केएमएस/विदेशी कुंजी में।

RBAC: न्यूनतम अधिकार, पढ़ ने/लिखने/व्यवस्थापक भूमिकाओं का पृथक्करण।

बहु-किरायेदार परिदृश्यों के लिए आरएलएस (रो-लेवल सिक्योरिटी)।

PII: मास्किंग/अलियासिंग, शेल्फ लाइफ।

ऑडिट: क्रिटिकल टेबल (वॉलेट/लेजर) पर 'pgaudit '/ऑडिट ट्रिगर।

16) iGaming के लिए विशिष्ट टेम्पलेट

16. 1 बटुआ और खाता (सख्त स्थिरता)

Индексы: 'वॉलेट (player_id PK)', 'लेजर (player_id, ts DESC)' + 'ALICE (delta_cents, कारण)'।

लेनदेन शेष को अद्यतन करता है और खाते को लिखता है; अर्ध-सिंक प्रतिकृति; केवल प्रक्षेपण के रूप में कैश।

16. 2 सट्टेबाजी इतिहास (उच्च टीपीएस, खिलाड़ी/समय पढ़ ना)

दिन/सप्ताह द्वारा विभाजन, ब्रिन द्वारा समय + बी-ट्री '(player_id, created_at DESC)'।

OLAP के लिए संग्रह 'DETACH PARTION' के माध्यम से प्रतिधारण।

16. 3 वेबहूक पीएसपी (बर्स्टामी, रेट्राई)

"कच्ची" घटनाओं की कतार (केवल), समय विभाजन, 'स्टैटस =' लंबित पर आंशिक सूचकांक।

'idempotency _ key '/' psp _ tx' (UNABLE) द्वारा पहचान.

17) कार्यान्वयन चेकलिस्ट

1. SLO और रीड-आफ्टर-राइट पॉलिसी कमिट करें।

2. वास्तविक प्रश्नों (क्वेरी प्रोफ़ाइल) के लिए डिज़ाइन कुंजी अनुक

3. विभाजन शामिल करें जहाँ अवधारण/परिशिष्ट पैटर्न हैं।

4. गर्म तालिकाओं पर एवी/विश्लेषण सेट करें, ब्लोट/रैपराउंड पर नजर रखें।

5. शिखर विंडो के लिए ट्यून मेमोरी, WAL और चौकियों।

6. कनेक्शन पूल और 'pg _ stat _ stations' डालें; अलर्ट मिलता है।

7. प्रतिकृति + DR + PITR - आवश्यक; व्यायाम करें।

8. JSONB और GIN को केवल आवश्यक रास्तों तक सीमित करें; आंशिक सूचकांक का उपयोग करें।

9. लेनदेन की अवधि को न्यूनतम करें, कार्य कतारों के लिए 'SKIP LOCKED' का उपयोग करें

10. भुगतान शुरू करने से पहले ऑडिट/पीआईआई/एन्क्रिप्शन/रोल मॉडल।

18) एंटीपैटर्न

हर चीज पर एक "सार्वभौमिक" सूचकांक और कोई क्वेरी विश्लेषण न

लंबे "हैंगिंग" लेनदेन ('लेनदेन में निष्क्रिय') → अवरुद्ध/ब्लोट वृद्धि।

अंतराल के बिना पढ़ ने के बाद लिखने के लिए प्रतिकृतियों पर भरोसा करें।

JSONB में सब कुछ स्टोर करें "बस मामले में" और एक GIN के साथ पूरे दस्तावेज़ को सूचीबद्ध करें।

शून्य नियंत्रण VECUUM/ANALYZE और लैग/चौकियों की कोई निगरानी नहीं।

पीक आवर्स के दौरान बड़े पैमाने पर पलायन/डीडीएल।

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/मेमोरी, प्रतिकृति/डीआर और अवलोकन अनुशासित हैं। प्रश्नों और एसएलओ की एक प्रोफ़ाइल के साथ शुरू करें, वास्तविक पैटर्न के लिए सूचकांक का निर्माण करें, गर्म तालिकाओं को अलग करें, सख्त परिचालन स्वच्छता चालू करें - और आधार अनुमानित रूप से टूर्नामेंट और शिखर भुगतान रखेगा।