My Diagnosis Was Wrong: What Profiling a 60-Million-Row Leaderboard Actually Taught Me

An admin dashboard endpoint felt broken. It was a simple-looking ranking query — “show me the members who created the most notes, bookmarks, and highlights” — paginated at 20 rows per page, over a table that had grown past 60 million rows.

I was confident I knew the culprit. A GROUP BY with ORDER BY COUNT(*) over 60M rows, no index possible on an aggregate. I was already drafting the story in my head: how I made a catastrophically slow query fast.

Then I profiled it.

SET profiling = 1;
SELECT member_seq, COUNT(*) AS cnt FROM bible_sync_note
WHERE NOT (delete_yn <=> 'Y') GROUP BY member_seq ORDER BY cnt DESC LIMIT 0, 21;
SHOW PROFILES;

Enter fullscreen mode Exit fullscreen mode

Query_ID  Duration   Query
1         0.567101   SELECT member_seq, COUNT(*) AS cnt FROM bible_sync_note ...

Enter fullscreen mode Exit fullscreen mode

567 milliseconds. Not the multi-second disaster I’d assumed.

My mental model was wrong, and the fix I’d been about to build was aimed at the wrong target. This post is about what the numbers actually said, and the architecture that followed once I stopped guessing.

Reading the Real Problem

567ms isn’t catastrophic in isolation. But look at what produces it, and the shape of the problem changes.

// dashboard.service.ts — Original Implementation
async getMemoRanking(dto: MemoRankingReqDto): Promise<MemoRankingResDto> {
  const { tab, page, size } = dto;
  const offset = (page - 1) * size;

  // Table name comes from a whitelist map — never string-interpolated user input
  const tableName = TABLE_MAP[tab];

  const sql = `
    SELECT member_seq, COUNT(*) AS cnt
    FROM ${tableName}
    WHERE NOT (delete_yn <=> 'Y')
    GROUP BY member_seq
    ORDER BY cnt DESC
    LIMIT ?, ?
  `;

  const rows = await this.mobileDs.query(sql, [
    offset,
    size + 1, // +1 to detect hasMore
  ]);

  const hasMore = rows.length > size;
  const items = rows.slice(0, size).map((r, i) => ({
    rank: offset + i + 1,
    memberSeq: Number(r.member_seq),
    count: Number(r.cnt ?? 0),
  }));

  return { items, hasMore, currentPage: page, tab };
}

Enter fullscreen mode Exit fullscreen mode

There’s a LIMIT. It asks for 21 rows. So where does half a second go?

LIMIT Applies After the Sort

Read the logical execution order rather than the text order:

1. WHERE filters ~60M rows
2. GROUP BY member_seq aggregates them into 59,601 distinct groups (I measured this too)
3. ORDER BY cnt DESC sorts all 59,601 groups — and cnt doesn’t exist until step 2 finishes
4. LIMIT 0, 21 finally slices off 21 rows Step 3 is the trap. To know who ranks #1, the database must compute every member’s count and sort the entire result. The LIMIT discards 99.96% of that work at the very last moment.

Which means:

The cost is identical whether you request page 1 or page 500. Every request pays for a full scan and a full sort of the entire member base.

That reframes the problem. 567ms isn’t the issue — paying 567ms repeatedly, forever, to produce an answer that barely changes is the issue. Three tabs, every admin, every page click, every refresh. All hitting the same Aurora cluster that serves our ~900K mobile users.

The fix was never going to be making the query faster. It was not running it on every request.

Ruling Out the Cheap Fixes First

Before reaching for a cache, I worked through the standard checklist. Being able to explain why each one fails is what makes caching a decision rather than a reflex:

Fix Why it fails here Index the sort column cnt is an aggregate result, not a column. There is nothing to index. A covering index on member_seq speeds up grouping, but the filesort over 59,601 aggregated groups remains. Keyset / cursor pagination Cursor pagination replaces OFFSET with a WHERE predicate on an indexed, ordered column. cnt is neither. There’s no cursor to seek on. Archive old rows The rows are the data — this is a lifetime ranking. Deleting them changes the answer.

The Reframe: Cache the Ranking, Not the Page

Once the aggregation was established as irreducible, one subtlety determined whether caching would work at all:

The expensive operation produces a whole sorted ranking, not a page.

That rules out the most tempting design. If you cache per page — ranking:note:page:1, ranking:note:page:2 — then every cold page triggers another full aggregation. You’d cache the cheap part (slicing) and re-pay the expensive part (scanning and sorting) on every miss. Hit rate would be poor and tail latency would stay bad.

