PolicyPlugin: resolveManyToManyJoinTable does an uncached full-schema scan on every query (~29% CPU self-time)

[abetss]

Summary

In @zenstackhq/plugin-policy, PolicyHandler.resolveManyToManyJoinTable(tableName) performs an O(models × fields) linear scan of the entire schema on every invocation, with no memoization. It is invoked once per table per (sub)query while building policy filters (and again on the mutation/validation path). The result is a pure function of (schema, tableName), and the schema is immutable for the client's lifetime, so it is trivially cacheable.

In CPU profiling of a read-heavy endpoint, getModelPolicyFilterForManyToManyJoinTable (which calls resolveManyToManyJoinTable) is 28.9% of total CPU self-time, and the m2m-resolution cluster is ~40%. The database itself is 0.28% of CPU — the cost is entirely in this JS resolution. Because it's single-threaded CPU work it serializes under concurrency, which on a 1-vCPU container turns into large p50 latency increases.

Version

@zenstackhq/plugin-policy and @zenstackhq/orm 3.6.4; kysely 0.28.17; Postgres 16. Latest stable is 3.7.2; from the changelog there's no perf change on this path, so I believe it's still present on main — happy to confirm against a specific ref.

Root cause

resolveManyToManyJoinTable scans every field of every model to find the join table matching tableName, recomputed on each call (src/policy-handler.ts, paraphrased from 3.6.4):

resolveManyToManyJoinTable(tableName) {
  for (const model of Object.values(this.client.$schema.models))
    for (const field of Object.values(model.fields)) {
      const m2m = QueryUtils.getManyToManyRelation(this.client.$schema, model.name, field.name);
      if (m2m?.joinTable === tableName) {
        // ...build + return the descriptor
      }
    }
  // returns undefined for every non-m2m table — *after* scanning the whole schema
}

The inner getManyToManyRelation (in @zenstackhq/orm) is itself non-trivial per call (requireField ×2, requireIdFields ×2, attribute inspection).

It's on the hot path because buildPolicyFilter calls it first, for every table referenced in a query:

buildPolicyFilter(model, alias, operation) {
  const m2mFilter = this.getModelPolicyFilterForManyToManyJoinTable(model, alias, operation); // -> resolveManyToManyJoinTable
  if (m2mFilter) return m2mFilter;
  // ...normal policy compilation
}

So even a regular (non-m2m) model pays a full schema scan that returns nothing. It's also reached via isManyToManyJoinTable and tryRejectNonexistentModel → tryRejectNonexistingTables. Net cost per query ≈ (#tables in query) × (#models × #fields) getManyToManyRelation calls. For a deep read with several includes that traverse implicit m2m relations, this dominates the request.

Evidence

CPU self-time, deep read query, 56,750 samples (Node 20, node:inspector):

% self-time	function	package
28.9%	getModelPolicyFilterForManyToManyJoinTable	plugin-policy
16.1%	getModel	orm
9.0%	resolveManyToManyJoinTable	plugin-policy
1.7%	getManyToManyRelation	orm
0.28%	(actual SQL / pg)	pg

Buckets: plugin-policy 39.3%, orm 26.5%, kysely 4.4%, pg 0.28%, idle/GC ~29%. The policy transform adds +1ms to a PK lookup but **+20ms to a deep multi-include read** (warm/reused client). Under concurrency it scales ~linearly (single-thread CPU serialization).

Full CPU profile (sanitized; opens in Chrome DevTools → Performance, or speedscope): https://gist.github.com/abetss/58446f08bf2210876603d9838731f892

Proposed fix

Memoize resolveManyToManyJoinTable by tableName. The schema is fixed for the client's lifetime, so an instance-level cache that also caches the negative result removes the repeated scans:

#m2mCache = new Map(); // tableName -> descriptor | undefined

resolveManyToManyJoinTable(tableName) {
  if (this.#m2mCache.has(tableName)) return this.#m2mCache.get(tableName);
  const result = /* ...existing scan... */;
  this.#m2mCache.set(tableName, result);
  return result;
}

Or stronger: precompute a Map<joinTableName, descriptor> once when the schema is bound (single pass over the schema), making resolveManyToManyJoinTable an O(1) lookup. getModel (16%) looks similarly memoizable.

Reproduction

The profile above is from a deep read with nested implicit-m2m includes. I can put together a minimal standalone repro (a small schema with one implicit m2m relation + a @@allow read policy, profiling a nested read) if that would help — happy to do so.

Environment

Node 20 (profiled) and Bun 1.3 (our production runtime); cause ranking is identical on both engines, magnitude ~4× larger on Bun/JavaScriptCore.