# Reducing role queries across Forem’s role-heavy paths

> An open source Rails performance optimization that made role checks association-aware across Forem’s admin member list, CSV export, async user payload, and reCAPTCHA service.

- Canonical: https://haseebeqx.com/case-studies/forem-repeated-role-queries/
- Published: 2026-09-09


## At a glance

| | |
|---|---|
| **Problem** | The same users’ roles were queried repeatedly while each path evaluated several authorization questions |
| **Scope** | Forem’s shared authorization policy, admin member list and CSV export, async user payload, and reCAPTCHA exemptions |
| **Result** | Role queries fell by 75.0%–99.3% across four measured paths, with lower SQL time, allocations, and total duration |
| **Delivery** | Regression-tested open source optimization merged upstream |

![Forem role-query optimization results across four paths: role queries fell from 568 to 4 for the admin member list, 139 to 3 for its CSV export, 13 to 3 for the async user payload, and 4 to 1 for the reCAPTCHA service.](/images/case-studies/forem-role-queries-focus-points.svg)

## The problem

Forem 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.

| Metric | Before mean | After mean | Mean change | Before median | After median |
|---|---:|---:|---:|---:|---:|
| Request duration (ms) | 1,409.14 | 893.24 | **−36.6%** | 1,371.71 | 889.69 |
| SQL duration (ms) | 284.29 | 58.47 | **−79.4%** | 283.62 | 58.22 |
| Allocated objects | 965,895 | 751,768 | **−22.2%** | 965,916 | 751,768 |
| SQL events | 683 | 119 | **−82.6%** | 683 | 119 |
| Duplicate query fingerprints | 661 | 96 | **−85.5%** | 661 | 96 |
| Role queries | 568 | 4 | **−99.3%** | 568 | 4 |
| Role snapshot queries | 0 | 2 | n/a | 0 | 2 |
| Role `COUNT`/`EXISTS` queries | 1 | 1 | **0.0%** | 1 | 1 |

### 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.

| Metric | Before mean | After mean | Mean change | Before median | After median |
|---|---:|---:|---:|---:|---:|
| Request duration (ms) | 229.24 | 124.98 | **−45.5%** | 220.05 | 129.37 |
| SQL duration (ms) | 64.66 | 10.24 | **−84.2%** | 62.09 | 10.50 |
| Allocated objects | 106,728 | 50,653 | **−52.5%** | 106,727 | 50,654 |
| SQL events | 152 | 16 | **−89.5%** | 152 | 16 |
| Duplicate query fingerprints | 139 | 2 | **−98.6%** | 139 | 2 |
| Role queries | 139 | 3 | **−97.8%** | 139 | 3 |
| Role snapshot queries | 0 | 2 | n/a | 0 | 2 |
| Role `COUNT`/`EXISTS` queries | 1 | 1 | **0.0%** | 1 | 1 |

### 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.

| Metric | Before mean | After mean | Mean change | Before median | After median |
|---|---:|---:|---:|---:|---:|
| Request duration (ms) | 119.53 | 116.66 | **−2.4%** | 119.69 | 102.60 |
| SQL duration (ms) | 18.46 | 14.17 | **−23.2%** | 18.81 | 13.92 |
| Allocated objects | 30,580 | 27,485 | **−10.1%** | 30,575 | 27,484 |
| SQL events | 33 | 23 | **−30.3%** | 33 | 23 |
| Duplicate query fingerprints | 10 | 2 | **−80.0%** | 10 | 2 |
| Role queries | 13 | 3 | **−76.9%** | 13 | 3 |
| Role snapshot queries | 1 | 1 | **0.0%** | 1 | 1 |
| Role `COUNT`/`EXISTS` queries | 8 | 0 | **−100.0%** | 8 | 0 |

### 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.

| Metric | Before mean | After mean | Mean change | Before median | After median |
|---|---:|---:|---:|---:|---:|
| Service duration (ms) | 10.22 | 3.31 | **−67.7%** | 9.59 | 2.92 |
| SQL duration (ms) | 4.76 | 1.41 | **−70.4%** | 4.53 | 1.25 |
| Allocated objects | 2,670 | 1,176 | **−56.0%** | 2,567 | 1,072 |
| SQL events | 5 | 2 | **−57.7%** | 5 | 2 |
| Duplicate query fingerprints | 2 | 0 | **−100.0%** | 2 | 0 |
| Role queries | 4 | 1 | **−75.0%** | 4 | 1 |
| Role snapshot queries | 0 | 1 | n/a | 0 | 1 |
| Role `COUNT`/`EXISTS` queries | 4 | 0 | **−100.0%** | 4 | 0 |

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 →](https://github.com/forem/forem/pull/23817)