Cache the entire ranking once per TTL, and serve pagination out of the cache.

Choosing the Data Structure

Approach Cost per cache hit Notes JSON blob (SET a serialized array) O(N) — transfer + JSON.parse on every request 59,601 entries is a multi-MB payload parsed per request; burns much of the win and pressures GC Redis Sorted Set (ZSET) O(log N + M) — Redis slices server-side, sends only the page Purpose-built for leaderboards; cost independent of total cardinality

The ZRANGE documentation specifies O(log(N)+M) where M is the number of elements returned. With M = 21, retrieval cost is effectively constant whether the ranking holds 59,601 members or 500,000.

A sorted set is exactly the shape of this problem: members scored by a number, queried by rank range. Choosing it wasn’t a clever trick — it was noticing that the domain model and the data structure were already the same thing.

A memory check before committing. Our Redis runs with maxmemory 256mb and allkeys-lru, so keys can be evicted before their TTL under pressure. At roughly 60–100 bytes per ZSET element, 59,601 members is about 4–6MB per tab, ~15MB for all three. Comfortable headroom — no eviction risk, and no need for the Top-K fallback design I’d sketched as a contingency. Worth measuring rather than assuming; at 10× the cardinality this decision would have gone differently.

The Implementation

1. Reading a Page from the Sorted Set

/** Slice one page out of the cached ranking — output shape identical to the SQL path */
private async readPageFromZset(
  zkey: string, tab: MemoTab, page: number, size: number, offset: number,
): Promise<MemoRankingResDto> {
  // Inclusive range [offset, offset + size] returns size + 1 elements,
  // mirroring the original `LIMIT offset, size + 1` hasMore trick
  const rows = await this.redis.zrangeRevWithScores(zkey, offset, offset + size);

  const hasMore = rows.length > size;
  const items = rows.slice(0, size).map((r, i) => ({
    rank: offset + i + 1,          // positional rank, same as before
    memberSeq: Number(r.member),
    count: r.score,
  }));

  return { items, hasMore, currentPage: page, tab };
}

Enter fullscreen mode Exit fullscreen mode

The helper uses ZRANGE ... REV, since ZREVRANGE has been deprecated since Redis 6.2 — with a version fallback I’ll explain in the debugging section below:

// redis.service.ts
/** REV option requires Redis 6.2+; detect once and fall back on older servers */
private supportsZrangeRev: boolean | null = null;

async zrangeRevWithScores(
  key: string, start: number, stop: number,
): Promise<Array<{ member: string; score: number }>> {
  let flat: string[];

  if (this.supportsZrangeRev === false) {
    flat = await this.client.zrevrange(key, start, stop, 'WITHSCORES');
  } else {
    try {
      flat = await (this.client.zrange as any)(key, start, stop, 'REV', 'WITHSCORES');
      this.supportsZrangeRev = true;
    } catch (e: any) {
      if (typeof e?.message === 'string' && e.message.includes('syntax error')) {
        this.supportsZrangeRev = false;
        flat = await this.client.zrevrange(key, start, stop, 'WITHSCORES');
      } else {
        throw e;
      }
    }
  }

  const out: Array<{ member: string; score: number }> = [];
  for (let i = 0; i < flat.length; i += 2) {
    out.push({ member: flat[i], score: Number(flat[i + 1]) });
  }
  return out;
}

Enter fullscreen mode Exit fullscreen mode

One detail worth verifying before trusting scores: ZSET scores are IEEE-754 doubles. Counts here peak around 9,274 and could theoretically reach 60M — both comfortably below 2^53, so integer values round-trip exactly. No precision loss.

2. Building the Cache: Temp Key + Atomic Rename

This is the part I’d single out if someone asked what separates a working cache from a correct one.

