Parameters

PostgreSQL parameters can be configured at multiple levels with different scopes and precedence. Pigsty supports four configuration levels, from global to local:

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 are shared across all instances (primary and replicas) in a PostgreSQL cluster. In Pigsty, cluster parameters are managed via Patroni and stored in DCS (etcd by default).

Pigsty provides four pre-configured Patroni tuning templates optimized for different workloads, specified via pg_conf:

Template files are located in roles/pgsql/templates/ and contain auto-calculated values based on hardware specs. Templates are rendered to /etc/patroni/patroni.yml during cluster initialization. See Tuning Templates for details.

Before cluster creation, you can adjust these templates to modify initial parameters. Once initialized, parameter changes should be made via Patroni’s configuration management.

Patroni DCS Config

Patroni stores cluster config in DCS (etcd by default), ensuring consistent configuration across all members.

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:

1. Init: Template (e.g., oltp.yml) rendered via Jinja2 to /etc/patroni/patroni.yml
2. Start: Patroni reads local config, writes PostgreSQL parameters to DCS
3. 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):

1. postgresql.conf: Dynamically generated by Patroni with DCS cluster params
2. postgresql.base.conf: Loaded via include, static base config
3. 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 only to a single PostgreSQL instance, overriding cluster-level config. These are written to postgresql.auto.conf, which loads last and can override any cluster parameter.

This is a powerful technique for setting instance-specific values:

• 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

Use ./pgsql.yml -l <cls> -t pg_param to apply parameters, which renders 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

PostgreSQL has special parameters accepting comma-separated lists. In YAML config, the entire value must be quoted, 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:

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. Implemented via ALTER DATABASE ... SET, stored 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

Database params are set via ALTER DATABASE ... SET. Pigsty auto-selects correct syntax:

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: While log_destination is in the database whitelist, its context is sighup, so it cannot take effect at database level. Configure it at instance level (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 of a specific database user. Implemented via ALTER USER ... SET, also stored 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:

When a user connects to a specific database and the same parameter is set at both levels, PostgreSQL uses the user-level parameter since it 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)