Skip to content

This is the multi-page printable view of this section. .

Return to the regular view of this page.

Param Templates

Use Pigsty’s built-in Patroni config templates or customize your own

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

Template CPU Cores Use Case Characteristics
/docs/pgsql/template/oltp.yml 4-128C OLTP transactions High concurrency, low latency
/docs/pgsql/template/olap.yml 4-128C OLAP analytics Large queries, high parallelism
/docs/pgsql/template/crit.yml 4-128C Consistency-first Consistency-first, detailed auditing
/docs/pgsql/template/tiny.yml 1-3C Tiny instances Resource-constrained envs

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

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

All four standard templates set wal_level to logical. PostgreSQL 18.6 adds the output_plugin_libraries security allowlist; Pigsty permits the built-in pgoutput and test_decoding plugins plus wal2json, which is installed by the default pgsql-main package set. To use another logical-decoding output plugin, review its code and privilege boundary, then add its exact library name through pg_parameters. Patroni filters the template setting on older PostgreSQL versions that do not support it.

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 /docs/pgsql/template/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 /docs/pgsql/template/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

Parameter OLTP OLAP CRIT TINY
max_connections 500/1000 500 500/1000 250
work_mem range 64MB-1GB 64MB-8GB 64MB-1GB 16MB-256MB
maintenance_work_mem 25% shmem 50% shmem 25% shmem 25% shmem
max_locks_per_transaction 1-2x maxconn 2-4x maxconn 1-2x maxconn 1-2x maxconn

Parallel Query

Parameter OLTP OLAP CRIT TINY
max_worker_processes max(cpu+16, 24) max(cpu+20, 28) max(cpu+16, 24) max(cpu+12, 20)
max_parallel_workers 50% cpu 80% cpu 50% cpu 50% cpu
max_parallel_workers_per_gather 20% cpu (max 8) 50% cpu 0 (off) 0 (off)
parallel_setup_cost 2000 1000 2000 1000
parallel_tuple_cost 0.2 0.1 0.2 0.1

Sync Replication

Parameter OLTP OLAP CRIT TINY
synchronous_mode depends pg_rpo depends pg_rpo forced on depends pg_rpo
data_checksums optional optional forced on optional

Vacuum Config

Parameter OLTP OLAP CRIT TINY
vacuum_cost_delay 20ms 10ms 20ms 20ms
vacuum_cost_limit 2000 10000 2000 2000
autovacuum_max_workers 3 3 3 2

Timeout & Security

Parameter OLTP OLAP CRIT TINY
idle_in_transaction_session_timeout 10min off 1min 10min
log_min_duration_statement 100ms 1000ms 100ms 100ms
default_statistics_target 400 1000 400 200
track_activity_query_size 8KB 8KB 32KB 8KB
log_connections auth auth full default

IO Config (PG18)

Parameter OLTP OLAP CRIT TINY
io_workers 25% cpu (4-16) 50% cpu (4-32) 25% cpu (4-8) 3
temp_file_limit 1/20 disk, max 100GB 1/5 disk, max 400GB 1/20 disk, max 100GB 1/20 disk, max 100GB

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: Candidate-replica lag threshold; setting it to 0 enables synchronous replication in the general templates
  • pg_max_conn: Override template max connections
  • pg_shared_buffer_ratio: Shared buffer memory ratio
  • pg_storage_type: Storage type, affects IO params

1 - Parameter Optimization Policy

Learn the parameter optimization strategies Pigsty uses for the 4 different PostgreSQL workload scenarios.

Pigsty provides four scenario-based parameter templates by default, which can be specified and used through the pg_conf parameter.

  • tiny.yml: Optimized for small nodes, VMs, and demos (the template is labeled for 1-3 cores)
  • oltp.yml: Optimized for OLTP workloads and latency-sensitive applications (4C8GB+) (default template)
  • olap.yml: Optimized for OLAP workloads and throughput (4C8G+)
  • crit.yml: Optimized for data consistency and critical applications (4C8G+)

Pigsty adopts different parameter optimization strategies for these four default scenarios, as shown below:

Memory Parameter Tuning

Pigsty automatically detects the system’s memory size and uses it as the basis for setting the maximum number of connections and memory-related parameters.

  • pg_max_conn: PostgreSQL maximum connections, auto will use recommended values for different scenarios
  • pg_shared_buffer_ratio: Shared buffer memory ratio, default is 0.25

By default, Pigsty uses 25% of memory as PostgreSQL shared buffers. The rest is shared among connections, work_mem, background processes, and the operating-system cache.

By default, if the user has not set a pg_max_conn maximum connections value, Pigsty will use defaults according to the following rules:

  • oltp: 500 (pgbouncer) / 1000 (postgres)
  • crit: 500 (pgbouncer) / 1000 (postgres)
  • tiny: 250
  • olap: 500

