I promise users unlimited practice sessions. Every session is a stream of trials, and every trial has timing, correctness, and metadata. Do the math, and a heavy user generates thousands of rows a week. Store all of it naively and both your storage bill and your history queries fall over. Here's how I kept "unlimited" honest.
Separate the thing you write a lot from the thing you read a lot. Trials are write-heavy and rarely read individually. Session summaries are read constantly (the history page, the percentile). So I split them: an append-only trial log, and a materialised summary row per session computed when the session ends.
sessions: id, user_id, game_type, started_at, score, percentile, accuracy, mean_rt
trials: id, session_id, index, stimulus, response, correct, rt_ms
The history page never touches the trial table. It reads sessions filtered by user_id, ordered by started_at, with an index on (user_id, started_at desc). That query stays fast no matter how many trials exist underneath, because it doesn't join to them.
Cold trial data doesn't need to live in hot storage. Users rarely drill into an individual trial from six months ago. So raw trials past a certain age get rolled up: I keep the session summary forever, compress the per-trial detail into a single JSON blob on the session (or push it to object storage), and drop the individual rows. History stays complete; the hot table stays small.
Compute summaries once, not on every read. The percentile, mean reaction time, and accuracy are calculated when the session closes and written to the summary row. Recomputing them on every page load would mean rescanning trials constantly — the exact thing the split was meant to avoid.
The principle generalises to anything with high-volume detail and low-volume summaries: write detail cheaply, read summaries fast, and age the detail out on a schedule. "Unlimited" is a storage strategy, not a promise to keep every byte hot forever.
This is the data model behind CogniPrep, a practice platform for psychometric assessments: https://cogniprep.app
Top comments (0)