Parameters

Configure PostgreSQL parameters at cluster, instance, database, and user levels

You can set PostgreSQL parameters at several levels with different scopes and priorities. Pigsty supports four levels, from global to local:

Level	Scope	Configuration Method	Storage Location
Cluster	All instances in cluster	Patroni DCS / Tuning Templates	etcd + `postgresql.conf`
Instance	Single PG instance	`pg_parameters` / `ALTER SYSTEM`	`postgresql.auto.conf`
Database	All sessions in a DB	`pg_databases[].parameters`	`pg_db_role_setting`
User	All sessions of a user	`pg_users[].parameters`	`pg_db_role_setting`

Priority from low to high: Cluster < Instance < Database < User < Session (SET command). Higher priority settings override lower ones.

For complete PostgreSQL parameter documentation, see PostgreSQL Docs: Server Configuration.

Cluster Level

Cluster-level parameters apply to every primary and replica in a PostgreSQL cluster. Pigsty manages them through Patroni and stores them in DCS, which is etcd by default.

Pigsty provides four Patroni tuning templates for different workloads. Select one with pg_conf:

Template	Use Case	Characteristics
`oltp.yml`	OLTP transactions	Low latency, high concurrency (default)
`olap.yml`	OLAP analytics	Large queries, high throughput
`crit.yml`	Critical/Financial	Max durability, safety over perf
`tiny.yml`	Tiny instances	Resource-constrained, dev/test

Template files are in roles/pgsql/templates/. They calculate parameter values from the hardware specifications. During cluster initialization, Pigsty generates /etc/patroni/patroni.yml from the template. See Tuning Templates.

Before cluster creation, adjust the templates to change initial parameters. After initialization, change parameters through Patroni configuration management.

Patroni DCS Config

Patroni stores cluster configuration in DCS, which is etcd by default. This keeps all members configured consistently.

Storage Structure:

/pigsty/                          # namespace (patroni_namespace)
  └── pg-meta/                    # cluster name (pg_cluster)
      ├── config                  # cluster config (shared)
      ├── leader                  # current primary info
      ├── members/                # member registration
      │   ├── pg-meta-1
      │   └── pg-meta-2
      └── ...

Rendering Flow:

Init: Template (e.g., oltp.yml) rendered via Jinja2 to /etc/patroni/patroni.yml
Start: Patroni reads local config, writes PostgreSQL parameters to DCS
Runtime: Patroni periodically syncs DCS config to local PostgreSQL

Local Cache:

Each Patroni instance caches DCS config locally at /pg/conf/<instance>.yml:

On start: Load from DCS, cache locally
Runtime: Periodically sync DCS to local cache
DCS unavailable: Continue with local cache (no failover possible)

Config File Hierarchy

Patroni renders DCS config to local PostgreSQL config files:

/pg/data/
├── postgresql.conf          # Main config (managed by Patroni)
├── postgresql.base.conf     # Base config (via include directive)
├── postgresql.auto.conf     # Instance overrides (ALTER SYSTEM)
├── pg_hba.conf              # Client auth config
└── pg_ident.conf            # User mapping config

Load Order (priority low to high):

postgresql.conf: Dynamically generated by Patroni with DCS cluster params
postgresql.base.conf: Loaded via include, static base config
postgresql.auto.conf: Auto-loaded by PostgreSQL, instance overrides

Since postgresql.auto.conf loads last, its parameters override earlier files.

Instance Level

Instance-level parameters apply to one PostgreSQL instance and override cluster-level configuration. Pigsty writes them to postgresql.auto.conf. This file loads last and can override any cluster parameter.

Use instance parameters for settings such as:

Set hot_standby_feedback = on on replicas
Adjust work_mem or maintenance_work_mem for specific instances
Set recovery_min_apply_delay for delayed replicas

Using pg_parameters

In Pigsty config, use pg_parameters to define instance-level parameters:

