One-click Postgres Pro optimization with pgpro_tune

If you've worked with PostgreSQL, you know the default settings are designed to be minimal — just enough to run on virtually any machine, even a coffee maker. They are not performance-oriented out of the box.

Manual tuning usually means diving deep into documentation, reviewing dozens of parameters — from shared_buffers and work_mem to effective_cache_size and wal_buffers — running tests, and using trial and error. This process is time-consuming and requires in-depth knowledge of both the database internals and the expected workload. A mistake during tuning can degrade performance or even destabilize the system.

This becomes especially problematic when you're under time pressure or scaling infrastructure. Manual tuning often becomes a bottleneck.

To address this, we created an automatic configuration tool for Postgres Pro that saves you from spending hours reading documentation or running experiments. Meet autotuner pgpro_tune — a command-line tool coming in the May release of Postgres Pro.

How the autotuner works

pgpro_tune is a CLI utility that optimizes Postgres Pro’s initial server configuration based on your hardware. It doesn't replace fine-tuned performance adjustments for specific workloads, but it provides a much better starting point than the default settings.

Here’s how it works:

1. System analysis. pgpro_tune scans your server to detect key hardware specs — such as number of CPU cores (ncpu) and available RAM in MiB (memmb).

2. Expert logic execution. These values (ncpuand memmb) are passed to a set of shell scripts that embed the tuning knowledge of our engineers. Using heuristics and system data, the scripts calculate recommended values for critical PostgreSQL parameters, such as:

   • memory management:  shared_buffers,  effective_cache_size,  maintenance_work_mem, work_mem, temp_buffers

   • autovacuum tuning:  autovacuum_max_workers,  autovacuum_work_mem,  vacuum_cost_limit

   • connection limits: max_connections

   • statistics collection: pg_stat_statements.max

3. Config generation. The shell scripts output a set of recommended parameters tailored to your system.

4. Config application. pgpro_tune appends these settings as a new block at the end of postgresql.conf. Since PostgreSQL uses the last occurrence of a parameter, the new settings override earlier defaults. The inserted block includes timestamped comments and tuning details for easy identification and manual editing if needed.

The tuning logic is based on years of experience supporting Postgres Pro and deep knowledge of its internals, especially around optimal resource usage across different hardware types.

Flexible tuning

The main goal of pgpro_tune is to provide a strong, hardware-aware baseline configuration. It supports presets, which are defined as shell scripts for different use cases.

Currently, there is a basic preset available, designed for general performance improvements across different Postgres Pro editions (Standard, Enterprise). It focuses on making the most of your hardware’s capabilities.

Future releases will include specialized presets:

• For 1C-based application workloads. The tuning logic for 1C will be based on many years of experience and methodologies developed in collaboration with domain experts — for example, leveraging approaches by Anton Doroshkevich. This preset will be aimed at achieving maximum performance in typical 1C usage scenarios.

• For administrative tasks. These presets will focus on optimizing performance for specific DBA tasks, such as fast data loading during migrations or performing backups.

Admins can also create custom presets using shell scripts without needing approval from Postgres Professional. As long as the script outputs data in the pgpro_tune format, it will work.

Full control for DBAs

Despite the automation, pgpro_tune gives database administrators full control:

• Preset selection. DBAs can choose which preset to use based on their use case.

• Command-line parameters. You can pass parameters directly to pgpro_tune to adjust its behavior. For example, the basic preset may include optional logic for 1C that can be activated via a special flag at runtime, instead of using a separate preset file. This makes the base scripts more flexible.

• Automatic initialization. pgpro_tune runs automatically during cluster initialization (via pg-setup initdb or direct initdb). This ensures that new clusters start with optimal settings based on the host system — no need to pass --tune flags like before.

• Multiple runs. The utility can be run not only during cluster initialization (via pg-setup or initdb), but also manually at any later time. This is useful, for example, after changes to the server configuration (such as adding RAM or CPU cores) or when the workload changes — for instance, if the database starts being used for 1C.

• Applying changes. You apply generated settings the usual PostgreSQL way — via SELECT pg_reload_conf();or by restarting the server, depending on the parameters.

Profit

pgpro_tune solves a common pain point in PostgreSQL administration — baseline configuration tuning. It automates system analysis and applies proven heuristics to calculate settings like shared_buffers or work_mem, based on real-world experience. Instead of spending time manually tuning parameters or risking poor performance with suboptimal default values (for example, shared_buffers = 128MB), you get a tool that configures the database for optimal operation.

For DBAs, pgpro_tune speeds up deployment and reduces the risk of misconfiguration. For CTOs and IT leaders, it provides consistency, faster rollout, and better ROI on hardware.

While not a replacement for advanced tuning of high-load or specialized systems, pgpro_tune is a powerful starting point — freeing up engineering time and enabling you to focus on solving architectural challenges instead of tweaking config files.