PostgreSQL: best practices жана индекстөө

(Бөлүк: Технология жана инфраструктура)

Кыскача резюме

PostgreSQL - акча үчүн "чындык өзөгү", KYC жана iGaming юридикалык маанилүү жазуулар. Бул ACID кепилдик, күчтүү SQL жана узартуу берет. Турнирлердин жана PSP-вебхуктардын туу чокуларына туруштук берүү үчүн: компетенттүү схема, индекстер, партиялаштыруу, автоакуум, WAL тюнинги жана байкоо жүргүзүү. Төмөндө - практиктердин жана шаблондордун конструктору.

1) Архитектура жана SLO

PostgreSQL ролу: жазуу боюнча лидер + окуу боюнча реплика; ысык экрандар үчүн - кэш/проекция (Redis/материалдык көрүнүшү).
SLO мисалдар: p99 'INSERT/UPDATE' капчык ≤ 25-40 ms; p99 баланс окуу ≤ 10-15ms; лаг реплика ≤ 2-5 с; жеткиликтүүлүгү ≥ 99. 9%.
read-after-write саясаты: транзакциядан кийин колдонуучу экрандар лидер менен окуйт же репликациялык артта калууну күтөт.

2) Схемаларды долбоорлоо

Акча ядросун нормалдаштыруу (капчыктар, ledger) + окуу үчүн денормалдаштыруу (CQRS/проекциялар).
Катуу чектөөлөр: 'NOT NULL', 'CHECK', 'UNIQUE', FK максаттуу 'ON DELETE/UPDATE' (RESTRICT/SET NULL/NO ACTION).
схемасы чыгаруу: up/down көчүрүү, feature-желектер; ысык убакта атын өзгөртүүдөн качыңыз.
ID: '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-гео, радиустар), интервалдар, 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 Жарым-жартылай жана сөз айкаштары

Partial Index: ысык төмөнкү көптүктөрдү тездетет (активдүү, '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-UPDATING: 'fillfactor' (мисалы, 90) баракта бош орун үчүн ысык таблицаларда сактаңыз.
TOAST: чоң JSONB/тексттер - компакт-мониторинг жүргүзүү; чоң талааларды өзүнчө таблицада сактаңыз.
UPSERT: 'ON CONFLICT... DO UPDATE 'idempotent логикасы менен.

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 '/' SERIALIZABLE' так (отчеттор, оффлайн-батчи). Аларды көпкө кармабаңыз.
Блокировкалардын түрлөрү: row-level (пессимизм 'FOR UPDATE'), table-level (DDL), бөлүштүрүлгөн мьютекстер үчүн advisory locks.
Deadlocks: кыска бүтүмдөр, жандарды жаңылоонун бирдиктүү тартиби, таймауттар ('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) Унаа ташуу, статистика жана bloat

VACUUM/ANALYZE: Учурдагы статистиканы ('default _ statistics _ target') сактаңыз, ысык таблицаларда AVны тюнинг (төмөнкү босого).
wraparound (age (txid) <2 млрд), 'vacuum _ freeze _ min _ age'.
bloat-control: жогорку чегинен тышкары оор индекстерде үзгүлтүксүз 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 жана текшерүү пункттары

WAL үчүн NVMe, өзүнчө том; 'wal _ compression = on'.
'checkpoint _ timeout' 10-15 мин, 'max _ wal _ size' өзгөрүүлөрдүн көлөмү боюнча, 'checkpoint _ completion _ target ≈ 0. 9`.
Кошумчалардын туу чокулары үчүн - батч кошумчалары жана топтук коммиттер.

9) Репликация жана бузулууга туруктуулук

Лидер-реплика: RPO ≈ 0-30с үчүн жакынкы (жарым-sync) үчүн синхрондуу; окуу/аналитика үчүн асинхрондук.
Промоушен жана fencing: Patroni/реплика-менеджер; деген сөздөр алынып салынсын.
Hot standby: 'hot _ standby _ feedback' этияттык менен (өсүш bloat), жакшы - репликалар боюнча узакка созулган бүтүмдөрдү камсыздап.

10) Backup жана PITR

