nate.swenson_
Local Fitness

Benchmarks that count connections, not just milliseconds

· 9 min read ·v0.21.0

Shipped

This release retired my fitness agent’s React web UI outright, about 3,800 lines, because every real interaction has gone through Claude and opencode as MCP clients for months. Retiring it exposed a gap: committing, discarding, or abandoning a training plan only had UI buttons, so three new MCP tools now cover the full plan lifecycle. The same release fixed the slow half of the tool surface: the function behind the daily brief was opening nine SQLite connections per call, and now opens one. That last change is the one worth teaching; the fix itself was a routine refactor, and the proof is what keeps it fixed. Here is how to build a benchmark harness that asserts structure, saves a baseline, and fails any pull request that regresses it.

The problem: one question, nine connections

The classic N+1 query problem is an application making database queries in a loop instead of one query that fetches everything at once. My version was a duller cousin. Each helper function in the brief-assembly chain opened its own sqlite3.connect(), did one read, and closed it. Every helper was fine in isolation. Composed into one top-level call, assemble_brief_context opened nine connections against the same local file; two sibling tools opened six and four.

The queries themselves could not be merged, they answer different questions (today’s metrics, recent workouts, the active plan). So the fix is connection reuse rather than batching: every function in the chain accepts an optional connection, and the top-level caller opens one and passes it down. Callers that pass nothing keep the old behavior, so nothing upstream breaks.

You can build the harness in an empty directory. The layout for this walkthrough:

perf-demo/
  app.py        # the read path, with the fix applied
  fixture.py    # synthetic data builder
  test_perf.py  # connection-count assertions + benchmarks
  pytest.ini

Install the two dependencies into whatever environment you use:

pip install pytest pytest-benchmark

Thread one connection through the call chain

The pattern that makes optional connection-passing painless is a tiny context manager that either borrows the caller’s connection or owns a fresh one:

# app.py
"""A small read path with the connection-reuse fix applied."""
import sqlite3
from contextlib import contextmanager


def connect(db_path):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn


@contextmanager
def use_conn(db_path, conn=None):
    """Yield the caller's connection if one was passed, otherwise open
    a fresh one and close it on exit."""
    if conn is not None:
        yield conn
        return
    owned = connect(db_path)
    try:
        yield owned
    finally:
        owned.close()


def todays_metrics(db_path, conn=None):
    with use_conn(db_path, conn) as c:
        return c.execute(
            "SELECT * FROM metrics ORDER BY day DESC LIMIT 1"
        ).fetchone()


def recent_workouts(db_path, conn=None, days=35):
    with use_conn(db_path, conn) as c:
        return c.execute(
            "SELECT * FROM workouts WHERE day >= date("
            "(SELECT MAX(day) FROM metrics), ?) ORDER BY day DESC",
            (f"-{days} days",),
        ).fetchall()


def active_plan(db_path, conn=None):
    with use_conn(db_path, conn) as c:
        return c.execute(
            "SELECT * FROM plans WHERE status = 'active' LIMIT 1"
        ).fetchone()


def assemble_summary(db_path, conn=None):
    """The top-level call an MCP tool (or API route) would make."""
    with use_conn(db_path, conn) as c:
        return {
            "metrics": dict(todays_metrics(db_path, conn=c)),
            "workouts": [dict(r) for r in recent_workouts(db_path, conn=c)],
            "plan": dict(p) if (p := active_plan(db_path, conn=c)) else None,
        }

The ownership rule lives in one place: whoever opens the connection closes it, and a borrowed connection is never closed. In my real codebase this parameter got threaded through four modules; the shape per function is identical to the three helpers above.

Assert the connection count, not just the clock

A wall-clock benchmark can pass for the wrong reason, like a warm disk cache or a CI runner that happens to be fast that day. The claim I wanted to enforce is structural, this call opens exactly one connection, and a structural claim deserves a direct assertion. So the test file measures both axes, with a counting wrapper around connect for the structural one:

# test_perf.py
import pytest

import app
from fixture import build_fixture


@pytest.fixture(scope="module")
def fixture_db(tmp_path_factory):
    path = tmp_path_factory.mktemp("perf") / "app.db"
    build_fixture(path)
    return str(path)


def count_opens(monkeypatch):
    """Wrap app.connect so every real connection open increments a counter."""
    counts = {"n": 0}
    real_connect = app.connect

    def counting_connect(*args, **kwargs):
        counts["n"] += 1
        return real_connect(*args, **kwargs)

    monkeypatch.setattr(app, "connect", counting_connect)
    return counts


def test_summary_opens_one_connection(fixture_db, monkeypatch):
    counts = count_opens(monkeypatch)
    result = app.assemble_summary(fixture_db)
    assert result["metrics"]
    assert counts["n"] == 1, f"expected 1 connection open, got {counts['n']}"


def test_bench_assemble_summary(benchmark, fixture_db):
    result = benchmark(app.assemble_summary, fixture_db)
    assert result["metrics"]

The fixture is synthetic and fabricated, never real user data, but big enough that a bounded query and an unbounded one behave differently:

# fixture.py
"""Build a synthetic multi-year fixture database. Fabricated data only."""
from datetime import date, timedelta

import app