For OLTP and CRIT templates, if the service is not pointing to the pgbouncer connection pool but directly connects to the postgres database, the maximum connections will be doubled to 1000.

After determining the maximum connections, work_mem is calculated from shared memory size / maximum connections and limited to the range of 64MB ~ 1GB.

{% raw %}
{% if pg_max_conn != 'auto' and pg_max_conn|int >= 20 %}{% set pg_max_connections = pg_max_conn|int %}{% else %}{% if pg_default_service_dest|default('postgres') == 'pgbouncer' %}{% set pg_max_connections = 500 %}{% else %}{% set pg_max_connections = 1000 %}{% endif %}{% endif %}
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
{% set pg_max_locks_per_transaction = (2 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else pg_max_connections %}
{% set pg_shared_buffers = (node_mem_mb|int * pg_shared_buffer_ratio|float) | round(0, 'ceil') | int %}
{% set pg_maintenance_mem = (pg_shared_buffers|int * 0.25)|round(0, 'ceil')|int %}
{% set pg_effective_cache_size = node_mem_mb|int - pg_shared_buffers|int  %}
{% set pg_workmem =  ([ ([ (pg_shared_buffers / pg_max_connections)|round(0,'floor')|int , 64 ])|max|int , 1024])|min|int %}
{% endraw %}

CPU Parameter Tuning

In PostgreSQL, there are 4 important parameters related to parallel queries. Pigsty automatically optimizes parameters based on the current system’s CPU cores. The templates first calculate a parallel/extension worker budget and then add another eight reserved slots when writing max_worker_processes. The final GUC is therefore eight higher than the intermediate variable defined near the top of each template.

OLTP Setting Logic Range Limits
max_worker_processes max(CPU + 8, 16) + 8 max(CPU + 16, 24)
max_parallel_workers max(ceil(50% CPU), 2) 1/2 CPU rounded up, minimum 2
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU rounded up, minimum 2
max_parallel_workers_per_gather min(max(ceil(20% CPU), 2),8) 1/5 CPU rounded down, minimum 2, max 8
OLAP Setting Logic Range Limits
max_worker_processes max(CPU + 12, 20) + 8 max(CPU + 20, 28)
max_parallel_workers max(ceil(80% CPU, 2)) 4/5 CPU rounded up, minimum 2
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU rounded up, minimum 2
max_parallel_workers_per_gather max(floor(50% CPU), 2) 1/2 CPU rounded up, minimum 2
CRIT Setting Logic Range Limits
max_worker_processes max(CPU + 8, 16) + 8 max(CPU + 16, 24)
max_parallel_workers max(ceil(50% CPU), 2) 1/2 CPU rounded up, minimum 2
max_parallel_maintenance_workers max(ceil(33% CPU), 2) 1/3 CPU rounded up, minimum 2
max_parallel_workers_per_gather 0, enable as needed
TINY Setting Logic Range Limits
max_worker_processes max(CPU + 4, 12) + 8 max(CPU + 12, 20)
max_parallel_workers max(floor(50% CPU), 1) 50% CPU rounded down, minimum 1
max_parallel_maintenance_workers max(floor(33% CPU), 1) 33% CPU rounded down, minimum 1
max_parallel_workers_per_gather 0 Disables parallel gather per query

Note that the CRIT and TINY templates disable parallel queries by setting max_parallel_workers_per_gather = 0. Users can enable parallel queries as needed by setting this parameter.

Both OLTP and CRIT templates additionally set the following parameters, doubling the parallel query cost to reduce the tendency to use parallel queries.

parallel_setup_cost: 2000           # double from 100 to increase parallel cost
parallel_tuple_cost: 0.2            # double from 0.1 to increase parallel cost
min_parallel_table_scan_size: 32MB  # 4x default 8MB, prefer non-parallel scan
min_parallel_index_scan_size: 2MB   # 4x default 512kB, prefer non-parallel scan

Note that adjustments to the max_worker_processes parameter only take effect after a restart. Additionally, when a replica’s configuration value for this parameter is higher than the primary’s, the replica will fail to start. This parameter must be adjusted through Patroni configuration management, which ensures consistent primary-replica configuration and prevents new replicas from failing to start during failover.

Storage Space Parameters

Pigsty automatically detects the total space of the disk where the /data/postgres main data directory is located and uses it as the basis for specifying the following parameters:

{% raw %}
{% set pg_size_twentieth = ([([(node_fs_bytes|int / 21474836480)|round(0, 'ceil')|int, 1])|max, 100])|min %}
min_wal_size: {{ ([pg_size_twentieth, 200])|min }}GB                  # 1/20 disk size, max 200GB
max_wal_size: {{ ([pg_size_twentieth * 4, 2000])|min }}GB             # 2/10 disk size, max 2000GB
max_slot_wal_keep_size: {{ ([pg_size_twentieth * 6, 3000])|min }}GB   # 3/10 disk size, max 3000GB
temp_file_limit: {{ ([pg_size_twentieth, 200])|min }}GB               # 1/20 of disk size, max 200GB
{% endraw %}
  • pg_size_twentieth is one twentieth of disk capacity rounded up, clamped to 1-100GB.
  • Therefore, in the three standard templates, the effective cap for temp_file_limit and min_wal_size is 100GB.
  • The effective cap for max_wal_size is 400GB.
  • The effective cap for max_slot_wal_keep_size is 600GB.

The OLAP template sets temp_file_limit to pg_size_twentieth × 4, for an effective cap of 400GB. Existing 200GB/2TB/3TB comments at the ends of template lines do not account for the 100GB cap already applied to pg_size_twentieth; the rendered expression is authoritative.

Manual Parameter Tuning

In addition to using Pigsty’s automatically configured parameters, you can also manually tune PostgreSQL parameters.

Use the pg edit-config <cluster> command to interactively edit cluster configuration:

pg edit-config pg-meta

Or use the -p parameter to directly set parameters:

pg edit-config -p log_min_duration_statement=1000 pg-meta
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain' pg-meta

You can also use the Patroni REST API to modify configuration:

curl -u 'postgres:Patroni.API' \
    -d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
    -s -X PATCH http://10.10.10.10:8008/config | jq .

2 - OLTP Template

PostgreSQL config template optimized for online transaction processing workloads

oltp.yml is Pigsty’s default config template, optimized for online transaction processing (OLTP). Designed for 4-128 core CPUs with high concurrency, low latency, and high throughput.

Pair with node_tune = oltp for OS-level tuning.

Use Cases

OLTP template is ideal for:

  • E-commerce: Order processing, inventory, user transactions
  • Social apps: User feeds, messaging, following relationships
  • Gaming backends: Player data, leaderboards, game state
  • SaaS applications: Multi-tenant business systems
  • Web apps: CRUD-intensive workloads

Workload characteristics:

  • Many short transactions (millisecond-level)
  • High concurrent connections (hundreds to thousands)
  • Read/write ratio typically 7:3 to 9:1
  • Latency-sensitive, requires fast response
  • High data consistency requirements

Usage

oltp.yml is the default template, no explicit specification needed:

pg-oltp:
  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-oltp
    # pg_conf: oltp.yml  # PostgreSQL config template (default)
    # node_tune: oltp    # OS tuning template (default)

Or explicitly specify:

pg-oltp:
  vars:
    pg_conf: oltp.yml    # PostgreSQL config template
    node_tune: oltp      # OS tuning template

Parameter Details

Connection Management 

max_connections: 500/1000   # depends on pgbouncer usage
superuser_reserved_connections: 10
  • When pg_default_service_dest is pgbouncer, max_connections is set to 500
  • When traffic connects directly to PostgreSQL, max_connections is set to 1000
  • Override via pg_max_conn parameter

Memory Config

OLTP template memory allocation strategy:

Parameter Formula Description
shared_buffers mem × pg_shared_buffer_ratio Default ratio 0.25
maintenance_work_mem shared_buffers × 25% For VACUUM, CREATE INDEX
work_mem 64MB - 1GB Based on shared_buffers/max_connections
effective_cache_size total mem - shared_buffers Estimated cache memory

work_mem calculation:

work_mem = min(max(shared_buffers / max_connections, 64MB), 1GB)

Ensures each connection has sufficient sort/hash memory without over-allocation.

Parallel Query

OLTP template moderately limits parallel queries to prevent resource contention:

max_worker_processes: max(cpu + 16, 24)
max_parallel_workers: 50% × cpu (min 2)
max_parallel_workers_per_gather: 20% × cpu (2-8)
max_parallel_maintenance_workers: 33% × cpu (min 2)

Parallel cost estimates are increased to favor serial execution:

parallel_setup_cost: 2000      # 2x default (1000)
parallel_tuple_cost: 0.2       # 2x default (0.1)
min_parallel_table_scan_size: 32MB   # 4x default (8MB), prefer non-parallel scan
min_parallel_index_scan_size: 2MB    # 4x default (512kB), prefer non-parallel scan

WAL Config 

min_wal_size: disk/20 (effective max 100GB)
max_wal_size: disk/5 (effective max 400GB)
max_slot_wal_keep_size: disk×3/10 (effective max 600GB)
wal_buffers: 16MB
wal_writer_delay: 20ms
wal_writer_flush_after: 1MB
commit_delay: 20
commit_siblings: 10
checkpoint_timeout: 15min
checkpoint_completion_target: 0.80

Balances data safety and write performance.

Vacuum Config 

vacuum_cost_delay: 20ms         # sleep after each vacuum round
vacuum_cost_limit: 2000         # cost limit per vacuum round
autovacuum_max_workers: 3
autovacuum_naptime: 1min
autovacuum_vacuum_scale_factor: 0.08    # 8% table change triggers vacuum
autovacuum_analyze_scale_factor: 0.04   # 4% table change triggers analyze
autovacuum_freeze_max_age: 1000000000

Conservative vacuum settings avoid impacting online transaction performance.

Query Optimization 

random_page_cost: 1.1           # SSD optimized
effective_io_concurrency: 200   # SSD concurrent IO
default_statistics_target: 400  # Statistics precision

Enables planner to generate better query plans.

Logging & Monitoring 

log_min_duration_statement: 100         # log queries > 100ms
log_statement: ddl                      # log DDL statements
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024                    # log temp files > 1MB
log_autovacuum_min_duration: 1s
track_io_timing: on
track_functions: all
track_activity_query_size: 8192

Client Timeouts 

deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 10min

10-minute idle transaction timeout prevents zombie transactions holding locks.

Extension Config 

shared_preload_libraries: 'pg_stat_statements, auto_explain'

# auto_explain
auto_explain.log_min_duration: 1s
auto_explain.log_analyze: on
auto_explain.log_verbose: on
auto_explain.log_timing: on
auto_explain.log_nested_statements: true

# pg_stat_statements
pg_stat_statements.max: 10000
pg_stat_statements.track: all
pg_stat_statements.track_utility: off
pg_stat_statements.track_planning: off

Template Comparison

Feature OLTP OLAP CRIT
max_connections 500-1000 500 500-1000
work_mem 64MB-1GB 64MB-8GB 64MB-1GB
Parallel query Moderate limit Aggressive Disabled
Vacuum intensity Conservative Aggressive Conservative
Txn timeout 10min Disabled 1min
Slow query threshold 100ms 1000ms 100ms

Why OLTP over OLAP?

  • Queries are mostly simple point/range lookups
  • Transaction response time requires milliseconds
  • High concurrent connections
  • No complex analytical queries

Why OLTP over CRIT?

  • Small probability of data loss acceptable (async replication)
  • Complete audit logs not required
  • Better write performance desired

Performance Tuning Tips

Connection Pooling

For high concurrency, use PgBouncer connection pool:

pg-oltp:
  vars:
    pg_default_service_dest: pgbouncer  # default
    pgbouncer_poolmode: transaction     # transaction-level pooling

Read Separation

Use read replicas to share read load:

pg-oltp:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica }

