跳转到主要内容

这是本节的多页打印视图。 .

返回本页常规视图.

场景模板

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

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

模板 CPU 核心 适用场景 特点
/docs/pgsql/template/oltp.yml 4-128C OLTP 事务处理 高并发、低延迟、高吞吐
/docs/pgsql/template/olap.yml 4-128C OLAP 分析处理 大查询、高并行、长事务
/docs/pgsql/template/crit.yml 4-128C 一致性优先业务 一致性优先、详细审计
/docs/pgsql/template/tiny.yml 1-3C 微型实例 资源受限、低配环境

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

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

四套标准模板都将 wal_level 设为 logical。从 PostgreSQL 18.6 起，服务端新增 output_plugin_libraries 安全白名单；Pigsty 默认允许内置的 pgoutput、test_decoding 与默认随 pgsql-main 安装的 wal2json。如果要使用其他逻辑解码输出插件，应在评审其代码与权限边界后，将准确的库名加入 pg_parameters；Patroni 会在不支持该参数的旧 PostgreSQL 版本上过滤模板项。

使用模板

要使用特定的配置模板，只需在集群定义中设置 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      # 操作系统调优模板（默认值）

对于核心金融业务场景，您可以使用 /docs/pgsql/template/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      # 操作系统关键业务调优

对于低配虚拟机或开发环境，可以使用 /docs/pgsql/template/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      # 操作系统微型实例调优

模板对比

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

连接与内存

参数 OLTP OLAP CRIT TINY
max_connections 500/1000 500 500/1000 250
work_mem 范围 64MB-1GB 64MB-8GB 64MB-1GB 16MB-256MB
maintenance_work_mem 25% 共享缓冲区 50% 共享缓冲区 25% 共享缓冲区 25% 共享缓冲区
max_locks_per_transaction 1-2x maxconn 2-4x maxconn 1-2x maxconn 1-2x maxconn

并行查询

参数 OLTP OLAP CRIT TINY
max_worker_processes max(cpu+16, 24) max(cpu+20, 28) max(cpu+16, 24) max(cpu+12, 20)
max_parallel_workers 50% cpu 80% cpu 50% cpu 50% cpu
max_parallel_workers_per_gather 20% cpu (max 8) 50% cpu 0（禁用） 0（禁用）
parallel_setup_cost 2000 1000 2000 1000
parallel_tuple_cost 0.2 0.1 0.2 0.1

同步复制

参数 OLTP OLAP CRIT TINY
synchronous_mode 取决于 pg_rpo 取决于 pg_rpo 强制开启 取决于 pg_rpo
data_checksums 可选 可选 强制开启 可选

Vacuum 配置

参数 OLTP OLAP CRIT TINY
vacuum_cost_delay 20ms 10ms 20ms 20ms
vacuum_cost_limit 2000 10000 2000 2000
autovacuum_max_workers 3 3 3 2

超时与安全

参数 OLTP OLAP CRIT TINY
idle_in_transaction_session_timeout 10min 禁用 1min 10min
log_min_duration_statement 100ms 1000ms 100ms 100ms
default_statistics_target 400 1000 400 200
track_activity_query_size 8KB 8KB 32KB 8KB
log_connections 仅授权 仅授权 全部阶段 默认

IO 配置（PG18）

参数 OLTP OLAP CRIT TINY
io_workers 25% cpu (4-16) 50% cpu (4-32) 25% cpu (4-8) 3
temp_file_limit 1/20 磁盘，上限 100GB 1/5 磁盘，上限 400GB 1/20 磁盘，上限 100GB 1/20 磁盘，上限 100GB

选择建议

  • 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：候选副本落后阈值；设为 0 时通用模板启用同步复制
  • pg_max_conn：覆盖模板的最大连接数
  • pg_shared_buffer_ratio：共享缓冲区占内存比例
  • pg_storage_type：存储类型，影响 IO 相关参数

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

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

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

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

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

内存参数调整

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

