PostgreSQL: en iyi uygulamalar ve indeksleme
(Bölüm: Teknoloji ve Altyapı)
Kısa Özet
PostgreSQL, para, KYC ve iGaming'deki yasal olarak önemli kayıtlar için "gerçeğin çekirdeği'dir. ACID garantileri, güçlü SQL ve genişletilebilirlik sağlar. Turnuvaların ve PSP web kitaplarının zirvelerine dayanmak için, bunlar kritiktir: yetkili şema, endeksler, bölümleme, otomatik vakum, WAL'ı ayarlama ve gözlemlenebilirlik. Aşağıda uygulamaların ve şablonların yapıcısı bulunmaktadır.
1) Mimari ve SLO
PostgreSQL rolü: okumak için + kopyaları yazmak için lider; Sıcak ekranlar için - önbellek/projeksiyonlar (Redis/materyalize görünümler).
SLO örnekleri: p99 'INSERT/UPDATE' cüzdan ≤ 25-40 ms; P99 denge okuma ≤ 10-15 ms; Kopya gecikme ≤ 2-5 s; kullanılabilirlik ≥ 99. 9%.
Yazma sonrası okuma ilkesi: Bir işlem liderden okunduktan veya bir çoğaltma gecikmesi bekledikten sonra kullanıcı ekranları.
2) Şematik tasarım
Paranın çekirdeğinin normalleştirilmesi (cüzdanlar, defter defteri) + okuma için denormalleştirme (CQRS/projeksiyonlar).
Sıkı kısıtlamalar: 'NOT NULL', 'CHECK', 'UNIQUE', FK hedefli 'ON DELETE/UPDATE' (RESTRICT/SET NULL/NO ACTION).
Şema sürüm oluşturma: yukarı/aşağı geçişler, özellik bayrakları; Sıcak zaman yeniden adlandırmayı kırmaktan kaçının.
Tanımlayıcılar: 'BIGINT' + dizileri (veya dağıtım için ULID/UUIDv7). Sıcak ekler için - ayrı bir masa boşluğunda/WAL hacminde diziler.
3) İndeksleme: ne, nerede ve nasıl
3. 1 B Ağacı (varsayılan)
Ne zaman: tam eşleşmeler, aralıklar, sıralar, FK tarafından 'JOIN'.
Desenler: 'WHERE player_id =?', 'ORDER BY created_at DESC LIMIT 100'.
- Çok sütunlu dizinler - Koşulların sırasını eşleştirin.
- Kaplama: 'INCLUDE (...)' для sadece indeks taraması.
- 'ORDER BY' altındaki bireysel indeks... Kasetlerde DESC '.
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 CIN (JSONB, diziler, tam metin)
Ne zaman: JSONB anahtar/yol arama, etiket dizileri, FTS.
Değişkenler: Trigramlar için 'cin _ trgm _ ops', 'jsonb _ path _ ops'/' jsonb _ ops'.
Uygulama: Arama alanlarını kesinlikle sınırlayın ve kardinalite hakkında düşünün.
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 (coğrafi/aralıklar/imzalar)
Ne zaman: Coğrafi konum (IP-geo, yarıçap), aralıklar, en yakın komşu.
Paranın özünde daha az kullanılır, coğrafi kısıtlamalar/sorumlu oyun için kullanışlıdır.
3. 4 BRIN (zaman içinde büyük "ek" tablolar)
Ne zaman: Milyarlarca satır, zaman içinde doğal korelasyon (bahis/etkinlik günlükleri).
Artıları: küçük boyutlu, ucuz hizmet.
Eksileri: kaba seçicilik - bölümleme ile birleştirilebilir.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Nadiren gerekli: aralıkların yokluğunda bir sütun ile eşitlik; Daha sık B-Tree yeterlidir.
3. 6 Kısmi ve ifadeler
Kısmi dizin: sıcak alt kümeleri hızlandırır (aktif, 'durum =' beklemede ').
Expression index: ön hesaplama anahtarı ('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 İndeks Antipatterns
"Her şey için indeks": endekslerin fazlalığı yazmayı ve VAKUM'u yavaşlatır.
Dizinleri çoğaltın (aynı sütun kümesi/sırası).
Çok düşük kardinaliteye sahip bir sütuna dizin (örneğin, 2 değere sahip 'durum') - kısmi yapın.
4) Bölümleme
Neden: şişkinliği azaltın, VAKUM/taramaları hızlandırın, tutma/arşivlemeyi kolaylaştırın.
Şemalar: Bahis günlükleri için tarihe (gün/hafta) göre RANGE; Büyük kullanıcı tabloları için 'player _ id'ile HASH; birleşik.
Rotasyon uygulaması: gelecekteki partileri önceden oluşturun, 'ATTACH PARTITION', eskilerin arşivi - 'DETACH' + hareketi.
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) Minimum parçalanma ile eklemeler/güncellemeler
SICAK güncellemeler: 'Fillfactor' tutun (örn. 90) sayfadaki boş alan için sıcak elektronik tablolarda.
TOAST: büyük JSONB/metinler - sıkıştırmaya dikkat edin; Büyük alanları ayrı bir tabloda saklayın.
UPPERT: 'ON CONFLICT' kullanın... Idempotent mantığı ile 'UPDATE' yapın.
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) İşlemler, kilitler ve rekabet
İzolasyon seviyeleri: Çoğu yol için 'READ COMMITTED'; 'TEKRARLANABİLİR OKUMA'/' HIZLANABİLİR' noktasal olarak (raporlar, çevrimdışı gruplar). Onları uzun süre tutmayın.
Kilit türleri: satır seviyesi ('GÜNCELLEME İÇİN' kötümserlik), tablo seviyesi (DDL), dağıtılmış muteksler için danışma kilitleri.
Deadlocks: kısa işlemler, güncellenen varlıkların tek tip sırası, zaman aşımları ('lock _ timeout', 'statement _ timeout').
Görev kuyrukları: Dağıtılmış iş havuzu için 'SKIP LOCKED'.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Otomatik vakum, istatistik ve şişirme
VACUUM/ANALYZE: güncel istatistikleri tutun ('default _ statistics _ target'), AV'yi sıcak tablolarda ayarlayın (aşağıdaki tetikleme eşiği).
Sarmalamaya dikkat edin (age (txid) <2 billion), 'vacuum _ freeze _ min _ age'.
Bloat kontrolü: ağır tepe dışı indekslerde düzenli reindeks/CLUSTER; Bölümleme, sorunun ölçeğini azaltır.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Hafıza, kontrol noktaları ve WAL
Bellek
'shared _ buffers': %20-25 RAM (profile bağımlı).
'work _ mem': operasyonla! Rolleri bildirmek için konservatif (örneğin 8-64MB) olarak yapılandırın ve noktasal olarak artırın.
'maintenance _ work _ mem': büyük indeksler/kurtarma (görev 512MB-2GB).
WAL ve kontrol noktaları
WAL için NVMe, tek hacim; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 dakika, 'max _ wal _ size' değişebilir, 'checkpoint _ completion _ target ≈ 0. 9`.
Uç eklemek için - toplu ekler ve grup taahhüt eder.
9) Çoğaltma ve hata toleransı
Lider çoğaltma: RPO≈0 -30c için en yakın (yarı senkronize) senkronize; Okuma/analiz için asenkron.
Promosyon ve eskrim: Patroni/çoğaltma yöneticisi; "Çifte lider" hariç.
Hot standby: 'hot _ standby _ feedback' dikkatle (bloat growth), daha iyi - replikalarda uzun işlemleri evcilleştirin.
10) Yedeklemeler ve PITR'ler
Tam + artımlı yedekleme, site dışı kopyalar; WAL için 'archive _ command'.
PITR: standlarda zaman noktasına kurtarma kontrol; RPO/RTO'yu düzenler (cüzdanlar - dakikalar, günlükler - onlarca dakika).
DR (oyun günü) egzersizleri: düzenli kurtarma kontrolü.
11) JSONB ve "esnek devre" modeli
JSONB - nadiren okunan/değişken nitelikleri için mükemmel (KYC meta verileri, PSP parametreleri).
İlişkisel sütunlarda gerekli alanları kesinlikle doğrulayın; JSONB - nüansların "kuyruğu" için.
Dizinleme: Kullanılan yollara göre nokta GIN; "Her şey için dev bir CIN'den kaçının".
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) Tam metin ve bulanık arama
Dahili FTS: 'tsvector' + GIN; yazım hataları için - 'pg _ trgm'.
Günlüklere/oyunlara göre zor bir arama için - arama motoruna (ES/OpenSearch) çıkın ve bağlantıyı/meta verileri PG'de saklayın.
13) Gözlemlenebilirlik ve profilleme
pg_stat_statements: üst yavaş/sık sorgular, normalleştirme.
EXPLAIN (ANALYZE, BUFFERS): planları okuyun, sıcak pistlerde seq taraması yapın.
Metrikler: TPS, p95/p99, checkpoints, 'replication _ lag', deadlocks, bloat, AV loops, cache-hit ≥ %95.
Uyarılar: gecikmelerin büyümesi, "işlemde boşta kalma", beklenmedik seq taraması, WAL fırtınası.
14) Bağlantı havuzu ve hazırlanmış sorgular
Web trafiği için 'işlem' modunda Puller (PgBouncer); 'session' - uzun imleçler/arka ofis için.
Hazırlanan ifadeler/parametrelendirme - daha az ayrıştırma, kararlı planlar.
Maksimum arka uç sayısını sınırla ('max _ connections' düşük; Havuz vana tarafından devralınır).
15) Güvenlik ve uyumluluk
TLS aktarımı, disk şifrelemesi, KMS/yabancı anahtarlar.
RBAC: minimal haklar, okuma/yazma/yönetici rollerinin ayrılması.
Çok kiracılı senaryolar için RLS (Row-Level Security).
PII: maskeleme/takma ad, raf ömrü.
Denetim: Kritik tablolarda (cüzdan/defter) 'pgaudit'/denetim tetikleyicileri.
16) iGaming için tipik şablonlar
16. 1 Cüzdan ve defter (katı tutarlılık)
Индексы: 'cüzdan (player_id PK)', 'defter (player_id, ts DESC)' + 'INCLUDE (delta_cents, sebep)'.
İşlem bakiyeyi günceller ve deftere yazar; Yarı senkronize çoğaltma; Sadece projeksiyon olarak önbellek.
16. 2 Bahis Geçmişi (Yüksek TPS, Oyuncu/Zaman Okuma)
Güne/haftaya göre bölümleme, zamana göre BRIN + B Ağacı '(player_id, created_at DESC)'.
'DETACH PARTITION' yoluyla tutma - OLAP'a arşivleme.
16. 3 Webhooks PSP (burstami, retrai)
"Ham" olayların kuyruğu (yalnızca append-only), zaman bölümleri, 'status =' üzerinde kısmi dizin beklemede
'Idempotency _ key'/' psp _ tx' (UNIQUE) tarafından idempotency.
17) Uygulama kontrol listesi
1. SLO ve yazma sonrası okuma politikasını uygulayın.
2. Gerçek sorgular için anahtar dizinleri tasarlayın (sorgu profili).
3. Tutma/ek desenlerin olduğu bölümlemeleri dahil edin.
4. Sıcak masalarda AV/ANALYZE ayarlayın, şişkinliğe/sarmalamaya dikkat edin.
5. Zirve pencereleri için bellek, WAL ve kontrol noktalarını ayarlayın.
6. Bağlantı havuzunu ve 'pg _ stat _ statements'; Uyarı alın.
7. Replikasyon + DR + PITR - gerekli; tatbikatlar yapın.
8. JSONB ve GIN'i yalnızca gerekli yollarla sınırlandırın; Kısmi indeksleri kullanın.
9. İşlem süresini en aza indirin, iş kuyrukları için 'SKIP LOCKED' kullanın.
10. Denetim/PII/şifreleme/rol modeli - ödemelere başlamadan önce.
18) Antipatterns
Her şeyde bir "evrensel" dizin ve sorgu analizi yok.
Uzun "asılı" işlemler ('işlemde boşta') - büyümeyi engelleme/şişirme.
Gecikmeden okuma-yazma için kopyalara güvenin.
Her şeyi JSONB'de "Her ihtimale karşı" depolayın ve tüm belgeyi bir GIN ile endeksleyin.
Sıfır kontrol vakum/analiz ve gecikmeler/kontrol noktaları izleme yok.
Yoğun saatlerde toplu göçler/DDL.
19) Yararlı snippet'ler
Sorgu planı ve tamponlar
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Ağır sorgu profilleme
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;Parti rotasyonu (fikir)
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');Özet
PostgreSQL, yükte doğrusal bir artışla iGaming platformunda "para ve gerçeği" çekebilir - endeksler, taraflar, otomatik vakum, WAL/bellek, replika/DR ve gözlemlenebilirlik disiplinli ise. Sorguların ve SLO'ların bir profili ile başlayın, gerçek kalıplar için endeksler oluşturun, sıcak tabloları izole edin, sıkı operasyonel hijyeni açın - taban tahmin edilebilir bir şekilde turnuvaları ve en yüksek ödemeleri tutacaktır.