Monitoring Metrics

Focus on these metrics:

  • Connections: Active/waiting connection counts
  • Transaction rate: TPS, commit/rollback ratio
  • Response time: Query latency percentiles (p50/p95/p99)
  • Lock waits: Lock wait time, deadlock counts
  • Replication lag: Replica delay time and bytes

References

3 - OLAP Template

PostgreSQL config template optimized for online analytical processing workloads

olap.yml is optimized for online analytical processing (OLAP). Designed for 4-128 core CPUs with support for large queries, high parallelism, relaxed timeouts, and aggressive vacuum.

Pair with node_tune = olap for OS-level tuning.

Use Cases

OLAP template is ideal for:

  • Data warehouses: Historical data storage, multidimensional analysis
  • BI reports: Complex report queries, dashboard data sources
  • ETL processing: Data extraction, transformation, loading
  • Data analysis: Ad-hoc queries, data exploration
  • HTAP mixed workloads: Analytical replicas

Workload characteristics:

  • Complex queries (seconds to minutes)
  • Low concurrent connections (tens to hundreds)
  • Read-intensive, writes typically batch operations
  • Throughput-sensitive, tolerates higher latency
  • Scans large data volumes

Usage

Specify pg_conf = olap.yml in cluster definition:

pg-olap:
  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-olap
    pg_conf: olap.yml    # PostgreSQL analytics template
    node_tune: olap      # OS analytics tuning