/** Run the expensive aggregation once, load it into a temp key, swap it in atomically */
private async rebuildMemoRanking(tab: MemoTab, zkey: string): Promise<void> {
  const started = Date.now();
  const table = MEMO_RANKING_TABLE[tab];

  // Note: no ORDER BY. The sorted set owns ordering, so sorting in SQL is wasted work.
  const rows = await this.mobileDs.query(`
    SELECT member_seq, COUNT(*) AS cnt
    FROM ${table}
    WHERE NOT (delete_yn <=> 'Y')
    GROUP BY member_seq
  `);

  if (rows.length === 0) return; // don't cache an empty ranking

  const pairs: Array<[number, string]> =
    rows.map((r) => [Number(r.cnt ?? 0), String(r.member_seq)]);

  const tmp = `${zkey}:building:${Date.now()}:${randomUUID().slice(0, 8)}`;

  try {
    await this.redis.del(tmp);
    await this.redis.zaddBulk(tmp, pairs);                      // pipelined in chunks
    await this.redis.expireKey(tmp, MEMO_RANKING_TTL_SEC);
    await this.redis.rename(tmp, zkey);                          // atomic swap
  } catch (e) {
    try { await this.redis.del(tmp); } catch { /* noop */ }
    throw e;
  }

  this.logger.log(
    `[memo-ranking] rebuilt tab=${tab} members=${pairs.length} in ${Date.now() - started}ms`,
  );
}

Enter fullscreen mode Exit fullscreen mode

In production this logs:

[memo-ranking] rebuilt tab=note members=59601 in 734ms

Enter fullscreen mode Exit fullscreen mode

734ms to aggregate 60M rows, transfer 59,601 counts, and load them into Redis. Dropping the SQL ORDER BY matters here — the sorted set imposes ordering on insert, so sorting in the database first would be duplicated work.

Why not just ZADD into the live key? Because loading 59,601 members takes multiple pipelined round trips. During that window the key exists but is half full — and concurrent readers would happily serve a partial, wrong ranking. You can’t detect it from outside; EXISTS returns true either way.

Building into a throwaway key and swapping with RENAME eliminates the window entirely. The live key holds either the previous complete ranking or the new complete one, never an intermediate state. Redis RENAME is atomic, and per the EXPIRE documentation the source key’s TTL transfers to the destination — so setting TTL on the temp key before the swap is sufficient.

Bulk loading goes through a pipeline to avoid one round trip per member:

/** Bulk ZADD via chunked pipeline — minimizes network round trips */
async zaddBulk(
  key: string,
  pairs: Array<[number, string]>,
  chunkSize = 2000,
): Promise<void> {
  if (pairs.length === 0) return;

  const pipeline = this.client.pipeline();
  for (let i = 0; i < pairs.length; i += chunkSize) {
    const args: (string | number)[] = [];
    for (const [score, member] of pairs.slice(i, i + chunkSize)) {
      args.push(score, member); // ZADD key s1 m1 s2 m2 ...
    }
    (pipeline.zadd as any)(key, ...args);
  }

  const results = await pipeline.exec();
  // A pipeline doesn't throw on individual failures — it returns [err, reply][].
  // Promote any error so the caller falls back to the database.
  for (const [err] of results ?? []) if (err) throw err;
}

Enter fullscreen mode Exit fullscreen mode

3. Preventing a Cache Stampede

The moment the TTL expires, every concurrent request misses simultaneously. Without coordination, all of them launch the same aggregation at once — a thundering herd that can hurt more than the slow query ever did.

We already had a distributed lock in our Redis service, so the fix was to use it:

async getMemoRanking(dto: MemoRankingReqDto): Promise<MemoRankingResDto> {
  const { tab, page, size } = dto;
  const offset = (page - 1) * size;
  const zkey = this.memoRankingKey(tab);

  // ── 1) Cache hit ───────────────────────────────────────
  try {
    if (await this.redis.exists(zkey)) {
      return await this.readPageFromZset(zkey, tab, page, size, offset);
    }
  } catch (e) {
    // Redis unavailable → degrade to the database, quietly
    this.logger.warn(`[memo-ranking] cache read failed → DB fallback: ${e.message}`);
    return this.queryMemoRankingFromDb(tab, page, size, offset);
  }

  // ── 2) Miss → acquire lock, rebuild once ───────────────
  const lockKey = this.memoRankingLockKey(tab);
  const lockVal = randomUUID();
  let locked = false;
  try { locked = await this.redis.acquireLock(lockKey, lockVal, 120); }
  catch { locked = false; }

  if (locked) {
    try {
      // Double-check: another worker may have populated it while we waited
      if (!(await this.redis.exists(zkey))) {
        await this.rebuildMemoRanking(tab, zkey);
      }
      if (await this.redis.exists(zkey)) {
        return await this.readPageFromZset(zkey, tab, page, size, offset);
      }
      return { items: [], hasMore: false, currentPage: page, tab };
    } catch (e) {
      this.logger.error(`[memo-ranking] rebuild failed → DB fallback: ${e.message}`, e.stack);
      return this.queryMemoRankingFromDb(tab, page, size, offset);
    } finally {
      try { await this.redis.releaseLock(lockKey, lockVal); } catch { /* noop */ }
    }
  }

  // ── 3) Lock held elsewhere → brief retry, then fall back ──
  for (let attempt = 0; attempt < 5; attempt++) {
    await this.sleep(200);
    try {
      if (await this.redis.exists(zkey)) {
        return await this.readPageFromZset(zkey, tab, page, size, offset);
      }
    } catch { break; }
  }
  return this.queryMemoRankingFromDb(tab, page, size, offset);
}

