这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

场景模板

使用 Pigsty 预置的四种场景化 Patroni 模版，或者基于这些模板自定义您的配置模板

Pigsty 提供四种预置的 Patroni/PostgreSQL 配置模板，针对不同的使用场景进行了参数优化：

模板CPU核心适用场景特点
oltp.yml4-128COLTP 事务处理高并发、低延迟、高吞吐
olap.yml4-128COLAP 分析处理大查询、高并行、长事务
crit.yml4-128C核心/金融业务数据安全、审计合规、零丢失
tiny.yml1-3C微型实例资源受限、低配环境

您可以通过 pg_conf 参数来选择使用哪个配置模板，默认为 oltp.yml。

通常，数据库调优模板 pg_conf 应当与机器调优模板 node_tune 配套使用。

使用模板

要使用特定的配置模板，只需在集群定义中设置 pg_conf 参数。 建议同时设置 node_tune 参数，使操作系统级别的调优与数据库调优保持一致：

pg-test:
  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-test
    pg_conf: oltp.yml    # PostgreSQL 配置模板（默认值）
    node_tune: oltp      # 操作系统调优模板（默认值）

对于核心金融业务场景，您可以使用 crit.yml 模板：

pg-finance:
  hosts:
    10.10.10.21: { pg_seq: 1, pg_role: primary }
    10.10.10.22: { pg_seq: 2, pg_role: replica }
    10.10.10.23: { pg_seq: 3, pg_role: replica }
  vars:
    pg_cluster: pg-finance
    pg_conf: crit.yml    # PostgreSQL 关键业务模板
    node_tune: crit      # 操作系统关键业务调优

对于低配虚拟机或开发环境，可以使用 tiny.yml 模板：

pg-dev:
  hosts:
    10.10.10.31: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-dev
    pg_conf: tiny.yml    # PostgreSQL 微型实例模板
    node_tune: tiny      # 操作系统微型实例调优

模板对比

四种模板在关键参数上有显著差异，以适应不同的业务场景。以下是主要差异对比：

连接与内存

参数OLTPOLAPCRITTINY
max_connections500/1000500500/1000250
work_mem 范围64MB-1GB64MB-8GB64MB-1GB16MB-256MB
maintenance_work_mem25% 共享缓冲区50% 共享缓冲区25% 共享缓冲区25% 共享缓冲区
max_locks_per_transaction1-2x maxconn2-4x maxconn1-2x maxconn1-2x maxconn

并行查询

参数OLTPOLAPCRITTINY
max_worker_processescpu+8cpu+12cpu+8cpu+4
max_parallel_workers50% cpu80% cpu50% cpu50% cpu
max_parallel_workers_per_gather20% cpu (max 8)50% cpu0（禁用）0（禁用）
parallel_setup_cost2000100020001000
parallel_tuple_cost0.20.10.20.1

同步复制

参数OLTPOLAPCRITTINY
synchronous_mode取决于 pg_rpo取决于 pg_rpo强制开启取决于 pg_rpo
data_checksums可选可选强制开启可选

Vacuum 配置

参数OLTPOLAPCRITTINY
vacuum_cost_delay20ms10ms20ms20ms
vacuum_cost_limit20001000020002000
autovacuum_max_workers3332

超时与安全

参数OLTPOLAPCRITTINY
idle_in_transaction_session_timeout10min禁用1min10min
log_min_duration_statement100ms1000ms100ms100ms
default_statistics_target4001000400200
track_activity_query_size8KB8KB32KB8KB
log_connections仅授权仅授权全部阶段默认

IO 配置（PG17+）

参数OLTPOLAPCRITTINY
io_workers25% cpu (4-16)50% cpu (4-32)25% cpu (4-8)3
temp_file_limit1/20 磁盘1/5 磁盘1/20 磁盘1/20 磁盘

选择建议

  • OLTP 模板：适用于大多数在线事务处理场景，是默认选择。适合电商、社交、游戏等高并发低延迟应用。

  • OLAP 模板：适用于数据仓库、BI 报表、ETL 等分析型负载。特点是允许大查询、高并行度、宽松的超时设置。

  • CRIT 模板：适用于金融交易、核心账务等对数据一致性和安全性有极高要求的场景。强制同步复制、数据校验和、完整审计日志。

  • TINY 模板：适用于开发测试环境、资源受限的虚拟机、树莓派等场景。最小化资源占用，禁用并行查询。

自定义模板

您可以基于现有模板创建自定义配置模板。模板文件位于 Pigsty 安装目录的 roles/pgsql/templates/ 下：

roles/pgsql/templates/
├── oltp.yml    # OLTP 事务处理模板（默认）
├── olap.yml    # OLAP 分析处理模板
├── crit.yml    # CRIT 关键业务模板
└── tiny.yml    # TINY 微型实例模板

