Reducing role queries across dev.to role-heavy paths

This is part of casestudies for my Rails performance service.

At a glance

The problem

Forem (the opensource dev.to codebase) uses Rolify to answer authorization questions such as whether a user is an admin, trusted user, tag moderator, or subforem moderator. Several request paths asked multiple role-based questions about the same users, and each check could issue another COUNT or EXISTS query even when the user’s complete role collection could have been loaded once.

The pattern appeared in four measured paths. Rendering the admin member manager triggered 568 role queries and 683 SQL events. Exporting members to CSV produced 139 role queries among 152 SQL events. The authenticated async payload issued 13 role queries while assembling one user’s client-side data, and the reCAPTCHA exemption service issued four while deciding whether one user should bypass a challenge.

Measured result

I benchmarked each path with five measured executions after three warmups. The changes and their results are described together below.

Shared authorization layer

The common fix sits in Authorizer, which backs role-derived methods such as trusted?, any_admin?, and the moderator checks. Its private helpers previously called Rolify’s database-backed has_role? or has_any_role?, so loading user.roles elsewhere did not prevent further queries.

I changed those helpers to inspect Active Record’s association state. When the complete roles association is loaded, they use Rolify’s has_cached_role? against that in-memory collection; otherwise, they retain the original database-backed behavior. I also routed global tag- and subforem-moderator checks through these helpers with Rolify’s :any resource selector, while resource-specific checks continue to pass the resource itself.

This gave each optimized path one reusable, association-aware role-checking strategy without changing the behavior of callers that do not preload roles.

Admin member list: GET /admin/member_manager/users

The member list renders up to 50 users per page and checks several roles while deriving each user’s status. I added preload(:roles) to the paginated relation so Active Record fetches the page’s roles as a collection and Authorizer reuses them. This removed the per-user N+1 queries without changing pagination, filtering, or rendered controls.

Admin CSV export: GET /admin/member_manager/users/export.csv

The export calls the same user_status logic for every CSV row and also reads each user’s organizations. I changed its relation to preload both :organizations and :roles, allowing organization names and role-backed statuses to come from batched association data while preserving the CSV columns and status precedence.

Async user data: GET /async_info/base_data

This payload calculates several permission-derived values for one user. I made AsyncInfo call @user.roles.load once during initialization, before building the response, so direct and policy-mediated role checks share the loaded association.

reCAPTCHA decision: ReCaptcha::CheckEnabled.call(user)

For signed-in users, the service evaluates several role-backed exemptions and restrictions. I loaded the user’s roles once after the configuration and anonymous-user early returns, allowing the remaining checks to share one snapshot through Authorizer while retaining their existing order and outcome.

Across the four paths, SQL duration improved by 23.2%–84.2%, allocations fell by 10.1%–56.0%, and duplicate query fingerprints fell by 80.0%–100%. Timings depend on the surrounding workload, but the query counts show the deterministic improvement: repeated per-user and per-check lookups became bounded association loads.

Validation

Policy specs preload global and resource-scoped roles, verify the correct answers for admin, tag-moderator, and subforem-moderator checks, and assert that those checks issue no further role queries. Request coverage confirms that the admin member list does not query roles separately for each displayed user.

Additional specs count role queries while building async user data and evaluating reCAPTCHA exemptions. Manual verification covered the member list, CSV contents, async user data, and exemption behavior for admins, trusted users, and moderators. No user-facing behavior or UI changed.

Together, the changes established one reusable association-aware role-checking strategy rather than four isolated endpoint fixes. The optimization was reviewed, approved, and merged into Forem upstream.

View the merged Forem pull request →