DBtune’s multi-dimensional performance tuning space
This features a selection of the key parameters under DBtune’s control, and why they matter.
Last updated: December 10, 2025
DBtune's AI engine manages and optimizes a complex, multi-dimensional parameter space across five core areas of database performance: memory and caching, query planning and costing, parallelism, WAL and checkpointing, and the background writer.
The tables below detail a selection of the key parameters under DBtune’s control, and why they matter.
Memory and caching parameters
| Parameter | Purpose | Why it matters for a DBA |
|---|---|---|
| shared_buffers | Sets how much memory is dedicated to caching data inside PostgreSQL. | Too low wastes RAM potential, too high can starve the OS cache; usually, one of the most important performance knobs. |
| work_mem | Memory per sort/hash operation before spilling to disk. | Impacts query speed and total memory use under load. |
| effective_cache_size | Informs the planner how much memory is available to cache information (indexes and tables) in memory. | High effective cache size will make index scans more likely. |
| maintenance_work_mem | Defines how much memory is made available for maintenance operations, e.g., VACUUM. | Higher maintenance memory can accelerate execution of maintenance tasks - e.g., vacuum completes faster. |
Query planning and costing parameters
| Parameter | Purpose | Why it matters for a DBA |
|---|---|---|
| random_page_cost | Cost estimate for random page reads. | Guides planner toward/away from index scans depending on storage type. |
| seq_page_cost | Cost estimate for sequential page reads. | Balances planner’s choice between sequential vs. index scans. |
| effective_io_concurrency | The number of parallel I/O operations storage can handle. | Enables faster bitmap heap scans and parallel reads. |
| default_statistics_target | The amount of statistics collected to inform the planner. | Higher values (>100) will improve planner accuracy for columns with irregular data distribution. |
Parallelism parameters
| Parameter | Purpose | Why it matters for a DBA |
|---|---|---|
| max_parallel_workers_per_gather | Workers allowed per parallel query node. | Boosts single-query performance without hogging all workers. |
| max_parallel_workers | Total parallel workers across the system. | Controls resource sharing and system-wide parallel query load. |
| max_worker_processes | Maximum background worker processes (parallel, logical replication, extensions). | Sets an upper bound on concurrency; must be high enough for parallelism and extensions to function. |
| max_parallel_maintenance_workers | Number of worker processes that can be started by a single utility command (e.g, VACUUM). | Significantly accelerates maintenance (e.g. VACUMM up to 4x), but may consume more CPU. |
WAL and checkpointing parameters
| Parameter | Purpose | Why it matters for a DBA |
|---|---|---|
| max_wal_size | Max WAL generated before a checkpoint is forced. | Larger values mean fewer checkpoints but more recovery time/disk use. |
| min_wal_size | Minimum WAL retained after checkpoint. | Avoids constant WAL recycling and reduces I/O churn. |
| wal_buffers | Shared memory used for WAL data that has not yet been written to disk. | Can improve write performance on a busy server with many parallel transactional sessions. |
| checkpoint_completion_target | How evenly are checkpoint writes spread over time. | Prevents I/O spikes and latency during checkpoints. |
Background writer parameters
| Parameter | Purpose | Why it matters for a DBA |
|---|---|---|
| bgwriter_lru_maxpages | Dirty pages background writer flushes per round. | Balances background cleanup with query I/O needs to manage buffer clean-up more efficiently, especially in write-heavy workloads. |
| bgwriter_delay | Sleep time between background writer cycles. | Tunes steady background writes vs. I/O overhead. |
The majority of critical tuning parameters—including those for memory, query planning, and WAL—can be adjusted dynamically without requiring a server restart. Only shared_buffers, wal_buffers, and max_worker_processes require a server restart. DBtune’s ‘reload-only’ mode optimizes database parameters without restarting the database server, using only configuration reloads.
This crucial feature enables DBtune to safely apply tuning recommendations in continuous, low-risk intervals of 5-10 minutes and achieve significant database performance improvements in a matter of hours.
DBtune supports:
- Self-hosted PostgreSQL
- EDB’s Postgres Advanced Server
- AWS RDS PostgreSQL
- AWS Aurora PostgreSQL
- Azure Database for PostgreSQL
- Google Cloud SQL for PostgreSQL
- Aiven for PostgreSQL.
See this article “How We Used DBtune to Cut Our Postgres Query Time by 50% on AWS RDS” for a DBtune success story on RDS PostgreSQL.