PostgreSQL: 모범 사례 및 색인

(섹션: 기술 및 인프라)

간략한 요약

PostgreSQL은 돈, KYC 및 iGaming의 법적으로 중요한 기록을위한 "진실의 핵심" 입니다. ACID 보증, 강력한 SQL 및 확장 성을 제공합니다. 토너먼트 및 PSP 웹 후크의 피크를 견딜 수 있도록 유능한 체계, 지수, 파티션, 자동 진공, WAL 튜닝 및 관찰 성이 중요합니다. 아래는 실습 및 템플릿의 생성자입니다.

1) 건축 및 SLO

PostgreSQL 역할: 읽기위한 + 복제본 작성 리더; 핫 스크린 용-캐시/프로젝션 (Redis/실제보기).
SLO 예: p99 'INSERT/업데이트' 지갑 약 25-40 ms; p99 밸런스 판독 레플리카 지연 지연 2-5 초; 가용성은 99 이상입니 9%.
쓰기 후 정책: 트랜잭션 후 사용자 화면을 리더로부터 읽거나 복제 지연을 기다립니다.

2) Schematic 디자인

돈의 핵심 (지갑, 원장) + 판독 비정규화 (CQRS/프로젝션) 의 정규화.
엄격한 제한: 'NOT', 'CHECK', 'UNIQue', FK를 대상으로하는 'ON DELETE/Updates' (RESTRICT/SET/NO ACTION).
스키마 버전 지정: 업/다운 마이그레이션, 기능 플래그; 더운 시간 이름을 바꾸지 마십시오.
식별자: 'BIGINT' + 시퀀스 (또는 배포를위한 ULID/UUIDv7). 핫 인서트의 경우-별도의 테이블 스페이스/WAL 볼륨의 시퀀스.

3) 색인: 무엇, 어디서, 어떻게

3. 1 B- 트리 (기본값)

언제: FK의 정확한 일치, 범위, 종류, 'JOIN'.
패턴: 'WHERE player _ id =?', 'DESC LIMIT 100을 생성하십시오'.

• 다중 열 색인-조건의 순서와 일치합니다.
• 적용: 'INCLUDE (...)'
• 'ORDER Ł에 따른 개별 지수... "테이프" 에 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, radii), 간격, 가장 가까운 이웃.
돈의 핵심에서 덜 사용되며 지리 제한/책임있는 놀이에 유용합니다.

3. 4 BRIN (시간에 큰 "부록" 테이블)

언제: 수십억의 선, 시간에 따른 자연적인 상관 관계 (베팅/이벤트 로그).
장점: 작고 저렴한 서비스.
단점: 거친 선택성 → 파티셔닝과 결합됩니다.

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

3. 5 해시

드물게 필요합니다: 범위가없는 경우 하나의 열에 의한 평등; 더 자주 B-Tree로 충분합니다.

3. 6 부분 및 표현

부분 지수: 핫 서브 세트를 가속화합니다 (활성, '상태 =' 보류 중 ').
표현 색인: 사전 계산 키 ('낮음 (이메일)', '(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 색인 안티 패턴

"모든 것에 대한 색인": 초과 지수는 글쓰기와 VACUM을 느리게합니다.
중복 인덱스 (동일한 열 세트/순서).
카디널리티가 매우 낮은 열 (예: 2 값의 '상태') 을 색인화하십시오. 부분적으로 수행하십시오.

4) 파티션

이유: 팽만감을 줄이고 VACUM/스캔 속도를 높이고 보존/보관을 용이하게합니다.
계획: 베팅 로그에 대한 날짜 (주간/주) RANGE; 큰 사용자 테이블의 경우 'player _ id' 별 HASH; 결합.
회전 연습: 미래의 당사자, 'ATTACH PARITION', 오래된 당사자 보관소- '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: CONFLICT에서 사용하십시오... 엄청난 논리로 업데이트하십시오.

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) 거래, 자물쇠 및 경쟁

