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

wpnews.pro

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

Overview Key Innovation Architecture Repository Structure Installation Quick Start Documentation Research Foundation Experimental Results Future Work Contributing License Citation 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, withfixed_v1

retained for compatibilityIntelligent Restarts: CDBTune-inspired batched restarts (every 10 generations) balance configuration changes vs. overhead

See docs/architecture/feature-driven-scoring.md for the scoring model, policy metadata, and migration notes.

See 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`

Workersrc/tuners/pbt/worker.py

Evolutionsrc/tuners/pbt/evolution.py

Generation Barrierssrc/tuners/engine/barriers.py

Workload Orchestratorsrc/tuners/engine/orchestrator.py

Environment Backendssrc/utils/environments/

Knob Spacesrc/knobs/knob_space.py

Scoring (v2)src/utils/scoring/

See 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 s + 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+(withpg_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 adapterpsutil
System resource monitoringnumpy
Numerical operations for PBTpandas
Knob metadata processingpython-dotenv
Environment variable managementpytest
Unit test runnerruff
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 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
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:

LOG=results/oltp/oltp_read_write/pbt_runs/minimal/pbt_tuning_TIMESTAMP.html

start "$LOG"

open "$LOG"

xdg-open "$LOG"
python -m src.tuners pbt --help

Key Arguments:

Argument	Options	Default
`--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:

python -m src.tuners pbt --benchmark sysbench --sysbench-workload oltp_read_only --tier core --config standard

python -m src.tuners pbt --benchmark sysbench --sysbench-workload oltp_read_write --tier core --config standard

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:

python -m src.tuners pbt --tier core --config standard --scoring-policy feature_driven_v2

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.

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 for full formatting details.

Comprehensive documentation available in docs/:

Navigation map for all project docsDocumentation Index
Worker, Evolution, Population, lockstep generation barriersPBT Core Components- Scoring-v2 policies, workload features, normalization, and reliability gateFeature-Driven Scoring- WorkloadOrchestrator, PerformanceMetrics, scoring integrationPerformance Evaluation- KnobSpace, tier CSVs, KnobApplicator, verify() read-backConfiguration Management
Database connection, knob retrieval, tuning metadata, policy filterPostgreSQL Connection and Knobs- Installation, configuration, troubleshootingEnvironment Setup- Canonical comparative-evaluation commands, outputs, and reproducibility checksEvaluation Reproducibility Runbook

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 learningContribution: Adaptive metric normalization using percentile-based ranges

CDBTune (2019): Deep reinforcement learning for database knob tuningContribution: Intelligent restart management, batched restarts every N iterations

QTune (2019): Query-aware database configuration tuningContribution: 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 weightingCloud 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.

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

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.