N+1: DefaultTransformStrategy loads the owner user once per row of every REST/GraphQL response

[wezell]

Problem Statement

DefaultTransformStrategy resolves the owner user of every contentlet individually, producing one SELECT per row of every REST and GraphQL response. This is a classic N+1: a response returning 100 contentlets issues up to 100 additional user queries, each a separate Postgres round trip on the request thread.

Observed call chain:

DotContentletTransformerImpl.transform
  └─ AbstractTransformStrategy.apply
       └─ DefaultTransformStrategy.addCommonProperties
            └─ UserAPIImpl.loadUserById
                 └─ DotConnect.executeQuery → Postgres  (thread parked in sun.nio.ch.Net.poll)

The transform pipeline runs for every contentlet in every REST and GraphQL response, so the cost scales linearly with page size — exactly the dimension a client controls via limit / depth / GraphQL selection sets.

Who it impacts: any tenant serving content over /api/content, /api/es/search, /api/v1/page, or GraphQL. Worst on API-first and headless tenants with large result sets.

Evidence

Glowroot main-thread profile, caliber (GraphQL-heavy tenant — GraphQL 54% of transactions, /api/es/search 12%, /api/content 10%), 7-day window ending 2026-08-07, 893 sampled stacks:

1.1%  AbstractTransformStrategy.apply
 1.0%  UserAPIImpl.loadUserById
  0.9%  DotConnect.executeQuery
   0.8%  sun.nio.ch.Net.poll   RUNNABLE

AbstractTransformStrategy.apply appears ~40 times across that profile as the frame immediately above per-row work. The same loadUserById pattern is visible in the k8s.dairyqueen profile under ContentletDataFetcher → DotContentletTransformerImpl.lambda$transform$1.

Note this is a sampled profile: it establishes that the call is hot and per-row, not an exact query count. Confirm the multiplier with SQL logging (see repro step 3).

Steps to Reproduce

1. Create ~100 contentlets of any type, owned by a mix of users.
2. Enable statement logging on the Postgres connection, or attach a profiler to a node under load.
3. Request them in one call — e.g. GET /api/content/query/+contentType:MyType/limit/100, or the equivalent GraphQL query selecting a field that triggers the default transform.
4. Count SELECT statements issued against the user table for that single request.

Expected: owner users are resolved in bulk (one query for the distinct owner IDs) or served from cache.

Actual: approximately one user query per contentlet in the response.

Acceptance Criteria

• A response containing N contentlets issues O(distinct owners) user lookups, not O(N) — ideally one batched query, or cache hits after the first.
• Distinct owner IDs are de-duplicated before lookup (a response where all rows share one owner issues exactly one lookup).
• Serialized output is byte-for-byte unchanged for the affected fields (owner name, owner ID, and any other user-derived property currently emitted).
• Behaviour is unchanged when an owner user has been deleted or is invalid — the response degrades exactly as it does today, no new exception.
• Permission and visibility semantics are unaffected: batching must not expose user attributes that a per-row lookup would have withheld.
• Regression test asserting query count: N contentlets sharing M distinct owners produce ≤ M user queries.
• Verified against a profiler or SQL log that loadUserById no longer appears once per row.

dotCMS Version

main branch. Observed on production tenant clusters via Glowroot central collector 0.14.6, 7-day windows ending 2026-08-07.

Severity

Medium - Some functionality impacted

Links

NA — found during request-cost (@RequestCost) instrumentation analysis of production thread profiles, not via a support ticket.

Related: the batch-vs-scalar guidance in CLAUDE.md (prefer permissionAPI.filterCollection over per-item loops) is the same class of problem in the permission layer.