PostgreSQL 参数修改最佳实践 - 文章 - 开发者社区

前言

在 PostgreSQL 中，对于参数的生效时机有如下几种类型：

postmaster：需要重启数据库才可以生效。
superuser-backend：该类型的参数可以由超级用户来改变，可以在 postgresql.conf 中对这些设置进行更改，而无需重新启动 PostgreSQL 实例，只对新连接生效。
user：不需要重启数据库，通过 set 命令即可设置。
internal：只读参数，无法通过配置文件进行修改。
backend ：不需要重启数据库，给 postmaster 进程发送 sighup 信号即可生效，只对新连接生效。
sighup：不需要重启数据库，给 postmaster 进程发送 sighup 信号即可生效。与 beckend 区别在于发送 sighup 之后，新的参数值会应用到现有的连接。
superuser ：不需要重启数据库，超级用户通过 set 命令即可设置。

可以通过如下命令来查看参数的生效的方式：

postgres=# select distinct(context) from pg_settings;

用户场景

用户想设置参数 max_parallel_workers 和 max_parallel_workers_per_gather 来改善特定分析场景下，特定应用程序的查询效率，但是不想全局生效，该如何设置？

postgres=# select name,setting,context from pg_settings where name in ('max_parallel_workers','max_parallel_workers_per_gather');
              name               | setting | context 
---------------------------------+---------+---------
 max_parallel_workers            | 8       | user
 max_parallel_workers_per_gather | 2       | user
(2 rows)

查看到这两个参数均可以在 session级别来进行设置，设置的方式有如下三种：

通过 session 设置

postgres=# set max_parallel_workers = 4;
SET
postgres=# set max_parallel_workers_per_gather = 4;
SET

通过 PGOPTIONS 环境变量设置

[postgres@rudonx ~]$ export PGOPTIONS='-c max_parallel_workers_per_gather=4 -c max_parallel_workers=8'
postgres=# select name,setting,context from pg_settings where name in ('max_parallel_workers','max_parallel_workers_per_gather');
              name               | setting | context 
---------------------------------+---------+---------
 max_parallel_workers            | 8       | user
 max_parallel_workers_per_gather | 4       | user
(2 rows)

通过连接串指定

如果是 psql 客户端，可以使用如下方式进行设置：

psql "host=dbhost port=dbport dbname=dbname user=dbuser password=dbpassword options='-c max_parallel_workers_per_gather=4 -c max_parallel_workers=8' "

或者可以使用下面的这种形式

psql postgresql://dbuser@dbhost:dbport/dbname?options=-c%20max_parallel_workers_per_gather%3D4%20-c%20max_parallel_workers%3D8

如果使用的是 jdbc，如下指定连接串即可：

Properties props = new Properties();
props.setProperty("options", "-c max_parallel_workers_per_gather=4 -c max_parallel_workers=8");
Connection conn = DriverManager.getConnection(url, props);
或者使用如下形式：
String url = "jdbc:postgresql://dbuser@dbhost:dbport/dbname?options=-c%20max_parallel_workers_per_gather=4%20-c%20max_parallel_workers=8";
Connection conn = DriverManager.getConnection(url);