Param Templates

Pigsty provides four preset Patroni/PostgreSQL config templates optimized for different workloads:

Use pg_conf to select a template; default is oltp.yml.

The database tuning template pg_conf should be paired with the OS tuning template node_tune.

Usage

Set pg_conf in your cluster definition. It’s recommended to set node_tune accordingly for OS-level tuning:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-test
    pg_conf: oltp.yml    # PostgreSQL config template (default)
    node_tune: oltp      # OS tuning template (default)

For critical financial workloads, use crit.yml:

pg-finance:
  hosts:
    10.10.10.21: { pg_seq: 1, pg_role: primary }
    10.10.10.22: { pg_seq: 2, pg_role: replica }
    10.10.10.23: { pg_seq: 3, pg_role: replica }
  vars:
    pg_cluster: pg-finance
    pg_conf: crit.yml    # PostgreSQL critical template
    node_tune: crit      # OS critical tuning

For low-spec VMs or dev environments, use tiny.yml:

pg-dev:
  hosts:
    10.10.10.31: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-dev
    pg_conf: tiny.yml    # PostgreSQL tiny template
    node_tune: tiny      # OS tiny tuning

Comparison

The four templates differ significantly in key parameters:

Connections & Memory

Parallel Query

Sync Replication

Vacuum Config

Timeout & Security

IO Config (PG17+)

Selection Guide

• OLTP Template: Default choice for most transaction processing. Ideal for e-commerce, social, gaming apps.

• OLAP Template: For data warehouses, BI reports, ETL. Allows large queries, high parallelism, relaxed timeouts.

• CRIT Template: For financial transactions, core accounting with strict consistency/security requirements. Forced sync replication, checksums, full audit.

• TINY Template: For dev/test environments, resource-constrained VMs, Raspberry Pi. Minimizes resource usage, disables parallel queries.

Custom Templates

Create custom templates based on existing ones. Templates are in roles/pgsql/templates/:

roles/pgsql/templates/
├── oltp.yml    # OLTP template (default)
├── olap.yml    # OLAP template
├── crit.yml    # CRIT critical template
└── tiny.yml    # TINY micro template

Steps to create a custom template:

1. Copy an existing template as base
2. Modify parameters as needed
3. Place in roles/pgsql/templates/
4. Reference via pg_conf

Example:

cp roles/pgsql/templates/oltp.yml roles/pgsql/templates/myapp.yml
# Edit myapp.yml as needed

Then use in your cluster:

pg-myapp:
  vars:
    pg_conf: myapp.yml

Templates use Jinja2 syntax; parameters are dynamically computed based on node resources (CPU, memory, disk).

Tuning Strategy

For technical details on template parameter optimization, see Tuning Strategy:

• Memory tuning (shared buffers, work mem, max connections)
• CPU tuning (parallel query worker config)
• Storage tuning (WAL size, temp file limits)
• Manual parameter adjustment

Related Parameters

• pg_conf: PostgreSQL config template
• node_tune: OS tuning template, should match pg_conf
• pg_rto: Recovery time objective, affects failover timeout
• pg_rpo: Recovery point objective, affects sync replication
• pg_max_conn: Override template max connections
• pg_shared_buffer_ratio: Shared buffer memory ratio
• pg_storage_type: Storage type, affects IO params