격리 수준: 대부분의 경로에 대한 'COMMITTED 읽기'; 'REPEATABLE READ '/' SERIALIZABLE' (보고서, 오프라인 배치). 오랫동안 잡지 마십시오.
자물쇠의 종류: 행 수준 (비관론 'FOR 업데이트'), 테이블 수준 (DDL), 분산 뮤텍스에 대한 자문 잠금 장치.
데드 락: 짧은 트랜잭션, 균일 한 업데이트 엔터티 순서, 타임 아웃 ('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) 자동 진공, 통계 및 팽창

VACUM/ANALYZE: 현재 통계 ('기본 _ 통계 _ 대상') 를 유지하고 핫 테이블에서 AV를 조정하십시오 (아래 트리거 임계 값).
랩 어라운드 (연령 (txid) <20 억), '진공 _ 동결 _ min _ age' 를 조심하십시오.
팽만감 제어: 무거운 피크가 아닌 지수에서 일반 재 인덱스/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

메모리

'공유 _ 버퍼': 20-25% RAM (프로파일 종속).
'work _ mem': 작동으로! 보수적으로 설정하고 (예: 8-64MB) 역할을보고하려면 포인트 단위로 증가하십시오.
'유지 보수 _ work _ mem': 큰 인덱스/복구 (작업 512MB-2GB).

WAL 및 체크 포인트

WAL을위한 NVMe, 단일 볼륨; 'wal _ compress = on'.
(PHP 3 = 3.0.6, PHP 4) 9`.
삽입 피크의 경우-배치 인서트 및 그룹 커밋.

9) 복제 및 내결함

리더 복제본: RPO λ0 -30c에 대해 가장 가까운 (세미 싱크) 와 동기화; 읽기/분석을위한 비동기식.
프로모션 및 펜싱: Patroni/replica 관리자; "이중 리더" 는 제외합니다.
뜨거운 대기: 'hot _ standby _ feedport' 주의 깊게 (팽팽한 성장), 더 나은-복제품에 대한 긴 거래를 길들입니다.

10) 백업 및 PITR

전체 + 증분 백업, 오프 사이트 사본; WAL의 'archive _ 명령'.
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) 전체 텍스트 및 퍼지 검색

내장 FTS: 'tsvector' + GIN; 오타- 'pg _ trgm'.
로그/게임으로 어려운 검색을 보려면 검색 엔진 (ES/OpenSearch) 으로 이동하여 링크/메타 데이터를 PG에 저장하십시오.

13) 관찰 및 프로파일 링

pg _ stat _ 문: 상단 느린/빈번한 쿼리, 정규화.
EXPLAIN (ANALYZE, BUFFERS): 계획을 읽고 핫 트랙에서 seq 스캔을 찾으십시오.

메트릭: TPS, p95/p99, 체크 포인트, 'replication _ lag', 교착 상태, 팽창, AV 루프, 캐시 적중

경고: 지연의 성장, "거래시 유휴", 예기치 않은 seq 스캔, WAL 폭풍.

14) 연결 풀 및 준비된 쿼리

웹 트래픽에 대한 '트랜잭션' 모드의 Puller (PgBouncer); '세션' -긴 커서/백 오피스 용.
준비된 진술/매개 변수화 → 덜 구문 분석, 안정적인 계획.
최대 백엔드 수를 제한합니다 ('max _ 연결' 낮음). 수영장은 밸브에 의해 인수됩니다).

15) 안전 및 준수

전송 TLS, 디스크 암호화, KMS/외국 키.
RBAC: 최소한의 권리, 읽기/쓰기/관리 역할의 분리.
멀티 테넌트 시나리오를위한 RLS (행 레벨 보안).
PII: 마스킹/앨리어싱, 저장 수명.
감사: 중요한 테이블 (지갑/원장) 에서 'pgaudit '/감사 트리거.

16) iGaming을위한 일반적인 템플릿

16. 지갑 및 원장 1 개 (엄격한 일관성)

지갑 (player _ id PK) ',' ledger (player _ id, ts DESC) '' + 'INCLUDE (delta _ cents, reason)'.
거래는 잔액을 업데이트하고 원장에게 씁니다. 반동기 복제; 프로젝션 만 캐시합니다.

16. 2 베팅 기록 (높은 TPS, 플레이어/시간 읽기)

시간별 BRIN + B-Tree '(플레이어 _ id, 생성 된 DESC)'.
'DETACH PARTITION' 을 통한 유지 → OLAP에 보관.

16. 3 웹 후크 PSP (burstami, retrai)

"원시" 이벤트 (추가 전용), 시간 파티션, '상태 =' 보류중인 부분 인덱스 "의 큐

'dedempotency _ key '/' psp _ tx' (UNIQUE) 에 의한 이념성.

17) 구현 점검표

1. SLO 및 쓰기 후 정책을 시행하십시오.
2. 실제 쿼리에 대한 키 색인 설계 (쿼리 프로필).
3. 보존/부록 패턴이있는 파티션을 포함합니다.
4. 뜨거운 테이블에 AV/ANALYZE를 설정하고 팽만감/랩 어라운드를 주시하십시오.
5. 피크 창의 메모리, WAL 및 체크 포인트 조정.
6. 연결 풀과 'pg _ stat _ shapts' 를 넣으십시오. 경고를받습니다.
7. 복제 + DR + PITR-필요; 운동을합니다.

8. JSONB 및 GIN을 필요한 경로로만 제한하십시오. 부분 인덱스를 사용하십시오

9. 트랜잭션 기간을 최소화하고 작업 대기열에 'SKIP LOCKED' 를 사용하십시오.
10. 결제를 시작하기 전에 감사/PII/암호화/역할 모델.

18) 안티 패턴

모든 것에 대한 하나의 "범용" 색인과 쿼리 분석이 없습니다.
긴 "매달린" 거래 ('유휴 거래') → 차단/팽창 성장.
지연없이 읽은 후 읽기를 위해 복제본을 사용하십시오.
JSONB에 모든 것을 "만일의 경우에 맞게" 저장하고 전체 문서를 하나의 GIN으로 색인화하십시오.
제로 제어 VACUM/ANALYZE 및 지연/체크 포인트 모니터링 없음.
피크 시간 동안 대량 마이그레이션/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은 인덱스, 파티, 자동 진공, WAL/메모리, 레플리카/DR 및 관찰 가능성이 훈련 된 경우 iGaming 플랫폼에서 "돈과 진실" 을 선형으로 증가시킬 수 있습니다. 쿼리 및 SLO 프로필로 시작하고 실제 패턴에 대한 색인을 작성하고 핫 테이블을 분리하며 엄격한 운영 위생을 켜십시오. 기본은 토너먼트와 최고 지불을 유지할 것으로 예상됩니다.