The 70% Tuner: Defining the Boundary of pig pg tune

Ruohang FengAuthor of pig and founder of Pigsty.

Decision date: 2026-03-21
Status: Released in pig v1.3.2, following implementation on 2026-03-23.
Current reference: pig pg tune
Scope: Generate a starting configuration from hardware, PostgreSQL version, and workload profile.

Decision

pig pg tune calculates core PostgreSQL parameters and prints them for review. It detects CPU, memory, and disk capacity, accepts explicit overrides, and applies a selected workload profile. Text output displays the generated parameters; JSON and YAML output include the inputs and parameters. Review the values and select the settings to apply through your configuration system.

The original “70% tuner” name describes a practical starting point. It is not a measured accuracy score.

Context

A fresh installation needs an initial configuration before workload measurements are available. One static configuration cannot fit both a small development machine and a large database server. Hardware-based formulas provide a repeatable baseline that the operator can inspect and adjust.

Alternatives considered

• Ship one fixed configuration. Memory and parallelism settings need to scale with the host.
• Build an adaptive tuner. This would require workload data, experiments, and a feedback loop.
• Apply settings automatically. The operator needs to review the generated values and choose how to deploy them through the existing configuration workflow.
• Calculate every PostgreSQL parameter. Replication, security, and availability settings also depend on requirements that hardware detection cannot determine.

Contract

• Use the detected hardware or the operator’s explicit CPU, memory, and disk values.
• Select formulas through oltp, olap, tiny, or crit.
• Produce the same parameters for the same resolved inputs and PostgreSQL version.
• Include the resolved profile, hardware, and PostgreSQL version in structured results.
• Print text parameters in a stable order and identify the SSD assumption in the header.
• Validate input ranges and omit parameters unavailable in the selected PostgreSQL version.
• Leave configuration files and the running server unchanged.

Consequences

Operators can use the output for a new installation or initial sizing. Review the values, apply them through the configuration system in use, then measure the workload. Query concurrency, checkpoint behavior, storage latency, and replication requirements guide later adjustments.

Verification and evolution

The initial implementation is 60eecfe. The v1.3.2 release introduced parameter generation. The current implementation returns a result for rendering. Tests in tune_test.go cover workload formulas, resource limits, overrides, and version-dependent parameters.

Current status

Parameter generation is released. See the pig pg reference for inputs and output examples.