OLTP 模板
针对在线事务处理负载优化的 PostgreSQL 配置模板
oltp.yml 是 Pigsty 的默认配置模板，针对在线事务处理（OLTP）负载进行了优化。适用于 4-128 核 CPU 的服务器，特点是高并发连接、低延迟响应、高事务吞吐量。
建议同时使用
node_tune=oltp进行操作系统级别的配套调优。
适用场景
OLTP 模板适用于以下场景：
- 电商系统：订单处理、库存管理、用户交易
- 社交应用：用户动态、消息推送、关注关系
- 游戏后端：玩家数据、排行榜、游戏状态
- SaaS 应用：多租户业务系统
- Web 应用：常规的 CRUD 操作密集型应用
特征负载：
- 大量短事务（毫秒级）
- 高并发连接（数百到数千）
- 读写比例通常在 7:3 到 9:1
- 对延迟敏感，要求快速响应
- 数据一致性要求高
使用方法
oltp.yml 是默认模板，无需显式指定：
pg-oltp:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary }
10.10.10.12: { pg_seq: 2, pg_role: replica }
vars:
pg_cluster: pg-oltp
# pg_conf: oltp.yml # PostgreSQL 配置模板（默认值）
# node_tune: oltp # 操作系统调优模板（默认值）
或显式指定：
pg-oltp:
vars:
pg_conf: oltp.yml # PostgreSQL 配置模板
node_tune: oltp # 操作系统调优模板
参数详解
连接管理
max_connections: 500/1000 # 取决于是否使用 pgbouncer
superuser_reserved_connections: 10
- 当
pg_default_service_dest为pgbouncer时，max_connections设为 500 - 当流量直连 PostgreSQL 时，
max_connections设为 1000 - 可通过
pg_max_conn参数覆盖
内存配置
OLTP 模板的内存分配策略：
| 参数 | 计算公式 | 说明 |
|---|---|---|
shared_buffers | 内存 × pg_shared_buffer_ratio | 默认比例 0.25 |
maintenance_work_mem | shared_buffers × 25% | 用于 VACUUM、CREATE INDEX |
work_mem | 64MB - 1GB | 根据 shared_buffers/max_connections 计算 |
effective_cache_size | 总内存 - shared_buffers | 可用于缓存的预估内存 |
work_mem 计算逻辑：
work_mem = min(max(shared_buffers / max_connections, 64MB), 1GB)
这确保每个连接有足够的排序/哈希内存，但不会过度分配。
并行查询
OLTP 模板对并行查询做了适度限制，以避免并行查询抢占过多资源影响其他事务：
max_worker_processes: cpu + 8 (最小16)
max_parallel_workers: 50% × cpu (最小2)
max_parallel_workers_per_gather: 20% × cpu (2-8)
max_parallel_maintenance_workers: 33% × cpu (最小2)
同时提高了并行查询的成本估算，让优化器倾向于串行执行：
parallel_setup_cost: 2000 # 默认值 1000 的两倍
parallel_tuple_cost: 0.2 # 默认值 0.1 的两倍
min_parallel_table_scan_size: 16MB # 默认值 8MB 的两倍
min_parallel_index_scan_size: 1024 # 默认值 512 的两倍
WAL 配置
min_wal_size: 磁盘/20 (最大200GB)
max_wal_size: 磁盘/5 (最大2000GB)
max_slot_wal_keep_size: 磁盘×3/10 (最大3000GB)
wal_buffers: 16MB
wal_writer_delay: 20ms
wal_writer_flush_after: 1MB
commit_delay: 20
commit_siblings: 10
checkpoint_timeout: 15min
checkpoint_completion_target: 0.80
这些设置平衡了数据安全性和写入性能。
Vacuum 配置
vacuum_cost_delay: 20ms # 每轮 vacuum 后休眠
vacuum_cost_limit: 2000 # 每轮 vacuum 的代价上限
autovacuum_max_workers: 3
autovacuum_naptime: 1min
autovacuum_vacuum_scale_factor: 0.08 # 8% 表变化触发 vacuum
autovacuum_analyze_scale_factor: 0.04 # 4% 表变化触发 analyze
autovacuum_freeze_max_age: 1000000000
OLTP 模板使用保守的 vacuum 设置，避免 vacuum 操作影响在线事务性能。
查询优化
random_page_cost: 1.1 # SSD 优化
effective_io_concurrency: 200 # SSD 并发 IO
default_statistics_target: 400 # 统计信息精度
这些设置让优化器能够生成更好的查询计划。
日志与监控
log_min_duration_statement: 100 # 记录超过 100ms 的慢查询
log_statement: ddl # 记录 DDL 语句
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024 # 记录超过 1MB 的临时文件
log_autovacuum_min_duration: 1s
track_io_timing: on
track_functions: all
track_activity_query_size: 8192
客户端超时
deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 10min
10 分钟的空闲事务超时可以防止长时间持有锁的僵尸事务。
扩展配置
shared_preload_libraries: 'pg_stat_statements, auto_explain'
# auto_explain
auto_explain.log_min_duration: 1s
auto_explain.log_analyze: on
auto_explain.log_verbose: on
auto_explain.log_timing: on
auto_explain.log_nested_statements: true
# pg_stat_statements
pg_stat_statements.max: 10000
pg_stat_statements.track: all
pg_stat_statements.track_utility: off
pg_stat_statements.track_planning: off
与其他模板的对比
| 特性 | OLTP | OLAP | CRIT |
|---|---|---|---|
| max_connections | 500-1000 | 500 | 500-1000 |
| work_mem | 64MB-1GB | 64MB-8GB | 64MB-1GB |
| 并行查询 | 适度限制 | 激进启用 | 禁用 |
| vacuum 激进度 | 保守 | 激进 | 保守 |
| 事务超时 | 10min | 禁用 | 1min |
| 慢查询阈值 | 100ms | 1000ms | 100ms |
为什么选择 OLTP 而非 OLAP？
- 您的查询大多数是简单的点查和范围查询
- 事务响应时间要求在毫秒级
- 有大量并发连接
- 不需要执行复杂的分析查询
为什么选择 OLTP 而非 CRIT？
- 可以接受极小概率的数据丢失（异步复制）
- 不需要完整的审计日志
- 希望获得更好的写入性能
性能调优建议
连接池
对于高并发场景，强烈建议使用 PgBouncer 连接池：
pg-oltp:
vars:
pg_default_service_dest: pgbouncer # 默认值
pgbouncer_poolmode: transaction # 事务级池化
只读分离
使用只读从库分担读取负载：
pg-oltp:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary }
10.10.10.12: { pg_seq: 2, pg_role: replica }
10.10.10.13: { pg_seq: 3, pg_role: replica }
监控指标
关注以下监控指标：
- 连接数：活跃连接数、等待连接数
- 事务率：TPS、提交/回滚比例
- 响应时间：查询延迟百分位（p50/p95/p99）
- 锁等待：锁等待时间、死锁次数
- 复制延迟：从库延迟时间和字节数
参考资料
pg_conf：PostgreSQL 配置模板选择参数node_tune：操作系统调优模板，应与pg_conf配套- OLAP 模板：分析处理模板对比
- CRIT 模板：关键业务模板对比
- TINY 模板：微型实例模板对比
- 集群配置：PostgreSQL 集群类型配置
- 高可用：高可用架构设计