Use olap.yml template for dedicated offline replicas:

pg-mixed:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: offline, pg_conf: olap.yml }  # offline analytics replica
  vars:
    pg_cluster: pg-mixed
    pg_conf: oltp.yml    # primary and online replicas use OLTP
    node_tune: oltp      # OS OLTP tuning

Parameter Details

Connection Management 

max_connections: 500
superuser_reserved_connections: 10

OLAP scenarios typically don’t need many connections; 500 is sufficient for most analytical workloads.

Memory Config

OLAP template uses more aggressive memory allocation:

Parameter Formula Description
shared_buffers mem × pg_shared_buffer_ratio Default ratio 0.25
maintenance_work_mem shared_buffers × 50% Faster index creation and VACUUM
work_mem 64MB - 8GB Larger sort/hash memory
effective_cache_size total mem - shared_buffers Estimated cache memory

work_mem calculation (differs from OLTP):

work_mem = min(max(shared_buffers / max_connections, 64MB), 8GB)

Larger work_mem allows bigger sort and hash operations in memory, avoiding disk spill.

Locks & Transactions 

max_locks_per_transaction: 2-4x maxconn   # OLTP: 1-2x

OLAP queries may involve more tables (partitions, many JOINs), requiring more lock slots.

Parallel Query

OLAP template aggressively enables parallel queries:

max_worker_processes: max(cpu + 20, 28)      # OLTP: max(cpu + 16, 24)
max_parallel_workers: 80% × cpu (min 2)      # OLTP: 50%
max_parallel_workers_per_gather: 50% × cpu   # OLTP: 20% (max 8)
max_parallel_maintenance_workers: 33% × cpu

Parallel cost estimates use defaults to favor parallel plans:

# parallel_setup_cost: 1000    # default, not doubled
# parallel_tuple_cost: 0.1     # default, not doubled

Partition-wise optimization enabled:

enable_partitionwise_join: on       # smart partition JOIN
enable_partitionwise_aggregate: on  # smart partition aggregation

IO Config (PG18) 

io_workers: 50% × cpu (4-32)    # OLTP: 25% (4-16)

More IO workers support parallel large table scans.

WAL Config 

min_wal_size: disk/20 (effective max 100GB)
max_wal_size: disk/5 (effective max 400GB)
max_slot_wal_keep_size: disk×3/10 (effective max 600GB)
temp_file_limit: disk/5 (effective max 400GB)   # OLTP: disk/20, effective max 100GB

Larger temp_file_limit allows bigger intermediate results to spill to disk.

Vacuum Config

OLAP template uses aggressive vacuum settings:

vacuum_cost_delay: 10ms         # OLTP: 20ms, faster vacuum
vacuum_cost_limit: 10000        # OLTP: 2000, more work per round
autovacuum_max_workers: 3
autovacuum_naptime: 1min
autovacuum_vacuum_scale_factor: 0.08
autovacuum_analyze_scale_factor: 0.04

Analytical databases often have bulk writes requiring aggressive vacuum to reclaim space.

Query Optimization 

random_page_cost: 1.1
effective_io_concurrency: 200
default_statistics_target: 1000    # OLTP: 400, more precise stats

Higher default_statistics_target provides more accurate query plans, crucial for complex analytics.

Logging & Monitoring 

log_min_duration_statement: 1000    # OLTP: 100ms, relaxed threshold
log_statement: ddl
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024
log_autovacuum_min_duration: 1s
track_io_timing: on
track_cost_delay_timing: on         # PG18+, track vacuum cost delay
track_functions: all
track_activity_query_size: 8192

Client Timeouts 

deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 0   # OLTP: 10min, disabled

Analytical queries may need to hold transactions for extended periods, so idle timeout is disabled.

Key Differences from OLTP

Parameter OLAP OLTP Reason
max_connections 500 500-1000 Fewer analytical connections
work_mem limit 8GB 1GB Support larger in-memory sorts
maintenance_work_mem 50% buffer 25% buffer Faster index creation
max_locks_per_transaction 2-4x 1-2x More tables in queries
max_parallel_workers 80% cpu 50% cpu Aggressive parallelism
max_parallel_workers_per_gather 50% cpu 20% cpu Aggressive parallelism
parallel_setup_cost 1000 2000 Default, encourages parallel
parallel_tuple_cost 0.1 0.2 Default, encourages parallel
enable_partitionwise_join on off Partition optimization
enable_partitionwise_aggregate on off Partition optimization
vacuum_cost_delay 10ms 20ms Aggressive vacuum
vacuum_cost_limit 10000 2000 Aggressive vacuum
temp_file_limit 1/5 disk 1/20 disk Allow larger temp files
io_workers 50% cpu 25% cpu More parallel IO
log_min_duration_statement 1000ms 100ms Relaxed slow query threshold
default_statistics_target 1000 400 More precise stats
idle_in_transaction_session_timeout Disabled 10min Allow long transactions

Performance Tuning Tips

With TimescaleDB

OLAP template works great with TimescaleDB:

pg-timeseries:
  vars:
    pg_conf: olap.yml
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_extensions:
      - timescaledb

With pg_duckdb

For ultimate analytical performance, combine with pg_duckdb:

pg-analytics:
  vars:
    pg_conf: olap.yml
    pg_libs: 'pg_duckdb, pg_stat_statements, auto_explain'

