PostgreSQL: best pract.ru և ինդեքսավորում

(Բաժին ՝ Տեխնոլոգիաներ և ենթակառուցվածքներ)

Live ռեզյումե

PostgreSQL-ը փողի, KYC-ի և iGaming-ի օրինականորեն նշանակալի գրառումներ է։ Այն տալիս է ACID երաշխիքներ, հզոր SQL և ընդարձակումը։ Մրցավարների և PMS-webhuks-ի պիկի դիմակայելու համար քննադատական են 'գրագետ սխեմա, ինդեքսներ, կուսակցություններ, ավտովակում, WAL-ի թյունինգը և դիտարկումը։ Ներքևում պրակտիկայի և ձևանմուշների դիզայներ է։

1) Ճարտարապետությունը և SLO-ն

PostgreSQL-ի դերը 'գրելու + կարդալու կրկնօրինակները։ տաք էկրանների համար 'քաշ/պրոյեկցիա (Redis/նյութականացված ներկայացումներ)։

SLO օրինակներ ՝ p99 'INSAE/MSATE' դրամապանակը 25-40 մզ; p99 հավասարակշռության ընթերցում 10-15 ms; lag redik no 2-5 s; հասանելիությունը 3699 է։ 9%.

Read-after-write-ի քաղաքականությունը, օգտագործողների էկրանները գործարքից հետո կարդում են առաջնորդի հետ կամ սպասում են կրկնվող լագին։

2) Սխեմաների նախագծումը

Փողի միջուկի նորմալացումը (դրամապանակ, ledger) + կարդալու համար (CQRS/պրոյեկցիա)։

Խիստ սահմանափակումներ ՝ "MST NMS", "MSK", "UNIQUE", FK նպատակային "ON SYETE/WINTRICT/MS ACTION)։

Սխեմայի տարբերակումը 'www.up/down, feature դրոշներ; խուսափեք տաք ժամանակում սխալ անվանումներից։

Բաղադրիչները ՝ «BIGINT» + հաջորդականությունը (կամ ULID/UUIDv7 բաշխման համար)։ Տաք ներդիրների համար հաջորդականությունները առանձին tablespace/WAL-թոմում։

3) Ինդեքսավորում ՝ ինչ, որտեղ և ինչպես։

3. 1 B-Tree (դեֆոլտ)

Երբ 'ճշգրիտ պարամետրերը, միջակայքերը, տեսակավորումը, «JOIN» -ը FK-ով։

Արտոնագրեր ՝ «WHMS 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-ը որոնում է բաները/ճանապարհները, թեգերի զանգվածները, FOX-ը։

Տարբերակներ ՝ «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 (geo/միջակայքներ/ստորագրություններ)

Երբ 'երկրաչափություն (IP-geo, ճառագայթներ), ընդմիջումներ, nearest-neighbor։

Ավելի քիչ օգտագործվում է փողի միջուկում, օգտակար է գեո սահմանափակումների/պատասխանատու խաղի համար։

3. 4 BRIN (մեծ «appendix» - tablits ժամանակի ընթացքում)

Երբ միլիարդավոր տողեր են, ժամանակի բնական հարաբերակցությունը (ամսագրեր/իրադարձություններ)։

Պլյուսներ ՝ փոքր չափսեր, էժան ծառայություն։

Մինուսներ 'կոպիտ ընտրողականությունը պետք է համադրվի կուսակցության հետ։

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

3. 5 Hash

Հազվադեպ է անհրաժեշտ, մեկ սյունի հավասարումը միջակայքի բացակայության դեպքում։ ավելի հաճախ B-Tree-ն է։

3. 6 Մասնակի և արտահայտություններ