默认情况下，Pigsty 使用 25% 的内存作为 PostgreSQL 共享缓冲区；其余内存还会被连接、work_mem、后台进程及操作系统缓存共同使用。

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

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

其中对于 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 核数进行参数优化。 模板先计算并行/扩展 worker 预算，然后在写入 max_worker_processes 时再额外增加 8 个保留槽。因此最终 GUC 比模板顶部的中间变量多 8。

OLTP 设置逻辑 范围限制
max_worker_processes max(CPU + 8, 16) + 8 即 max(CPU + 16, 24)
max_parallel_workers max(ceil(50% CPU), 2) 1/2 CPU 上取整，最少两个
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU 上取整，最少两个
max_parallel_workers_per_gather min(max(ceil(20% CPU), 2),8) 1/5 CPU 下取整，最少两个，最多 8 个
OLAP 设置逻辑 范围限制
max_worker_processes max(CPU + 12, 20) + 8 即 max(CPU + 20, 28)
max_parallel_workers max(ceil(80% CPU, 2)) 4/5 CPU 上取整，最少两个
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU 上取整，最少两个
max_parallel_workers_per_gather max(floor(50% CPU), 2) 1/2 CPU 上取整，最少两个
CRIT 设置逻辑 范围限制
max_worker_processes max(CPU + 8, 16) + 8 即 max(CPU + 16, 24)
max_parallel_workers max(ceil(50% CPU), 2) 1/2 CPU 上取整，最少两个
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU 上取整，最少两个
max_parallel_workers_per_gather 0, 按需启用
TINY 设置逻辑 范围限制
max_worker_processes max(CPU + 4, 12) + 8 即 max(CPU + 12, 20)
max_parallel_workers max(floor(50% CPU), 1) 50% CPU 下取整，最少 1 个
max_parallel_maintenance_workers max(floor(33% CPU), 1) 33% CPU 下取整，最少 1 个
max_parallel_workers_per_gather 0，按需启用 禁用单个查询的并行 gather

请注意，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: 32MB  # 4x default 8MB, prefer non-parallel scan
min_parallel_index_scan_size: 2MB   # 4x default 512kB, prefer non-parallel scan

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

存储空间参数

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

{% raw %}
{% set pg_size_twentieth = ([([(node_fs_bytes|int / 21474836480)|round(0, 'ceil')|int, 1])|max, 100])|min %}
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 %}
  • pg_size_twentieth 先按磁盘容量的 1/20 向上取整，并限制在 1～100GB。
  • 因此前三种标准模板中，temp_file_limit 与 min_wal_size 的 实际有效上限是 100GB。
  • max_wal_size 的实际有效上限是 400GB。
  • max_slot_wal_keep_size 的实际有效上限是 600GB。

OLAP 模板将 temp_file_limit 设为 pg_size_twentieth × 4，实际有效上限是 400GB。模板行尾现有的 200GB/2TB/3TB 注释没有计入 pg_size_twentieth 自身的 100GB 上限，应以渲染表达式为准。

手工调整参数

除了使用 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
  • 当 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: max(cpu + 16, 24)
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: 32MB   # 默认值 8MB 的四倍，倾向于不使用并行扫描
min_parallel_index_scan_size: 2MB    # 默认值 512kB 的四倍，倾向于不使用并行扫描

WAL 配置 

min_wal_size: 磁盘/20 (实际最大100GB)
max_wal_size: 磁盘/5 (实际最大400GB)
max_slot_wal_keep_size: 磁盘×3/10 (实际最大600GB)
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）
  • 锁等待：锁等待时间、死锁次数
  • 复制延迟：从库延迟时间和字节数

参考资料

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_mem shared_buffers × 50% 加速索引创建和 VACUUM
work_mem 64MB - 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: max(cpu + 20, 28)      # OLTP: max(cpu + 16, 24)
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 配置（PG18） 

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

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

WAL 配置 

