PostgreSQL: საუკეთესო პრაქტიკა და ინდექსაცია

(განყოფილება: ტექნოლოგიები და ინფრასტრუქტურა)

მოკლე რეზიუმე

PostgreSQL არის „სიმართლის ბირთვი“ ფულისთვის, KYC და იურიდიულად მნიშვნელოვანი ჩანაწერები iGaming- ში. იგი იძლევა ACID გარანტიებს, მძლავრ SQL და გაფართოებას. ტურნირებისა და PSP-webhuks- ის მწვერვალების შესანარჩუნებლად, კრიტიკულია: კომპეტენტური სქემა, ინდექსები, წვეულება, ავტო-ვაკუუმი, WAL tuning და დაკვირვება. ქვემოთ მოცემულია პრაქტიკისა და შაბლონების დიზაინერი.

1) არქიტექტურა და SLO

PostgreSQL- ის როლი: ჩანაწერის ლიდერი + კითხვის შენიშვნები; ცხელი ეკრანებისთვის - ქეში/პროექციები (Redis/მატერიალიზებული წარმოდგენები).
SLO მაგალითები: p99 'INSERT/განახლება' საფულე 25-40 ms; p99 წონასწორობის კითხვა 10-15 ms; რეპლიკების ლაგი 2-5 წმ; ხელმისაწვდომობა 99. 9%.
Read-after-write პოლიტიკა: მომხმარებლის ეკრანები იკითხება ლიდერის მხრიდან გარიგების შემდეგ ან ელოდება რეპლიკაციის გზას.

2) სქემების დიზაინი

ფულის ბირთვის ნორმალიზაცია (საფულეები, ლედგერი) + კითხვის დენორმალიზაცია (CQRS/პროექციები).

მკაცრი შეზღუდვები: 'NOT NULL', 'CHECH', 'UNIQUE', FK მიზნობრივი 'ON DELETE/განახლება "(RESTRICT/SET NULLL LD D D LE E E E IED D D AD D D D D D DUIIIID D D D D D ID D D D EEED D D d

სქემის ვერსია: მიგრაცია up/down, feature დროშები; მოერიდეთ ცხელი დროის შეცვლას.
იდენტიფიკატორები: 'BIGINT' + თანმიმდევრობა (ან ULID/UUIDv7 განაწილებისთვის). ცხელი ჩანართებისთვის - თანმიმდევრობა ცალკეულ tablespace/WAL ტომში.

3) ინდექსაცია: რა, სად და როგორ

3. 1 B-Tree (ნაგულისხმევი)

როდის: ზუსტი შესაბამისობა, დიაპაზონი, დახარისხება, 'JOIN' FK- ზე.
ნიმუშები: 'WHERE player _ id =?', 'ORDER BY created _ at DESC LIMIT 100'.

• მრავალფუნქციური ინდექსები - შეასრულეთ პირობები.
• Covering: `INCLUDE (...)` для index-only scan.
• ცალკეული ინდექსი 'ORDER BY... 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' ტრიგრამებისთვის, '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, რადიუსი), ინტერვალები, nearest-neighbor.
ნაკლებად გამოიყენება ფულის ბირთვში, სასარგებლოა გეო შეზღუდვების/პასუხისმგებლობის თამაშისთვის.

3. 4 BRIN (დროის დიდი „დანართი“)

როდის: მილიარდობით სტრიქონი, დროის ბუნებრივი კორელაცია (ფსონების/მოვლენების ჟურნალები).
დადებითი: მცირე ზომა, იაფი მომსახურება.
უარყოფითი მხარეები: უხეში სელექციურობა შეიძლება გაერთიანდეს პარტიზანასთან.

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

3. 5 Hash

იშვიათად არის საჭირო: ერთი სვეტის თანასწორობა დიაპაზონის არარსებობის შემთხვევაში; უფრო ხშირად საკმარისია B-Tree.

3. 6 ნაწილობრივი და გამონათქვამები