Partial index: արագացնում է տաք-շատ (ակտիվ, "status =" pending ")։

Expression index: բանալին («lower (email)», «(wwww.a->> prone _ 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 Antipattern ինդեքսներ

«Ինդեքսը ամեն ինչի վրա», ինդեքսների ավելցուկը խոչընդոտում է ձայնագրությունը և VACUUM-ը։

Կրկնվող ինդեքսները (նույն զանգվածների/կարգի)։

Ինդեքսը շատ ցածր կարդինալություն ունեցող սյունակի վրա (օրինակ ՝ «status» -ը երկու արժեքով), դարձրեք մասնակի։

4) Կուսակցությունը

Ինչու 'նվազեցնել բլոատը, արագացնել VACUUM/սկանները, հեշտացնել retention/արխիվ։

Սխեմաներ ՝ RANGE ամսաթվով (օր/շաբաթ) 2019 թվականի համար։ HASH 'player _ id' մեծ օգտագործողական աղյուսակների համար։ համակցված է։

Ռոտացիայի պրակտիկան 'նախապես ստեղծել ապագա կուսակցություններ, "ATTACH PARTSA", հին արխիվը' "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-նորարարություններ 'պահեք «www.lfactor» (օրինակ, 90) տաք աղյուսակների վրա, որպեսզի ազատ տեղանքի համար։

TOASS: մեծ JSONB/տեքստերը հետևեք կոմպակտմանը։ պահեք բարձրաձայն դաշտերը առանձին աղյուսակում։

UPS.RU 'օգտագործեք' ON SYLICT... DO CORATE-ը գաղափարական տրամաբանությամբ։

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 READ »/« SERIALIZABABE» կետային (հաշվետվություններ, օֆֆլինգ)։ Երկար ժամանակ մի պահեք դրանք։

Բլոկների տեսակները ՝ row-level (pesimism 'FOR SYMATE "), table-level (DDL), advisory producks բաշխված մյուտեքսների համար։

Deadlocks: Կարճ գործարքներ, էակների թարմացման միասնական կարգ, թայմաուտներ («www.k _ timeout», «statrone _ timeout»)։

Առաջադրանքների հերթերը '«SKIP DRKED» բաշխված գողացված փամփուշտի համար։

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

7) Ավտովակում, վիճակագրությունը և բլոատը

VACUUM/ANMS ZE: Պահեք իրական վիճակագրությունը («wwww.d.m. _ statist.ru _ target»), սեղմեք AV տաք աղյուսակների վրա (կրակոցի շեմն ներքևում)։

Հետևեք wraparound (age (txid) <2 միլիարդ), «vacuum _ freeze _ min _ age»։

bloat-վերահսկողություն: wwww.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) Հիշողություն, chekpoints և WAL-ը

Հիշողություն

«shared _ buffers»: 20-25% RAM (կախված է կոդից)։

«work _ mem» 'վիրահատության ժամանակ։ Տեղադրեք պահպանողական (օրինակ, 8-64MB) և ճշգրիտ բարձրացրեք հաշվետվական դերերի համար։

«maintenme _ work _ mem»: մեծ ինդեքսներ/վերականգնումը (512MB-2GB առաջադրանքով)։

WAL և chekpoints

NVMe-ի համար, առանձին հատոր; «wal _ compression = on»։

«wwww.kpoint _ timeout '10-15 րոպե,» max _ wal _ size «փոփոխության ծավալի տակ,» www.kpoint _ completion _ target _ target 240։ 9`.

Ներդիրների գագաթների համար 'batch-112 և խմբակային համայնքներ։

9) Վերափոխումը և անկայունությունը

Առաջնորդը ՌՊՈ-ի համար համաժամանակյա է մոտակա (semi-entnc) համար '0-30c; ասինխրոն ընթերցանության/վերլուծաբանների համար։

Պրոմուշենը և fencing: Patroni/կրկնօրինակման մենեջեր; «բացառել երկակի առաջնորդը»։

Hot standby: «hot _ standby _ feedback» զգույշ է (bloat բարձրացում), ավելի լավ 'հեռացրեք երկար գործարքները կրկնօրինակների վրա։

10) Բեքապները և PITR-ը

Ամբողջական + հաստատվում է իրական բեքապը, օֆսայթ պատճենները, «archive _ command» -ը WAL-ի համար։

PITR 'Ստուգեք վերականգնումը մինչև ժամանակի կետը։ կարդացեք RPO/RTO (դրամապանակներ - րոպեներ, լոգներ - տասնյակ րոպեներ)։

DR ուսուցումները (game day) 'վերականգնման հիբրիդային ստուգում։

11) JSONB-ը և «ճկուն սխեմայի» մոդելը

JSONB-ը հիանալի է հազվադեպ ընթերցվող/փոփոխական ատրիբուտների համար (KYC-մետատվյալներ, PSA պարամետրեր)։

Խստորեն վալիդիդիրացրեք պարտադիր դաշտերը ռեալացիոն սյունակներում։ 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-որոնումը

Ներկառուցված FBS: "tsvector '+ GIN; տպիչների համար '«pg _ trgm»։

Լոգարանների/խաղերի ծանր որոնման համար 'որոնման համակարգում (ES/OpenSearch), իսկ PG-ում պահել հղումը/մետատվողները։

13) Դիտողությունն ու ավելացումը

pg _ stat _ statements: Լավագույն դանդաղ/հաճախակի հարցումներ, նորմալացում։

DRAIN (ANFSZE, BUFFERS) 'ռուսական պլաններ, փնտրեք seq scan տաք ճանապարհների վրա։

