The Project: Database Performance Testing in Two Days
A developer built a database performance testing tool in roughly two days, with AI writing most of the code. The project, called "Database Performance Testing," runs performance tests against a relational database. It covers four scenarios: N+1 query detection, deadlock simulation, query regression tracking across schema changes, and slow query detection.
Why Test Database Performance?
The author was motivated by a real project where data performance was critical. They argue that performance issues often hide in slow queries, not just slow API endpoints. The tool aims to surface those issues before they reach production.
The Four Test Scenarios
N+1 Query Detection
This classic ORM problem occurs when an application runs 1 initial query plus N additional queries. For example, fetching 20 orders and then querying user emails for each order individually. The tool detects and simulates this pattern.
Deadlock Simulation
Two transactions acquiring locks in reverse order can cause a deadlock. The database resolves it by rolling back one transaction, but that rollback has a cost. The test measures recovery time and timing impact.
Query Regression Tracking
This scenario captures EXPLAIN ANALYZE output before and after a schema change, then diffs the results. It validates that optimizations (like adding an index) actually improve performance.
Slow Queries
A set of critical queries must stay under a configurable threshold (SLOW_QUERY_THRESHOLD_MS in config.py). If any breach the threshold in CI, the pipeline fails. Five queries are monitored, with results like:
| Query | Avg (ms) | p95 (ms) | Max (ms) |
|---|---|---|---|
| user_lookup | 0.20 | 0.29 | 0.35 |
| order_history | 0.31 | 0.43 | 0.75 |
| inventory_search | 0.24 | 0.32 | 0.62 |
Project Structure
The repository is organized into modules:
analysis/– independent modules for N+1 detection, deadlock simulation, and EXPLAIN analysisbenchmarks/– test scenarios and SQL query filesdata/– seed data and distributionsmigrations/– baseline and sample migration for regression demoreports/– delta reporter and metrics export (with Grafana integration)docker/– Docker Compose with PostgreSQL and auto-provisioned Grafana.github/workflows/– CI pipeline that applies schema, seeds data, and runs pytest
Concrete Results: Index Optimization
The most compelling data came from the query regression scenario. After adding a composite index idx_orders_user_created via migration 002_add_indexes.sql, the order_history query improved dramatically:
Before (Sequential Scan):
- Execution time: 0.532ms
- Touched 39 blocks, filtered 2,497 rows to find 3 matches
- Total cost estimate: 101.27
After (Bitmap Index Scan):
- Execution time: 0.090ms
- Read only 5 blocks
- Total cost estimate: 10.92
That's an 83% reduction in execution time and a ~10x drop in planner cost. Without regression tracking, this improvement would have gone unnoticed.
Honest Reflections on AI-Assisted Development
The author spent more time validating test correctness than implementing code. AI excelled at boilerplate (Docker, pytest fixtures, file organization) but needed human oversight for edge cases and schema mismatches. The Docker + Grafana setup was generated in minutes, saving half a day. However, AI-generated SQL files sometimes had mismatched column names, and the N+1 detection logic required an extra pass for concurrent scenarios. The author concludes that the human-in-the-loop is essential.
Why This Matters
Database performance testing is often an afterthought. This tool provides a structured way to catch regressions early, with CI integration and visual dashboards. The AI-assisted approach accelerated development, but the real value is in the testing methodology itself.
