PostgreSQL：最佳實踐和索引

（部分： 技術和基礎設施）

簡短摘要

PostgreSQL是金錢，KYC和iGaming中具有法律意義的條目的「真相核心」。它提供ACID保修、強大的SQL和可擴展性。為了抵禦錦標賽和PSP webhook的高峰，關鍵是：素養方案，索引，派對，自動休假，WAL調音和可觀察性。下面-實踐和模板設計師。

1）體系結構和SLO

PostgreSQL角色：寫入+復制副本的領導者；熱屏幕-緩存/投影（Redis/實例化視圖）。

SLO示例： p99 「INSERT/UPDATE」錢包≤ 25-40毫秒；p99讀取余額≤ 10-15毫秒；復制副本≤ 2-5 c；可用性≥ 99。9%.

Read-after-write policy：自定義的交易後屏幕從領導者處讀取或等待復制時差。

2）電路設計

貨幣核心（錢包，ledger）規範化+非規範化（CQRS/投影）。
嚴格的限制是：「NOT NULL」，「CHECK」，「UNIQUE」，帶有有針對性的「ON DELETE/UPDATE」（RESTRICT/SET NULL/NO ACTION）的FK。
模式轉換：上下/下移，功能標誌；避免在熱時中斷重命名。
標識符：「BIGINT」+序列（或分布ULID/UUIDv7）。對於熱插件-單獨的tablespace/WAL卷中的序列。

3）索引： 什麼，在哪裏以及如何

3.1 B樹（默認值）

時間：FK上的精確匹配，範圍，排序，「JOIN」。
模式：「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。
變體：trigram的「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地理，半徑），間隔，近鄰。
較少用於金錢核心，有利於地理約束/負責任的遊戲。

3.4 BRIN（大型「appendix」-按時間排列）

時間：數十億行，自然時間相關性（投註/事件日誌）。
優點：小尺寸，便宜的服務。
缺點：粗略的選擇性→與黨派結合。

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。
重復索引（相同的列集/順序）。
每個基數非常低的列的索引（例如，具有2個值的「狀態」）-做部分。

4）參與式

為什麼：減少斑點，加快VACUUM/掃描速度，促進撤回/存檔。
電路：投註日誌的日期（日/周）RANGE；大型用戶表的「player_id」 HASH；組合起來。
輪換練習：提前創建未來的派對,「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 UPDATE具有偶數邏輯。

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），用於分布式mutex的咨詢鎖。
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） Autovacum,統計數據和bloat

VACUUM/ANALYZE：保持最新統計數據（「default_statistics_target」）,在熱表上調節AV（觸發閾值如下）。
註意wraparound（age（txid）<20億），「vacuum_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」：RAM的20-25％（取決於配置文件）。
'work_mem'：通過操作！以保守方式（例如,8-64MB）進行配置,並逐點提升報告角色。
「maintenance_work_mem」：主要索引/恢復（任務512MB-2GB）。

WAL和Checkpoints

NVMe for WAL,一個單獨的卷；'wal_compression=on'。

"checkpoint_timeout" 10-15分鐘，"max_wal_size"在更改量下，"checkpoint_completion_target ≈ 0。9`.

對於插入峰值，是batch插入和組commites。

9）復制和容錯能力

引導復制：同步至最接近（半同步）的RPO≈0 -30秒；異步讀取/分析。
促銷和召集：Patroni/復制經理；排除「雙重領導者」。
Hot standby： 'hot_standby_feedback'謹慎（bloat生長）,最好在副本上馴服冗長的事務。

10）Bacaps和PITR

完整+增量備份，離網副本；WAL的「archive_command」。
PITR：在展位上檢查恢復時間點；規範RPO/RTO（錢包-分鐘,logi-幾十分鐘）。
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」。
對於繁重的Log/Game搜索-外賣到搜索引擎（ES/OpenSearch）,並在PG中存儲鏈接/元數據。

13）可觀察性和分析

pg_stat_statements：最慢的/頻繁的查詢，正常化。
EXPLAIN （ANALYZE, BUFFERS）：閱讀計劃,在熱軌上搜索seq掃描。
度量標準：TPS、p95/p99、checkpoints、「replication_lag」、deadlocks、bloat、AV循環、cache-hit ≥ 95%。
Alerts： lags增長,「idle in transaction」,意想不到的seq scan, WAL風暴。

14）連接池和準備好的查詢

Puller（PgBouncer）在Web流量的「交易」模式下；「會議」在長光標/後臺。
預置狀態/參數化→較少的解析,穩定計劃。
將最大貝肯德數（'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；半同步復制；緩存僅作為投影。

16.2投註歷史（高TPS，按玩家/時間閱讀）

每天/每周派對，BRIN計時+B-Tree'（player_id，created_at DESC）'。
通過「DETACH PARTITION」重建→在OLAP中存檔。

16.3個PSP Webhooks（burstami，retrai）

「原始」事件隊列（僅append-only），時間分期，「status='pending」部分索引。
「idempotency_key」/「psp_tx」（UNIQUE）的冪等性。

17）實施支票

1.確定SLO和read-after-write策略。
2.為實際查詢（查詢配置文件）設計關鍵索引。
3.在有重制/上標模式的地方啟用分期。
4.在熱桌上配置AV/ANALYZE, 請註意斑點/wraparound。
5.將內存、WAL和checkpoint調到高峰窗口。
6.設置連接池和「pg_stat_statements」；建立一個Alerta。
7.復制+DR+PITR-是強制性的；進行練習。
8.將JSONB和GIN限制為僅必要的路徑；使用partial indexes。
9.通過使用「SKIP LOCKED」來減少事務持續時間。
10.審計/PII/加密/角色扮演模型-在開始付款之前。

18）反模式

一個「通用」索引用於所有，並且沒有查詢分析。
冗長的「掛起」交易（「交易」）→鎖定/增長。
依靠副本進行閱讀後寫作,而不考慮滯後。
「以防萬一」將所有內容存儲在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;

對「重」查詢進行分析

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開始，根據真實模式構建索引，隔離熱桌，包括嚴格的操作衛生-並且基礎可以預測地保持比賽和峰值支付。