PostgreSQL 参数默认值与建议值

核心概念

PostgreSQL参数配置是优化数据库性能和安全性的重要手段。参数可以分为以下几类：

内存配置：控制数据库使用的内存资源
连接配置：管理客户端连接
优化器配置：影响查询计划生成
日志配置：控制日志记录的详细程度
WAL配置：管理Write-Ahead Logging
安全配置：控制数据库访问安全
资源限制：限制用户或查询使用的资源

内存配置参数

1. shared_buffers

默认值：

1GB以下内存系统：16MB
1GB以上内存系统：256MB

建议值：

对于专用数据库服务器，建议设置为系统内存的25%~40%
例如：8GB内存系统，建议设置为2GB；16GB内存系统，建议设置为4GB

配置方法：

sql

ALTER SYSTEM SET shared_buffers = '4GB';

2. work_mem

默认值：4MB

建议值：

根据查询复杂度和并发连接数调整
对于OLTP系统，建议设置为64MB~128MB
对于OLAP系统，建议设置为256MB~512MB
计算公式：work_mem = (系统内存 * 0.25) / max_connections

配置方法：

sql

ALTER SYSTEM SET work_mem = '64MB';

3. maintenance_work_mem

默认值：64MB

建议值：

建议设置为系统内存的10%~20%
最大不超过2GB
例如：16GB内存系统，建议设置为2GB

配置方法：

sql

ALTER SYSTEM SET maintenance_work_mem = '2GB';

4. effective_cache_size

默认值：4GB

建议值：

建议设置为系统内存的50%~75%
例如：16GB内存系统，建议设置为12GB
该参数不分配实际内存，仅用于优化器估计

配置方法：

sql

ALTER SYSTEM SET effective_cache_size = '12GB';

连接配置参数

1. max_connections

默认值：100

建议值：

根据系统资源和应用需求调整
对于OLTP系统，建议设置为200~500
对于OLAP系统，建议设置为50~100
计算公式：max_connections = (系统内存 * 0.8) / (每个连接使用的内存)

配置方法：

sql

ALTER SYSTEM SET max_connections = '200';

2. superuser_reserved_connections

默认值：3

建议值：

保持默认值或根据需要增加
建议设置为3~5

配置方法：

sql

ALTER SYSTEM SET superuser_reserved_connections = '5';

3. idle_in_transaction_session_timeout

默认值：0（禁用）

建议值：

建议设置为300秒（5分钟）
用于自动终止长时间空闲的事务

配置方法：

sql

ALTER SYSTEM SET idle_in_transaction_session_timeout = '300s';

4. statement_timeout

默认值：0（禁用）

建议值：

根据应用需求调整
对于OLTP系统，建议设置为30~60秒
对于OLAP系统，建议设置为300~600秒

配置方法：

sql

ALTER SYSTEM SET statement_timeout = '60s';

优化器配置参数

1. random_page_cost

默认值：4.0

建议值：

对于SSD存储，建议设置为1.1~1.5
对于HDD存储，建议设置为3.0~4.0
影响优化器选择索引扫描还是顺序扫描

配置方法：

sql

ALTER SYSTEM SET random_page_cost = '1.1';

2. effective_io_concurrency

默认值：1

建议值：

对于SSD存储，建议设置为200~400
对于HDD存储，建议设置为2~4
影响并行查询的I/O操作

配置方法：

sql

ALTER SYSTEM SET effective_io_concurrency = '200';

3. default_statistics_target

默认值：100

建议值：

对于复杂查询，建议设置为200~500
对于简单查询，建议保持默认值
影响优化器统计信息的详细程度

配置方法：

sql

ALTER SYSTEM SET default_statistics_target = '200';

4. enable_seqscan

默认值：on

建议值：

保持默认值on
除非有特殊需求，否则不建议禁用

配置方法：

sql

-- 一般不建议修改
ALTER SYSTEM SET enable_seqscan = 'on';

WAL配置参数

1. wal_buffers

默认值：

小于等于shared_buffers的3%，最大16MB

建议值：

建议设置为32MB~64MB
对于写入密集型应用，可以适当增加

配置方法：

sql

ALTER SYSTEM SET wal_buffers = '64MB';

2. checkpoint_timeout

默认值：5min

建议值：

建议设置为15min~30min
增加该值可以减少检查点次数，提高写入性能
但会增加崩溃恢复时间

