Show HN: PBTune – Evolutionary auto-tuning for PostgreSQL (no ML required)

A research implementation called PBTune applies Population-Based Training (PBT), an evolutionary algorithm from DeepMind, to automatically tune PostgreSQL database configurations without machine learning. The system maintains a population of configurations, allowing poor performers to copy from top performers and explore variations, achieving autonomous discovery of high-performance settings. The project is open-source with documentation available at alshawai.github.io/PBTune.

This repository contains a research implementation applying Population-Based Training PBT , originally developed by DeepMind for neural network hyperparameter optimization, to the domain of database configuration tuning. Our work demonstrates that evolutionary optimization strategies can autonomously discover high-performance database configurations without domain expertise. 📚 Full Documentation : alshawai.github.io/PBTune https://alshawai.github.io/PBTune/ Overview overview Key Innovation key-innovation Architecture architecture Repository Structure repository-structure Installation installation Quick Start quick-start Documentation documentation Research Foundation research-foundation Experimental Results experimental-results Future Work future-work Contributing contributing License license Citation citation Acknowledgments acknowledgments Database configuration tuning is a critical yet challenging task in database administration. PostgreSQL exposes over 300 configuration parameters "knobs" , with complex interdependencies and non-linear effects on performance. Traditional approaches rely on: Manual tuning by expert DBAs time-consuming, non-scalable Rule-based systems inflexible, limited adaptability Bayesian Optimization sample-inefficient for high-dimensional spaces Reinforcement Learning requires extensive training data, unstable This work proposes a novel alternative : applying Population-Based Training—an evolutionary algorithm that maintains a population of configurations, periodically allowing poor performers to "exploit" successful configurations and "explore" nearby variations. Key Advantages: Parallel Exploration : Evaluates multiple configurations simultaneously across isolated PostgreSQL instances Online Adaptation : Configurations evolve during optimization, avoiding wasted evaluations Sample Efficiency : Poor performers copy from elites rather than exploring randomly No Training Required : Unlike RL, PBT needs no pre-training phase or external datasets Automatic Convergence : Built-in exploitation naturally drives population toward optimal regions Novel Contributions: - First application of PBT to database configuration optimization - Adaptive metric normalization for heterogeneous workload types - Feature-driven composite scoring with compatibility mode for historical sessions - Intelligent restart management balancing exploration vs. overhead PBT maintains a population of - A configuration $\theta i$ knob values - A performance score $f \theta i $ throughput, latency, etc. At regular intervals generations , the algorithm performs: Truncation Selection Exploit : Bottom 20% of workers copy configurations from top 20% Perturbation Explore : Copied configurations are perturbed ±20% for continuous, probabilistic flip for categorical Evaluation : All workers evaluated in parallel on isolated database instances This creates a co-evolutionary process where configurations evolve during training , unlike traditional hyperparameter search methods that evaluate configurations independently. Our implementation extends vanilla PBT with database-specific adaptations: Proportional Perturbation : Categorical knobs perturbed based on value space size 30% for booleans, adaptive for enums Metric Normalization : OtterTune-inspired percentile-based normalization adapts to observed ranges Feature-Driven Scoring : Workload features drive composite metric weighting through scoring-v2, with fixed v1 retained for compatibility Intelligent Restarts : CDBTune-inspired batched restarts every 10 generations balance configuration changes vs. overhead See docs/architecture/feature-driven-scoring.md /alshawai/PBTune/blob/main/docs/architecture/feature-driven-scoring.md for the scoring model, policy metadata, and migration notes. See docs/architecture/pbt-core.md /alshawai/PBTune/blob/main/docs/architecture/pbt-core.md for detailed algorithm description. ┌───────────────────────────────────────────────────────────────┐ │ PBT Tuner System │ └───────────────────────────────────────────────────────────────┘ ┌──────────────────┐ │ Main Tuner │ │ Orchestrator │ └────────┬─────────┘ │ ┌──────────────────┼──────────────────┐ │ │ │ ▼ ▼ ▼ ┌────────────┐ ┌────────────┐ ┌────────────┐ │ Population │ │ Workload │ │ Instance │ │ Manager │───→│Orchestrator│───→│ Manager │ └────────────┘ └────────────┘ └────────────┘ │ │ │ │ │ │ ┌─────┴─────┐ ┌────┴────┐ ┌──────┴───┐ ▼ ▼ ▼ ▼ ▼ ▼ ┌────────┐ ┌────────┐ │ │ ┌────────┐ ┌────────┐ │Worker 0│ │Worker 1│ │ │ │ PG:5440│ │ PG:5441│ │config 0│ │config 1│ │ │ │Instance│ │Instance│ │score 0 │ │score 1 │ │ │ │worker 0│ │worker 1│ └────────┘ └────────┘ ... ...└────────┘ └────────┘ │ │ │ │ ↓ ↓ ↓ ↓ ┌─────────────────────────────────────────────────────┐ │ Evolution Exploit-Explore Cycle │ ├─────────────────────────────────────────────────────┤ │ • Truncation Selection: Bottom 20% copy top 20% │ │ • Perturbation: Continuous ±20% , Categorical 30% │ │ • Convergence Detection: 3 generations stable │ └─────────────────────────────────────────────────────┘ | Component | Purpose | Location | |---|---|---| Population | Manages worker pool, orchestrates PBT algorithm | src/tuners/pbt/population.py | Worker src/tuners/pbt/worker.py Evolution src/tuners/pbt/evolution.py Generation Barriers src/tuners/engine/barriers.py Workload Orchestrator src/tuners/engine/orchestrator.py Environment Backends src/utils/environments/ Knob Space src/knobs/knob space.py Scoring v2 src/utils/scoring/ See docs/architecture/pbt-core.md /alshawai/PBTune/blob/main/docs/architecture/pbt-core.md for component interaction details. . ├── src/ Source code │ ├── tuners/ Tuning engines PBT + shared base/engine │ │ ├── base.py BaseTuner shared session/CLI scaffolding │ │ ├── bo/ BO: Bayesian Optimization baseline │ │ ├── engine/ WorkloadOrchestrator + restart policy + barriers + base worker │ │ ├── lhs design/ LHS Design to create diverse data for knob importance analysis │ │ ├── pbt/ PBT: population, worker, evolution, config, tuner, cli │ │ └── main .py Routed CLI entry point python -m src.tuners pbt │ ├── utils/ Shared utilities │ │ ├── environments/ Docker / bare-metal database backends │ │ ├── scoring/ Feature-driven scoring v2 │ │ ├── logger/ Colored logging + HTML output + context │ │ ├── applicator.py KnobApplicator │ │ ├── metrics.py PerformanceMetrics │ │ ├── metric instrumentation.py │ │ ├── hardware info.py WorkerResources detection │ │ ├── calibration.py Post-hoc global score recalibration was rescoring.py │ │ └── types.py │ ├── benchmarks/ External benchmark executors sysbench, tpch │ ├── database/ psycopg2 / SQLAlchemy connections + lifecycle │ ├── config/ Env-derived database credentials + data-root resolution │ ├── knobs/ pg settings retrieval + tuning metadata + policy filter │ ├── analysis/ fANOVA + TreeSHAP + tier generation │ ├── evaluation/ Post-hoc default-vs-tuned comparison suite │ ├── visualization/ Plot loaders + registry + theme + CLI │ └── scripts/ Setup, cleanup, BO baseline, comparisons ├── docs/ Documentation see docs/README.md for the index ├── data/ Knob metadata, policy, tier CSVs │ ├── knob metadata.json │ ├── knob policy.json │ ├── expert defined knobs/ minimal | core | standard | extensive CSVs │ └── data driven knobs/ workload-specific tiers from analysis pipeline ├── results/ Optimization results JSON + HTML logs │ ├── oltp/{workload}/{pbt runs,bo runs,comparisons,baselines}/ │ ├── olap/{pbt runs,bo runs,comparisons,baselines}/ │ └── analysis/{workload}/ ├── workloads/ Workload definitions OLTP, OLAP, custom ├── notebooks/ Jupyter notebooks for analysis ├── tests/ Unit test suite ├── requirements.txt Runtime Python dependencies ├── requirements-dev.txt Dev/test/lint/typecheck dependencies ├── pyproject.toml Ruff + mypy configuration └── Makefile Deterministic local validation targets Python 3.11+ PostgreSQL 14+ with pg ctl , initdb in PATH psutil system monitoring sysbench optional, for OLTP workloads git clone https://github.com/alshawai/PBTune.git cd PBTune pip install -r requirements.txt For contributor workflows tests, lint, type checks , install development dependencies as well: pip install -r requirements-dev.txt Key Dependencies: psycopg2-binary - PostgreSQL adapter psutil - System resource monitoring numpy - Numerical operations for PBT pandas - Knob metadata processing python-dotenv - Environment variable management pytest - Unit test runner ruff - Linting baseline high-signal correctness checks mypy - Type-check baseline for evaluation module Create a .env file from the template: cp .env.example .env Edit .env with your database e.g., PostgreSQL credentials: DB USER=postgres DB PASSWORD=your secure password DB HOST=localhost DB PORT=5432 DB NAME=test dataset See docs/getting-started/setup.md /alshawai/PBTune/blob/main/docs/getting-started/setup.md for detailed setup instructions. python -m src.scripts.setup database This creates: sbtest1 table with 10,000 rows OLTP workload testing - Indexes and constraints Run the deterministic command matrix used by CI: make lint make typecheck make test or run all gates make check-all Important Notes: - Actual runtime depends on your hardware CPU cores, RAM, storage speed . Times shown are estimates for modern multi-core systems with SSD storage. - The number of knobs does NOTscale proportionally to wall-clock time, as PBT applies a candidate configuration from the entire knob spaceat once. But tuning performance may be degraded, causing PBT to requiremore generationsto reach optimal performance. Optimize 5 core knobs with minimal population for quick testing: python -m src.tuners pbt \ --tier minimal \ --config rapid \ --generations 10 \ --population 4 Output: - JSON results: results/oltp/oltp read write/pbt runs/minimal/tuning sessions/pbt results TIMESTAMP.json - HTML log colored : results/oltp/oltp read write/pbt runs/minimal/pbt tuning TIMESTAMP.html - Best config: results/oltp/oltp read write/pbt runs/minimal/best configs/best config TIMESTAMP.json Tune 13 core knobs with standard PBT configuration: python -m src.tuners pbt \ --tier core \ --config standard \ --generations 30 \ --population 8 Larger knob space 36 parameters with thorough evaluation: python -m src.tuners pbt \ --tier standard \ --config thorough \ --generations 50 \ --population 12 \ --workload oltp python -m src.tuners pbt \ --tier core \ --config standard \ --workload-file workloads/custom queries.json Override automatic hardware detection to manually allocate RAM and CPU cores for each parallel worker up to 95% of host capacity : python -m src.tuners pbt \ --worker-ram 4G \ --worker-cpus 2 \ --parallel-workers 3 Open the HTML log in your browser for color-coded output: Path follows the workload-partitioned layout, e.g. for OLTP read-write minimal tier: LOG=results/oltp/oltp read write/pbt runs/minimal/pbt tuning TIMESTAMP.html Windows start "$LOG" macOS open "$LOG" Linux xdg-open "$LOG" python -m src.tuners pbt --help Key Arguments: | Argument | Options | Default | Description | |---|---|---|---| --tier | minimal , core , standard , extensive | minimal | Knob space tier 5, 13, 36, 80+ knobs | --config | rapid , standard , thorough , research , extreme | standard | PBT configuration profile | --population | integer | from profile | Worker count overrides profile | --generations | integer | from profile | Optimization iterations | --workload | oltp , olap , mixed | oltp | Workload type | --duration | seconds | 30 | Evaluation duration per worker | --sysbench-workload | oltp read only , oltp read write , oltp write only | oltp read write | Sysbench OLTP mode when --benchmark sysbench | --scoring-policy | fixed v1 , feature driven v2 | falls back to PBT config | Scoring policy feature driven v2 for new runs | --scoring-policy-version | version string | 2.0 | Scoring policy version | --scoring-calibration-evals | integer | 5 | Number of evals for normalization calibration | --tuning-mode | online , offline , adaptive | offline | Restart policy online = no restarts; offline = every gen; adaptive = every N gens | --verbose | DEBUG , INFO , WARNING , ERROR , TRACE | INFO | Console log level | Use explicit Sysbench workload mode selection when running OLTP benchmarks: Read-only OLTP benchmark python -m src.tuners pbt --benchmark sysbench --sysbench-workload oltp read only --tier core --config standard Read-write OLTP benchmark default python -m src.tuners pbt --benchmark sysbench --sysbench-workload oltp read write --tier core --config standard Write-only OLTP benchmark python -m src.tuners pbt --benchmark sysbench --sysbench-workload oltp write only --tier core --config standard Sysbench outputs are partitioned by mode: - PBT sessions: results/oltp/{sysbench workload}/pbt runs/{tier}/... - BO sessions: results/oltp/{sysbench workload}/bo runs/{tier}/... - Evaluation comparisons: results/oltp/{sysbench workload}/comparisons/{tier}/... Use feature-driven scoring during tuning for workload-aware metric weighting: Use feature-driven scoring during tuning python -m src.tuners pbt --tier core --config standard --scoring-policy feature driven v2 Re-evaluate a session with a different scoring policy python -m src.evaluation \ --session results/olap/pbt runs/extensive/tuning sessions/pbt results YYYYMMDD HHMM.json \ --scoring-policy feature driven v2 Available policies: fixed v1 — legacy static weights compatibility only; loaded automatically for historical sessions feature driven v2 — dynamic workload-feature-conditioned weights default for new runs This framework intentionally supports a two-pronged benchmarking methodology: Academic Baselines : For scientifically rigorous evaluations without Python overhead, use external C-binaries e.g. --benchmark sysbench . Custom Prototyping : For tuning proprietary application databases, use the internal JSON-based query templates. For full architectural details on this design, please read the Benchmarking Documentation /alshawai/PBTune/blob/main/docs/reference/benchmarking.md . The tuner allows you to define your own workload templates using JSON or YAML. These files are natively executed by the WorkloadExecutor , which supports dynamic parameter injection for variables such as {id} , {k val} , {threshold} , {low} , {high} , and {offset} . Example JSON my workload.json : { "name": "Custom Workload", "description": "Application-specific query patterns", "queries": { "sql": "SELECT FROM users WHERE id = {id}", "weight": 0.5, "description": "Primary key lookup" } } Run with: python -m src.tuners pbt --workload-file path/to/my workload.json See the workloads directory README /alshawai/PBTune/blob/main/workloads/README.md for full formatting details. Comprehensive documentation available in docs/ /alshawai/PBTune/blob/main/docs : - Navigation map for all project docs Documentation Index /alshawai/PBTune/blob/main/docs/README.md - Worker, Evolution, Population, lockstep generation barriers PBT Core Components /alshawai/PBTune/blob/main/docs/architecture/pbt-core.md - Scoring-v2 policies, workload features, normalization, and reliability gate Feature-Driven Scoring /alshawai/PBTune/blob/main/docs/architecture/feature-driven-scoring.md - WorkloadOrchestrator, PerformanceMetrics, scoring integration Performance Evaluation /alshawai/PBTune/blob/main/docs/architecture/performance-evaluation.md - KnobSpace, tier CSVs, KnobApplicator, verify read-back Configuration Management /alshawai/PBTune/blob/main/docs/architecture/configuration-management.md - Database connection, knob retrieval, tuning metadata, policy filter PostgreSQL Connection and Knobs /alshawai/PBTune/blob/main/docs/architecture/postgresql-connection-and-knobs.md - Installation, configuration, troubleshooting Environment Setup /alshawai/PBTune/blob/main/docs/getting-started/setup.md - Canonical comparative-evaluation commands, outputs, and reproducibility checks Evaluation Reproducibility Runbook /alshawai/PBTune/blob/main/docs/guides/evaluation-runbook.md This work builds upon several research directions: Jaderberg et al. 2017 : "Population Based Training of Neural Networks" - Original PBT algorithm for neural network hyperparameter optimization - OtterTune 2017 : Automated configuration tuning using ML and transfer learning Contribution : Adaptive metric normalization using percentile-based ranges - CDBTune 2019 : Deep reinforcement learning for database knob tuning Contribution : Intelligent restart management, batched restarts every N iterations - QTune 2019 : Query-aware database configuration tuning Contribution : Workload-specific metric weighting Latin Hypercube Sampling : Space-filling initial population generation Truncation Selection : Efficient exploitation mechanism Adaptive Perturbation : Context-aware exploration strategies See the curated analysis and references in: - Auto DBMS Tuner 5 papers - Reinforcement Learning for DB tuning 4 papers - Query Optimization 1 paper - Adaptive Indexing 3 papers - future work : This section describes the expected research methodology. Comprehensive benchmarking across diverse hardware configurations and workloads is ongoing. Results shown below are preliminary and illustrative of the system's capabilities.⚠️ Status System Used for Development: - Population: 4-8 workers - Generations: 10-30 - Knob Tier: core 13 parameters - Workload: OLTP Sysbench-compatible Observed Behavior: Convergence : The population shows convergence within 10-15 generations Configuration Evolution : Poor performers successfully adopt elite configurations Stability : Top-performing configurations remain stable across multiple generations Restart Management : Batched restarts every 10 generations successfully balance configuration changes with overhead Below is an actual configuration discovered by PBT on development hardware format only - not claiming optimality : { "shared buffers": 75984, // ~593 MB "effective cache size": 87009, // ~679 MB "work mem": 6800, // ~53 MB "maintenance work mem": 504084, // ~3.8 GB "random page cost": 1.98, // SSD-friendly "effective io concurrency": 156, "max parallel workers per gather": 2, "checkpoint completion target": 0.55, "checkpoint timeout": 439, // ~7 minutes "wal buffers": 272, // ~2 MB "default statistics target": 2405, "max connections": 81, "max worker processes": 6 } Note: Actual optimal configurations vary significantly based on hardware CPU cores, RAM, storage type , workload characteristics OLTP vs OLAP , and database size. The PBT system adapts to your specific environment. Future work includes rigorous benchmarking: - Multiple hardware configurations cloud and on-premise - Diverse workload types YCSB, TPC-C, real-world traces - Comparison with more baseline tuning methods See results/ directory for optimization traces from your runs. Advanced Features like Workload prediction -- Query clustering for adaptive metric weighting Cloud Deployment : Kubernetes orchestration for multi-instance database, AWS RDS/Aurora integration Multi-DBMS integration : MySQL, MariaDB. This is an academic research project under active development. For academic collaborators: Research Extensions : Contact repository maintainers for collaboration Bug Reports : Open GitHub issues with reproduction steps Documentation : Improvements to docs always welcome For external contributors: - Currently not accepting pull requests for core algorithm changes - Bug fixes and documentation PRs may be considered on case-by-case basis Be respectful, professional, and constructive. This is academic research—critiques should be evidence-based and cite relevant literature. PBTune is licensed under the GNU General Public License v3.0 /alshawai/PBTune/blob/main/LICENSE . This means you are free to use, modify, and distribute PBTune, provided that any derivative work is also released under GPL v3. All forks must remain open source. For commercial licensing inquiries , contact: Ibrahim Al-Shawa mailto:contact.alshaw.ai@gmail.com If you use this work in academic research, please cite: @software{pbtune2026, title = {PBTune: Population-Based Training for Automatic Database Parameter Tuning}, author = {Al-Shawa, Ibrahim and Hedia, AbdelRahman and Darwish, Mahmoud and Saber, Walaa and El-Sayed, Emad}, year = {2026}, url = {https://github.com/alshawai/PBTune}, license = {GPL-3.0} } PBTune is described in a paper currently in preparation: PBTune: Population-Based Training for Automatic Database Parameter TuningIbrahim Al-Shawa, AbdelRahman Hedia, Mahmoud Darwish, Walaa Saber, Emad El-Sayed A preprint will be available soon. | Tool | Approach | Key Paper | |---|---|---| | PBTune | Evolutionary PBT | In preparation | | OtterTune | GP + Lasso + NN | | Zhang et al., SIGMOD 2019 https://doi.org/10.1145/3299869.3300085 Kanellis et al., VLDB 2022 https://doi.org/10.14778/3551793.3551844 Lao et al., VLDB 2024 https://doi.org/10.14778/3659437.3659449 Note:Direct performance comparisons are omitted — these systems were evaluated on different hardware and workloads. PBTune was built by: With contributions from: Maintainer : Ibrahim Al-Shawa https://github.com/alshawai Email : contact.alshaw.ai@gmail.com mailto:contact.alshaw.ai@gmail.com Repository : https://github.com/alshawai/PBTune https://github.com/alshawai/PBTune Issues : https://github.com/alshawai/PBTune/issues https://github.com/alshawai/PBTune/issues Built with 🧬 Evolutionary Optimization | 🛢 Database Configuration | 🐍 Python Advancing the state-of-the-art in autonomous database systems