pg-meta:
  hosts:
    10.10.10.10:
      pg_seq: 1
      pg_role: primary
      pg_parameters:                              # instance-level params
        log_statement: all                        # log all SQL for this instance only
  vars:
    pg_cluster: pg-meta
    pg_parameters:                                # cluster default instance params
      log_timezone: Asia/Shanghai
      log_min_duration_statement: 1000

Run ./pgsql.yml -l <cls> -t pg_param to write these parameters to postgresql.auto.conf.

Override Hierarchy

pg_parameters can be defined at different Ansible config levels, priority low to high:

all:
  vars:
    pg_parameters:                    # global default
      log_statement: none

  children:
    pg-meta:
      vars:
        pg_parameters:                # cluster override
          log_statement: ddl
      hosts:
        10.10.10.10:
          pg_parameters:              # instance override (highest)
            log_statement: all

Using ALTER SYSTEM

You can also modify instance parameters at runtime via ALTER SYSTEM:

-- Set parameters
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET log_min_duration_statement = 1000;

-- Reset to default
ALTER SYSTEM RESET work_mem;
ALTER SYSTEM RESET ALL;  -- Reset all ALTER SYSTEM settings

-- Reload config to take effect
SELECT pg_reload_conf();

ALTER SYSTEM writes to postgresql.auto.conf.

Note: In Pigsty-managed clusters, postgresql.auto.conf is managed by Ansible via pg_parameters. Manual ALTER SYSTEM changes may be overwritten on next playbook run. Use pg_parameters in pigsty.yml for persistent instance-level params.

List-Type Parameters

Some PostgreSQL parameters accept comma-separated lists. In YAML, quote the entire value. Otherwise, YAML parses it as an array:

# Correct: quote the entire value
pg_parameters:
  shared_preload_libraries: 'timescaledb, pg_stat_statements'
  search_path: '"$user", public, app'

# Wrong: unquoted causes YAML parse error
pg_parameters:
  shared_preload_libraries: timescaledb, pg_stat_statements   # YAML parses as array!

Pigsty auto-detects these list parameters and renders them without outer quotes:

Parameter	Description	Example Value
`shared_preload_libraries`	Preload shared libs	`'timescaledb, pg_stat_statements'`
`search_path`	Schema search path	`'"$user", public, app'`
`local_preload_libraries`	Local preload libs	`'auto_explain'`
`session_preload_libraries`	Session preload libs	`'pg_hint_plan'`
`log_destination`	Log output targets	`'csvlog, stderr'`
`unix_socket_directories`	Unix socket dirs	`'/var/run/postgresql, /tmp'`
`temp_tablespaces`	Temp tablespaces	`'ssd_space, hdd_space'`
`debug_io_direct`	Direct I/O mode (PG16+)	`'data, wal'`

Rendering Example:

# pigsty.yml config (quotes required in YAML)
pg_parameters:
  shared_preload_libraries: 'timescaledb, pg_stat_statements'
  search_path: '"$user", public, app'
  work_mem: 64MB

# Rendered postgresql.auto.conf (list params unquoted)
shared_preload_libraries = timescaledb, pg_stat_statements
search_path = "$user", public, app
work_mem = '64MB'

Database Level

Database-level parameters apply to all sessions connected to a specific database. ALTER DATABASE ... SET sets them in pg_db_role_setting.

Configuration

Use the parameters field in pg_databases:

pg_databases:
  - name: analytics
    owner: dbuser_analyst
    parameters:
      work_mem: 256MB                              # analytics needs more memory
      maintenance_work_mem: 1GB                    # large table maintenance
      statement_timeout: 10min                     # allow long queries
      search_path: '"$user", public, mart'         # list param needs quotes

Like instance-level params, list-type values must be quoted in YAML.

Rendering Rules

Pigsty sets database parameters with ALTER DATABASE ... SET. It selects the required syntax automatically:

List-type params (search_path, temp_tablespaces, local_preload_libraries, session_preload_libraries, log_destination) without outer quotes:

ALTER DATABASE "analytics" SET "search_path" = "$user", public, mart;

Scalar params with quoted values:

