# Parameters

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

---

LLMS index: [llms.txt](/llms.txt)

---

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

| Level                         | Scope                    | Configuration Method               | Storage Location           |
|-------------------------------|--------------------------|------------------------------------|-----------------------------|
| [**Cluster**](#cluster-level) | All instances in cluster | Patroni DCS / Tuning Templates     | etcd + `postgresql.conf`    |
| [**Instance**](#instance-level) | Single PG instance     | `pg_parameters` / `ALTER SYSTEM`   | `postgresql.auto.conf`      |
| [**Database**](#database-level) | All sessions in a DB   | `pg_databases[].parameters`        | `pg_db_role_setting`        |
| [**User**](#user-level)       | All sessions of a user   | `pg_users[].parameters`            | `pg_db_role_setting`        |
{.full-width}

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**](https://www.postgresql.org/docs/current/runtime-config.html).


----------------

## 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**](/docs/pgsql/admin/patroni) and stored in DCS (etcd by default).

Pigsty provides four pre-configured [**Patroni tuning templates**](/docs/pgsql/template) optimized for different workloads, specified via [**`pg_conf`**](/docs/pgsql/param#pg_conf):

| Template                                    | Use Case           | Characteristics                    |
|---------------------------------------------|--------------------|------------------------------------|
| [**`oltp.yml`**](/docs/pgsql/template/oltp) | OLTP transactions  | Low latency, high concurrency (default) |
| [**`olap.yml`**](/docs/pgsql/template/olap) | OLAP analytics     | Large queries, high throughput     |
| [**`crit.yml`**](/docs/pgsql/template/crit) | Critical/Financial | Max durability, safety over perf   |
| [**`tiny.yml`**](/docs/pgsql/template/tiny) | Tiny instances     | Resource-constrained, dev/test     |
{.full-width}

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**](/docs/pgsql/template) 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**](/docs/pgsql/admin/patroni).


### Patroni DCS Config

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

**Storage Structure**:

```text
/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:

```text
/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`**](/docs/pgsql/param#pg_parameters) to define instance-level parameters:

```yaml
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:

```yaml
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`**](https://www.postgresql.org/docs/current/sql-altersystem.html):

```sql
-- 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:

```yaml
# 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'`                       |
{.full-width}

**Rendering Example**:

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

```ini
# 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`**](https://www.postgresql.org/docs/current/sql-alterdatabase.html), stored in `pg_db_role_setting`.


### Configuration

Use the `parameters` field in [**`pg_databases`**](/docs/pgsql/param#pg_databases):

```yaml
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:

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

**Scalar params** with quoted values:

```sql
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

```sql
-- 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

```sql
-- 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`**](https://www.postgresql.org/docs/current/sql-alterrole.html), also stored in `pg_db_role_setting`.


### Configuration

Use the `parameters` field in [**`pg_users`**](/docs/pgsql/param#pg_users) or [**`pg_default_roles`**](/docs/pgsql/param#pg_default_roles):

```yaml
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:

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

**Scalar params** with quoted values:

```sql
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:

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

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


### View User Params

```sql
-- 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

```sql
-- 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):

```text
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**:

```yaml
# 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)