Faster Packs

We recently overhauled how the website loads data when you’re browsing or searching for packs. If you’ve noticed the site feels a bit more responsive lately, you aren’t imagining it. The difference is night and day. Let me show you how we pulled it off.

The performance bottleneck

The search results and browse pages use a view we call the PackBrowser. It handles the filters, sorting options, and the list of packs you see on the screen.

Previously, these pages were “expensive” to load. Every time you refreshed the page or changed a filter, the database had to do a huge amount of math on the fly. To show you a single list of packs, the system had to:

Scan thousands of rows to calculate the number of words in each pack.
Cross-reference multiple tables to sort the list by things like “Most Played” or “Most Bookmarked.”

Because the database was calculating these totals from scratch every single time someone looked at a page, the overhead started to add up, leading to slower load times as our library of content grew.

To build a single page of results, the database had to scramble across multiple tables to find the pieces it needed:

[ SEARCH REQUEST ]
       |
       v
+--------------------------+
|       Packs table        |
|--------------------------|
| [id] [name] [image]      |    (1) Fetch & count
| [word_count: ???]        | -----------------------> [ Words table ]
| [game_count: ???]        | -----------------------> [ Games table ]
| [bookmark_count: ???]    | -----------------------> [ Bookmarks table ]
+--------------------------+           |
       |                               |
       v                               |  (2) Sort by count
[ AGGREGATE & SORT ] <-----------------+
       |
       v
[ DISPLAY RESULTS ]

Moving toward a faster configuration

To fix this, we gave the packs table its own “memory.” We added dedicated columns for word_count, game_count, and bookmark_count directly to the packs themselves, so the database no longer has to do the math on every page load.

Now, when you search for “Most Played” packs, the database doesn’t have to count millions of rows; it just looks at the game_count we’ve already prepared. With the addition of “indexes”—which act like a pre-sorted catalog—the database can find and sort the top results almost instantly.

Instead of a multi-step search mission, the query now has a single, direct path to the data:

[ SEARCH REQUEST ]
       |
       v
+--------------------------+
|       Packs table        |
|--------------------------|
| [id] [name] [image]      |
| [word_count: 50]         | <---\
| [game_count: 1200]       | <---+--- Data is already here
| [bookmark_count: 85]     | <---/
+--------------------------+
       |
       v
[ INDEX SORT ⚡ ] -> [ DISPLAY RESULTS ]

The results

The impact was immediate. Execution time for our search and browse queries dropped from an average of ~400ms to just ~18ms—a 95% reduction in latency.

Statistics are one thing, but seeing the before and after on a timeline really puts the optimization into perspective.

Database CPU usage

This chart tracks how hard the database has to work over time. You can see exactly where the new configuration took over and relieved the strain.

Page response time

This chart tracks the overall response time across the entire site. Because our search and browse pages were so time-intensive, they were dragging up the global average. You can see the exact moment the optimization went live:

p99 (the slowest 1% of requests) dropped from over 3 seconds to just barely 2 seconds.
p95 (the slowest 5%) saw a massive 60% reduction, falling from 2 seconds to under 1 second.
p90 (the slowest 10%) stabilized significantly, replacing those frustrating “hanging” moments with consistent, snappy responses.
p50 (the median) response times flattened out almost entirely. 🥞

We’re thrilled with how much snappier the site feels, but we aren’t stopping here. We’ll continue to hunt down slow spots to ensure your vocabulary gaming experience is as smooth as possible.