Tuning modes

Overview

DBtune supports different tuning modes that determine which database parameters can be optimized and whether the database needs to be restarted during the tuning process. The choice of tuning mode depends on your operational requirements and tolerance for downtime.

Reload-only tuning mode

Recommended for production environments

This tuning mode optimizes database parameters without restarting the database server, using only configuration reloads. This approach:

• Causes zero downtime - your database remains available throughout the tuning process
• Is safe for production - no service interruptions or connection resets
• Tunes dynamic parameters - parameters that can be changed without a restart
• Provides significant improvements - most performance gains come from parameters in this mode

How it works

The agent modifies parameters that PostgreSQL can apply dynamically through a configuration reload (pg_reload_conf()). These changes take effect immediately without requiring a database restart or disconnecting active sessions.

When to use

• Production databases that require high availability
• Systems where downtime is not acceptable
• Initial tuning sessions to validate DBtune's effectiveness
• Databases with strict SLAs or continuous operations

Restart tuning mode

For systems that can tolerate brief interruptions

This tuning mode optimizes additional parameters that require database restarts to take effect. This mode:

• Restarts the database up to 30 times during a tuning session.
• Tunes static parameters - parameters like shared_buffers that require a restart
• Can provide additional performance - some workloads benefit significantly from tuning restart-only parameters
• Includes all reload-only parameters - restart mode tunes both dynamic and static parameters

How it works

The tuning session explores different configurations for parameters that require a restart. Each configuration change triggers a database restart, followed by a period of monitoring to measure the performance impact.

When to use

• Development or staging environments
• Systems with maintenance windows or planned downtime
• Databases that can handle brief interruptions, typically seconds per restart
• After successful reload-only tuning, when seeking additional optimization
• Systems with robust failover or high-availability configurations

Important considerations

• Each restart disconnects active sessions and briefly interrupts service
• Applications should have proper connection retry logic
• The 30 restarts typically occur over 2-4 hours, not continuously
• Most of our user base prefers reload-only tuning to avoid disruptions

Choosing a tuning mode

Factor	Reload-only	Restart
Downtime	None	Brief interruptions (seconds per restart)
Production safety	Very high	Moderate
Parameters tuned	Dynamic parameters only	Dynamic + static parameters
Performance gains	Significant	Potentially higher
Session interruption	None	Yes, during each restart
Typical use case	Production databases	Dev/test or systems with HA

Platform specific parameter lists

The specific parameters tuned in each mode vary by database platform and hosting environment. Check your database-specific documentation for the exact list:

• PostgreSQL - Full parameter list
• AWS RDS for PostgreSQL - Full parameter list
• AWS Aurora PostgreSQL - Limited by AWS API
• Google Cloud SQL for PostgreSQL - Full parameter list (except effective_cache_size)
• Aiven for PostgreSQL - Limited by Aiven API
• EnterpriseDB Postgres Advanced Server (EPAS) - Same as PostgreSQL

Switching between modes

You can start with reload-only tuning and later run a restart tuning session if:

• The reload-only results are satisfactory and you want to explore further optimization
• You've arranged a maintenance window for restart-based tuning
• Your system's availability requirements have changed

Each tuning session is independent, allowing you to choose the appropriate mode based on current operational needs.