def build_fixture(db_path, years=3):
    conn = app.connect(db_path)
    conn.executescript("""
        CREATE TABLE metrics  (day TEXT PRIMARY KEY, resting_hr INTEGER);
        CREATE TABLE workouts (day TEXT, minutes INTEGER);
        CREATE TABLE plans    (name TEXT, status TEXT);
    """)
    start = date(2026, 7, 9) - timedelta(days=365 * years)
    for i in range(365 * years):
        d = (start + timedelta(days=i)).isoformat()
        conn.execute("INSERT INTO metrics VALUES (?, ?)", (d, 50 + i % 10))
        if i % 2 == 0:
            conn.execute("INSERT INTO workouts VALUES (?, ?)", (d, 30 + i % 45))
    conn.execute("INSERT INTO plans VALUES ('10k block', 'active')")
    conn.commit()
    conn.close()

Before landing my fix I ran the count test against the pre-fix code via git stash and recorded the numbers it reported (nine, six, and four opens for the three call chains) in the test file’s docstring, so the before state is a measured fact rather than a memory.

Benchmarks are slow by design, so keep them out of every ordinary test run. The pytest-benchmark usage docs give you a skip flag and an only flag, and the only flag overrides the skip, which is exactly the composition you want:

# pytest.ini
[pytest]
addopts = --benchmark-skip

Now a plain pytest never pays the benchmark cost, and a dedicated CI step opts back in with --benchmark-only.

Gate pull requests against a saved baseline

pytest-benchmark can save runs and compare against them: --benchmark-autosave stores each run under an auto-numbered id, --benchmark-compare=0001 compares the current run against save 0001, and --benchmark-compare-fail=min:15% fails the suite if the minimum observed time got 15% worse than that baseline. Wire it into CI as its own step:

- name: Perf-benchmark regression gate
  run: |
    pytest test_perf.py --benchmark-only \
      --benchmark-storage=file://./.benchmarks --benchmark-autosave \
      --benchmark-compare=0001 --benchmark-compare-fail=min:15%

- name: Upload this run's benchmark save
  if: always()
  uses: actions/upload-artifact@v4
  with:
    name: perf-run-${{ github.run_id }}
    path: .benchmarks/
    include-hidden-files: true

The baseline 0001 is committed to the repo, and the upload step exists so that any CI run’s save can be downloaded and promoted to a new baseline after a deliberate perf improvement. If your project runs a global coverage gate through addopts, add --no-cov to the benchmark invocation; more on that in the gotchas.

Run it and read the numbers

Three commands, run from the project directory. First, confirm ordinary test runs skip the benchmark but still run the count assertion:

pytest -q

.s                                                                       [100%]
1 passed, 1 skipped in 0.02s

Second, capture a baseline:

pytest --benchmark-only --benchmark-storage=file://./.benchmarks --benchmark-autosave -q

--------------------------------------------- benchmark: 1 tests ---------------------------------------------
Name (time in us)                    Min         Max      Mean    StdDev    Median  Rounds
test_bench_assemble_summary     130.8341  2,380.4172  267.6388  141.9217  308.6660    1696
1 passed, 1 skipped in 1.69s

Third, run the gate the way CI will:

pytest --benchmark-only --benchmark-storage=file://./.benchmarks \
  --benchmark-compare=0001 --benchmark-compare-fail=min:15% -q

Comparing against benchmarks from: Darwin-CPython-3.14-64bit/0001_unversioned_20260712_151332.json
test_bench_assemble_summary (0001_unversi)     130.8341 (1.0)
test_bench_assemble_summary (NOW)              131.6250 (1.01)
1 passed, 1 skipped in 1.19s

Those are real outputs from my machine, trimmed to the interesting columns. Note the directory in that first line, Darwin-CPython-3.14-64bit. That string is about to matter.

Gotchas

A matching machine-id string is not a matching machine. pytest-benchmark groups saved runs by an id built in get_machine_id(): platform, Python implementation, Python version, architecture. I captured my first baseline in a python:3.12-slim linux/amd64 container on an Apple Silicon Mac. Its id string, Linux-CPython-3.12-64bit, matched GitHub’s ubuntu-latest runners exactly, so the comparison ran without complaint, and the first real CI run reported regressions of 27 to 42% on every benchmark. Nothing had regressed; Docker Desktop’s emulated CPU is just far slower than real cloud hardware, and the id string carries no information about that. The escape is to capture the baseline on the same runner class that runs the gate. I let the CI step itself autosave, downloaded that run’s artifact, and promoted it to 0001.

Your artifact upload can silently drop the one directory you need. actions/upload-artifact@v4 ignores hidden files by default, where hidden means any path beginning with a dot, and .benchmarks/ begins with a dot. The upload step succeeded with no error; the artifact was simply empty. The symptom spans two steps: pytest’s log says it saved benchmark data, and the upload step says no files were found at the path, with nothing connecting the two lines. include-hidden-files: true fixes it, and it is already in the YAML above.

A global coverage gate will fail a benchmark-only run on coverage, not on performance. My addopts enforces a coverage floor across the full test suite. A --benchmark-only invocation imports a thin slice of the codebase, so it fails the coverage check no matter how fast the code is. The benchmark step needs --no-cov to opt out; without it the gate is red for a reason that has nothing to do with what it gates.

A workflow_dispatch capture workflow cannot be dispatched until it exists on the default branch. I wrote a dedicated baseline-capture workflow, then discovered I could not trigger it from the feature branch that introduced it, because GitHub only dispatches workflows that are already on the default branch. The autosave-plus-upload pattern in the CI step doubles as the escape: every run produces a downloadable save, so you can bootstrap a genuine baseline from any pull request build without the capture workflow existing yet.

Sources

Changelog

  • Dev (#95) (37f67bb)
  • chore: bump version to 0.21.0 (#101) (#102) (1c409d1)