How We Made the Business Members Page ~100x Faster

What the Page Does

The Business Members page lists every member of a business. Alongside each member's name and role, the grid shows five computed financial columns:

None of these values are stored anywhere as finished numbers. They are computed from each member's raw work entries, the individual shift records stored in our Redis database. Producing one row of the grid means loading a member's profile, loading their full year of work entries, splitting hours into regular and overtime, and summing gross pay. Multiply that by every member of the business, and that is the work the page does.

• Year-to-date gross — total earnings so far this year
• Total hours — all hours worked this year
• Regular hours — hours at the standard rate
• Overtime hours — hours beyond the regular threshold
• Trailing baseline — a rolling reference figure used for comparison

The Problem, Part 1 — The N+1 Query Pattern

The original implementation made one query to fetch the list of members, and then, for each of roughly 100 members, made separate, sequential round-trips to Redis: one for the member's profile, and more for their full year of work entries.

Small businesses with a handful of members were largely unaffected; the stacked latency only becomes noticeable as organizations grow to dozens or hundreds of members, where the per-member round trips accumulate into seconds of server time.

This is the classic “N+1” pattern: one query to get the list, then N more queries issued one at a time for the items in it. Each individual Redis lookup is fast, well under a millisecond of actual work, but every round-trip also pays a fixed cost in network latency. Issued sequentially, those costs do not overlap; they stack linearly. Hundreds of sequential round-trips meant hundreds of stacked latency payments before the page could render anything.

The database was never the bottleneck. The conversation with the database was.

The Problem, Part 2 — Recomputing Everything on Every View

The second flaw compounded the first. All of that payroll math, splitting a year of work entries into regular and overtime hours, computing year-to-date gross, deriving the trailing baseline, for every member, was redone from scratch on every single page view.

Work entries do not change very often. Between two consecutive views of the Members page, the underlying data is almost always identical. Yet every visit paid the full cost of recomputing results that had just been computed moments earlier and then thrown away.

Combined, the two flaws produced about 1.8 seconds of server time per page load, measured with PayCal Lens, our built-in server timing instrumentation.

The Fix, Part 1 — Redis Pipelining

Redis supports pipelining: sending a batch of commands in a single round-trip and receiving all of the answers together. Instead of asking one question, waiting, asking the next, and waiting again, the server now asks all of its questions at once.

We added a batched lookup method, Database::pipelineHgetall(), and converted the members grid to use it. All member profile lookups are gathered into a single round-trip, and all work-entry lookups into another, rather than one round-trip per member per data type.

// Before - one round-trip per member, latency stacks linearly
foreach ($memberIds as $id) {
    $profiles[$id] = Database::hgetall($profileKey($id));
}

// After - one round-trip for the whole batch
$profiles = Database::pipelineHgetall(array_map($profileKey, $memberIds));

This change alone collapsed hundreds of sequential latency payments into a handful.

The Fix, Part 2 — A Materialized Cache

Pipelining makes the computation cheaper to feed; the second fix avoids repeating the computation at all. We introduced BusinessMembersCache, a server-side cache that stores the finished computed grid data, the per-member financial summaries, in Redis.

Two rules govern cache freshness:

• A 5-minute expiry. Every cached grid automatically expires after 5 minutes, bounding how old the data can possibly be.
• Explicit invalidation on change. Any member-related change, a role update, a member added, a member removed, deletes the cached grid immediately. The cache is invalidated immediately after relevant edits, ensuring fresh data is generated on the next request.

Member identity and permission checks are deliberately not cached. Every request still runs the full access-control path; only the expensive financial arithmetic is reused.