Enter fullscreen mode Exit fullscreen mode

The lock is released with a Lua compare-and-delete, so a worker can never delete a lock that already expired and was re-acquired by someone else:

async releaseLock(key: string, value: string): Promise<boolean> {
  const script = `
    if redis.call("get", KEYS[1]) == ARGV[1] then
      return redis.call("del", KEYS[1])
    else
      return 0
    end
  `;
  const result = await this.client.eval(script, 1, key, value);
  return result === 1;
}

Enter fullscreen mode Exit fullscreen mode

Note the interaction between lock TTL and the temp-key design: if a rebuild outruns its lock TTL, a second rebuild may start. Because both write to separate temp keys and swap atomically, the outcome is still a complete, correct ranking — the only cost is duplicated work. Correctness doesn’t depend on the lock holding. That’s a property worth designing for; locks expire at the worst possible times.

4. Never Break the Contract: Database Fallback

This endpoint feeds a Next.js frontend. The response shape could not change — not a field, not a type.

So every failure path funnels back to the original query:

/** Direct DB path — the original query, unchanged (Redis down / rebuild failed / lock contention) */
private async queryMemoRankingFromDb(
  tab: MemoTab, page: number, size: number, offset: number,
): Promise<MemoRankingResDto> {
  const table = MEMO_RANKING_TABLE[tab];
  const rows = await this.mobileDs.query(`
    SELECT member_seq, COUNT(*) AS cnt
    FROM ${table}
    WHERE NOT (delete_yn <=> 'Y')
    GROUP BY member_seq
    ORDER BY cnt DESC
    LIMIT ?, ?
  `, [Number(offset), Number(size + 1)]);

  const hasMore = rows.length > size;
  const items = rows.slice(0, size).map((r, i) => ({
    rank: offset + i + 1, memberSeq: Number(r.member_seq), count: Number(r.cnt ?? 0),
  }));
  return { items, hasMore, currentPage: page, tab };
}

Enter fullscreen mode Exit fullscreen mode

Cache hit, cache miss, Redis outage, rebuild failure — all four return identical payloads. Introducing a cache must not introduce a new availability dependency. This matches how our Redis service was already written: it logs and continues if the connection fails at boot rather than crashing the app.

5. Conditional Cache Warming

Even with everything above, the first request after each expiry still pays the full rebuild.

The naive fix is a cron job on a fixed schedule. But an unconditional rebuild every 5 minutes means 864 full-table aggregations per day across three tabs — running at 3 AM when nobody is looking, against the database serving our mobile users. That risks putting more sustained load on Aurora than the original slow endpoint ever did.

So the warmer checks remaining TTL first and only rebuilds what’s about to expire:

@Cron(CronExpression.EVERY_5_MINUTES)
async warmMemoRanking(): Promise<void> {
  const REFRESH_BELOW_SEC = 120; // only rebuild when the cache is nearly stale

  for (const tab of Object.keys(MEMO_RANKING_TABLE) as MemoTab[]) {
    const zkey = this.memoRankingKey(tab);

    try {
      const ttl = await this.redis.ttl(zkey); // -2 missing, -1 no expiry, >=0 seconds left
      if (ttl > REFRESH_BELOW_SEC) continue;  // still fresh → skip the expensive aggregation
    } catch {
      continue; // Redis unhealthy → skip warming; reads still have their fallback
    }

    const lockKey = `${this.memoRankingLockKey(tab)}:warm`;
    const lockVal = randomUUID();

    // We deploy blue-green, so two containers run this cron.
    // The lock ensures only one of them actually rebuilds.
    if (await this.redis.acquireLock(lockKey, lockVal, 120).catch(() => false)) {
      try { await this.rebuildMemoRanking(tab, zkey); }
      catch (e) { this.logger.error(`[warm] ${tab} failed: ${e.message}`); }
      finally { await this.redis.releaseLock(lockKey, lockVal).catch(() => {}); }
    }
  }
}

Enter fullscreen mode Exit fullscreen mode