Columnar Storage

Consider columnar storage extensions:

pg_extensions:
  - citus_columnar  # or pg_mooncake

Resource Isolation

For mixed workloads, isolate analytics to dedicated replicas:

pg-mixed:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }               # OLTP writes
    10.10.10.12: { pg_seq: 2, pg_role: replica }               # OLTP reads
    10.10.10.13: { pg_seq: 3, pg_role: offline }               # OLAP analytics
  vars:
    pg_cluster: pg-mixed

Monitoring Metrics

Focus on these metrics:

  • Query time: Long query execution time distribution
  • Parallelism: Parallel worker utilization
  • Temp files: Temp file size and count
  • Disk IO: Sequential and index scan IO volume
  • Cache hit ratio: shared_buffers and OS cache hit rates

References

4 - CRIT Template

PostgreSQL parameter template for consistency-first workloads, with strict synchronous replication, data checksums, and detailed connection logging.

crit.yml targets transactional workloads with elevated consistency and audit requirements. It forces data checksums and Patroni strict synchronous mode, adds connection logging, and adjusts selected WAL, timeout, and parallel-query parameters.

The template increases write latency and may block writes when no synchronous replica is available. Before use, confirm consistency objectives, failure domains, client commit settings, and availability requirements.

Also evaluate node_tune: crit, although host tuning and database parameters can be selected independently.

Usage 

pg-critical:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica }
  vars:
    pg_cluster: pg-critical
    pg_conf: crit.yml
    node_tune: crit

A three-node topology leaves room to select another synchronous replica after one node fails. Continued write availability still depends on remaining node state, DCS, network, and synchronous-replica selection. Exercise failures on the target topology.

Strict Synchronous Replication

CRIT does not derive synchronous mode from pg_rpo. It enables these settings unconditionally:

synchronous_mode: true
synchronous_mode_strict: true

synchronous_mode_strict prevents Patroni from falling back to asynchronous replication when no synchronous replica is available. The primary therefore blocks writes that require synchronous acknowledgment.

The mode targets preservation of acknowledged transactions when:

  • the session has not lowered synchronous_commit to local, off, or another asynchronous level;
  • a synchronous replica acknowledges WAL during commit;
  • failover selects only an eligible node containing the required WAL.

RPO must therefore be validated against client parameters, replication state, and the failure model; it cannot be inferred from the template name alone.

To require acknowledgment from multiple synchronous replicas, change Patroni dynamic configuration:

pg edit-config pg-critical
synchronous_node_count: 2

A higher synchronous-replica count imposes stricter conditions for accepting writes.

Data Checksums

CRIT initialization always includes:

initdb:
  - data-checksums

This overrides a disabled pg_checksum setting and enables page checksums for a new cluster. Checksums detect page damage after write; they do not detect logical errors or every memory error.

Connection and Query Logging

CRIT logs DDL, statements taking longer than 100 ms, and disconnection events:

log_statement: ddl
log_min_duration_statement: 100
log_disconnections: 'on'

PostgreSQL 18 and later use:

log_connections: 'receipt,authentication,authorization'

Earlier versions use log_connections: on. These records support connection auditing but are not fine-grained SQL audit logs. Enable pgaudit separately to record object reads and writes, roles, or statement classes.

track_activity_query_size is set to 32 KiB to retain longer active-query text. Logs may contain SQL and business data; restrict access and set an appropriate retention period.

Watchdog

CRIT changes Patroni watchdog from disabled to automatic:

watchdog:
  mode: automatic
  device: /dev/watchdog

automatic activates only when the system has a usable watchdog device. If fencing must be mandatory, verify hardware, virtualization support, and device permissions before setting required explicitly. A bad configuration can prevent primary startup or disrupt failover.

Key Parameter Differences

Parameter CRIT OLTP Default Effect
synchronous_mode Always enabled Derived from pg_rpo Consistency first
synchronous_mode_strict true General template behavior Blocks writes without a synchronous replica
data-checksums Always enabled Controlled by pg_checksum Page-damage detection
max_parallel_workers_per_gather 0 Calculated from CPU Reduces parallel-query variability
wal_writer_delay 10ms 20ms Processes WAL more frequently
wal_writer_flush_after 0 1MB Changes WAL flush behavior
idle_replication_slot_timeout 3d 7d Removes idle replication slots sooner
idle_in_transaction_session_timeout 1min 10min Terminates idle transactions sooner
track_activity_query_size 32KiB 8KiB Retains longer query text
log_connections Detailed connection events PostgreSQL 18 logs authorization by default Adds connection-audit detail
log_disconnections on off Records disconnections

