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

1: Parameter Optimization Policy
2: OLTP Template
3: OLAP Template
4: CRIT Template
5: TINY Template

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. The 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 from the default pgsql-main package set. Before you 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 the cluster definition. We recommend a corresponding node_tune setting for OS 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

Use an existing template as the basis for a custom template. The 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

To create a custom template:

Copy an existing template.
Change the parameters as required.
Put the file in roles/pgsql/templates/.
Select it with pg_conf.

Example:

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

Then select the template in the cluster configuration:

pg-myapp:
  vars:
    pg_conf: myapp.yml

Templates use Jinja2 syntax. They calculate parameter values from node resources: CPU, memory, and 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

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 parameter templates for different workloads. Select a template with pg_conf.

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+)

The following sections describe the optimization settings for these four templates.

Memory Parameter Tuning

Pigsty detects the system’s memory size. It uses this value to set 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 assigns 25% of memory to PostgreSQL shared buffers. Connections, work_mem, background processes, and the operating-system cache share the remaining memory.

If you do not set pg_max_conn, Pigsty selects the maximum connections from these defaults:

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

For OLTP and CRIT, the maximum is 500 when the service connects through PgBouncer. If the service connects directly to PostgreSQL, the maximum doubles to 1000.

Pigsty calculates work_mem as shared memory size divided by maximum connections. It limits the result to 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

PostgreSQL has 4 important parameters for parallel queries. Pigsty sets them from the number of CPU cores. The templates first calculate a worker budget for parallel queries and extensions. They add eight reserved slots when they write max_worker_processes. The final GUC is therefore eight higher than the intermediate variable near the start 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

The CRIT and TINY templates set max_parallel_workers_per_gather = 0 to disable parallel queries. To enable parallel queries, change this parameter as required.

The OLTP and CRIT templates set the following parameters. They double parallel query costs to make the planner less likely to choose 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

Changes to max_worker_processes take effect only after a restart. A replica fails to start if its value is higher than the primary value. Change this parameter through Patroni configuration management. Patroni keeps primary and replica settings consistent and prevents new replicas from failing to start during failover.

Storage Space Parameters

Pigsty detects the total disk capacity for the /data/postgres main data directory. It uses this value to set these 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, with an effective limit of 400GB. Existing 200GB/2TB/3TB comments at the ends of template lines omit the 100GB limit already applied to pg_size_twentieth. Use the rendered expression as the authoritative value.

Manual Parameter Tuning

You can manually adjust PostgreSQL parameters in addition to the values that Pigsty sets automatically.

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 configuration template for online transaction processing (OLTP). It is designed for CPUs with 4-128 cores and provides high concurrency, low latency, and high throughput.

Pair with node_tune = oltp for OS-level tuning.

Use Cases

Use the OLTP template 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. You do not need to select it explicitly:

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)

To select it explicitly, use:

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)

This calculation gives each connection sufficient sort/hash memory without excessive 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

These settings balance 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

These settings help the planner 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

The 10-minute idle transaction timeout prevents inactive transactions from keeping locks indefinitely.

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

pg_conf: PostgreSQL config template selection
node_tune: OS tuning template, should match pg_conf
OLAP Template: Analytics template comparison
CRIT Template: Critical business template comparison
TINY Template: Micro instance template comparison
Cluster Config: PostgreSQL cluster type configuration
High Availability: HA architecture design

3 - OLAP Template

PostgreSQL config template optimized for online analytical processing workloads

olap.yml is optimized for online analytical processing (OLAP) on CPUs with 4-128 cores. It supports large queries, high parallelism, relaxed timeouts, and aggressive vacuum.

Pair with node_tune = olap for OS-level tuning.

Use Cases

Use the OLAP template 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 workloads usually need fewer connections. The limit of 500 is sufficient for most analytical workloads.

Memory Config

The OLAP template assigns more memory to operations:

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 access more tables through partitions and multiple JOINs. They therefore require more lock slots.

Parallel Query

The OLAP template assigns more workers to 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

The template enables partition-wise optimization:

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

A higher default_statistics_target provides more accurate query plans. This is important 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 keep transactions open for long periods. Thus, the template disables the idle timeout.

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

You can use the OLAP template with TimescaleDB:

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

With pg_duckdb

To improve analytical performance, combine the template 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

pg_conf: PostgreSQL config template selection
node_tune: OS tuning template, should match pg_conf
OLTP Template: Transaction template comparison
CRIT Template: Critical business template comparison
TINY Template: Micro instance template comparison
Offline Replica: Dedicated analytics instances

