I built this lab to revisit a performance problem I encountered while working on a production identity and access management system. The original architecture had good reasons for existing, but its behavior became increasingly expensive as requests crossed more services and authorization boundaries.

I wanted to reproduce those interactions, understand where the work was actually happening, and measure what changed when retrieval and authorization were redesigned around batches instead of individual records. Rather than rely on recollection or make claims about a system I no longer have access to, I built a standalone implementation with synthetic data, distributed tracing, and enough moving parts to expose the same kinds of problems.

The synthetic dataset is large enough that the authorization path has somethign real to chew on.

Database service	Table	Records
account-db	accounts	641,047
authz-db	capability_grants	6,091,487
group-db	groups	1,122,093
group-db	group_users	1,501,045
organization-db	msp_managed_organizations	160,007
organization-db	organizations	447,795
organization-db	organization_accounts	641,047
user-db	users	1,000,000

GraphQL at Scale

One controlled request. The faster path combines changes to retrieval, authorization, and cache configuration

There is a lot to cover here. I'm going to gloss over the details as much as possible to have something actually readable, but I will go into further detail on linked pages for anyone who cares to dive in.

GraphQL was the visible edge of this performance problem, but it was not its cause. The expensive part lived underneath: remote object retrieval, hierarchy traversal, and authorization work repeated once per record. The query language made it easy to ask for a connected result. Whether that query was practical depended on the shape of the computation used to assemble it.

The system in this case study is a distributed identity and access management application. Separate Rails services own users, accounts, organizations, groups and memberships, and capability grants. A User Management service composes those REST resources into application-facing GraphQL responses. The test data includes individual customers, enterprises with account trees, and managed service providers (MSPs) linked to thousands of client organizations.

That sounds like an ambitious workload, but the system did not begin in crisis. Its ordinary authorization path worked reasonably well.

The system works

Access is capability-based. Grants belong to groups, users belong to groups, and each grant names a permission and a scope. A small effective set might look like this:

account.read
account.users.read
account.users.write

Account grants flow downward through a physical parent_account_id chain. MSP access adds a second, authorization-only edge: a grant on a provider account or its ancestors can cover accounts in a linked client organization. The provider account never appears in the client's returned account hierarchy. Affiliation alone grants nothing; the actor still needs the relevant ordinary capability.

Before, the only authorization possible is asking Authorization Service for the user's full list of capabilities in a given Account context, then searching the list for a matching capability string.

After, we added a /can endpoint to Authorization Service that would return true if every Account in a batch had a matching capability for a requesting User.

For a typical application request, this is tractable. The actor operates in one account context, the receiving service knows the records and policy it owns, and Authorization Service evaluates the requested permission. The design is doing useful work: it keeps authorization beside the data being disclosed and preserves service ownership.

User management changes the problem

A user-management screen is not a typical request. It needs users across many accounts, the groups attached to those users, the account records used to present them, and sometimes account ancestry. Every apparent relationship crosses an HTTP boundary.

ActiveResource makes remote resources feel like local objects, but the syntax hides a significant amount of work. Accessing account.parent, user.groups, or calling find can trigger an HTTP request to another service. And that request is only the beginning.

The receiving service owns the resource and is responsible for authorizing it before returning it to the caller. It retrieves the requested records from the database, then invokes another http request to the authorization service to determine what the actor is permitted to see. That authorization may require resolving the actor's group memberships, traversing account ancestry, discovering MSP provider relationships, and querying grants. Only authorized records make it into the HTTP response.

The calling service receives what looks like an ordinary collection of ActiveResource objects. What it doesn't see is the authorization work already performed on the other side of the HTTP boundary.

Now consider what happens when those returned objects lead to more remote relationships, each requiring another HTTP request and another round of authorization.

The resulting multiplication goes well beyond a database N+1:

records
× remote relationship requests
× authorization lookups
× hierarchy traversal

Each remote relationship introduces another HTTP request and another authorization boundary. Those checks are necessary: permission to retrieve an account does not imply permission to retrieve its groups. The performance problem arises from how that authorization work is performed, particularly when each record triggers its own capability resolution instead of sharing the work across a batch.

The original object-at-a-time paths were reasonable extensions of code built for one user on one account. The workload changed. Once a page crossed hundreds of account contexts, the unit of work could no longer remain one record.

Batching changes the shape of retrieval

The useful optimization was not shaving a few milliseconds from Account.find. It was replacing a series of independent questions with one set-shaped question.

Previously, retrieving the hierarchy for ten accounts meant ten separate requests to Account Service. Now, we can send all ten account IDs in one request and get their hierarchies back together.

The same approach applies to users, groups, and group memberships. Instead of requesting each relationship individually, we collect the IDs we need and retrieve the corresponding records in batches.

GraphQL-Ruby's DataLoader makes this possible without requiring every field resolver to coordinate with its neighbors. It collects requests for related data, combines them into larger calls, and distributes the results back to the fields that requested them.

Under the hood, Account Service uses a recursive SQL query to retrieve multiple account hierarchies at once, keeping the results associated with their original accounts.

We still enforce pagination and batch-size limits. Batching reduces the number of requests; it doesn't mean asking a service to return everything in one enormous response.

One trace makes the difference concrete. In an October 5 production-stack laboratory run, loading eight account hierarchies individually took 0.577 seconds, made eight top-level requests, and produced 88 traced HTTP client spans plus 144 application SQL spans. Sending the same eight roots to the batch endpoint took 0.097 seconds, made one top-level request, and produced 11 HTTP client spans plus 18 SQL spans. Both returned 172 account rows with the same SHA-256 result hash.

That is not a historical before-and-after measurement of a deployed application. It is a controlled execution of both current code paths. What it establishes is narrower and still valuable: when the same relationship work is expressed as a set, the owning service can replace repeated network and database work with set-oriented SQL.

