Back to selected work

Case study

Rolantir, 50x faster chart queries on a live 24.5 GB database

Rolantir is analytics for the Roblox platform. It records player counts over time so a studio can see which games rise and fade.

1M+ games tracked
in a 24.5 GB live database
300M rows of history
player counts over time
9.5x smaller history
35 GB stored in 3.7 GB
50x faster chart queries
400 to 900 ms down to 3 to 8 ms

The problem

Rolantir now tracks over a million games in a 24.5 GB database. I started the backend on SQLite to get it running quickly, and moved it to PostgreSQL with TimescaleDB once the data grew.

Two things forced the move. The SQLite driver makes the whole server wait while a query runs. In production a 2 s count and an 8 s genre query each froze the service. Uncompressed, the history would also have outgrown its 60 GB disk. By the cutover the SQLite file held 13.9 GB, and the move had to happen live, without losing any history.

What I did

  • My partI started Rolantir by building the backend, the crawler and the game tracker, and wrote almost all of the backend. I planned and ran the move of the live database to PostgreSQL with TimescaleDB. The website came later, and I built about half of it.
  • Keeping the historyThe new code sat behind the existing database interface as a faithful copy, so the two could be checked against each other. The cutover pre-loaded the data and then copied only what had changed, with completeness checks on the copy. That kept about 100M rows out of the maintenance window.
  • Two problems on the wayThe first version of the chart summaries stored about 1 KB per row and filled the disk while it back-filled. I redesigned it around plain running totals at about 160 B a row, still exact for averages, spread and change over any window. A recurring deadlock, about 6 every 2 hours, came from two writers taking locks in a different order. A fixed lock order, with a retry as a safety net, ended it.
SQLite13.9 GBPostgreSQL+ TimescaleDBpre-loadcopy what changedold database codenew database codechecked against each other, behind one interface
How the database move kept the history

The result

before
400 to 900 ms
after
3 to 8 ms
Hourly chart query time, measured on production, June 2026

I rewrote the derived-metric chart queries to fetch once and scan in memory. It gives bit-exact results against the old path. Hourly chart queries dropped from 400 to 900 ms to 3 to 8 ms, as measured on production. Compression keeps 300M rows of history in 3.7 GB instead of 35 GB.

The code is private. The product is live at rolantir.dev (beta) (opens in a new tab).

Ask about a first project