A typing profile needs lifetime totals, personal records, a recent chart and a history table. My profile page already did the right big thing: it asked the database for totals instead of downloading every test. But a closer look at what it actually read turned up two quieter problems, and fixing them is what this post is about.
The first was in the data. Every saved test stores its per-second speed chart, a JSON array that is easily the largest field on the row. The profile query selected it for up to a hundred tests, and the public profile then stripped it back out before sending the page. None of the profile's views draws a per-test chart. The fix was to stop selecting it at all, which also deleted the helper code that removed it afterwards.
The second was in the indexes. Several existed that no query used, while some of the queries that did run had no index shaped like them. I replaced them with composite indexes that match each query's filter and sort order exactly.
Five queries, each with its index
Pick a step, or use the arrows, to walk through the diagram.
Let the database calculate lifetime values
Typestrix asks Postgres for counts, sums, averages, and maxima. The aggregate returns total tests and time, average speed and accuracy, character totals, and personal records.
That avoids transferring every test to JavaScript to calculate a few numbers. It does not make the underlying aggregate free: the database still has to process the matching user's records. The improvement is bounded response size and pushing the work to the system that stores those records.
A separate query fetches the latest hundred tests. The performance chart uses the newest fifty of those and reverses them into chronological order. The history table gets the latest hundred in descending order.
The object is called allTests internally, but it is a bounded history window, not an export of every saved run. Describing that limit accurately matters to users with more than a hundred tests.
Select what the screen consumes
The profile query selects headline metrics, character counters, mode settings, IDs, and timestamps. It leaves out the potentially large per-second progression JSON stored with each test.
That progression is useful for an individual result chart. It is unnecessary for a profile chart whose points represent whole tests. Avoiding it saves response bytes without sacrificing the profile's visible information.
Best time-mode and word-mode runs use separate queries ordered by WPM descending and creation time descending. The timestamp breaks ties by preferring the newer run. Those best-score cards are based on WPM, while the global leaderboard uses an accuracy-weighted score; they are different rankings.
Parallel queries, separate questions
The aggregate, latest history, two mode-best queries, and earliest five WPM values run concurrently. The private profile also fetches its user record alongside those metrics.
Concurrency avoids unnecessary sequential waits. It does not imply that all queries execute in one transaction or share one database snapshot. A newly saved test during a profile request can appear in one query before another.
React's request-level caching wraps the profile loaders. That can reuse work within a render/request lifecycle, including public-profile metadata and page rendering. It is not a persistent cross-request cache of personal statistics.
Indexes mirror filtering and ordering
The schema includes an index beginning with user ID and ending with descending creation time for recent history. Another includes user, mode, mode option, and descending creation time, matching the personal-baseline query used during score validation.
For best-score lookups, an index includes user ID, mode, descending WPM, and descending creation time. Public room discovery has its own privacy-and-date index.
These are query-shaped indexes: their columns reflect actual filters and sort order. The tracked migration removes some broader old indexes and creates the more specific combinations. The trade-off is that every index costs disk space and a little extra work on each insert, so unused ones were worth removing, not just adding new ones.
Improvement is a comparison, not a prediction
The growth calculation compares the average of the newest five WPM values with the average of the earliest five, expressed as a percentage. It requires at least ten total tests and returns zero if the old average is zero.
That metric is easy to explain, but it mixes test modes and lengths across the player's history. The label “fastest improvement” does not mean the application searched every possible period or fitted a learning curve. It reports one specific early-versus-recent comparison.
Public profiles use an explicit projection
A shared profile normalizes and validates the player tag, then selects identity and avatar fields without email. It reuses the same metric loader, and its serialized history converts dates into ISO strings.
The presentation layer receives a public/private union and only supplies an email when that field exists. Privacy starts at the data selection, before hiding anything in the UI. Public test history is still public by design; excluding email is not a general anonymity guarantee.
The result is a profile whose network payload stays bounded as history grows, with lifetime calculations, recent views, and privacy choices expressed separately.