With a 30-minute TTL, this settles into roughly 2 rebuilds per tab per hour instead of 12 — while the cache is effectively never cold, because it refreshes just before it would have expired.

A Bug That Only Logs Would Catch

After deploying, the logs showed something I’d have missed entirely from the outside:

LOG   [memo-ranking] rebuilt tab=note members=59601 in 734ms
ERROR [memo-ranking] rebuild failed → DB fallback: ERR syntax error
ReplyError: ERR syntax error

Enter fullscreen mode Exit fullscreen mode

The rebuild succeeded — the “rebuilt” line only prints after the RENAME completes. Then the very next operation failed. The cache was being populated correctly and then never read, so every request silently fell through to the database.

The cause: ZRANGE key start stop REV WITHSCORES — the REV argument was introduced in Redis 6.2. Production runs redis:7-alpine and was fine, but my local Redis was older and rejected it outright.

Two things this reinforced:

1. Log both sides of a swap. A single “rebuild complete” log would have looked like success. Logging the failure path separately is what localized this to the read, not the write.
2. A silent fallback is a double-edged sword. The fallback did its job — no user saw an error. But it also meant a completely non-functional cache produced correct responses, indefinitely. Fallbacks need loud logs, and ideally a hit-rate metric, or they hide the very failures they absorb. The fix was the version-detecting fallback shown earlier: attempt ZRANGE ... REV, and on a syntax error latch a flag and use ZREVRANGE for the rest of the process’s life.

Testing: Locking Down the Contract

The highest-risk part of this change wasn’t performance — it was silently altering the response and breaking the frontend. So the unit tests target shape parity across every code path:

it('cache hit: serves from ZSET without touching the database', async () => {
  redis.exists.mockResolvedValue(true);
  redis.zrangeRevWithScores.mockResolvedValue([
    { member: '10', score: 50 },
    { member: '20', score: 40 },
  ]);

  const r = await service.getMemoRanking(req({ page: 1, size: 20 }));

  expect(mobileDs.query).not.toHaveBeenCalled();
  expect(redis.zrangeRevWithScores).toHaveBeenCalledWith(expect.any(String), 0, 20);
  expect(r).toEqual({
    items: [
      { rank: 1, memberSeq: 10, count: 50 },
      { rank: 2, memberSeq: 20, count: 40 },
    ],
    hasMore: false,
    currentPage: 1,
    tab: MemoTab.NOTE,
  });
});

it('redis failure: falls back to the DB with an identical shape', async () => {
  redis.exists.mockRejectedValue(new Error('ECONNREFUSED'));
  mobileDs.query.mockResolvedValue([{ member_seq: 1, cnt: 3 }]);

  const r = await service.getMemoRanking(req({ page: 1, size: 20 }));

  const [sql, params] = mobileDs.query.mock.calls[0];
  expect(sql).toMatch(/ORDER BY cnt DESC/);
  expect(params).toEqual([0, 21]);
  expect(r).toEqual({
    items: [{ rank: 1, memberSeq: 1, count: 3 }],
    hasMore: false, currentPage: 1, tab: MemoTab.NOTE,
  });
});

Enter fullscreen mode Exit fullscreen mode

The suite covers hit, miss-and-rebuild, Redis failure, the hasMore boundary at exactly size + 1, rank offsets on deep pages, and the empty-ranking case. The rebuild test also asserts the aggregation query contains no ORDER BY — encoding the design decision that ordering belongs to the sorted set, so a future refactor can’t quietly reintroduce a wasted filesort.

Results

All figures measured on production infrastructure (Aurora MySQL, redis:7-alpine, bible_sync_note at ~60M rows / 59,601 distinct members):

Metric Before After Aggregation query 567ms, on every request 734ms, ~2× per hour, off the request path Rows scanned per request ~60M 0 — Redis serves a rank range Groups sorted per request 59,601 0 — ordering is maintained on insert Full aggregations per hour 1 per request (unbounded) 2 per tab (bounded, predictable) Cost of page 500 vs page 1 Identical full scan + sort Identical O(log N + M) slice End-to-end API response — 50ms (code=200 time=0.050396) Behavior when Redis is down N/A Original query, unchanged response API response shape — Identical; frontend untouched

Two honest caveats on these numbers:

• The 50ms end-to-end figure includes HTTP overhead, JWT verification, and an admin lookup query — not just the Redis read. It isn’t a like-for-like comparison against the bare 567ms SQL timing, and I’m not claiming “11× faster.”
• 567ms was measured with a warm buffer pool. A cold cache would be worse, which only strengthens the argument for keeping this off the request path. The number I actually care about is the fourth row. Aggregation load went from unbounded and proportional to traffic to fixed at 2 per tab per hour. That’s the property that scales.