Մետրիկները ՝ TPS, p95/p99, chekpoints, «replection _ lag», deadlocks, bloat, AV-ցիկլներ, cache-hit 3695 տոկոսը։

Ալբերտները 'ճամբարների աճը, «idle in transaction», անսպասելի seq scan, WAL փոթորիկ։

14) Պուլ

Pupler (PgBouncer) ռեժիմում 'transaction' վեբ ծառայության համար; «session» - երկար կուրսորների/back գրասենյակների համար։

Propared statements/dechrization-ը ավելի քիչ է, կայուն պլաններ։

Սահմանափակեք բեքենդների առավելագույն քանակը («max _ connections» ցածր; փամփուշտը վերցնում է օդափոխությունը)։

15) Անվտանգությունն ու կոմպլենսը

TFC-ը տրանզիտում, սկավառակների կոդավորումը, KFC/արտաքին բանալիները։

RBAC 'նվազագույն իրավունքները, դերերի բաժանումը կարդալու/ձայնագրելու/ադմինի։

RSA (Row-Level System) մուլտֆիլմի-տենանտային կոմպոզիցիաների համար։

PII 'դիմակավորում/կեղծանունացում, պահպանման ժամանակահատվածը։

Աուդիտ '"pgaudit '/traggers տեղադրված են կրիտիկական սպիտակուցների վրա (դրամապանակ/ledger)։

16) iGaming-ի տիպային ձևանմուշները

16. 1 Դրամապանակ և ledger (խիստ համաձայնություն)

Индексы: `wallet(player_id PK)`, `ledger(player_id, ts DESC)` + `INCLUDE (delta_cents, reason)`.

Գործարքը նորարարում է հավասարակշռությունը և գրում է ledger; semi-ensnc կրկնօրինակումը; քեշը միայն որպես պրոյեկցիա։

16. 2 Մրցույթի պատմությունը (բարձր TPS, խաղացողի/ժամանակի ընթերցում)

Կուսակցությունը օր/շաբաթ, BRIN ժամանակի ընթացքում + B-Tree «(player _ id, created _ at DESC)»։

Retenshn «DETACH PARTIM» -ի միջոցով ռուսական արխիվացիան OLAP-ում։

16. 3 Webhuki PHL (բուրգերներ, retrai)

"Հում" իրադարձությունների հերթը (append-only), ժամանակի կուսակցությունը, "status =" pending "մասնակի ինդեքսը։

«Idempotency _ key »/« pult _ tx» (UNIQUE)։

17) Chek-Lister-ը ներդրման

1. Տեղադրեք SLO և read-after-write քաղաքականությունը։

2. Նախագծեք հիմնական ինդեքսները իրական հարցումների համար (հարցումների պրոֆիլը)։

3. Միացրեք նվագակցումը այնտեղ, որտեղ կա retenshn/append-pattern։

4. Patte AV/ANMS ZE-ը տաք գյուղացիների վրա, հետևեք bloat/wraparound-ին։

5. Հիշեք, WAL-ը և chekpoints-ը գագաթնակետային պատուհանների տակ։

6. Տեղադրեք թուղթ և «pg _ stat _ stements»; դրեք ալերտները։

7. Կրկնօրինակումը + DR + PITR-ը պարտադիր է։ ուսուցում կատարիր։

8. Սահմանափակեք JSONB-ը և GIN-ը միայն անհրաժեշտ ճանապարհներով։ օգտագործեք partial indexes։

9. Նվազեցրեք գործարքների տևողությունը, օգտագործեք «SKIP MSKED» գողացված հերթերի համար։

10. Audit/PII/կոդավորումը/roley մոդելը մինչև վճարումը։

18) Անտիպատերնի

Մեկ «համընդհանուր» ինդեքսը բոլոր հարցումների վերլուծության բացակայությունն է։

Երկար «կախված» գործարքները («idle in transaction») արգելափակման/բլոատի աճը։

Ապավինել read-after-write-write-ի համար առանց հաշվի առնելու ճամբարը։

Պահել ամեն ինչ JSONB-ում «ամեն դեպքում» և ինդեքսավորել ամբողջ փաստաթուղթը մեկ GIN-ում։

VACUUM/ANMS ZE-ի զրոյական վերահսկումը և բայերի/չեկպոինտների մոնիտորինգի բացակայությունը։

Զանգվածային կոմպոզիցիաները/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 և դիտարկումը։ Սկսեք հարցումներից և SLO-ից, կառուցեք ինդեքսներ իրական փամփուշտների համար, տրամադրեք տաք սեղաններ, միացրեք խիստ գործառնական հիգիենան, և բազան կանխատեսելիորեն կպահանջի մրցավարներ և խնայողություններ։