CRIT also disables parallel gather for individual queries and adjusts parallel costs, autovacuum, WAL, and statistics parameters. The active values for a release are defined in roles/pgsql/templates/crit.yml.

Preloaded Extensions

CRIT generates shared_preload_libraries from pg_libs. The role default sets:

pg_libs: 'pg_stat_statements, auto_explain'

Selecting crit.yml alone does not load passwordcheck. Configure it explicitly when password-complexity checks are required:

pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain'

ha/safe includes this override. To use pgaudit, also add it to pg_libs and configure the audit scope:

pg_libs: '$libdir/passwordcheck, pg_stat_statements, auto_explain, pgaudit'
pg_parameters:
  pgaudit.log: 'ddl, role, write'

Performance and Availability Impact

  • Synchronous commit waits for a synchronous replica; write latency includes at least replica network and WAL durability time.
  • Strict synchronous mode blocks writes when no synchronous replica is available.
  • Disabling parallel gather can reduce throughput for large queries, but also reduces resource variability from parallel execution.
  • More detailed logging and statistics consume additional I/O, CPU, and storage.
  • A shorter idle-transaction timeout may terminate application sessions that hold a transaction open without executing statements.

The impact depends on hardware, network, queries, and client behavior. Test with the actual workload instead of relying on a fixed latency or throughput percentage.

Launch Checklist

  • Deploy at least one usable synchronous replica and verify write behavior during node failure
  • Check whether applications change synchronous_commit
  • Select watchdog automatic or required according to availability requirements
  • Verify collection, access control, and retention for connection logs
  • Configure pg_libs and extension parameters explicitly when password checks or SQL auditing are required
  • Test write latency, throughput, and idle-transaction timeouts with the production workload
  • Exercise primary, synchronous-replica, DCS, and network-partition failures

Related Documentation

5 - TINY Template

PostgreSQL config template optimized for micro instances and resource-constrained environments

tiny.yml is optimized for micro instances and resource-constrained environments. Designed for 1-3 core CPUs with minimal resource usage, conservative memory allocation, and disabled parallel queries.

Pair with node_tune = tiny for OS-level tuning.

Use Cases

TINY template is ideal for:

  • Dev/test: Local development, CI/CD testing
  • Low-spec VMs: 1-2 core CPU, 1-4GB RAM cloud instances
  • Edge computing: Raspberry Pi, embedded devices
  • Demos: Quick Pigsty experience
  • Personal projects: Resource-limited blogs, small apps

Resource constraints:

  • 1-3 CPU cores
  • 1-8 GB RAM
  • Limited disk space
  • May share resources with other services

Usage

Specify pg_conf = tiny.yml in cluster definition:

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

Single-node development:

pg-local:
  hosts:
    127.0.0.1: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-local
    pg_conf: tiny.yml    # PostgreSQL micro instance template
    node_tune: tiny      # OS micro instance tuning

Parameter Details

Connection Management 

max_connections: 250   # OLTP: 500-1000, reduced connection overhead
superuser_reserved_connections: 10

Micro instances don’t need many concurrent connections; 250 is sufficient for dev/test.

Memory Config

TINY template uses conservative memory allocation:

Parameter Formula Description
shared_buffers mem × pg_shared_buffer_ratio Default ratio 0.25
maintenance_work_mem shared_buffers × 25% For VACUUM, CREATE INDEX
work_mem 16MB - 256MB Smaller sort/hash memory
effective_cache_size total mem - shared_buffers Estimated cache memory

work_mem calculation (differs from OLTP):

work_mem = min(max(shared_buffers / max_connections, 16MB), 256MB)

Smaller work_mem limit (256MB vs OLTP’s 1GB) prevents memory exhaustion.

Parallel Query (Fully Disabled)

TINY template completely disables parallel queries:

max_worker_processes: max(cpu + 12, 20)     # OLTP: max(cpu + 16, 24)
max_parallel_workers: 50% × cpu (min 1)      # OLTP: 50% (min 2)
max_parallel_workers_per_gather: 0           # parallel queries disabled
max_parallel_maintenance_workers: 33% × cpu (min 1)

max_parallel_workers_per_gather: 0 ensures queries won’t spawn parallel workers, avoiding resource contention on low-core systems.

IO Config (PG18) 

io_workers: 3   # fixed value, OLTP: 25% cpu (4-16)

Fixed low IO worker count suitable for resource-constrained environments.

Vacuum Config 

vacuum_cost_delay: 20ms
vacuum_cost_limit: 2000
autovacuum_max_workers: 2          # OLTP: 3, one fewer worker
autovacuum_naptime: 1min
# autovacuum_vacuum_scale_factor uses default
# autovacuum_analyze_scale_factor uses default

Fewer autovacuum workers reduce background resource usage.

Query Optimization 

