At a glance
| Problem | Repeated project-type and type-variant queries while serializing work packages |
| Scope | GET /api/v3/work_packages in OpenProject’s Rails application |
| Result | 55.7% lower median server duration and 83.9% fewer SQL events for a page of 20 work packages |
| Delivery | Regression-tested open source change merged upstream |
The problem
OpenProject’s API v3 serializer calls Project#type_variant while building work package resources, schemas, and links. Although a response can contain many work packages with the same project and type, each call could query the project_types join and then load its associated variant.
For the profiled page of 20 work packages, both query patterns occurred 381 times:
- finding a
project_typeby project and type; and - loading that project type’s variant by ID.
The application was repeatedly retrieving the same data while constructing one response. In total, the request generated 922 SQL events, including 827 served by Rails’ query cache.
What I changed
I connected the existing work package eager-loading path to the model lookup that needed the data:
- the collection loader now preloads each project’s
project_typesand every project type’svariant; Project#type_variantchecks whether theproject_typesassociation is loaded;- when it is loaded, the method finds the matching project type in memory and uses its preloaded variant; and
- when it is not loaded, the original database lookup remains as a fallback for other callers.
The existing type.default_variant fallback is also preserved. This keeps the model method safe outside the API collection path while allowing serialization to benefit from the eager-loaded association graph.
Measured result
I measured the endpoint with Rack Mini Profiler using an admin API key and a page size of 20 work packages.
| Metric | Before median | After median | Change |
|---|---|---|---|
| Server duration | 2,277 ms | 1,009 ms | −55.7% |
| SQL duration | 149.81 ms | 92.79 ms | −38.1% |
| SQL events | 922 | 148 | −83.9% |
| Cached SQL events | 827 | 64 | −92.3% |
| Duplicate query fingerprints | 865 | 91 | −89.5% |
| Project/type lookups | 381 | 0 | −100% |
| Variant-by-ID lookups | 381 | 0 | −100% |
The server-duration figures came from a local Docker development environment, so they are subject to runtime variance. The stable query counts show the direct result: both targeted repeated-query patterns were eliminated. Response contents and size were unchanged.
Validation
The model spec preloads project types and variants, resolves a type variant, and asserts that the operation executes zero queries. The API integration spec verifies that the eager loader loads all project types—including one not used by the work package—preloads each associated variant, and allows type_variant to run without another query.
These checks cover both halves of the optimization: the collection loader supplies the complete association graph, and the model consumes it without returning to the database. The change was approved and merged into OpenProject upstream.