Database Performance Tests

A PostgreSQL performance testing suite focused on three scenarios:

• N+1 Query Detection — instrument queries, surface repeated patterns, compare bad vs. fixed implementations
• Deadlock Simulation — reproduce the classic reverse lock-order deadlock and document the fix
• Query Regression Tracking Across Schema Changes — benchmark before and after a migration, diff execution plans, gate on latency thresholds with pytest

Schema: users → orders → order_items + inventory

---

Prerequisites

• Docker + Docker Compose
• Python 3.10+

---

Setup

1. Start the database

docker compose -f docker/docker-compose.yml up -d

2. Create virtual environment and install dependencies

python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

3. Configure environment variables

Create .env at the project root:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=perfdb
DB_USER=perftest
DB_PASSWORD=perftest

4. Apply the schema and seed data

python scripts/setup_schema.py --schema baseline/001_initial_schema
python data/seed.py 10000

setup_schema.py applies the SQL file and saves a schema_v1.sql snapshot next to it.

---

Scenarios

N+1 Query Detection

python analysis/n_plus_one_detector.py

Attaches a SQLAlchemy event listener, runs a simulated N+1 code path (one query per order row), then the fixed version (single JOIN). Prints a count of repeated normalized queries so the pattern is unmistakable.

[BAD]  20 orders fetched → 21 queries fired (1 + 20)
  N+1 candidates detected:
    [20x] SELECT EMAIL FROM USERS WHERE ID = $?

[GOOD] 20 orders+emails fetched → 1 query fired

To instrument your own code, import attach_logger, reset_log, and detect from analysis/n_plus_one_detector.py.

---

Deadlock Simulation

python analysis/deadlock_simulator.py

Spawns two threads that acquire row locks in opposite order using a threading.Barrier to make the deadlock deterministic. PostgreSQL detects the cycle and rolls back one transaction automatically. The script prints which transaction was the victim and shows the consistent lock-ordering fix.

Transaction A: committed
Transaction B: rolled back — DeadlockDetected

PostgreSQL detected the cycle and rolled back Transaction B.

---

Query Regression Tracking Across Schema Changes

The workflow is: baseline → capture plan → migrate → measure impact → pytest gate.

1. Capture baseline performance

python reports/query_regression_report.py low

2. Capture EXPLAIN plan before migration

python analysis/explain_analyzer.py

Plans are saved as JSON to reports/plans/.

3. Apply migration

python scripts/setup_schema.py --schema v2_add_indexes/002_add_indexes

4. Measure the impact

python reports/query_regression_report.py low

Query            avg ms    p95 ms    max ms  Δ vs last
order_history      0.38      0.62      0.95  -88.1% ✓
inventory_search   0.28      0.45      0.70  -74.5% ✓

Queries that regressed by more than 20% are flagged with ⚠.

5. Run the full test suite

pytest -v

Runs all four test modules. Fails if any critical query exceeds SLOW_QUERY_THRESHOLD_MS (200 ms, set in config.py), if N+1 patterns appear in the optimized path, if deadlock detection doesn't behave as expected, or if the planner falls back to a Seq Scan on an indexed query.

---

Test Suite

Tests live in benchmarks/ and are discovered automatically via pyproject.toml. Run all:

pytest -v

Run a specific scenario with a marker:

pytest -v -m n_plus_one   # N+1 detection tests
pytest -v -m deadlock     # deadlock simulation tests
pytest -v -m explain      # EXPLAIN ANALYZE plan regression tests

File	Marker	What it checks
test_n_plus_one.py	n_plus_one	Bad path produces repeated queries; good path produces none
test_deadlock.py	deadlock	Exactly one transaction commits and one is rolled back
test_explain.py	explain	No Seq Scan on indexed queries; actual time within threshold
test_slow_queries.py	(none)	Five critical queries complete within 200 ms

Fixtures (engine, instrumented_engine) are defined in conftest.py. JUnit XML is written to reports/junit.xml after every run.

---

CI

Tests run automatically on every push and pull request to main via .github/workflows/performance-tests.yml. The workflow spins up a PostgreSQL 16 service container, applies the baseline schema with psql, seeds 1 000 rows, and runs pytest. Results are published as a check via dorny/test-reporter and the JUnit XML is uploaded as an artifact.

---

Grafana Dashboard

Benchmark results are automatically exported to a benchmark_results table in PostgreSQL and visualized in Grafana.

Start Grafana:

docker compose -f docker/docker-compose.yml up -d

Open http://localhost:3000 — login admin / admin.

The Query Benchmark Results dashboard loads automatically. No manual setup needed: the PostgreSQL datasource and dashboard are provisioned on startup.

Panels:

Panel	What it shows
Avg Latency Over Time	avg_ms per query, color-coded — turns yellow at 100 ms, red at 200 ms
P95 Latency Over Time	p95_ms per query — highlights tail latency spikes across migrations
Latest Benchmark Run	Table of the most recent run: avg / p95 / max per query

Use the Volume dropdown at the top to switch between low, medium, and high data sets.

Every time you run the regression report, results are written to the table automatically:

python reports/query_regression_report.py low
# → runs benchmarks, saves JSON, exports to benchmark_results, prints delta table

To backfill Grafana from previously saved JSON files:

python reports/export_metrics.py

---

Seeding data

python data/seed.py 1000      # quick smoke test
python data/seed.py 10000     # development
python data/seed.py 100000    # regression benchmarks

Each run truncates all tables. The seed is deterministic (SEED = 42).

---

Project Structure

.
├── config.py                          # DB_URL and SLOW_QUERY_THRESHOLD_MS
├── conftest.py                        # pytest fixtures: engine, instrumented_engine
├── pyproject.toml                     # pytest config and markers
├── requirements.txt
├── analysis/
│   ├── n_plus_one_detector.py         # N+1 detection and simulation
│   ├── deadlock_simulator.py          # Concurrent deadlock demo
│   └── explain_analyzer.py            # EXPLAIN plan capture and diff
├── benchmarks/
│   ├── queries/                       # Raw .sql files (12 queries)
│   ├── scenarios/
│   │   └── run_benchmark.py           # Volume benchmark runner
│   ├── test_n_plus_one.py             # N+1 detection tests
│   ├── test_deadlock.py               # Deadlock tests
│   ├── test_explain.py                # EXPLAIN ANALYZE plan tests
│   └── test_slow_queries.py           # Latency threshold gate (5 critical queries)
├── data/
│   ├── seed.py
│   └── distributions.json
├── migrations/
│   ├── baseline/
│   │   └── 001_initial_schema.sql
│   └── v2_add_indexes/
│       └── 002_add_indexes.sql        # Sample migration for regression demo
├── reports/
│   ├── query_regression_report.py     # Delta reporter (also exports to Grafana)
│   ├── export_metrics.py              # Writes results to benchmark_results table
│   ├── output/                        # Timestamped benchmark JSON results
│   └── plans/                         # Saved EXPLAIN plans
├── scripts/
│   └── setup_schema.py                # Apply migration + snapshot schema
├── .github/
│   └── workflows/
│       └── performance-tests.yml      # CI: schema → seed → pytest
└── docker/
    ├── docker-compose.yml             # PostgreSQL + Grafana
    ├── init.sql
    └── grafana/
        ├── provisioning/
        │   ├── datasources/postgres.yml
        │   └── dashboards/dashboard.yml
        └── dashboards/benchmark.json  # Auto-provisioned dashboard

---

References

• PostgreSQL EXPLAIN documentation
• PostgreSQL deadlock detection
• Use The Index, Luke