PostgreSQL: najlepsze praktyki i indeksowanie
(Sekcja: Technologia i infrastruktura)
Krótkie podsumowanie
PostgreSQL jest „jądrem prawdy” dla pieniędzy, KYC i legalnie istotnych rekordów w iGaming. Zapewnia gwarancje ACID, potężny SQL i rozszerzalność. Aby wytrzymać szczyty turniejów i haków PSP, są one krytyczne: kompetentny schemat, indeksy, partycjonowanie, auto-próżnia, dostrajanie WAL i obserwowalność. Poniżej znajduje się konstruktor praktyk i szablonów.
1) Architektura i SLO
PostgreSQL rola: lider do pisania + repliki do czytania; dla ekranów gorących - pamięć podręczna/projekcje (Redis/zmaterializowane widoki).
Przykłady SLO: p99 portfel „INSERT/UPDATE” ≤ 25-40 ms; p99 odczyt równowagi ≤ 10-15 ms; replika lag ≤ 2-5 s; dostępność ≥ 99. 9%.
Zasady odczytu po zapisie: ekrany użytkowników po transakcji są odczytywane z lidera lub czekają na opóźnienie replikacji.
2) Projekt schematyczny
Normalizacja rdzenia pieniędzy (portfele, księga) + denormalizacja do odczytu (CQRS/projekcje).
Ścisłe ograniczenia: „NIE NIEWAŻNE”, „SPRAWDŹ”, „UNIKALNE”, FK z ukierunkowanym „USUŃ/UAKTUALNIJ” (RESTRICT/SET NULL/NO ACTION).
Wersioning schematu: migracje w górę/w dół, flagi funkcji; unikać łamania hot-time przemiany.
Identyfikatory: sekwencje „BIGINT” + (lub ULID/UUIDv7 do dystrybucji). Dla wstawek gorących - sekwencje w oddzielnej przestrzeni tablicy/objętości WAL.
3) Indeksowanie: co, gdzie i jak
3. 1 Drzewo B (domyślnie)
Kiedy: dokładne mecze, zakresy, rodzaje, 'JOIN' przez FK.
Wzory: „WHERE player_id =?”, „ORDER BY created_at DESC LIMIT 100”.
- Multi-column indexes - Dopasuj kolejność warunków.
- Obejmujące: „INCLUDE (...)” дла index-only scan.
- Indeks indywidualny "ORDER BY... DESC 'na „taśmach”.
sql
CREATE INDEX idx_tx_player_created_desc
ON tx (player_id, created_at DESC) INCLUDE (amount_cents, status);3. 2 GIN (JSONB, tablice, pełny tekst)
Kiedy: wyszukiwanie klucza/ścieżki JSONB, tablice znaczników, FTS.
Warianty: 'gin _ trgm _ ops' dla trygramów,' jsonb _ path _ ops'/' jsonb _ ops'.
Praktyka: Ściśle ograniczyć pola wyszukiwania i myśleć o kardynalności.
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/zakresy/podpisy)
Kiedy: geolokacja (IP-geo, radii), odstępy czasu, najbliższy sąsiad.
Mniej wykorzystywane w rdzeniu pieniędzy, przydatne do geo-ograniczeń/odpowiedzialnej gry.
3. 4 BRIN (duże tabele „dodatek” w czasie)
Kiedy: Miliardy linii, naturalna korelacja w czasie (dzienniki zakładów/zdarzeń).
Plusy: mały rozmiar, tania obsługa.
Minusy: gruba selektywność → być połączone z przegrody.
sql
CREATE INDEX idx_bets_brin ON bets USING BRIN (created_at) WITH (pages_per_range = 64);3. 5 Hash
Rzadko potrzebne: równość przez jedną kolumnę w przypadku braku zakresów; częściej wystarczy B-Tree.
3. 6 Wyrażenia częściowe i wyrażenia
Indeks częściowy: przyspiesza podzespoły gorące (aktywne, 'status =' oczekujące ').
Indeks wyrażenia: klucz precomputerowy ('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 Indeks Antipatterns
„Indeks dla wszystkiego”: nadmiar indeksów spowalnia pisanie i VACUUM.
Duplikat indeksów (ten sam zbiór kolumn/kolejność).
Indeks do kolumny o bardzo niskiej kardynalności (na przykład 'status' z 2 wartościami) - zrobić częściowe.
4) Podział
Dlaczego: zmniejszyć wzdęcia, przyspieszyć VACUUM/skany, ułatwić retencję/archiwum.
Schematy: ZAKRES według daty (dzień/tydzień) dla dzienników zakładów; HASH przez 'player _ id' dla dużych tabel użytkownika; łącznie.
Praktyka rotacyjna: tworzenie przyszłych stron z wyprzedzeniem, „ATTACH PARTITION”, archiwum starych - „DETACH” + ruch.
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) Wstawki/aktualizacje z minimalnym rozdrobnieniem
Aktualizacje HOT: Zachowaj 'fillfactor' (np. 90) na gorących arkuszach kalkulacyjnych za darmo miejsca na stronie.
TOAST: duże JSONB/teksty - mieć oko na zagęszczenie; Przechowywać obszerne pola w oddzielnej tabeli.
Używanie "w konflikcie... DO UPDATE 'z idempotentną logiką.
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) Transakcje, zamki i konkurencja
Poziomy izolacji: „CZYTAJ ZOBOWIĄZANY” dla większości ścieżek; "POWTARZALNY ODCZYt'/" SERIALIZOWALNY" punkt zwrotny (raporty, partie offline). Nie trzymaj ich długo.
Rodzaje zamków: poziom wiersza (pesymizm' FOR UPDATE "), poziom tabeli (DDL), zamki doradcze dla muteksów rozproszonych.
Impas: krótkie transakcje, jednolity porządek podmiotów aktualizujących, terminy („lock _ timeout”, „statement _ timeout”).
Kolejki zadań: 'SKIP LOCKED' dla rozproszonej puli zadań.
sql
-- batch processor removes work without racing
SELECT id FROM jobs
WHERE status='pending'
FOR UPDATE SKIP LOCKED
LIMIT 100;7) Próżnia automatyczna, statystyki i wzdęcia
VACUUM/ANALYZE: zachować bieżące statystyki („default _ statistics _ target”), tune AV na gorących tabel (próg spustowy poniżej).
Uważaj na owijanie (wiek (txid) <2 mld), 'próżnia _ zamrażanie _ min _ age'.
sterowanie wzdęciami: regularne renifery/CLUSTER na ciężkich wskaźnikach off-peak; podział zmniejsza skalę problemu.
sql
ALTER TABLE tx SET (
autovacuum_vacuum_scale_factor = 0. 05,
autovacuum_analyze_scale_factor = 0. 02,
autovacuum_vacuum_cost_limit = 4000
);8) Pamięć, punkty kontrolne i WAL
Pamięć
'shared _ bufory': 20-25% RAM (profil zależny).
'work _ mem': przez działanie! Konfigurować ostrożnie (na przykład 8-64MB) i zwiększyć punkt widzenia dla ról sprawozdawczych.
„maintenance _ work _ mem”: duże indeksy/odzyskiwanie (zadanie 512MB-2GB).
WAL i punkty kontrolne
NVMe dla WAL, pojedyncza objętość; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 min, 'max _ wal _ size' podlegający zmianie, 'checkpoint _ completion _ target _ 0. 9`.
Do wstawiania pików - wstawki wsadowe i popełnienia grupowe.
9) Replikacja i tolerancja uszkodzeń
Replika lidera: synchroniczna z najbliższą (półsynchronizacją) dla RPO ≤ 0 -30c; asynchroniczny do odczytu/analizy.
Promocja i ogrodzenie: Patroni/replica manager; wykluczyć „podwójnego lidera”.
Hot standby: 'hot _ standby _ feedback' starannie (wzrost wzdęcia), lepiej - oswoić długie transakcje na replikach.
10) Kopie zapasowe i PITR
Pełna + dodatkowa kopia zapasowa, kopie offsite; „archive _ command” dla WAL.
PITR: sprawdzenie odzysku do punktu czasowego na stoiskach; regulować RPO/RTO (portfele - minuty, dzienniki - dziesiątki minut).
DR (dzień gry) ćwiczenia: regularna kontrola odzysku.
11) JSONB i model „elastycznego obwodu”
JSONB - doskonały dla atrybutów rzadko odczytywanych/zmiennych (metadane KYC, parametry PSP).
Ściśle zatwierdzać wymagane pola w kolumnach relacyjnych; JSONB - dla „ogona” niuansów.
Indeksowanie: punkt GIN według wykorzystanych ścieżek; unikać „jednego giganta GIN za wszystko”.
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) Pełny tekst i niewyraźne wyszukiwanie
Wbudowany FTS: „tsvector” + GIN; dla typos - 'pg _ trgm'.
Dla trudnego wyszukiwania przez dzienniki/gry - wyjdź do wyszukiwarki (ES/OpenSearch) i zapisz link/metadane w PG.
13) Obserwowalność i profilowanie
pg_stat_statements: top slow/frequent queries, normalizacja.
EXPLAIN (ANALYZE, BUFFERS): przeczytaj plany, poszukaj skanowania seq na gorących ścieżkach.
Wskaźniki: TPS, p95/p99, punkty kontrolne, „replikacja _ lag”, impasy, wzdęcia, pętle AV, cache-hit ≥ 95%.
Wpisy: wzrost opóźnień, „bezczynność w transakcji”, nieoczekiwany skan seq, burza WAL.
14) Pula połączeń i przygotowane zapytania
Puller (PgBouncer) w trybie „transakcji” dla ruchu internetowego; „sesja” - dla długich kursorów/zaplecza.
Przygotowane oświadczenia/parametryzacja → mniej parsing, stabilne plany.
Ograniczyć maksymalną liczbę podświetleń („max _ connections” low; basen przejęty jest przez zawór).
15) Bezpieczeństwo i zgodność
TLS w tranzycie, szyfrowanie dysku, KMS/klucze zagraniczne.
RBAC: minimalne prawa, oddzielenie ról read/write/admin.
RLS (Row-Level Security) dla scenariuszy dla wielu najemców.
PII: maskowanie/aliasing, okres trwałości.
Audyt: „pgaudit ”/wyzwalacze audytu na tabelach krytycznych (portfel/księga).
16) Typowe szablony dla iGaming
16. 1 Portfel i księga (ścisła konsystencja)
Индекса: „portfel (player_id PK)”, „ledger (player_id, ts DESC)” + „INCLUDE (delta_cents, powód)”.
Transakcja aktualizuje saldo i zapisuje do księgi; replikacja półsynchronizacyjna; cache tylko jako projekcja.
16. 2 Historia zakładów (High TPS, Player/Time Reading)
Podział według dnia/tygodnia, BRIN według czasu + B-Tree '(player_id, created_at DESC)'.
Retencja za pomocą 'separacji partycji' → archiwizacja do OLAP.
16. 3 Webhooks PSP (burstami, retrai)
Kolejka „surowych” zdarzeń (tylko dodatek), przegrody czasowe, indeks częściowy na 'status =' oczekujący '
Idempotencja przez 'idempotence _ key '/' psp _ tx' (UNIQUE).
17) Lista kontrolna wdrażania
1. Popełnić SLO i read-after-write zasady.
2. Projektowanie indeksów kluczy do prawdziwych zapytań (profil zapytania).
3. Należy uwzględnić partycje, w których występują wzory retencji/dodatków.
4. Ustaw AV/ANALYZE na gorących stołach, pilnuj wzdęcia/owijania.
5. Pamięć tune, WAL i punkty kontrolne dla okien szczytowych.
6. Umieść pulę połączeń i 'pg _ stat _ statements'; dostać wpisy.
7. Replikacja + DR + PITR - wymagane; prowadzenie ćwiczeń.
8. ograniczyć JSONB i GIN do wymaganych ścieżek; używać indeksów częściowych.
9. Zminimalizuj czas trwania transakcji, użyj 'SKIP LOCKED' do kolejek roboczych.
10. Model audytu/PII/szyfrowania/roli - przed rozpoczęciem płatności.
18) Antypattery
Jeden „uniwersalny” indeks na wszystko i brak analizy zapytań.
Długie transakcje „wiszące” („bezczynne w transakcji”) → wzrost blokowania/wzdęcia.
Polegaj na replikach do odczytu po zapisie bez opóźnienia.
Przechowywać wszystko w JSONB „na wszelki wypadek” i indeksować cały dokument jednym GIN.
Zero kontroli VACUUM/ANALYZE i brak monitorowania opóźnień/punktów kontrolnych.
Masowe migracje/DDL w godzinach szczytu.
19) Przydatne snippety
Plan zapytania i bufory
sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;Ciężkie profilowanie zapytań
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;Rotacja partii (pomysł)
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');Podsumowanie
PostgreSQL jest w stanie wyciągnąć „pieniądze i prawdę” na platformę iGaming z liniowym wzrostem obciążenia - jeśli indeksy, strony, auto-próżnia, WAL/pamięć, replika/DR i obserwowalność są zdyscyplinowane. Zacznij od profilu zapytań i SLO, zbuduj indeksy dla prawdziwych wzorów, odizoluj gorące tabele, włącz ścisłą higienę operacyjną - a baza będzie przewidywalnie utrzymywać turnieje i szczytowe płatności.