Building a Performance Metric System with SQLite (Part 1)

#sqlite #analytics

Hello, I'm Ganesh. I'm working on FreeDevTools online, currently building a single platform for all development tools, cheat codes, and TL; DRs — a free, open-source hub where developers can quickly find and use tools without the hassle of searching the internet.

We all want our software to be fast. But "fast" isn't a feeling—it's a number. And unfortunately, software has a natural tendency to get slower over time as we add new features, tracking scripts, and new designs.

To stop this "performance rot," you need a warning system. You need to know exactly when your numbers drop, and by how much.

In this two-part series, we are going to build a lightweight, performance tracker using nothing but SQLite.

But before we start implementing, we need to understand what we are actually measuring.

The Big Three Metrics

When tracking performance, raw numbers (like " response time of API is 1.2s") are useful, but trends are powerful. We track trends using three simple timeframes:

1. DoD (Day-over-Day)

This compares today’s performance against yesterday’s. It is your immediate feedback loop.

Why it matters: If your score drops 10 points DoD, it usually means the code you just merged caused a regression. You can catch it before it hits production users.

2. WoW (Week-over-Week)

This compares today’s performance against the same day last week.

Why it matters: sometimes daily data is noisy. Maybe the network was just slow yesterday. WoW smooths out the noise. It helps you see if your performance is slowly creeping up (getting slower) over a sprint or a development cycle.

3. MoM (Month-over-Month)

This compares this month’s average against last month’s average.

Why it matters: This is the "Health Check." It tells you if your product is maturing and getting optimized, or if "feature bloat" is slowly killing your user experience.

The Strategy: "Burst" vs. "Calculated"

To calculate these three metrics accurately, we can't just throw numbers into a database randomly. We need a strategy.

We are going to use a Two-Table Architecture:

The Run Table (Burst): When we test our site, we don't just run the test once; we run it 3-5 times in a "burst" to ensure accuracy. We save all this raw.
The Calculated Table (Stable): We take the average (or median) of that burst and save a single, clean "snapshot" here.

By comparing the snapshots in our Calculated Table, we can easily generate our DoD, WoW, and MoM reports.

Conclusion

Building a robust metric system doesn't require a complex stack of expensive observability tools. By understanding the "Big Three" trends—DoD, WoW, and MoM—and implementing a smart Burst vs. Calculated data strategy, you can catch performance regressions the moment they happen.

You now have the conceptual blueprint. In Part 2, we will move on actually building it.