配置方法：

sql

ALTER SYSTEM SET checkpoint_timeout = '30min';

3. max_wal_size

默认值：1GB

建议值：

建议设置为4GB~8GB
与checkpoint_timeout配合使用

配置方法：

sql

ALTER SYSTEM SET max_wal_size = '8GB';

4. min_wal_size

默认值：80MB

建议值：

建议设置为1GB~2GB
确保有足够的WAL空间

配置方法：

sql

ALTER SYSTEM SET min_wal_size = '2GB';

日志配置参数

1. log_statement

默认值：none

建议值：

生产环境：建议设置为'ddl'或'all'
开发环境：建议设置为'all'

配置方法：

sql

ALTER SYSTEM SET log_statement = 'ddl';

2. log_min_duration_statement

默认值：-1（禁用）

建议值：

建议设置为100ms~500ms
记录执行时间超过该值的语句

配置方法：

sql

ALTER SYSTEM SET log_min_duration_statement = '200ms';

3. log_checkpoints

默认值：off

建议值：

建议设置为on
记录检查点信息，便于性能分析

配置方法：

sql

ALTER SYSTEM SET log_checkpoints = 'on';

4. log_connections

默认值：off

建议值：

建议设置为on
记录连接信息，便于安全审计

配置方法：

sql

ALTER SYSTEM SET log_connections = 'on';

安全配置参数

1. password_encryption

默认值：scram-sha-256（PostgreSQL 14+）

建议值：

建议使用'scram-sha-256'，比'md5'更安全
PostgreSQL 14以下版本建议使用'md5'

配置方法：

sql

ALTER SYSTEM SET password_encryption = 'scram-sha-256';

2. ssl

默认值：off

建议值：

建议设置为on
启用SSL加密连接

配置方法：

sql

ALTER SYSTEM SET ssl = 'on';

3. listen_addresses

默认值：localhost

建议值：

对于生产环境，建议设置为特定的IP地址
不建议设置为'*'（允许所有IP访问）

配置方法：

sql

ALTER SYSTEM SET listen_addresses = '192.168.1.100,127.0.0.1';

4. max_connections

默认值：100

建议值：

根据实际需要调整，避免设置过大导致资源耗尽

配置方法：

sql

ALTER SYSTEM SET max_connections = '200';

资源限制参数

1. max_connections

默认值：100

建议值：

根据系统资源和应用需求调整
建议设置为200~500

配置方法：

sql

ALTER SYSTEM SET max_connections = '200';

2. max_locks_per_transaction

默认值：64

建议值：

对于复杂查询，可以适当增加
建议设置为128~256

配置方法：

sql

ALTER SYSTEM SET max_locks_per_transaction = '128';

3. max_pred_locks_per_transaction

默认值：64

建议值：

对于复杂查询，可以适当增加
建议设置为128~256

配置方法：

sql

ALTER SYSTEM SET max_pred_locks_per_transaction = '128';

配置验证与最佳实践

1. 验证配置生效

sql

-- 查看参数当前值
SHOW shared_buffers;

-- 查看参数来源
SELECT name, setting, source FROM pg_settings WHERE name = 'shared_buffers';

-- 查看所有参数
SHOW all;

2. 最佳实践

根据硬件调整：参数配置应根据服务器硬件资源调整
循序渐进：每次只修改少量参数，观察效果
测试验证：在测试环境验证参数变更效果
监控性能：使用监控工具跟踪参数变更对性能的影响
文档记录：记录参数变更的原因和效果
定期审查：定期审查参数配置，根据业务需求调整

3. 参数配置示例

以下是一个8GB内存专用数据库服务器的参数配置示例：

sql

-- 内存配置
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET effective_cache_size = '6GB';

-- 连接配置
ALTER SYSTEM SET max_connections = '200';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '300s';
ALTER SYSTEM SET statement_timeout = '60s';

-- 优化器配置
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET effective_io_concurrency = '200';
ALTER SYSTEM SET default_statistics_target = '200';

-- WAL配置
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET checkpoint_timeout = '30min';
ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM SET min_wal_size = '2GB';

-- 日志配置
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_min_duration_statement = '200ms';
ALTER SYSTEM SET log_checkpoints = 'on';
ALTER SYSTEM SET log_connections = 'on';

