October 13, 2026 · 11 min read

From 1.27 GB to 35 MB — how I slimmed down the WattLog.pro database

On the morning of October 12, 2026, starting a ride stopped working in production. The rest of WattLog.pro was fine, but clicking "Start" hung for 30 seconds and ended in an error. The cause was not the code but the disk. The server WattLog.pro runs on had 3.6 GB free out of 19 GB, and the message broker that passes commands to the trainer deliberately stops sending when free space gets that low. I cleaned up and rides started again. What remained was a chart showing it would happen again: that day the nightly database backup grew from 21 to 73 MB.

I build WattLog.pro on my own and I pay for the server, so the question "how much space does one hour of riding take" is, for me, the question of how many riders the same machine can hold. Below is the whole story: what I measured, what I changed and how I deleted data without losing a single ride.

Where the space was going

A smart trainer, a power meter and a heart rate strap send a reading roughly once a second: power, cadence, speed, distance, heart rate. A Strava import looks the same, with every activity a stream of values second by second. From day one WattLog.pro stored each of those readings as a separate row in a PostgreSQL database. It is the natural choice, because every number is easy to find and count later.

Out of dozens of tables, two mattered. I measured them on a copy of the production database:

Those two tables were about 1.5 GB of a database that weighed 1.56 GB. And the indexes weighed more than the data itself. A power value is a few bytes, but every row has a header, an id, a reference to its ride and a timestamp, and every index keeps its own copy of some of those fields. A few bytes become 155.

The simplest way to put it: one hour of riding cost about 1.4 MB, whatever the source (Strava import 1.38 MB, a live ERG ride 1.54 MB, a TCX file 1.00 MB). A 10-hour ride from Strava took 14.2 MB. The most active rider had 770 hours of riding in the last year, so on their own they would take about 1.1 GB a year.

How Strava does it

I looked at how the app actually reads this data. The key was that it never asks for one second of one ride. The ride page, the power chart, zones, FIT/TCX export, the post-ride analysis: all of them load the whole ride at once. Charts that span many weeks, such as TSS and fitness over time, no longer read individual seconds but small summaries computed once after each ride.

If a ride is always read as a whole, there is no reason to keep it as thousands of separate rows. Strava has long served activities as streams: for each activity, a separate array per channel (time, power, heart rate, cadence…) [1]. WattLog.pro already did the same with route geometry: the whole GPS track of a ride is one compressed object in the database. The samples needed the same treatment.

One ride, one stream

The new format stores all samples of a ride as one binary object (a blob):

• Channels instead of rows. Each channel (power from a given device, cadence, heart rate, distance…) is a separate array of numbers. Fields that always have the same value within a ride (for example empty ones) are stored once instead of in every sample.
• Differences instead of values (delta encoding). Instead of timestamps 10:00:00, 10:00:01, 10:00:02 I store the first time and then only the differences: +1 s, +1 s, +1 s. Power of 182, 184, 183 W becomes 182, +2, −1. Whole numbers go in as 4-byte differences, everything else as full floating-point numbers.
• Compression. Finally the whole thing goes through zlib, the standard compression algorithm built into Python. "+1 s, +1 s, +1 s…" repeated 36,000 times compresses to almost nothing, and power differences, mostly small numbers close to zero, compress much better than the watts themselves.

The first test on two real rides gave these results:

Those numbers were too good, though. In that test I rounded time to whole seconds, while live rides record it to the millisecond: 99.9–100% of their samples do not fall on a whole second. Some readings repeat with the same timestamp. The finished format stores all of that exactly, so the real result had to be lower. The lzma algorithm compressed better, but I chose zlib because it is faster, and a few kilobytes stop mattering once the whole thing is over a hundred times smaller anyway.

Lossless, down to the bit

I could have stored power rounded to the watt and time to the second, and got an even better result. I did not, because from those same samples WattLog.pro computes normalized power, zones and power curve records, and exports FIT and TCX files. If after the format change your 5-second record moved by a watt, or an export differed from the original, the change would be worse than the problem it solves.

So the rule is strict: the stream read back must be identical to the rows, down to the type and the bit. A plain "equals" comparison is not enough here, because it treats −0.0 and 0.0 as the same and "not a number" (NaN) as different from itself. So I wrote a separate comparison function that lets no such difference through. It is the one that later decides whether rows may be deleted.

Why not an off-the-shelf tool?

None of this is a new idea. Storing differences instead of values, then compressing, is a standard technique for time series: Facebook described an elaborate version of it in 2015 for its Gorilla database [3], and Strava keeps activities as streams. Before writing my own format, I looked at ready-made tools:

• TimescaleDB. A PostgreSQL extension with built-in columnar compression for time series. On this data it would most likely reach the same order of magnitude. But it would mean replacing the database itself on a server with about 1 GB of free memory, and it would keep the row model, even though the app always reads a whole ride at once.
• Parquet and Apache Arrow. Ready-made columnar formats with good compression. I could store a Parquet file in the database instead of my own format, but the library is over 100 MB of dependencies for a single function.
• A better compression algorithm. zstd or lzma would save a few more percent at the cost of a new dependency or slower runs. With data 120 times smaller, that no longer matters.