პარტიული ინდექსი: აჩქარებს ცხელ ქუდს (აქტიური, 'status =' pending ").
Expression index: გასაღების წინასწარ განსაზღვრა ('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- ს.
დუბლირების ინდექსები (იგივე სვეტების ნაკრები/რიგი).
სვეტზე ინდექსი ძალიან დაბალი კარდინალობით (მაგალითად, 'status' 2 მნიშვნელობით) - გააკეთეთ ნაწილობრივი.

4) განაწილება

რატომ: შეამციროთ bloat, დააჩქაროთ VACUUM/სკანერები, შეამსუბუქეთ retention/არქივი.
სქემები: 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: გამოიყენეთ 'ON CONFLICT... DO განახლება 'იდემპოტენტური ლოგიკით.

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 COMMITTED' ტრასების უმეტესობისთვის; 'REPEATABLE '/' SERIALIZABLE' წერტილოვანი (მოხსენებები, ოფლაინ-ბრძოლები). ნუ შეინარჩუნებთ მათ დიდხანს.
დაბლოკვის ტიპები: row-level (პესიმიზმი 'FOR განახლება'), table-level (DDL), advisory locks განაწილებული mutex- ისთვის.
Deadlocks: მოკლე გარიგებები, ერთეულების განახლების ერთიანი პროცედურა, Timeout ('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) Avtovakum, სტატისტიკა და bloat

VACUUM/ANALYZE: შეინარჩუნეთ შესაბამისი სტატისტიკა ('default _ statistics _ target'), tune AV ცხრილებზე (ქვემოთ მოყვანილი ბარიერი).
დააკვირდით wraparound (age (txid) <2 მილიარდი), 'vacuuum _ freeze _ min _ age ".
bloat კონტროლი: რეგულარული 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) და გაზარდეთ წერტილოვანი საანგარიშო როლებისთვის.
'maintenance _ work _ mem': დიდი ინდექსები/აღდგენა (512MB-2GB დავალებით).

WAL და checkpoints

NVMe WAL- ისთვის, ცალკეული ტომი; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 წთ, 'max _ wal _ size' ცვლილებების მოცულობით, 'checkpoint _ completion _ target _ 0. 9`.
ჩანართების მწვერვალებისთვის - ბატჩის ჩანართები და ჯგუფური კომუნები.

9) რეპლიკაცია და წინააღმდეგობა

ლიდერის შენიშვნები: სინქრონიზებული უახლოესი (semi-sync) RPO-0-30s- ისთვის; ასინქრონული კითხვისთვის/ანალიტიკოსებისთვის.
პრომოუშენი და fencing: Patroni/რეპლიკა მენეჯერი; გამორიცხეთ „ორმაგი ლიდერი“.
Hot standby: 'hot _ standby _ feedback' ფრთხილად (bloat ზრდა), უკეთესი - მოიპარეთ გრძელი გარიგებები რეპლიკებზე.

10) Bacaps და PITR

სრული + სავარაუდო ზურგჩანთა, ოფსეტური ასლები; '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) სრული ტექსტი და fuzzy ძებნა

ჩაშენებული FTS: 'tsvector' + GIN; ტიპებისთვის - 'pg _ trgm'.
ლოგოების/თამაშების მძიმე მოსაძებნად - გადაყვანა საძიებო სისტემაში (ES/OpenSearch), ხოლო PG- ში შეინახეთ ბმული/მეტამონაცემები.

13) დაკვირვება და პროფილირება

pg _ statements: ტოპ ნელი/ხშირი მოთხოვნა, ნორმალიზაცია.
EXPAIN (ANALYZE, BUFFERS): წაიკითხეთ გეგმები, მოძებნეთ seq scan ცხელ გზაზე.
მეტრიკა: TPS, p95/p99, checkpoints, 'replication _ lag', deadlocks, bloat, AV ციკლები, cache-hit - 95%.
ალერტები: ლაგების ზრდა, „indle in transaction“, მოულოდნელი seq scan, WAL ქარიშხალი.

14) ნაერთების აუზი და მომზადებული მოთხოვნები

პულერი (PgBouncer) 'ტრანსფორმაციის' რეჟიმში ვებ ტრაფიკისთვის; 'სესია "- გრძელი კურსებისთვის/სარეზერვო ოფისისთვის.
Prepared statements/პარამეტრიზაცია ნაკლები პარსინგია, სტაბილური გეგმები.
შეზღუდეთ ბეკენდების მაქსიმალური რაოდენობა ('max _ connections' დაბალია; აუზი იღებს სარქველს).