创建自定义模板的步骤：

  1. 复制一个现有模板作为基础
  2. 根据需要修改参数
  3. 将模板放置在 roles/pgsql/templates/ 目录
  4. 在集群定义中通过 pg_conf 引用新模板

例如，创建一个名为 myapp.yml 的自定义模板：

cp roles/pgsql/templates/oltp.yml roles/pgsql/templates/myapp.yml
# 编辑 myapp.yml 进行自定义

然后在集群中使用：

pg-myapp:
  vars:
    pg_conf: myapp.yml

请注意，模板文件使用 Jinja2 模板语法，参数值会根据节点的实际资源（CPU、内存、磁盘）动态计算。

参数优化策略

了解更多关于模板参数优化的技术细节，请参阅 参数优化策略，其中详细介绍了：

  • 内存参数调整（共享缓冲区、工作内存、最大连接数）
  • CPU 参数调整（并行查询工作进程配置）
  • 存储空间参数（WAL 大小、临时文件限制）
  • 手工调整参数的方法

相关参数

  • pg_conf：指定使用的 PostgreSQL 配置模板
  • node_tune：指定使用的操作系统调优模板，应与 pg_conf 配套
  • pg_rto：恢复时间目标，影响故障切换超时
  • pg_rpo：恢复点目标，影响同步复制模式
  • pg_max_conn：覆盖模板的最大连接数
  • pg_shared_buffer_ratio：共享缓冲区占内存比例
  • pg_storage_type：存储类型，影响 IO 相关参数

1 - 默认配置模板的参数优化策略说明

了解在 Pigsty 中，预置的四种 Patroni 场景化模板所采用的不同参数优化策略

Pigsty 默认提供了四套场景化参数模板，可以通过 pg_conf 参数指定并使用。

  • tiny.yml：为小节点、虚拟机、小型演示优化（1-8核，1-16GB）
  • oltp.yml：为OLTP工作负载和延迟敏感应用优化（4C8GB+）（默认模板）
  • olap.yml：为OLAP工作负载和吞吐量优化（4C8G+）
  • crit.yml：为数据一致性和关键应用优化（4C8G+）

Pigsty 会针对这四种默认场景，采取不同的参数优化策略，如下所示：

内存参数调整

Pigsty 默认会检测系统的内存大小，并以此为依据设定最大连接数量与内存相关参数。

默认情况下，Pigsty 使用 25% 的内存作为 PostgreSQL 共享缓冲区，剩余的 75% 作为操作系统缓存。

默认情况下，如果用户没有设置一个 pg_max_conn 最大连接数，Pigsty 会根据以下规则使用默认值：

  • oltp: 500 (pgbouncer) / 1000 (postgres)
  • crit: 500 (pgbouncer) / 1000 (postgres)
  • tiny: 300
  • olap: 300

其中对于 OLTP 与 CRIT 模版来说，如果服务没有指向 pgbouncer 连接池，而是直接连接 postgres 数据库，最大连接会翻倍至 1000 条。

决定最大连接数后，work_mem 会根据共享内存数量 / 最大连接数计算得到，并限定在 64MB ~ 1GB 的范围内。

