Param Templates

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

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)

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

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

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

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

pg-myapp:
  vars:
    pg_conf: myapp.yml

1 - Parameter Optimization Policy

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.

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

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:

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

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

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:

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

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

• 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

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

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

• 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

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:

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

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

3. 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