15) უსაფრთხოება და შესაბამისობა

TLS ტრანზიტში, დისკის დაშიფვრა, KMS/გარე გასაღებები.
RBAC: მინიმალური უფლებები, როლების გამიჯვნა კითხვა/ჩაწერა/admin.
RLS (Row-Level Security) მულტფილმის სცენარებისთვის.
PII: შენიღბვა/ფსევდონიმი, შენახვის ვადა.
აუდიტი: "pgaudit '/აუდიტის გამომწვევი კრიტიკულ ცხრილებზე (საფულე/ლედგერი).

16) ტიპიური შაბლონები iGaming- ისთვის

16. 1 საფულე და ლედგერი (მკაცრი კოორდინაცია)

Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.
გარიგება განაახლებს ბალანსს და წერს ledger- ში; semi-sync- ის რეპლიკაცია; ქეში მხოლოდ პროექციის მსგავსად.

16. 2 განაკვეთების ისტორია (მაღალი TPS, მოთამაშის კითხვა/დრო)

განაწილება დღის/კვირის მიხედვით, BRIN დროის მიხედვით + B-Tree '(player _ id, created _ at DESC) ".
Retenshn 'DETACH PARTITION' - ის საშუალებით არის არქივი OLAP- ში.

16. 3 Webhuks PSP (bursts, retrai)

"ნედლეული" მოვლენების ხაზი (append-only), დროის წვეულება, ნაწილობრივი ინდექსი 'status =' pending ".
Idempotence 'idempotence _ key '/' psp _ tx "(UNIQUE).

17) განხორციელების შემოწმების სია

1. დააფიქსირეთ SLO და read-after-write პოლიტიკა.
2. შეიმუშავეთ ძირითადი ინდექსები რეალური მოთხოვნებისთვის (მოთხოვნის პროფილი).
3. ჩართეთ განაწილება, სადაც არის retenshn/append ნიმუშები.
4. AV/ANALYZE პარამეტრი ცხრილში, დააკვირდით bloat/wraparound.
5. გახსენით მეხსიერება, WAL და ქვები მწვერვალის ფანჯრების ქვეშ.
6. განათავსეთ ნაერთების აუზი და 'pg _ stat _ statements'; აიღეთ ალერტები.
7. რეპლიკაცია + DR + PITR - სავალდებულოა; ჩაატარეთ სწავლებები.
8. შეზღუდეთ JSONB და GIN მხოლოდ საჭირო გზებით; გამოიყენეთ partial indexes.
9. მინიმუმამდე დაიყვანეთ გარიგების ხანგრძლივობა, გამოიყენეთ 'SKIP LOCKED "საათის რიგებისთვის.
10. აუდიტი/PII/დაშიფვრა/როლური მოდელი - გადახდის დაწყებამდე.

18) ანტიპატერები

ერთი „უნივერსალური“ ინდექსი ყველაფრისთვის და მოთხოვნების ანალიზის არარსებობა.
გრძელი „ჩამოკიდებული“ გარიგებები ('idle in transaction') ბლოკირება/ზრდა bloat.
დაეყრდნო რეპლიკებს read-after-write- ისთვის, ლაგის გამოკლებით.
შეინახეთ ყველაფერი JSONB- ში „მხოლოდ შემთხვევაში“ და მთელი დოკუმენტის ინდექსირება ერთი GIN.
VACUUM/ANALYZE ნულოვანი კონტროლი და ლაგების/ჩეკების მონიტორინგის არარსებობა.
მასობრივი მიგრაცია/DDL პიკის საათებში.

19) სასარგებლო snippets

მოთხოვნის გეგმა და ბუფერები

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 და დაკვირვება არის მოწესრიგებული. დაიწყეთ მოთხოვნის პროფილით და SLO, ააშენეთ ინდექსები რეალური ნიმუშებისთვის, განათავსეთ ცხელი ცხრილები, ჩართეთ მკაცრი ოპერაციული ჰიგიენა - და ბაზა პროგნოზირებს ტურნირებს და პიკის გადახდებს.