ALTER DATABASE "analytics" SET "work_mem" = '256MB';
ALTER DATABASE "analytics" SET "statement_timeout" = '10min';

Note: log_destination is in the database whitelist, but its context is sighup. It cannot take effect at database level. Set it at instance level with pg_parameters.

View Database Params

-- View params for a specific database
SELECT datname, unnest(setconfig) AS setting
FROM pg_db_role_setting drs
JOIN pg_database d ON d.oid = drs.setdatabase
WHERE drs.setrole = 0 AND datname = 'analytics';

Manual Management

-- Set params
ALTER DATABASE analytics SET work_mem = '256MB';
ALTER DATABASE analytics SET search_path = "$user", public, myschema;

-- Reset params
ALTER DATABASE analytics RESET work_mem;
ALTER DATABASE analytics RESET ALL;

User Level

User-level parameters apply to all sessions for a specific database user. ALTER USER ... SET sets them in pg_db_role_setting.

Configuration

Use the parameters field in pg_users or pg_default_roles:

pg_users:
  - name: dbuser_analyst
    password: DBUser.Analyst
    parameters:
      work_mem: 256MB                              # more memory for analytics
      statement_timeout: 5min                      # allow longer queries
      search_path: '"$user", public, analytics'    # list param needs quotes
      log_statement: all                           # log all SQL

Rendering Rules

Same as database-level:

List-type params (search_path, temp_tablespaces, local_preload_libraries, session_preload_libraries) without outer quotes:

ALTER USER "dbuser_analyst" SET "search_path" = "$user", public, analytics;

Scalar params with quoted values:

ALTER USER "dbuser_analyst" SET "work_mem" = '256MB';
ALTER USER "dbuser_analyst" SET "statement_timeout" = '5min';

DEFAULT Value

Use DEFAULT (case-insensitive) to reset a parameter to PostgreSQL default:

parameters:
  work_mem: DEFAULT          # reset to default
  statement_timeout: 30s     # set specific value

ALTER USER "dbuser_app" SET "work_mem" = DEFAULT;
ALTER USER "dbuser_app" SET "statement_timeout" = '30s';

View User Params

-- View params for a specific user
SELECT rolname, unnest(setconfig) AS setting
FROM pg_db_role_setting drs
JOIN pg_roles r ON r.oid = drs.setrole
WHERE rolname = 'dbuser_analyst';

Manual Management

-- Set params
ALTER USER dbuser_app SET work_mem = '128MB';
ALTER USER dbuser_app SET search_path = "$user", public, myschema;

-- Reset params
ALTER USER dbuser_app RESET work_mem;
ALTER USER dbuser_app RESET ALL;

Priority

When the same parameter is set at multiple levels, PostgreSQL applies this priority (low to high):

postgresql.conf           ← Cluster params (Patroni DCS)
       ↓
postgresql.auto.conf      ← Instance params (pg_parameters / ALTER SYSTEM)
       ↓
Database level            ← ALTER DATABASE SET
       ↓
User level                ← ALTER USER SET
       ↓
Session level             ← SET command

Database vs User Priority:

If the same parameter is set for both the database and the connecting user, PostgreSQL uses the user-level parameter. The user setting has higher priority.

Example:

# Database: analytics has work_mem = 256MB
pg_databases:
  - name: analytics
    parameters:
      work_mem: 256MB

# User: analyst has work_mem = 512MB
pg_users:
  - name: analyst
    parameters:
      work_mem: 512MB

analyst connecting to analytics: work_mem = 512MB (user takes precedence)
Other users connecting to analytics: work_mem = 256MB (database applies)
analyst connecting to other DBs: work_mem = 512MB (user applies)

Parameters

Cluster Level

Patroni DCS Config

Config File Hierarchy

Instance Level

Using pg_parameters

Override Hierarchy

Using ALTER SYSTEM

List-Type Parameters

Database Level

Configuration

Rendering Rules

View Database Params

Manual Management

User Level

Configuration

Rendering Rules

DEFAULT Value

View User Params

Manual Management

Priority

Was this page helpful?