Толук + инкременталдык бэкап, оффсайт көчүрмөлөрү; WAL үчүн 'archive _ command'.
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_stat_statements: жогорку жай/тез-тез суроо, нормалдаштыруу.
EXPLAIN (ANALYZE, BUFFERS): ысык жолдордо seq scan издеп, пландарын окуп.
Метриктер: TPS, p95/p99, текшерүү пункттары, 'replication _ lag', deadlocks, bloat, AV циклдери, cache-hit ≥ 95%.
Алерталар: лагдардын өсүшү, "idle in transaction", күтүлбөгөн seq scan, WAL-бороон.

14) Байланыш бассейни жана даярдалган суроолор

Пуллер (PgBouncer) 'transaction' режиминде веб-трафик үчүн; 'session' - узак курсорлор/бэк-кеңсе үчүн.
Prepared statements/параметрлештирүү → аз parsing, туруктуу пландары.
Бекенддердин максималдуу санын чектеңиз ('max _ connections' төмөн; бассейн клапан алат).

15) Коопсуздук жана комплаенс

транзиттик TLS, дисктерди шифрлөө, KMS/тышкы ачкычтар.
RBAC: минималдуу укуктар, ролдорду бөлүү окуу/жазуу/админ.
RLS (Row-Level Security) көп тенанттык жагдайлар үчүн.
PII: маскировка/псевдоним, сактоо мөөнөтү.
Аудит: 'pgaudit '/критикалык таблицалар боюнча аудит триггерлери (капчык/ledger).

16) iGaming үчүн типтүү үлгүлөрү

16. 1 капчык жана ledger (катуу ырааттуулук)

Индексы: `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)'.
"DETACH PARTITION" аркылуу Retenshn → OLAP архивдөө.

16. 3 PSP Webhook (бурст, retrailer)

"Чийки" окуялардын кезеги (append-only), убакыт партиялары, жарым-жартылай индекс 'status =' pending '.
idempotency 'idempotency _ key '/' psp _ tx' (UNIQUE).

17) Киргизүү чек-тизмеси

1. SLO жана read-after-write саясатын бекитүү.
2. реалдуу суроо боюнча негизги индекстерди долбоорлоо (суроо-кароо).
3. Retenshn/Appand үлгүлөрү бар жерде партиялаштырууну күйгүзүңүз.
4. Hot столдордо AV/ANALYZE орнотуу, bloat/wraparound.
5. жогорку терезелер астында эс, WAL жана чекпоинттерди чөп.
6. Байланыш пулун жана 'pg _ stat _ statements'; Алерттерди ачыңыз.
7. Replication + DR + PITR - милдеттүү; машыгууларды өткөргүлө.
8. JSONB жана GIN гана зарыл жолдор менен чектөө; партиялык indexes колдонуу.
9. Транзакциялардын узактыгын азайтыңыз, ворк-кезек үчүн 'SKIP LOCKED' колдонуңуз.
10. Аудит/PII/шифрлөө/ролдук модель - төлөмдөр башталганга чейин.

18) Антипаттерндер

Бир "универсалдуу" бардык индекс жана суроо-талдап жок.
Узак "илинип" бүтүмдөр ('idle in transaction') → блоктор/өсүш bloat.
Лаганы эске албаганда, read-after-write үчүн репликаларга таянуу.
Баарын JSONBде сактаңыз жана бүт документти бир GIN менен индекстеңиз.
Нөлдүк VACUUM/ANALYZE контролдоо жана эч кандай мониторинг лагдар/текшерүү пункттары.
Массалык миграция/DDL чокусунда.

19) Пайдалуу сниппеттер

Суроо планы жана буферлер

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT amount_cents
FROM ledger
WHERE player_id = $1
ORDER BY ts DESC
LIMIT 100;

Profile "оор" суроолор

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 профилинен баштаңыз, индекстерди реалдуу үлгүлөр үчүн куруңуз, ысык таблицаларды изоляциялаңыз, катуу эксплуатациялык гигиенаны күйгүзүңүз - жана база турнирлерди жана эң жогорку төлөмдөрдү сактайт.