min_wal_size: 磁盘/20 (实际最大100GB)
max_wal_size: 磁盘/5 (实际最大400GB)
max_slot_wal_keep_size: 磁盘×3/10 (实际最大600GB)
temp_file_limit: 磁盘/5 (实际最大400GB)   # OLTP: 磁盘/20，实际最大100GB

更大的 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 模板的主要差异

参数 OLAP OLTP 差异原因
max_connections 500 500-1000 分析负载连接数少
work_mem 上限 8GB 1GB 支持更大的内存排序
maintenance_work_mem 50% buffer 25% buffer 加速索引创建
max_locks_per_transaction 2-4x 1-2x 更多表参与查询
max_parallel_workers 80% cpu 50% cpu 激进并行
max_parallel_workers_per_gather 50% cpu 20% cpu 激进并行
parallel_setup_cost 1000 2000 默认值，鼓励并行
parallel_tuple_cost 0.1 0.2 默认值，鼓励并行
enable_partitionwise_join on off 分区表优化
enable_partitionwise_aggregate on off 分区表优化
vacuum_cost_delay 10ms 20ms 激进 vacuum
vacuum_cost_limit 10000 2000 激进 vacuum
temp_file_limit 1/5 磁盘 1/20 磁盘 允许更大临时文件
io_workers 50% cpu 25% cpu 更多并行 IO
log_min_duration_statement 1000ms 100ms 放宽慢查询阈值
default_statistics_target 1000 400 更精确统计
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 面向一致性和审计要求较高的事务型业务。它强制启用数据校验和与 Patroni 严格同步模式，增加连接日志，并调整部分 WAL、超时和并行查询参数。

该模板会增加写入延迟，并可能在没有可用同步副本时阻塞写入。使用前应确认一致性目标、故障域、客户端提交设置和可用性要求。

建议同时评估 node_tune: crit，但主机调优与数据库参数可以独立选择。

使用方法 

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
    node_tune: crit

三节点拓扑可以在一个节点故障后保留重新选择同步副本的空间，但是否持续可写还取决于剩余节点状态、DCS、网络和同步副本选择。应在目标拓扑上执行故障演练。

严格同步复制

CRIT 不使用 pg_rpo 推导同步模式，而是固定启用：

synchronous_mode: true
synchronous_mode_strict: true

synchronous_mode_strict 禁止 Patroni 在没有同步副本时自动退回异步复制，因此主库会阻塞需要同步确认的写入。

在以下前提下，该模式以不丢失已确认事务为目标：

  • 会话没有将 synchronous_commit 降低为 local、off 等异步级别；
  • 提交时同步副本正常确认 WAL；
  • 故障切换只选择包含所需 WAL 的合格节点。

因此，RPO 结论必须结合客户端参数、复制状态和故障模型验证，不能只根据模板名称确定。

需要多个同步副本确认时，可以通过 Patroni 动态配置设置：

pg edit-config pg-critical
synchronous_node_count: 2

同步副本数量越多，可接受写入的节点条件越严格。

数据校验和

CRIT 初始化配置始终包含：

initdb:
  - data-checksums

这会忽略 pg_checksum 的关闭设置，并为新集群启用页级校验和。校验和用于发现写入后发生的页面损坏，不检测逻辑错误或所有内存错误。

连接与查询日志

CRIT 记录 DDL、执行时间超过 100 ms 的语句和连接断开事件：

log_statement: ddl
log_min_duration_statement: 100
log_disconnections: 'on'

PostgreSQL 18 及以上版本使用：

log_connections: 'receipt,authentication,authorization'

较早版本使用 log_connections: on。这些日志可以支持连接审计，但不等同于 SQL 细粒度审计。需要记录对象读写、角色或语句类别时，应另行启用 pgaudit。

track_activity_query_size 设置为 32 KiB，以保留更长的活动查询文本。日志可能包含 SQL 和业务数据，应限制访问并设置合适的保留周期。

Watchdog

CRIT 会将默认关闭的 Patroni watchdog 模式转换为 automatic：

