Auto DB Optimizer

An LLM-driven self-optimizing database system that automatically detects slow queries, analyzes workload patterns, and uses an LLM (Claude or Llama via NVIDIA NIM) to recommend and apply the best database indexes.

How It Works

The optimizer runs an 8-step pipeline:

1. Connect to a PostgreSQL database
2. Initialize schema with sample tables and seed data (customers, products, orders)
3. Simulate a realistic query workload
4. Monitor and detect slow queries
5. Analyze workload patterns (column access frequency, query types)
6. Generate candidate indexes (single-column and composite)
7. Consult LLM to select the best indexes with reasoning
8. Evaluate performance improvement (before vs. after)

Prerequisites

• Python >= 3.11
• PostgreSQL (running and accessible)

Quick Start

1. Clone and install dependencies

cd autodb
pip install -r requirements.txt

Or install as a package:

pip install .

2. Set up PostgreSQL

Make sure PostgreSQL is running, then create the database:

# Linux
sudo -u postgres psql -c "CREATE DATABASE autodb;"

# macOS (Homebrew)
createdb autodb

# Or connect to psql and run:
# CREATE DATABASE autodb;

3. Configure environment variables

cp auto_db_optimizer/.env.example auto_db_optimizer/.env

Edit .env with your settings. At minimum you need:

• A valid database connection (DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD)
• An LLM provider and its API key:
  • Anthropic: set LLM_PROVIDER=anthropic and ANTHROPIC_API_KEY=sk-ant-...
  • NVIDIA NIM: set LLM_PROVIDER=nvidia_nim and NIM_API_KEY=nvapi-...

4. Run the optimizer

cd auto_db_optimizer
python main.py

Environment Variables

Variable	Default	Description
DB_HOST	localhost	PostgreSQL host
DB_PORT	5432	PostgreSQL port
DB_NAME	autodb	Database name
DB_USER	postgres	Database user
DB_PASSWORD	postgres	Database password
SLOW_QUERY_THRESHOLD_MS	100	Slow query threshold in milliseconds
LLM_PROVIDER	anthropic	LLM backend: anthropic or nvidia_nim
ANTHROPIC_API_KEY	(empty)	Anthropic API key (required for anthropic provider)
LLM_MODEL	claude-sonnet-4-6-20250514	Claude model to use
NIM_API_KEY	(empty)	NVIDIA NIM API key (required for nvidia_nim provider)
NIM_BASE_URL	https://integrate.api.nvidia.com/v1	NVIDIA NIM endpoint
NIM_MODEL	meta/llama-3.1-70b-instruct	NVIDIA NIM model
WORKLOAD_SIZE	200	Number of queries to simulate

Project Structure

auto_db_optimizer/
├── main.py                  # Entry point — runs the 8-step pipeline
├── config/
│   └── settings.py          # Loads .env and exports configuration
├── database/
│   ├── db_connection.py     # PostgreSQL connection utilities
│   └── schema.sql           # Schema DDL and seed data
├── simulation/
│   └── workload_simulator.py  # Generates synthetic query workload
├── monitor/
│   └── query_monitor.py     # Query logging and slow query detection
├── analysis/
│   ├── workload_analyzer.py # Workload pattern analysis
│   └── query_features.py    # SQL query feature extraction
├── agents/
│   └── llm_optimizer_agent.py  # LLM integration (Claude / NIM)
├── optimizer/
│   ├── index_generator.py   # Candidate index generation
│   └── index_manager.py     # Index creation and management
└── evaluation/
    └── performance_evaluator.py  # Before/after performance measurement

Example Output

============================================================
  Auto DB Optimizer — Self-Optimizing Database System
============================================================

[1/8] Connecting to database...
  Connected.

[2/8] Setting up schema and seed data...
[Setup] Schema initialized and data seeded.

[3/8] Simulating workload (200 queries)...
  Workload complete: 200 queries, avg=12.3ms, max=85.4ms

[4/8] Detecting slow queries (threshold=100ms)...
  Found 15 slow queries out of 200 total.

[5/8] Analyzing workload patterns...
  Identified 8 column access patterns.

[6/8] Generating candidate indexes...
  Generated 6 candidate indexes.

[7/8] Consulting LLM optimizer agent...
  LLM recommended 3 indexes.

[8/8] Evaluating performance improvement...
  Overall: 245.3ms → 98.1ms (+60.0%)

============================================================
  Optimization Summary
============================================================
  Queries analyzed:    200
  Slow queries found:  15
  Indexes recommended: 3
  Indexes applied:     3
  Overall improvement: +60.0%
============================================================

License

MIT