4 - CRIT Template

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

crit.yml is for transactional workloads with increased consistency and audit requirements. It forces data checksums and Patroni strict synchronous mode. It also 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. You can select host tuning and database parameters 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 lets the cluster select another synchronous replica after one node fails. Continued write availability depends on the remaining node state, DCS, network, and synchronous-replica selection. Test failures on the target topology.

Strict Synchronous Replication

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

synchronous_mode: true
synchronous_mode_strict: true

synchronous_mode_strict prevents Patroni from switching to asynchronous replication when no synchronous replica is available. The primary then 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.

Validate RPO against client parameters, replication state, and the failure model. The template name alone does not establish RPO.

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 a 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 do not provide fine-grained SQL audit logs. Enable pgaudit separately to record object reads and writes, roles, or statement classes.

CRIT sets track_activity_query_size to 32 KiB to retain longer active-query text. Logs may contain SQL and business data. Restrict access and set a suitable 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 you explicitly set required. An incorrect configuration can prevent primary startup or interrupt 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 release file roles/pgsql/templates/crit.yml defines the active values.

Preloaded Extensions

CRIT generates shared_preload_libraries from pg_libs. The role default is:

pg_libs: 'pg_stat_statements, auto_explain'

Selecting crit.yml alone does not load passwordcheck. If you need password-complexity checks, configure it explicitly:

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 the actual workload. Do not rely 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

Security Model: where CRIT fits in the overall hardening path
Data Security: boundaries of synchronous replication, checksums, and auditing
ha/safe Configuration: three-node hardening example that includes CRIT
Sync Standby: synchronous replication configuration
Quorum Commit: synchronous replica count
OLTP Template: general transaction template

5 - TINY Template

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

tiny.yml is optimized for micro instances and environments with limited resources. It is designed for CPUs with 1-3 cores. It minimizes resource use, limits memory allocation, and disables parallel queries.

Pair with node_tune = tiny for OS-level tuning.

Use Cases

Use the TINY template 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 need fewer concurrent connections. The limit of 250 is sufficient for development and testing.

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 prevents queries from starting parallel workers. This avoids resource contention on systems with few CPU cores.

IO Config (PG18)

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

A fixed, low I/O worker count is suitable for environments with limited resources.

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)

The TINY template disables additional 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

The template reduces pg_stat_statements.max from 10000 to 2500. This saves approximately 75% of its memory use.

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 the application needs more resources, upgrade to the OLTP template:

Upgrade VM specs (4 core 8GB+)
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

Reconfigure cluster or redeploy

References

pg_conf: PostgreSQL config template selection
node_tune: OS tuning template, should match pg_conf
OLTP Template: Transaction template, upgrade for 4C8G+
OLAP Template: Analytics template
CRIT Template: Critical business template
Single-Node Install: Pigsty single-node installation guide

Param Templates

Usage

Comparison

Connections & Memory

Parallel Query

Sync Replication

Vacuum Config

Timeout & Security

IO Config (PG18)

Selection Guide

Custom Templates

Tuning Strategy

Related Parameters

1 - Parameter Optimization Policy

Memory Parameter Tuning

CPU Parameter Tuning

Storage Space Parameters

Manual Parameter Tuning

2 - OLTP Template

Use Cases

Usage

Parameter Details

Connection Management

Memory Config

Parallel Query

WAL Config

Vacuum Config

Query Optimization

Logging & Monitoring

Client Timeouts

Extension Config

Template Comparison

Why OLTP over OLAP?

Why OLTP over CRIT?

Performance Tuning Tips

Connection Pooling

Read Separation

Monitoring Metrics

References

3 - OLAP Template

Use Cases

Usage

Parameter Details

Connection Management

Memory Config

Locks & Transactions

Parallel Query

IO Config (PG18)

WAL Config

Vacuum Config

Query Optimization

Logging & Monitoring

Client Timeouts

Key Differences from OLTP

Performance Tuning Tips

With TimescaleDB

With pg_duckdb

Columnar Storage

Resource Isolation

Monitoring Metrics

References

4 - CRIT Template

Usage

Strict Synchronous Replication

Data Checksums

Connection and Query Logging

Watchdog

Key Parameter Differences

Preloaded Extensions

Performance and Availability Impact

Launch Checklist

Related Documentation

5 - TINY Template

Use Cases

Usage

Parameter Details

Connection Management

Memory Config

Parallel Query (Fully Disabled)

IO Config (PG18)