watchdog:
  mode: automatic
  device: /dev/watchdog

automatic 仅在系统存在可用 watchdog 设备时启用。需要强制 fencing 时，应确认硬件、虚拟化平台和设备权限后显式使用 required；配置错误会影响主库启动和故障切换。

主要参数差异

参数 CRIT OLTP 默认 影响
synchronous_mode 固定启用 由 pg_rpo 推导 一致性优先
synchronous_mode_strict true 按通用模板 无同步副本时阻塞写入
data-checksums 强制启用 由 pg_checksum 控制 页面损坏检测
max_parallel_workers_per_gather 0 根据 CPU 计算 降低并行查询波动
wal_writer_delay 10ms 20ms 更频繁处理 WAL
wal_writer_flush_after 0 1MB 改变 WAL 刷写行为
idle_replication_slot_timeout 3d 7d 更快清理闲置复制槽
idle_in_transaction_session_timeout 1min 10min 更快终止空闲事务
track_activity_query_size 32KiB 8KiB 保留更长查询文本
log_connections 详细连接事件 PostgreSQL 18 默认记录授权 增加连接审计信息
log_disconnections on off 记录断开事件

CRIT 还会禁用单个查询的并行 gather，并调整并行成本、autovacuum、WAL 和统计参数。完整值以实际版本的 roles/pgsql/templates/crit.yml 为准。

预加载扩展

CRIT 使用 pg_libs 生成 shared_preload_libraries。由于角色默认值已经将 pg_libs 设置为：

pg_libs: 'pg_stat_statements, auto_explain'

单独选择 crit.yml 不会自动加载 passwordcheck。需要口令复杂度检查时应显式配置：

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

ha/safe 已包含该覆盖值。需要 pgaudit 时，也应显式加入 pg_libs 并配置审计范围：

pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain, pgaudit'
pg_parameters:
  pgaudit.log: 'ddl, role, write'

性能与可用性影响

  • 同步提交需要等待同步副本，写入延迟至少包含副本网络与 WAL 持久化时间；
  • 严格同步模式在没有可用同步副本时阻塞写入；
  • 禁用并行 gather 可能降低大型查询吞吐，但减少并行执行造成的资源波动；
  • 更详细的日志和统计会增加 I/O、CPU 与存储占用；
  • 更短的空闲事务超时可能终止长时间保持事务但未执行语句的应用会话。

影响大小取决于硬件、网络、查询和客户端行为，应使用实际负载测试，不宜采用固定的延迟或吞吐百分比。

上线检查

  • 至少部署一个可用同步副本，并验证节点故障时的写入行为
  • 检查应用是否修改 synchronous_commit
  • 根据可用性要求选择 watchdog automatic 或 required
  • 验证连接日志的采集、访问权限和保留周期
  • 需要口令检查或 SQL 审计时，显式配置 pg_libs 和扩展参数
  • 使用生产负载测试写入延迟、吞吐和空闲事务超时
  • 执行主库、同步副本、DCS 和网络分区故障演练

相关文档

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_mem shared_buffers × 25% 用于 VACUUM、CREATE INDEX
work_mem 16MB - 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: max(cpu + 12, 20)     # OLTP: max(cpu + 16, 24)
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 配置（PG18） 

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 模板的主要差异

参数 TINY OLTP 差异原因
max_connections 250 500-1000 减少连接开销
work_mem 上限 256MB 1GB 避免内存溢出
max_worker_processes max(cpu+12, 20) max(cpu+16, 24) 减少后台进程
max_parallel_workers_per_gather 0 20% cpu 禁用并行查询
autovacuum_max_workers 2 3 减少后台负载
default_statistics_target 200 400 节省空间
pg_stat_statements.max 2500 10000 减少内存占用
io_workers 3 25% cpu 固定低值

资源估算

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

1 核 1GB 内存 

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

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

2 核 4GB 内存 

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

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. 重新配置集群 或重新部署

参考资料