Authorization becomes the next bottleneck

Batching retrieval revealed the next bad shape. The first authorization interface asked, in effect, “what can this actor do on each of these scopes?” It returned complete capability arrays. The caller then searched those arrays for the one permission required by the operation.

That interface is useful when an application really needs to render every action available to a user. It is wasteful when User Management Service only needs to know whether every one of 251 candidate accounts permit account.read.

The implementation amplified the mismatch. The batched capability endpoint preserved correct per-target results, but evaluated accounts one at a time internally: hierarchy resolution, provider lookup, membership lookup, and an all-permissions grant query repeated across the candidate set. Faster resource retrieval had exposed authorization as another collection of record-shaped computations.

Ask the smaller question

The replacement path asks for a decision about an operation and a bounded candidate set. For account reads, Authorization Service receives the candidate account IDs and account.read. It resolves their physical and virtual authorization paths in batches, loads the actor's explicit group memberships, and runs a grant query constrained by group, scope type, candidate scope IDs, and the requested permission. It correlates the matching scopes back to candidates and caches a boolean decision per actor, permission, and target.

Conceptually, the work changed from:

Before: Retrieve capabilities, authorize locally

Owning service loads candidate records from its database → retrieves the requesting user's capabilities for each record from Authorization Service → checks authorization locally → returns authorized records.

After: Authorize the batch remotely

Owning service loads candidate records from its database → asks Authorization Service to verify all records are authorized for acess in a single batch request → returns authorized records.

What the traces establish

The clearest authorization comparison uses the 251-account wide_org fixture containing 5,020 users. Both October 5 requests used batched retrieval with Redis disabled and passed exact checks for accounts, parent links, users, account scopes, groups, totals, and cursors.

The full-capabilities trace took 12.994 seconds. It contains 18,406 spans, including 2,319 HTTP client spans and 4,596 application SQL spans. The targeted path took 3.356 seconds, with 560 total spans, 88 HTTP client spans, and 137 SQL spans. In the waterfall, the capabilities trace is dominated by 760 hierarchy calls, 760 organization-context calls, and 760 provider-context calls. In the targeted trace those three categories appear 17 times each because the work is performed on chunks and permission-specific sets.

The most recent corrected trace pair, collected October 25, exercises the user-management request the screen actually needs. The request identifies only the MSP account and actor. It does not smuggle in a prepared list of users or managed accounts, ask for unrelated hierarchy fields, or choose the result size:

query MspUsers($mspAccountId: ID!, $actor: ID!) {
  mspUserManagement(mspAccountId: $mspAccountId, as: $actor) {
    loading
    loadedCount
    totalCount
    continuance
    accounts {
      id
      users {
        id
        email
        accountId
        groups { id name }
      }
    }
  }
}

The service discovers the managed accounts and loads their users. In the deterministic small_msp_10 fixture, that means all ten client accounts and their ten users; the eleventh fixture user is the MSP administrator making the request. The two measured requests and responses are byte-identical. Independent checks match the exact account IDs, user IDs, account scopes, group memberships, totals, and cursor.

The deliberately slow profile used truly serial retrieval and single-scope full-capability authorization with Redis disabled. It took 2.241 seconds and produced 1,008 spans: 387 receiving-service API spans and 575 application SQL spans. The optimized profile used batched retrieval and targeted authorization after all four scoped Redis databases were flushed. It took 0.368 seconds and produced 106 spans: 35 receiving-service API spans, 35 SQL spans, and 26 Redis pipeline spans. That is 6.09 times faster with 89.5% fewer spans in this single controlled sample. This comparison intentionally changes retrieval, authorization, and cache configuration together. It demonstrates the combined architecture, not the isolated contribution of any one change.

MSP all users · serial retrieval and full capabilities

The complete 1,008-span request for every user managed by this MSP. Truly serial retrieval and repeated single-scope capability, hierarchy, membership, and provider-context work dominate the waterfall.

Explore all 1008 spans·Full-size SVG·Jaeger JSON

MSP all users · batched retrieval and targeted authorization

The byte-identical 106-span response through batched retrieval and targeted, set-shaped authorization, measured after the scoped Redis flush.

Explore all 106 spans·Full-size SVG·Jaeger JSON

Cache state is not a footnote. In the optimized trace, “cold” means Redis DB 1 was flushed for account, authorization, group, and organization caches. Rails and PostgreSQL stayed warm, two workload warmups ran before the flush, and Active Record's request query cache remained enabled.

For a cold, 500 user retrieval against 25 accounts, the numbers are even wilder.

Profile	Authorization	Parent accounts	Redis	Client time
Before	capabilities	serial walk	off	23.852 s
After	/can	hierarchy API	on, cache flushed	0.381 s

That's a 62.5x increase.

Composition and execution are different concerns

GraphQL remained useful throughout. It describes the application result and lets DataLoader collect related field requests. REST remains the interface to the services that own the data and the authorization policy. They solve different problems in this architecture.

The failure was not that a graph query existed. The failure was executing a set request as a long sequence of remote object operations, then retrieving broad authorization state to answer narrow yes-or-no questions. Once retrieval, hierarchy resolution, and authorization were expressed in the shapes their owners could evaluate efficiently, GraphQL could continue doing its actual job: composing the result.

That does not mean GraphQL automatically scales. It means the scalability of a composition layer is determined below its syntax—by candidate-set boundaries, network fan-out, authorization semantics, cache scope, data ownership, and whether the traces show the work the code is really doing.

Conclusion

That's a lot of words for a conclusion that boils down to make sure set operations remain set operations throughout. If you are interested in even more details, please see the numbered sections of this case study. Thank you for reading this far.