My format is a few hundred lines built entirely from the Python standard library: zlib, array and struct. The stream sits in the same database as the rest of the data and goes into the same backup, with no new service to run. Above all, compression turned out to be the simplest part of the whole change. Any of these tools would have needed the same migration: moving 33 modules, checking every ride and safely deleting the old data. No library does that for me.

Four stages, each reversible

A change like this is not a one-evening job, because every chart in the app rests on this data. I split it into stages, each of which could be deployed and rolled back on its own:

1. The format. Code that turns samples into a stream and back, plus a report that ran every ride through it and checked that it came back identical. The result was 120 times less and zero differences.
2. Double write. Every completed ride gets a stream next to its rows. Nothing reads it yet, so a possible bug harms no one. A script filled in the history, comparing every stream with the rows before saving it.
3. One place to read from. Sample data was read by 33 modules of the app. I moved them one by one onto a single function that reads the stream and, when there is none, the rows. The tests run each of those modules twice, once on rows and once on the stream alone, and compare the results.
4. Deleting rows. Only when everything reads the stream do the rows stop being needed.

The third stage paid off right away, because the stream-only tests caught two bugs invisible on rows. The Power by gradient card on the progress page picked rides by whether they had distance rows. Once rows were deleted, it would find none. The second bug was worse: the analysis after saving a ride started by rebuilding the stream from the rows. With no rows left, it would have overwritten the ride with an empty stream. After the fourth stage, recalculating a ride would have silently erased it completely.

How I delete data without losing any

Once the rows are deleted, the stream is the only copy of the ride. So this stage has the most safeguards:

• A day of margin. A ride's rows go no earlier than 24 hours after it ends. During that time late samples can still arrive, and a possible bug in the format can be fixed while the rows still exist.
• A check before every delete. The tool that deletes rows reads the rows and the stream in one transaction per ride and compares them bit by bit. Any difference means that ride stays untouched, and the tool logs an error and moves on to the next one. Only rows that were checked get deleted. A sample that arrives meanwhile waits for the transaction to end and survives.
• A loud error instead of an empty ride. If the stream of a ride whose rows are already gone cannot be read, the app shows an error and computes nothing further. The worst case is a ride that silently has zero watts and overwrites your stats with zeros. That case is blocked outright.
• Late samples. When a sample arrives for an old ride, it is added to the stream, and the whole thing is checked again before saving.
• A backup before the first delete. Before the first run I set aside a full copy of the database in a separate place that backup rotation does not touch, and keep it for several months of the new format running.
• Old formats forever. The repository holds a saved stream in the first version of the format. A test makes sure every later version of the app can read it bit for bit, because a ride without rows has no other copy.

The dress rehearsal

I rehearsed the whole thing on a copy of the production database:

661 rides moved in under two minutes, none skipped, and a second run had nothing left to do. 4 rides that had ended less than a day earlier kept their rows, as they should. One more thing: after deleting rows, PostgreSQL does not give disk space back on its own. The table file stays just as big, and the empty space waits for new rows. The space only comes back with a one-off VACUUM FULL, which rewrites the table from scratch [2]. On the now small tables it took 2.3 seconds.

Reading got faster along the way. The Power by gradient card computed twice as fast from streams as from rows, because decoding one small blob is less work than fetching some fifteen thousand rows.

The first three stages already run in production: completed rides get a stream and the app reads them from it. Deleting rows in production is what I am rolling out now, slowly: a dry run first, then 10 rides, a check of the disk and of those rides' pages, then further batches of 100.

What it means for you

• Importing your whole Strava history is no longer a risk. A year of a serious rider's riding is now a few megabytes instead of over a gigabyte, so an import does not threaten the server's disk, and with it the ride of someone else who is starting a workout right then.
• Your data does not change. Every second of your ride is stored exactly as precisely as before: power, heart rate, cadence, time to the millisecond. Ride pages, zones and FIT/TCX export give the same results.
• One server can hold many more riders. What limited how many riders WattLog.pro can fit was the disk, not the CPU. A smaller database also means smaller and faster backups, taken before every deploy.

What it does not solve

What I take from it

The hanging ride start looked like a bug after a deploy, and it was a disk space problem. Only measuring showed where that space was going: not into the watts themselves, but into how they were stored. The format I picked at the start was good for building the app quickly and bad for running it. The change needed no new database and no new server, only storing the data the way the app reads it: one whole ride at a time.

Sources

1. Strava API v3 — Streams. developers.strava.com
2. PostgreSQL Documentation — VACUUM. postgresql.org
3. Pelkonen T, Franklin S, Teller J, Cavallaro P, Huang Q, Meza J, Veeraraghavan K. Gorilla: A Fast, Scalable, In-Memory Time Series Database. Proc VLDB Endow. 2015;8(12):1816–1827. vldb.org

← All blog posts