{% raw %}
{% if pg_max_conn != 'auto' and pg_max_conn|int >= 20 %}{% set pg_max_connections = pg_max_conn|int %}{% else %}{% if pg_default_service_dest|default('postgres') == 'pgbouncer' %}{% set pg_max_connections = 500 %}{% else %}{% set pg_max_connections = 1000 %}{% endif %}{% endif %}
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
{% set pg_max_locks_per_transaction = (2 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else pg_max_connections %}
{% set pg_shared_buffers = (node_mem_mb|int * pg_shared_buffer_ratio|float) | round(0, 'ceil') | int %}
{% set pg_maintenance_mem = (pg_shared_buffers|int * 0.25)|round(0, 'ceil')|int %}
{% set pg_effective_cache_size = node_mem_mb|int - pg_shared_buffers|int  %}
{% set pg_workmem =  ([ ([ (pg_shared_buffers / pg_max_connections)|round(0,'floor')|int , 64 ])|max|int , 1024])|min|int %}
{% endraw %}

CPU参数调整

在 PostgreSQL 中，有 4 个与并行查询相关的重要参数，Pigsty 会自动根据当前系统的 CPU 核数进行参数优化。 在所有策略中，总并行进程数量（总预算）通常设置为 CPU 核数 + 8，且保底为 16 个，从而为逻辑复制与扩展预留足够的后台 worker 数量，OLAP 和 TINY 模板根据场景略有不同。

OLTP设置逻辑范围限制
max_worker_processesmax(100% CPU + 8, 16)核数 + 4，保底 1，
max_parallel_workersmax(ceil(50% CPU), 2)1/2 CPU 上取整，最少两个
max_parallel_maintenance_workersmax(ceil(33% CPU), 2)1/3 CPU 上取整，最少两个
max_parallel_workers_per_gathermin(max(ceil(20% CPU), 2),8)1/5 CPU 下取整，最少两个，最多 8 个
OLAP设置逻辑范围限制
max_worker_processesmax(100% CPU + 12, 20)核数 + 12，保底 20
max_parallel_workersmax(ceil(80% CPU, 2))4/5 CPU 上取整，最少两个
max_parallel_maintenance_workersmax(ceil(33% CPU), 2)1/3 CPU 上取整，最少两个
max_parallel_workers_per_gathermax(floor(50% CPU), 2)1/2 CPU 上取整，最少两个
CRIT设置逻辑范围限制
max_worker_processesmax(100% CPU + 8, 16)核数 + 8，保底 16
max_parallel_workersmax(ceil(50% CPU), 2)1/2 CPU 上取整，最少两个
max_parallel_maintenance_workersmax(ceil(33% CPU), 2)1/3 CPU 上取整，最少两个
max_parallel_workers_per_gather0, 按需启用
TINY设置逻辑范围限制
max_worker_processesmax(100% CPU + 4, 12)核数 + 4，保底 12
max_parallel_workersmax(ceil(50% CPU) 1)50% CPU 下取整，最少1个
max_parallel_maintenance_workersmax(ceil(33% CPU), 1)33% CPU 下取整，最少1个
max_parallel_workers_per_gather`0, 按需启用

请注意，CRIT 和 TINY 模板直接通过设置 max_parallel_workers_per_gather = 0 关闭了并行查询。 用户可以按需在需要时设置此参数以启用并行查询。

OLTP 和 CRIT 模板都额外设置了以下参数，将并行查询的 Cost x 2，以降低使用并行查询的倾向。

parallel_setup_cost: 2000           # double from 100 to increase parallel cost
parallel_tuple_cost: 0.2            # double from 0.1 to increase parallel cost
min_parallel_table_scan_size: 16MB  # double from 8MB to increase parallel cost
min_parallel_index_scan_size: 1024  # double from 512 to increase parallel cost

请注意 max_worker_processes 参数的调整必须在重启后才能生效。此外，当从库的本参数配置值高于主库时，从库将无法启动。 此参数必须通过 patroni 配置管理进行调整，该参数由 Patroni 管理，用于确保主从配置一致，避免在故障切换时新从库无法启动。

存储空间参数

Pigsty 默认检测 /data/postgres 主数据目录所在磁盘的总空间，并以此作为依据指定下列参数：

{% raw %}
min_wal_size: {{ ([pg_size_twentieth, 200])|min }}GB                  # 1/20 disk size, max 200GB
max_wal_size: {{ ([pg_size_twentieth * 4, 2000])|min }}GB             # 2/10 disk size, max 2000GB
max_slot_wal_keep_size: {{ ([pg_size_twentieth * 6, 3000])|min }}GB   # 3/10 disk size, max 3000GB
temp_file_limit: {{ ([pg_size_twentieth, 200])|min }}GB               # 1/20 of disk size, max 200GB
{% endraw %}
  • temp_file_limit 默认为磁盘空间的 5%，封顶不超过 200GB。
  • min_wal_size 默认为磁盘空间的 5%，封顶不超过 200GB。
  • max_wal_size 默认为磁盘空间的 20%，封顶不超过 2TB。
  • max_slot_wal_keep_size 默认为磁盘空间的 30%，封顶不超过 3TB。

作为特例， OLAP 模板允许 20% 的 temp_file_limit ，封顶不超过 2TB

手工调整参数

除了使用 Pigsty 自动配置的参数外，您还可以手工调整 PostgreSQL 参数。

使用 pg edit-config <cluster> 命令可以交互式编辑集群配置：

pg edit-config pg-meta

或者使用 -p 参数直接设置参数：

pg edit-config -p log_min_duration_statement=1000 pg-meta
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain' pg-meta

您也可以使用 Patroni REST API 来修改配置：

curl -u 'postgres:Patroni.API' \
    -d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
    -s -X PATCH http://10.10.10.10:8008/config | jq .

2 - 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

内存配置

OLTP 模板的内存分配策略：

参数计算公式说明
shared_buffers内存 × pg_shared_buffer_ratio默认比例 0.25
maintenance_work_memshared_buffers × 25%用于 VACUUM、CREATE INDEX
work_mem64MB - 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

与其他模板的对比

特性OLTPOLAPCRIT
max_connections500-1000500500-1000
work_mem64MB-1GB64MB-8GB64MB-1GB
并行查询适度限制激进启用禁用
vacuum 激进度保守激进保守
事务超时10min禁用1min
慢查询阈值100ms1000ms100ms

为什么选择 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）
  • 锁等待：锁等待时间、死锁次数
  • 复制延迟：从库延迟时间和字节数

参考资料

3 - OLAP 模板

针对在线分析处理负载优化的 PostgreSQL 配置模板

olap.yml 是针对在线分析处理（OLAP）负载优化的配置模板。适用于 4-128 核 CPU 的服务器，特点是支持大查询、高并行度、宽松的超时设置和激进的 Vacuum 策略。

建议同时使用 node_tune = olap 进行操作系统级别的配套调优。

适用场景

OLAP 模板适用于以下场景：

  • 数据仓库：历史数据存储、多维分析
  • BI 报表：复杂报表查询、仪表盘数据源
  • ETL 处理：数据抽取、转换、加载
  • 数据分析：Ad-hoc 查询、数据探索
  • HTAP 混合负载：分析型从库

特征负载：

  • 复杂查询（秒级到分钟级）
  • 低并发连接（数十到数百）
  • 读密集型，写入通常是批量操作
  • 对吞吐量敏感，可以容忍较高延迟
  • 需要扫描大量数据

使用方法

在集群定义中指定 pg_conf = olap.yml：

pg-olap:
  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-olap
    pg_conf: olap.yml    # PostgreSQL 分析处理模板
    node_tune: olap      # 操作系统分析处理调优

也可以将 olap.yml 模板用于专用的离线从库：

pg-mixed:
  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: offline, pg_conf: olap.yml }  # 离线分析从库
  vars:
    pg_cluster: pg-mixed
    pg_conf: oltp.yml    # 主库和在线从库使用 OLTP 模板
    node_tune: oltp      # 操作系统 OLTP 调优

参数详解

连接管理

max_connections: 500
superuser_reserved_connections: 10

OLAP 场景通常不需要大量连接，500 个连接足以应对大多数分析负载。

内存配置

OLAP 模板的内存分配策略更为激进：

参数计算公式说明
shared_buffers内存 × pg_shared_buffer_ratio默认比例 0.25
maintenance_work_memshared_buffers × 50%加速索引创建和 VACUUM
work_mem64MB - 8GB更大的排序/哈希内存
effective_cache_size总内存 - shared_buffers可用于缓存的预估内存

work_mem 计算逻辑（与 OLTP 不同）：

work_mem = min(max(shared_buffers / max_connections, 64MB), 8GB)

更大的 work_mem 允许更大的排序和哈希操作在内存中完成，避免磁盘溢出。

锁与事务

max_locks_per_transaction: 2-4x maxconn   # OLTP 是 1-2x

OLAP 查询可能涉及更多表（分区表、大量 JOIN），因此需要更多的锁槽。

并行查询

OLAP 模板激进启用并行查询：

max_worker_processes: cpu + 12 (最小20)      # OLTP: cpu + 8
max_parallel_workers: 80% × cpu (最小2)      # OLTP: 50%
max_parallel_workers_per_gather: 50% × cpu   # OLTP: 20% (最大8)
max_parallel_maintenance_workers: 33% × cpu

并行查询成本保持默认值，让优化器更倾向于选择并行计划：

# parallel_setup_cost: 1000    # 默认值，不加倍
# parallel_tuple_cost: 0.1     # 默认值，不加倍

同时启用分区智能优化：

enable_partitionwise_join: on       # 分区表智能 JOIN
enable_partitionwise_aggregate: on  # 分区表智能聚合

IO 配置（PG17+）

io_workers: 50% × cpu (4-32)    # OLTP: 25% (4-16)

更多的 IO 工作线程支持并行扫描大表。

WAL 配置

min_wal_size: 磁盘/20 (最大200GB)
max_wal_size: 磁盘/5 (最大2000GB)
max_slot_wal_keep_size: 磁盘×3/10 (最大3000GB)
temp_file_limit: 磁盘/5 (最大2000GB)   # OLTP: 磁盘/20

更大的 temp_file_limit 允许更大的中间结果溢出到磁盘。

Vacuum 配置

OLAP 模板使用更激进的 vacuum 设置：

vacuum_cost_delay: 10ms         # OLTP: 20ms，更快的 vacuum
vacuum_cost_limit: 10000        # OLTP: 2000，每轮更多工作
autovacuum_max_workers: 3
autovacuum_naptime: 1min
autovacuum_vacuum_scale_factor: 0.08
autovacuum_analyze_scale_factor: 0.04

分析型数据库通常有大量批量写入，需要更激进的 vacuum 策略来回收空间。

查询优化

random_page_cost: 1.1
effective_io_concurrency: 200
default_statistics_target: 1000    # OLTP: 400，更精确的统计信息

更高的 default_statistics_target 提供更精确的查询计划，对复杂分析查询尤为重要。

日志与监控

log_min_duration_statement: 1000    # OLTP: 100ms，放宽慢查询阈值
log_statement: ddl
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024
log_autovacuum_min_duration: 1s
track_io_timing: on
track_cost_delay_timing: on         # PG18+，跟踪 vacuum 代价延迟
track_functions: all
track_activity_query_size: 8192

客户端超时

deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 0   # OLTP: 10min，禁用

分析查询可能需要长时间持有事务，因此禁用空闲事务超时。

与 OLTP 模板的主要差异

参数OLAPOLTP差异原因
max_connections500500-1000分析负载连接数少
work_mem 上限8GB1GB支持更大的内存排序
maintenance_work_mem50% buffer25% buffer加速索引创建
max_locks_per_transaction2-4x1-2x更多表参与查询
max_parallel_workers80% cpu50% cpu激进并行
max_parallel_workers_per_gather50% cpu20% cpu激进并行
parallel_setup_cost10002000默认值，鼓励并行
parallel_tuple_cost0.10.2默认值，鼓励并行
enable_partitionwise_joinonoff分区表优化
enable_partitionwise_aggregateonoff分区表优化
vacuum_cost_delay10ms20ms激进 vacuum
vacuum_cost_limit100002000激进 vacuum
temp_file_limit1/5 磁盘1/20 磁盘允许更大临时文件
io_workers50% cpu25% cpu更多并行 IO
log_min_duration_statement1000ms100ms放宽慢查询阈值
default_statistics_target1000400更精确统计
idle_in_transaction_session_timeout禁用10min允许长事务

性能调优建议

结合 TimescaleDB

OLAP 模板与 TimescaleDB 配合使用效果极佳：

pg-timeseries:
  vars:
    pg_conf: olap.yml
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_extensions:
      - timescaledb

结合 pg_duckdb

对于极致的分析性能，可以结合 pg_duckdb：

pg-analytics:
  vars:
    pg_conf: olap.yml
    pg_libs: 'pg_duckdb, pg_stat_statements, auto_explain'

列式存储

考虑使用 Citus 的列式存储或 pg_mooncake：

pg_extensions:
  - citus_columnar  # 或 pg_mooncake

资源隔离

对于混合负载，建议将分析查询隔离到专用从库：

pg-mixed:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }               # OLTP 写入
    10.10.10.12: { pg_seq: 2, pg_role: replica }               # OLTP 读取
    10.10.10.13: { pg_seq: 3, pg_role: offline }               # OLAP 分析
  vars:
    pg_cluster: pg-mixed

监控指标

关注以下监控指标：

  • 查询时间：长查询的执行时间分布
  • 并行度：并行工作进程的使用率
  • 临时文件：临时文件的大小和数量
  • 磁盘 IO：顺序扫描和索引扫描的 IO 量
  • 缓存命中率：shared_buffers 和 OS 缓存的命中率

参考资料

4 - CRIT 模板

针对核心金融业务优化的 PostgreSQL 配置模板，强调数据安全与审计合规

crit.yml 是针对核心金融业务优化的配置模板。适用于 4-128 核 CPU 的服务器，特点是强制同步复制、数据校验和、完整审计日志、严格的安全设置。这个模板牺牲一定的性能来换取最高级别的数据安全性。

建议同时使用 node_tune = crit 进行操作系统级别的配套调优，优化脏页数量。

适用场景

CRIT 模板适用于以下场景：

  • 金融交易：银行转账、支付清算、证券交易
  • 核心账务：总账系统、会计系统
  • 合规审计：需要完整操作记录的业务
  • 关键业务：任何不能容忍数据丢失的场景

特征需求：

  • 零数据丢失（RPO = 0）
  • 数据完整性校验
  • 完整的审计日志
  • 严格的安全策略
  • 可以接受一定的性能损失

使用方法

在集群定义中指定 pg_conf = crit.yml：

pg-finance:
  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 }
  vars:
    pg_cluster: pg-finance
    pg_conf: crit.yml    # PostgreSQL 关键业务模板
    node_tune: crit      # 操作系统关键业务调优

建议：关键业务集群至少配置 3 个节点，以确保在一个节点故障时仍能保持同步复制。

核心特性

强制同步复制

CRIT 模板强制启用同步复制，无论 pg_rpo 设置为何值：

synchronous_mode: true   # 强制开启，不受 pg_rpo 影响

这意味着每次事务提交都必须等待至少一个从库确认写入，确保 RPO = 0（零数据丢失）。

代价：写入延迟会增加（通常增加 1-5ms，取决于网络延迟）。

强制数据校验和

CRIT 模板强制启用数据校验和，无论 pg_checksum 设置为何值：

initdb:
  - data-checksums   # 强制启用，不检查 pg_checksum 参数

数据校验和可以检测到磁盘静默损坏（bit rot），这对金融数据尤为重要。

禁用并行查询

CRIT 模板禁用了并行查询的 gather 操作：

max_parallel_workers_per_gather: 0   # 禁用并行查询

同时提高了并行查询的成本估算：

parallel_setup_cost: 2000
parallel_tuple_cost: 0.2
min_parallel_table_scan_size: 16MB
min_parallel_index_scan_size: 1024

原因：并行查询可能导致查询延迟不稳定，对于延迟敏感的金融交易场景，稳定可预测的性能更为重要。

参数详解

连接管理

max_connections: 500/1000   # 取决于是否使用 pgbouncer
superuser_reserved_connections: 10

与 OLTP 模板相同。

内存配置

参数计算公式说明
shared_buffers内存 × pg_shared_buffer_ratio默认比例 0.25
maintenance_work_memshared_buffers × 25%用于 VACUUM、CREATE INDEX
work_mem64MB - 1GB与 OLTP 相同
effective_cache_size总内存 - shared_buffers可用于缓存的预估内存

WAL 配置（关键差异）

wal_writer_delay: 10ms              # OLTP: 20ms，更频繁刷新
wal_writer_flush_after: 0           # OLTP: 1MB，立即刷新，不缓冲
idle_replication_slot_timeout: 3d   # OLTP: 7d，更严格的槽位清理

wal_writer_flush_after: 0 确保每次 WAL 写入都立即刷到磁盘，最大程度减少数据丢失风险。

复制配置（PG15-）

vacuum_defer_cleanup_age: 500000    # 仅 PG15 及以下版本

这个参数保留最近 50 万个事务的变更不被 vacuum 清理，为从库提供更多的追赶缓冲。

审计日志（关键差异）

CRIT 模板启用完整的连接审计：

PostgreSQL 18+:

log_connections: 'receipt,authentication,authorization'

PostgreSQL 17 及以下:

log_connections: 'on'
log_disconnections: 'on'

这记录了每个连接的完整生命周期，包括：

  • 连接接收
  • 认证过程
  • 授权结果
  • 断开连接

查询日志

log_min_duration_statement: 100     # 记录超过 100ms 的查询
log_statement: ddl                  # 记录所有 DDL
track_activity_query_size: 32768    # OLTP: 8192，保存完整查询

32KB 的 track_activity_query_size 确保能捕获完整的长查询文本。

统计跟踪

track_io_timing: on
track_cost_delay_timing: on         # PG18+，跟踪 vacuum 代价延迟
track_functions: all
track_activity_query_size: 32768

客户端超时（关键差异）

idle_in_transaction_session_timeout: 1min   # OLTP: 10min，更严格

1 分钟的空闲事务超时可以快速释放持有锁的僵尸事务，避免阻塞其他交易。

扩展配置

shared_preload_libraries: '$libdir/passwordcheck, pg_stat_statements, auto_explain'

注意：CRIT 模板默认加载 passwordcheck 扩展，强制密码复杂度检查。

与 OLTP 模板的主要差异

参数CRITOLTP差异原因
synchronous_mode强制 true取决于 pg_rpo零数据丢失
data-checksums强制启用可选数据完整性
max_parallel_workers_per_gather020% cpu稳定延迟
wal_writer_delay10ms20ms更频繁刷新
wal_writer_flush_after01MB立即刷新
idle_replication_slot_timeout3d7d更严格清理
idle_in_transaction_session_timeout1min10min快速释放锁
track_activity_query_size32KB8KB完整查询记录
log_connections完整记录仅授权审计合规
log_disconnectionsonoff审计合规
passwordcheck启用未启用密码安全
vacuum_defer_cleanup_age5000000从库追赶缓冲

性能影响

使用 CRIT 模板会带来以下性能影响：

写入延迟增加

同步复制会增加 1-5ms 的写入延迟（取决于网络）：

异步复制: 提交 -> 本地刷盘 -> 返回客户端
同步复制: 提交 -> 本地刷盘 -> 等待从库确认 -> 返回客户端

写入吞吐量下降

由于需要等待从库确认，写入 TPS 可能下降 10-30%。

查询延迟更稳定

禁用并行查询后，查询延迟更加可预测，没有并行查询启动的开销波动。

资源开销略有增加

更频繁的 WAL 刷新和完整的审计日志会带来额外的 IO 开销。

高可用配置

最小推荐配置

pg-critical:
  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 }
  vars:
    pg_cluster: pg-critical
    pg_conf: crit.yml    # PostgreSQL 关键业务模板
    node_tune: crit      # 操作系统关键业务调优

3 节点配置确保在一个节点故障时仍能保持同步复制。

跨机房部署

对于金融级别的容灾要求：

pg-critical:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary, pg_weight: 100 }  # 机房 A
    10.10.10.12: { pg_seq: 2, pg_role: replica, pg_weight: 100 }  # 机房 A
    10.20.10.13: { pg_seq: 3, pg_role: replica, pg_weight: 0 }    # 机房 B（备用）
  vars:
    pg_cluster: pg-critical
    pg_conf: crit.yml    # PostgreSQL 关键业务模板
    node_tune: crit      # 操作系统关键业务调优

法定人数提交

对于更高的一致性要求，可以配置多个同步从库：

$ pg edit-config pg-critical
synchronous_mode: true
synchronous_node_count: 2    # 需要 2 个从库确认

安全加固建议

密码策略

CRIT 模板已启用 passwordcheck，建议进一步配置：

-- 设置密码最小长度
ALTER SYSTEM SET password_encryption = 'scram-sha-256';

审计扩展

考虑启用 pgaudit 扩展进行更详细的审计：

pg_libs: 'pg_stat_statements, auto_explain, pgaudit'
pg_parameters:
  pgaudit.log: 'ddl, role, write'

网络隔离

确保数据库网络与业务网络隔离，使用 HBA 规则 限制访问。

监控指标

对于关键业务集群，重点关注：

  • 复制延迟：同步复制延迟应接近 0
  • 事务提交时间：p99 延迟
  • 锁等待：长时间锁等待可能影响业务
  • 检查点：检查点持续时间和频率
  • WAL 生成速率：预测磁盘空间需求

参考资料

5 - TINY 模板

针对微型实例和资源受限环境优化的 PostgreSQL 配置模板

tiny.yml 是针对微型实例和资源受限环境优化的配置模板。适用于 1-3 核 CPU 的服务器，特点是最小化资源占用、保守的内存分配、禁用并行查询。

建议同时使用 node_tune = tiny 进行操作系统级别的配套调优。

适用场景

TINY 模板适用于以下场景：

  • 开发测试：本地开发环境、CI/CD 测试
  • 低配虚拟机：1-2 核 CPU、1-4GB 内存的云主机
  • 边缘计算：树莓派、嵌入式设备
  • Demo 演示：快速体验 Pigsty 功能
  • 个人项目：资源有限的个人博客、小型应用

资源限制：

  • 1-3 核 CPU
  • 1-8 GB 内存
  • 有限的磁盘空间
  • 可能与其他服务共享资源

使用方法

在集群定义中指定 pg_conf = tiny.yml：

pg-dev:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-dev
    pg_conf: tiny.yml    # PostgreSQL 微型实例模板
    node_tune: tiny      # 操作系统微型实例调优

单节点开发环境：

pg-local:
  hosts:
    127.0.0.1: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-local
    pg_conf: tiny.yml    # PostgreSQL 微型实例模板
    node_tune: tiny      # 操作系统微型实例调优

参数详解

连接管理

max_connections: 250   # OLTP: 500-1000，减少连接开销
superuser_reserved_connections: 10

微型实例不需要处理大量并发连接，250 个连接足以应对开发测试场景。

内存配置

TINY 模板使用保守的内存分配策略：

参数计算公式说明
shared_buffers内存 × pg_shared_buffer_ratio默认比例 0.25
maintenance_work_memshared_buffers × 25%用于 VACUUM、CREATE INDEX
work_mem16MB - 256MB更小的排序/哈希内存
effective_cache_size总内存 - shared_buffers可用于缓存的预估内存

work_mem 计算逻辑（与 OLTP 不同）：

work_mem = min(max(shared_buffers / max_connections, 16MB), 256MB)

更小的 work_mem 上限（256MB vs OLTP 的 1GB）避免内存溢出。

并行查询（完全禁用）

TINY 模板完全禁用了并行查询：

max_worker_processes: cpu + 4 (最小12)      # OLTP: cpu + 8
max_parallel_workers: 50% × cpu (最小1)      # OLTP: 50% (最小2)
max_parallel_workers_per_gather: 0           # 禁用并行查询
max_parallel_maintenance_workers: 33% × cpu (最小1)

max_parallel_workers_per_gather: 0 确保查询不会启动并行工作进程，避免在低核心环境下争抢资源。

IO 配置（PG17+）

io_workers: 3   # 固定值，OLTP: 25% cpu (4-16)

固定的低 IO 工作线程数量，适合资源受限环境。

Vacuum 配置

vacuum_cost_delay: 20ms
vacuum_cost_limit: 2000
autovacuum_max_workers: 2          # OLTP: 3，减少一个工作进程
autovacuum_naptime: 1min
# autovacuum_vacuum_scale_factor 使用默认值
# autovacuum_analyze_scale_factor 使用默认值

减少 autovacuum 工作进程数量，降低后台资源占用。

查询优化

random_page_cost: 1.1
effective_io_concurrency: 200
default_statistics_target: 200     # OLTP: 400，降低统计精度以节省空间

较低的 default_statistics_target 减少 pg_statistic 表的大小。

日志配置

log_min_duration_statement: 100    # 与 OLTP 相同
log_statement: ddl
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024
# log_connections 使用默认设置（不额外记录）

TINY 模板不启用额外的连接日志，以减少日志量。

客户端超时

deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 10min   # 与 OLTP 相同

扩展配置

shared_preload_libraries: 'pg_stat_statements, auto_explain'

pg_stat_statements.max: 2500      # OLTP: 10000，减少内存占用
pg_stat_statements.track: all
pg_stat_statements.track_utility: off
pg_stat_statements.track_planning: off

pg_stat_statements.max 从 10000 降到 2500，减少约 75% 的内存占用。

与 OLTP 模板的主要差异

参数TINYOLTP差异原因
max_connections250500-1000减少连接开销
work_mem 上限256MB1GB避免内存溢出
max_worker_processescpu+4cpu+8减少后台进程
max_parallel_workers_per_gather020% cpu禁用并行查询
autovacuum_max_workers23减少后台负载
default_statistics_target200400节省空间
pg_stat_statements.max250010000减少内存占用
io_workers325% cpu固定低值

资源估算

以下是 TINY 模板在不同配置下的资源使用估算：

1 核 1GB 内存

shared_buffers: ~256MB
work_mem: ~16MB
maintenance_work_mem: ~64MB
max_connections: 250
max_worker_processes: ~12

PostgreSQL 进程内存占用：约 400-600MB

2 核 4GB 内存

shared_buffers: ~1GB
work_mem: ~32MB
maintenance_work_mem: ~256MB
max_connections: 250
max_worker_processes: ~12

PostgreSQL 进程内存占用：约 1.5-2GB

4 核 8GB 内存

此配置建议使用 OLTP 模板而非 TINY 模板：

pg-small:
  vars:
    pg_conf: oltp.yml   # 4核8GB可以使用OLTP模板

性能调优建议

进一步减少资源

如果资源极度受限，可以考虑：

pg_parameters:
  max_connections: 100           # 进一步减少
  shared_buffers: 128MB          # 进一步减少
  maintenance_work_mem: 32MB
  work_mem: 8MB

禁用不需要的扩展

pg_libs: 'pg_stat_statements'    # 只保留必要扩展

关闭不需要的功能

pg_parameters:
  track_io_timing: off           # 禁用 IO 时间跟踪
  track_functions: none          # 禁用函数跟踪

使用外部连接池

即使在微型实例上，使用 PgBouncer 也能显著提高并发能力：

pg-tiny:
  vars:
    pg_conf: tiny.yml
    pg_default_service_dest: pgbouncer
    pgbouncer_poolmode: transaction

云平台推荐规格

AWS

  • t3.micro：1 vCPU, 1GB RAM - 适合 TINY
  • t3.small：2 vCPU, 2GB RAM - 适合 TINY
  • t3.medium：2 vCPU, 4GB RAM - 可考虑 OLTP

阿里云

  • ecs.t6-c1m1.small：1 vCPU, 1GB RAM - 适合 TINY
  • ecs.t6-c1m2.small：1 vCPU, 2GB RAM - 适合 TINY
  • ecs.t6-c1m4.small：1 vCPU, 4GB RAM - 适合 TINY

腾讯云

  • SA2.SMALL1：1 vCPU, 1GB RAM - 适合 TINY
  • SA2.SMALL2：1 vCPU, 2GB RAM - 适合 TINY
  • SA2.SMALL4：1 vCPU, 4GB RAM - 适合 TINY

边缘设备部署

树莓派 4

pg-pi:
  hosts:
    192.168.1.100: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-pi
    pg_conf: tiny.yml       # PostgreSQL 微型实例模板
    node_tune: tiny         # 操作系统微型实例调优
    pg_storage_type: SSD    # 建议使用 SSD 存储

Docker 容器

pg-docker:
  hosts:
    172.17.0.2: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-docker
    pg_conf: tiny.yml       # PostgreSQL 微型实例模板
    node_tune: tiny         # 操作系统微型实例调优

升级到 OLTP

当您的应用增长，需要更多资源时，可以轻松升级到 OLTP 模板：

  1. 升级虚拟机规格（4核 8GB 以上）
  2. 修改集群配置：
pg-growing:
  vars:
    pg_conf: oltp.yml    # 从 tiny.yml 改为 oltp.yml
    node_tune: oltp      # 从 tiny 改为 oltp
  1. 重新配置集群或重新部署

参考资料