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开始，根据真实模式构建索引，隔离热桌，包括严格的操作卫生-并且基础可以预测地保持比赛和峰值支付。