常见问题（FAQ）

Q1：如何确定参数的默认值？

A1：可以使用以下命令查看参数的默认值：

sql

-- 查看单个参数的默认值
SELECT name, reset_val FROM pg_settings WHERE name = 'shared_buffers';

-- 查看所有参数的默认值
SELECT name, reset_val FROM pg_settings;

Q2：修改参数后需要重启PostgreSQL吗？

A2：这取决于参数的类型：

动态参数：修改后立即生效，无需重启
静态参数：需要重启PostgreSQL才能生效

可以通过以下命令查看参数类型：

sql

SELECT name, vartype FROM pg_settings WHERE name = 'shared_buffers';

Q3：如何批量修改参数？

A3：可以使用以下方法批量修改参数：

直接编辑postgresql.conf文件，然后重启PostgreSQL
使用ALTER SYSTEM SET命令逐个修改动态参数
使用psql的\set命令批量执行ALTER SYSTEM SET命令

Q4：如何备份当前参数配置？

A4：可以使用以下命令备份当前参数配置：

sql

-- 导出所有参数配置
COPY (SELECT * FROM pg_settings) TO '/tmp/pg_settings_backup.csv' WITH CSV HEADER;

-- 或者使用psql命令
psql -c "SELECT name, setting FROM pg_settings" > /tmp/pg_settings_backup.txt

Q5：如何重置参数为默认值？

A5：可以使用以下命令重置参数为默认值：

sql

ALTER SYSTEM RESET shared_buffers;

-- 重置所有参数为默认值（谨慎使用）
-- 编辑postgresql.conf文件，删除所有自定义配置，然后重启PostgreSQL

PostgreSQL 参数默认值与建议值 ​

核心概念 ​

内存配置参数 ​

1. shared_buffers ​

2. work_mem ​

3. maintenance_work_mem ​

4. effective_cache_size ​

连接配置参数 ​

1. max_connections ​

2. superuser_reserved_connections ​

3. idle_in_transaction_session_timeout ​

4. statement_timeout ​

优化器配置参数 ​

1. random_page_cost ​

2. effective_io_concurrency ​

3. default_statistics_target ​

4. enable_seqscan ​

WAL配置参数 ​

1. wal_buffers ​

2. checkpoint_timeout ​

3. max_wal_size ​

4. min_wal_size ​

日志配置参数 ​

1. log_statement ​

2. log_min_duration_statement ​

3. log_checkpoints ​

4. log_connections ​

安全配置参数 ​

1. password_encryption ​

2. ssl ​

3. listen_addresses ​

4. max_connections ​

资源限制参数 ​

1. max_connections ​

2. max_locks_per_transaction ​

3. max_pred_locks_per_transaction ​

配置验证与最佳实践 ​

1. 验证配置生效 ​

2. 最佳实践 ​

3. 参数配置示例 ​

常见问题（FAQ） ​

Q1：如何确定参数的默认值？ ​

Q2：修改参数后需要重启PostgreSQL吗？ ​

Q3：如何批量修改参数？ ​

Q4：如何备份当前参数配置？ ​

Q5：如何重置参数为默认值？ ​

PostgreSQL 参数默认值与建议值

核心概念

内存配置参数

1. shared_buffers

2. work_mem

3. maintenance_work_mem

4. effective_cache_size

连接配置参数

1. max_connections

2. superuser_reserved_connections

3. idle_in_transaction_session_timeout

4. statement_timeout

优化器配置参数

1. random_page_cost

2. effective_io_concurrency

3. default_statistics_target

4. enable_seqscan

WAL配置参数

1. wal_buffers

2. checkpoint_timeout

3. max_wal_size

4. min_wal_size

日志配置参数

1. log_statement

2. log_min_duration_statement

3. log_checkpoints

4. log_connections

安全配置参数

1. password_encryption

2. ssl

3. listen_addresses

4. max_connections

资源限制参数

1. max_connections

2. max_locks_per_transaction

3. max_pred_locks_per_transaction

配置验证与最佳实践

1. 验证配置生效

2. 最佳实践

3. 参数配置示例

常见问题（FAQ）

Q1：如何确定参数的默认值？

Q2：修改参数后需要重启PostgreSQL吗？

Q3：如何批量修改参数？

Q4：如何备份当前参数配置？

Q5：如何重置参数为默认值？