Patroni (coming January 2026)

The DBtune agent features built-in HA awareness, designed to tune the cluster based on the primary workload.

1. It automatically detects the node's role within the Patroni cluster.
2. To ensure tuning accuracy, this built-in check verifies that configuration changes are only applied when the agent is running on the active Leader node.
3. This is crucial because standby replicas do not process the same active workload, and tuning them would lead to incorrect and potentially harmful optimizations for the cluster.

Prerequisites

Before starting the DBtune agent, there are a few setup steps you need to complete on your PostgreSQL cluster.

Install the pg_stat_statements extension to collect query metrics:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Make sure it is added to shared_preload_libraries:

SHOW shared_preload_libraries;

note: A PostgreSQL restart is required if pg_stat_statements is not already included in shared_preload_libraries.

Next, verify that the pg_stat_statements.track parameter is properly configured. By default, it is set to top upon creation. You can also set it to all, as DBtune supports both options.

SHOW pg_stat_statements.track;

The DBtune agent must be able to reach the Patroni REST API at http://<node-ip>:8008. Ensure that the local firewall allows the agent to communicate over port 8008. Since the agent and Patroni are running on the same VM, ensure that Patroni is listening on 127.0.0.1.

Setting up the agent

For an overview of how the DBtune agent works, see the agent overview.

The DBtune agent must be installed and run on the Leader node of your Patroni cluster, as tuning sessions are only permitted on the active Leader.

Refer to the Agent page in the DBtune platform for instructions on how to set up and install the agent.

Parameters tuned

By default, DBtune operates in reload-only mode, focusing on parameters that can be applied without causing application downtime. For a detailed explanation of tuning modes, see tuning modes.

Reload tuning mode

The following parameters are tuned by DBtune using a server reload which does not cause any application downtime:

1. work_mem
2. random_page_cost
3. seq_page_cost
4. checkpoint_completion_target
5. effective_io_concurrency
6. max_parallel_workers_per_gather
7. max_parallel_workers
8. max_wal_size
9. min_wal_size
10. bgwriter_lru_maxpages
11. bgwriter_delay
12. effective_cache_size
13. maintenance_work_mem
14. default_statistics_target
15. max_parallel_maintenance_workers

Restart tuning mode

The following parameters are tuned in restart mode, in addition to all reload-only parameters:

16. shared_buffers
17. max_worker_processes
18. wal_buffers
19. huge_pages

note

When tuning these parameters, DBtune utilizes the Patroni REST API to manage the high-availability cluster state. When a server restart is triggered, Patroni flags the node with a restart_pending: true status in the DCS (Distributed Configuration Store). DBtune then triggers a controlled restart via the /restart endpoint—restarting replicas first and the Leader last—ensuring the cluster maintains high availability with less downtime.

Patroni manages certain PostgreSQL parameters internally (such as replication settings, primary connection information, etc.). To prevent conflicts and maintain cluster stability, DBtune detects these Patroni-managed parameters and does not touch them.

Patroni-managed parameters:

DBtune will NOT modify these Patroni parameters to avoid conflicts:

1. hot_standby
2. primary_conninfo
3. primary_slot_name
4. recovery_min_apply_delay
5. synchronous_standby_names
6. transaction_read_only
7. max_connections
8. max_wal_senders
9. wal_level
10. max_replication_slots
11. cluster_name
12. in_hot_standby

For a complete list of parameters managed by Patroni, please refer to the Patroni documentation.

Failover detection

The DBtune agent automatically detects Patroni failover events during tuning sessions. If a failover occurs while tuning is in progress, the agent prioritizes cluster stability by suspending all tuning activities and metrics collection until recovery is complete.

During this recovery period, the agent will:

1. Wait for the new primary to become healthy and stable (including a 30-second stabilization period).
2. After this stabilization completes, a 2-minute grace period allows the platform to safely apply the baseline configuration, even if the old Leader is now a standby (since Patroni synchronizes configuration via DCS to all nodes).
3. The DBtune agent process is still running on your original Leader, but this machine is now a standby replica.

The agent will automatically resume metrics collection on the standby but will reject any new tuning sessions.

Post-failover manual adjustments needed

Since the agent runs only on the Leader, a failover requires the user to take action. To start the tuning post-failover, you must realign the DBtune agent with the new Leader. You have two options:

Option A: Move the agent to the new Leader

1. Stop the agent: Terminate the dbtune-agent process on the old Leader.
2. Use the correct configuration: The dbtune.yaml file on the new Leader is critical. It must use the exact same database_id as the original. This allows the new agent to take over management of the existing database in the platform, preserving its history. No need to create a new database in the UI. You must update the node_name and patroni_api_url in the patroni: section to match the new Leader's details as shown below.

patroni:
  node_name: "<New_Leader_Node>"
  patroni_api_url: "http://<New_Leader_IP>:8008"

3. Install and start the dbtune-agent on the newly promoted Leader node.

./dbtune-agent --patroni

Option B: Failback to the original Leader

If your operational procedures require returning leadership to the original machine, you can perform a failback, since the DBtune agent is already running on the former Leader node.

1. Execute a failback: Use Patroni tools (e.g., patronictl switchover) to make your original Leader the active Leader again.

patronictl switchover <cluster> --leader <current_leader_node> --candidate <old_leader_node>

2. Resume tuning: The original DBtune agent is now on the active Leader again. You can simply start a new tuning session from the UI.