The day I learned database architecture

TL;DR: V3 backtest was 5x slower than V2. Three times I thought I knew the cause, three times I was wrong. Only when I actually started measuring with profiling did I find the real bottleneck. The fix wasn't a quick patch but a fundamental table addition. This is how you learn.

The problem

I'm building a systematic swing trading system that goes live in a few weeks with €10,000 of my own capital. V2 was finished and good: +110% over 4 years in a €50K backtest, Sharpe ratio 1.05.

V3 was supposed to be even better. Five new filters to block typical losing trade patterns. On paper solid.

When I started the V3 backtest, it turned out to be 5x slower than V2. An hour of work became 5 hours. Unworkable for production development.

Three wrong guesses

Fix 1: Connection refresh interval

First hypothesis: the database connection limit gets exhausted by all the extra queries. Lower the refresh interval.

Result: backtest stopped crashing, but stayed equally slow. Treating symptoms, not fixing.

Fix 2: Disable a filter

Second hypothesis: filter 3 (200-day moving average check) does a separate query for each candidate stock. Disable it.

Result: still 20 sec per simulated day. The AI flagged afterwards: "MA200 was already batched, just a dictionary lookup."

Wrong diagnosis. Wrong fix.

Fix 3: Compute upgrade

Third hypothesis: the database server is too small for the query volume. Upgrade to a bigger tier.

For the project, an acceptable investment: about €60 per month.

Result after a 5 minute upgrade: timing per day identical. No improvement at all.

Then finally: measuring

At this point I was four hours in, ten times thought "this is it", every time wrong.

The right question wasn't "where do I think it is", but "where actually is it".

Profiling code added in five minutes. All time-intensive operations measured with a regular Python time.perf_counter() interval. No complex profiling tool, just timing around each query.

Output doesn't lie:

fetch_prices_history_range_200d_window:  13.85 sec  ← bottleneck
fetch_prices_highs_batched_90d:           3.78 sec
calculate_sector_medians:                 0.88 sec
fetch_eur_per_usd:                        0.04 sec
fetch_latest_fundamentals:                0.47 sec
TOTAL per day:                           19.60 sec

70 percent of the time in one query. Not the filter logic, not the compute, not the connection. A query that fetches 200 days × 542 tickers = 108,400 rows over the wire, every day again, for 1015 simulation days.

The real fix: pre-compute caching

My instinct thought in code-optimizations: fewer queries, faster queries, smarter queries. But the real question was architectural: how much data do I actually need over the wire?

The answer: not 108,400 rows per day for the moving average. One value per ticker per day. Pre-calculated and cached.

New table ticker_indicators_daily:

• ticker, date as primary key
• ma_200d (200-day moving average)
• high_90d_max (max high last 90 days)
• low_90d_min (min low last 90 days)

Backfill once, then daily updates after price refreshes. Backtest reads one query for all 542 tickers at once, gets 542 numbers back, not 108,400 rows.

Expected impact: 20 seconds per day to 3 seconds per day. V3 backtest from 5 hours to 75 minutes.

What I learned

Not what I thought I'd learn: that code optimization works. That's clear and known.

What I did learn:

1. Profile before you optimize. No exceptions. Every guess without data is an opportunity to throw away hours.

2. Treating symptoms solves nothing. Adjusting connection refresh was symptom treatment. The query was still slow, just no longer crashing.

3. Cache what you read hundreds of times during backtests. A 200-day moving average for a fixed date doesn't change. Fetching one hundred and four thousand rows for one number is waste.

4. A compute upgrade is not an architectural solution. If your query design is bad, more CPU helps nothing. It's the same problem on more expensive hardware.

5. AI doesn't automatically do this right either. The AI builder implements what I ask, but not always what I need. Three times a wrong fix because I asked for the wrong fix.

The production discipline lesson

For production systems, especially with real money on the line, this is a mandatory rule:

For every new feature that requires data fetching, ask first: "can this be pre-computed?". If the same calculation happens every day for historical data: cache it in a dedicated table.

No workaround, no quick fix. Architectural choice upfront.

This rule lives in my .cursorrules so every new feature gets tested against it. Defense in depth, also against my own blind spots.

What this means for the V3 launch

V3 isn't going to be "a bit faster", V3 becomes truly production-grade. Not just for the backtest, but also for live trading. From June 22, the system reads that one cached table daily, gets its signals, places orders, done. No waiting on data fetches.

That's what production discipline means. Not working code. Scalably working code.

This post is part of playitsmart.nl, an experiment where Erik publicly builds a trading system with €10,000 of own capital. Live launch: June 22, 2026.