random_page_cost: 1.1
effective_io_concurrency: 200
default_statistics_target: 200     # OLTP: 400, lower precision saves space

Lower default_statistics_target reduces pg_statistic table size.

Logging Config 

log_min_duration_statement: 100    # same as OLTP
log_statement: ddl
log_checkpoints: on
log_lock_waits: on
log_temp_files: 1024
# log_connections uses default (no extra logging)

TINY template doesn’t enable extra connection logging to reduce log volume.

Client Timeouts 

deadlock_timeout: 50ms
idle_in_transaction_session_timeout: 10min   # same as OLTP

Extension Config 

shared_preload_libraries: 'pg_stat_statements, auto_explain'

pg_stat_statements.max: 2500      # OLTP: 10000, reduced memory usage
pg_stat_statements.track: all
pg_stat_statements.track_utility: off
pg_stat_statements.track_planning: off

pg_stat_statements.max reduced from 10000 to 2500, saving ~75% memory.

Key Differences from OLTP

Parameter TINY OLTP Reason
max_connections 250 500-1000 Reduce connection overhead
work_mem limit 256MB 1GB Prevent memory exhaustion
max_worker_processes max(cpu+12, 20) max(cpu+16, 24) Fewer background processes
max_parallel_workers_per_gather 0 20% cpu Disable parallel queries
autovacuum_max_workers 2 3 Reduce background load
default_statistics_target 200 400 Save space
pg_stat_statements.max 2500 10000 Reduce memory usage
io_workers 3 25% cpu Fixed low value

Resource Estimates

TINY template resource usage by configuration:

1 Core 1GB RAM 

shared_buffers: ~256MB
work_mem: ~16MB
maintenance_work_mem: ~64MB
max_connections: 250
max_worker_processes: 20

PostgreSQL process memory: ~400-600MB

2 Core 4GB RAM 

shared_buffers: ~1GB
work_mem: ~32MB
maintenance_work_mem: ~256MB
max_connections: 250
max_worker_processes: 20

PostgreSQL process memory: ~1.5-2GB

4 Core 8GB RAM

Consider using OLTP template instead:

pg-small:
  vars:
    pg_conf: oltp.yml   # 4C8G can use OLTP template

Performance Tuning Tips

Further Resource Reduction

For extremely constrained resources:

pg_parameters:
  max_connections: 100           # further reduce
  shared_buffers: 128MB          # further reduce
  maintenance_work_mem: 32MB
  work_mem: 8MB

Disable Unnecessary Extensions 

pg_libs: 'pg_stat_statements'    # keep only essential extensions

Disable Unnecessary Features 

pg_parameters:
  track_io_timing: off           # disable IO timing tracking
  track_functions: none          # disable function tracking

Use External Connection Pool

Even on micro instances, PgBouncer significantly improves concurrency:

pg-tiny:
  vars:
    pg_conf: tiny.yml
    pg_default_service_dest: pgbouncer
    pgbouncer_poolmode: transaction

Cloud Platform Recommendations

AWS

  • t3.micro: 1 vCPU, 1GB RAM - suitable for TINY
  • t3.small: 2 vCPU, 2GB RAM - suitable for TINY
  • t3.medium: 2 vCPU, 4GB RAM - consider OLTP

Alibaba Cloud

  • ecs.t6-c1m1.small: 1 vCPU, 1GB RAM - suitable for TINY
  • ecs.t6-c1m2.small: 1 vCPU, 2GB RAM - suitable for TINY
  • ecs.t6-c1m4.small: 1 vCPU, 4GB RAM - suitable for TINY

Tencent Cloud

  • SA2.SMALL1: 1 vCPU, 1GB RAM - suitable for TINY
  • SA2.SMALL2: 1 vCPU, 2GB RAM - suitable for TINY
  • SA2.SMALL4: 1 vCPU, 4GB RAM - suitable for TINY

Edge Device Deployment

Raspberry Pi 4 

pg-pi:
  hosts:
    192.168.1.100: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-pi
    pg_conf: tiny.yml       # PostgreSQL micro instance template
    node_tune: tiny         # OS micro instance tuning
    pg_storage_type: SSD    # SSD storage recommended

Docker Container 

pg-docker:
  hosts:
    172.17.0.2: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-docker
    pg_conf: tiny.yml       # PostgreSQL micro instance template
    node_tune: tiny         # OS micro instance tuning

Upgrading to OLTP

When your application grows and needs more resources, easily upgrade to OLTP template:

  1. Upgrade VM specs (4 core 8GB+)
  2. Modify cluster config:
pg-growing:
  vars:
    pg_conf: oltp.yml    # change from tiny.yml to oltp.yml
    node_tune: oltp      # change from tiny to oltp
  1. Reconfigure cluster or redeploy

References