Lessons Learned

1. Profile Before You Optimize — Including Your Own Diagnosis

I was ready to write a post about fixing a catastrophically slow query. Profiling said 567ms. My assumption about why the endpoint felt broken was wrong, and if I’d skipped the measurement I would have optimized confidently in the wrong direction. The measurement didn’t just size the problem — it redefined it, from “this query is slow” to “this query runs far more often than it needs to.”

2. Read the Execution Order, Not the Text Order

LIMIT 0, 21 looks like it bounds the work. It doesn’t — it’s the last step, applied after a full scan and a full sort. Reasoning in logical execution order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) is what turned “why is this slow?” into an obvious answer.

3. Cache the Expensive Artifact, Not the Requested Slice

The instinctive move is to cache what the endpoint returns — a page. But the expensive computation produced a whole ranking. Caching at the wrong granularity would have looked like a cache while re-paying the real cost on every cold page. Identify what’s actually expensive, and cache exactly that.

4. Design So Correctness Doesn’t Depend on the Lock

Locks expire. Networks partition. Because rebuilds write to isolated temp keys and swap atomically, a lock expiring mid-rebuild costs duplicated CPU — never a corrupted ranking. The lock became an efficiency optimization rather than a correctness guarantee.

5. Silent Fallbacks Need Loud Logs

The DB fallback kept the endpoint correct while the cache was completely broken by a Redis version incompatibility. That’s exactly what a fallback is for — and exactly why it can hide a total failure indefinitely. Log the fallback path, and track hit rate if you can.

Common Pitfalls to Avoid

Pitfall 1: Caching Pages Instead of the Full Ranking

❌ Wrong: cache:ranking:page:1, cache:ranking:page:2, …
✅ Right: one sorted set holding the whole ranking, sliced per request

Per-page keys re-trigger the full aggregation for every cold page.

Pitfall 2: Populating the Live Key In-Place

Concurrent readers will serve a half-built ranking, and you can’t detect it from outside. Build into a temp key and RENAME.

Pitfall 3: No Stampede Protection

TTL expiry synchronizes your misses. Without a lock, every concurrent request launches the same expensive query simultaneously.

Pitfall 4: Unconditional Cache Warming

A cron that rebuilds regardless of freshness can put more sustained load on the database than the slow queries it replaced. Check remaining TTL and refresh only what’s about to expire.

Pitfall 5: Assuming Redis Command Availability

ZRANGE ... REV needs 6.2+. GETDEL needs 6.2. Hash field TTLs need 7.4. Verify with INFO server across every environment — the one that bites you is the one you don’t run in production.

Conclusion

I set out to fix a slow query and found it wasn’t especially slow. What it was, was repetitive: a full scan of 60 million rows and a full sort of 59,601 groups, re-executed on every request, to return 20 rows that barely change.

Once the problem was framed correctly, the design followed:

1. Compute the ranking once per TTL, not once per request
2. Store it in the structure that matches the problem — a sorted set
3. Serve pagination from Redis at O(log N + M)
4. Guard the rebuild with a distributed lock and an atomic temp-key swap
5. Keep a database fallback so the cache never becomes a dependency
6. Warm conditionally, so users never see a cold miss and the database isn’t hammered Aggregation load went from unbounded to two runs per tab per hour, the endpoint responds in 50ms end-to-end, and the frontend contract never changed by a single field.

The broader lesson: the fastest query is the one you don’t run. Before optimizing a computation, it’s worth asking whether it needs to happen at all — and before answering that, it’s worth measuring whether you understand the problem in the first place.

Key Takeaways

• Profile before optimizing, and be willing to discover your diagnosis was wrong — the measurement may redefine the problem, not just size it
• LIMIT doesn’t bound work when ORDER BY targets an aggregate; the full result set is materialized and sorted first
• Aggregate columns can’t be indexed and can’t serve as a keyset cursor — rule these out explicitly before caching
• Cache the expensive artifact (the full ranking), not the requested slice (a page)
• Redis sorted sets serve rank-range pagination in O(log N + M), independent of total cardinality
• Build into a temp key and RENAME — atomic, TTL-preserving, and it eliminates partial reads
• Protect rebuilds with a distributed lock, but design so correctness survives the lock expiring
• Always keep a fallback path — and log it loudly, or it will mask the failures it absorbs