perf: lazy SQL fingerprint + prototype aggregate reducers (−19.5 µs/op) - #30181

Linked issue

n/a — no ticket. This came in as a profiling brief with a measured baseline, not through issue triage.

Skill update

n/a — internal only. No user-facing surface changes: the emitted SQL is byte-identical, and the published .d.mts diffs byte-identical against a pristine build.

At a glance

The call site does not change. Its cost does.

await db.orm.public.Customer
  .select('id', 'companyName', 'contactName', /* … 8 more */)
  .orderBy((c) => c.id.asc())
  .limit(10)
  .offset(n)
  .all();

shape  scenario           before    after     Δ µs    Δ ops/s   wins
list   pg, named prepared  82.0     82.0      −0.0     +0.5%     2/5   ← control
list   db.sql, prepared   189.4    178.5     −10.9     +2.6%     4/5
list   db.sql, per req.   219.0    211.4      −7.6     +1.1%     4/5
list   db.orm             281.1    261.6     −19.5     +3.0%     5/5
point  pg, named prepared  66.7     65.7      −1.0     +0.9%     3/5   ← control
point  db.sql, prepared   134.8    124.1     −10.6     +4.1%     5/5
point  db.orm             215.0    197.9     −17.1     +4.1%     5/5

Client CPU µs per operation, median of 5 interleaved reps, both arms compiled into one build. The two raw pg rows are the control: nothing here can touch the driver, so a framework change that moved them would mean machine drift rather than a win.

The benchmark that produced these is deliberately not in this PR — see How the numbers were produced.

Reviewer notes

• This is a small diff carrying large claims. Three commits, seven files, +372/-25 — of which 234 lines are tests. The measurements behind the numbers were made with a benchmark harness kept out of this PR; if you want it in-tree to re-run them yourself, say so and I will open it as a separate PR.
• collection.ts uses a class static-initialisation block assigning a module-level binding. That looks odd on the diff and is deliberate: the reducer factory needs the private #includeScalarReducer, and a plain static member put an @internal symbol into the published .d.mts. The static block keeps the private access and emits nothing.
• The reducer collision rule changed shape but not outcome. The old check was operation in this inside the constructor; the new one is operation in Base.prototype. These resolve identically for a subclass prototype method, and an instance field still shadows the inherited reducer exactly as it shadowed the own-property one. aggregate-reducer-installation.test.ts pins both cases.
• A fourth optimisation was measured and reverted. Its six characterisation tests were kept — see the last commit. If you would rather they went too, say so.
• Pre-existing failures, not introduced here. pnpm test:packages fails 28 tests on this branch and the identical 28 on a pristine main: every one is a Mongo suite whose mongodb-memory-server binary cannot be downloaded in this environment, plus three tarball packaging tests. Two further files (module-identity, migration-cli.exit-scheme) failed only under full parallel load and pass in isolation on both trees.

Decision

Three changes ship, each measured on its own against the same build:

1. sql-runtime fingerprints SQL lazily. recordTelemetry ran three regex passes, a toLowerCase() and a SHA-256 over the full statement on every execution in both lanes, whether or not anyone read runtime.telemetry(). It now stores the raw SQL and derives the fingerprint on first read.
2. sql-orm-client installs aggregate reducers on a per-registry prototype. They were installed per instance with Object.defineProperty, and since every chain link clones through the constructor, a four-link chain paid 32 of those calls per request. It now pays zero.
3. sql-orm-client keys its two hottest metadata caches by nesting rather than by string. The column map is consulted once per returned row, and each lookup was building a template-string key.

A fourth was killed on the evidence and is not in this PR: rewriting mapStorageRowToModelFields to avoid two per-row allocations profiled at 3.8% of CPU and measured slower — the point lookup lost all five reps at +8.6 µs. Reverted; its tests kept.

The PR carries only the three production changes and their tests. The benchmark harness is not included.

How it fits together

1. Reproduce the stated baseline before changing anything. Against a six-model contract in one public namespace with snake_case columns and the eight aggregate operations the Postgres target declares, seeded to 191k rows. Nine of ten baseline rows land within 10% of the brief's table; the prepared point lookup sits at +15.4%, so every claim below is made against the locally measured baseline rather than the brief's.
2. Profile, windowed. The first --cpu-prof came back 58% (idle) — the round-trip. Dropping the first 30% of the timeline and excluding idle samples puts #installAggregateReducers at 4.2% of client CPU, and fingerprinting (computeSqlFingerprint + Hash + the whitespace regex) at roughly 4.5%.
3. Measure each hypothesis in a paired A/B, both arms compiled into one build and selected per process, interleaved rep by rep. This is what caught the regression: the fourth change looked merely flat on the list query (+2.5 µs, 2/5) and lost decisively on the point lookup (+8.6 µs, 0/5).
4. Gate every delta on the emitted SQL. Checked client-side by diffing captured execution plans, and server-side with log_statement='all' — each statement appears in the Postgres log exactly twice, once per arm.
5. Remove the toggles and confirm the published types. The declaration file diffs byte-identical against a pristine build.

Behavior changes & evidence

• Telemetry fingerprints are computed on read, not on execution. RuntimeTelemetryEvent is unchanged — same fields, same value, still frozen, still grouping statements that differ only in their literals. Lands below both lanes, so db.sql collects it too: db.sql-prepared moves −10.9 µs on the list shape. See packages/2-sql/5-runtime/src/sql-runtime.ts; evidence in packages/2-sql/5-runtime/test/sql-runtime.test.ts (5 tests written before the change, pinning read-stability, literal-insensitivity and structural separation).

• A chained ORM collection carries its reducers on a shared prototype. db.orm.public.Customer.where(…).limit(…) still answers count, avg and the rest; they are no longer own properties. Custom collection classes keep their own members over a same-named operation, direct new Collection(...) still takes the per-instance path, and two ORMs with different registries stay isolated. See packages/3-extensions/sql-orm-client/src/collection.ts and packages/3-extensions/sql-orm-client/src/orm.ts; evidence in packages/3-extensions/sql-orm-client/test/aggregate-reducer-installation.test.ts (8 tests).

• Model-metadata lookups allocate nothing. The win scales with row count — the ten-row list query gains seven times what the single-row point lookup does, which is what identifies it as per-row. See packages/3-extensions/sql-orm-client/src/collection-contract.ts; evidence in packages/3-extensions/sql-orm-client/test/collection-runtime.test.ts.

Where the cost went

On the list query the ORM layer — the cost above db.sql-per-request — was 62.1 µs and is now 50.2 µs: the prototype reducers and the metadata cache took out 11.9 µs, or 19% of it. The remaining 7.6 µs of the ORM's 19.5 µs win came from the lazy fingerprint, which sits below the ORM in shared runtime. On the point lookup the layer went from 29.9 to 20.8 µs.

The list query's remaining budget splits as: 96.5 µs below both lanes (lowering, parameter encode, row decode — shared with db.sql, deliberately untouched), 32.9 µs of per-request plan construction that db.prepare avoids, and 50.2 µs of ORM client.

Two corrections to the brief

Recorded here because they will otherwise be rediscovered:

• The orderBy accessor does not mint ~110 closures per call. createModelAccessor returns a Proxy; field accessors and their trait-gated comparison closures are built lazily in the get trap, per property actually touched. orderBy(c => c.id.asc()) builds exactly one.
• Memoising the model accessor would change the emitted SQL. ModelAccessorScope carries a mutable aliasCounter, incremented by #allocateAlias whenever a relation filter needs a table alias. Sharing an accessor across requests would emit __orm_rel_1 on one and __orm_rel_2 on the next.

Separately, and not stated in the brief: the two lanes never emitted byte-identical SQL. The ORM qualifies every column against the table (SELECT "customers"."id" AS "id"), the builder does not (SELECT "id" AS "id"). The harness therefore runs a raw pg floor per lane against that lane's exact text; the difference costs nothing measurable (78.5 vs 79.1 µs, inside noise).

How the numbers were produced

The benchmark harness is kept out of this PR to keep it to the production change. What it does is worth stating, because the numbers above are only as good as the method:

• One process per scenario, selected by environment variable. Two identical paths measured in one process answer differently depending on ordering, as call sites go polymorphic across scenarios.
• Client CPU via process.cpuUsage(), pool max: 1, sequential. The Δ ops/s column comes from those same sequential runs and is not a concurrency number.
• A raw pg floor per lane, issuing that lane's byte-identical SQL text — the two controls in the table above.
• A same-lane prepared/unprepared pair, which is what prices plan construction.
• A checksum over returned row ids, so a path that quietly returns fewer rows is caught rather than credited as a win. All rows above report the same checksum.
• Paired A/B: both arms compiled into one build, selected per process, interleaved rep by rep so machine drift cancels. The wins column is how many of the five reps the new arm won outright.
• Contract: six models in one public namespace, snake_case columns, the eight aggregate operations the Postgres target declares; seeded by generate_series to 191k rows (10k customers, 5k products, 1k suppliers, 200 employees, 50k orders, 125k order details).
• Environment: Node 24.19.0, linux-arm64, postgres:15-alpine, pg 8.22, 3 000 iterations per rep after a 300-iteration warm-up.

I can open the harness as a follow-up PR if the team wants it in-tree.

Testing performed

• pnpm --filter @internal/sql-orm-client test — 785 passed (71 files), no type errors
• pnpm --filter @internal/sql-runtime test — 348 passed (38 files)
• pnpm --filter integration-tests test test/sql-orm-client — 335 passed (42 files, PGlite)
• pnpm lint:deps — no dependency violations (2012 modules, 3127 dependencies)
• pnpm build — full workspace build
• pnpm test:packages — 15 478 passed; 28 failures reproduced identically on pristine main (see reviewer notes)
• Emitted SQL diffed client-side and confirmed against the Postgres statement log with log_statement='all'
• Published .d.mts diffed against a pristine build — byte-identical

Follow-ups

• Prepared statements for the ORM lane. db.prepare is builder-only, so the ORM cannot recover 32.9 µs of plan construction on the list query or 53 µs on the point lookup. Larger than everything in this PR combined, and a feature rather than an optimisation — worth its own ticket.
• Constructor-bypassing #clone. Cheaper to attempt now that reducers no longer live on the instance.
• Memoising only the accessor's prelude per execution context, constructing a fresh scope per call — the half of accessor memoisation that does not touch the alias counter. Small on this contract (38 inner iterations), likely larger on an extension-heavy one.

Alternatives considered

• Freeze AST nodes only in dev. freeze measures 0.8% of CPU, roughly 2 µs. The frozen-node invariant is specified in the architecture docs and relied on across the IR; trading it for 2 µs is a call for the team, not a profiling task, so it was left alone.
• Mutate CollectionImpl.prototype directly instead of deriving a subclass. Simpler, and wrong: two contracts in one process would leak each other's operations, and reservedCollectionMemberNames() scans that prototype.
• Keep the reducer factory as a static on the class. It works, and it publishes an @internal symbol in the .d.mts. The static-initialisation block gets the same private access with no emitted surface.
• Allocation-free row shaping. Measured slower and reverted, as above.
• Report cross-run before/after numbers. Discarded once a build-to-build comparison showed the raw pg floor drifting ±6 µs between runs. Everything quoted here is paired within one build.

Checklist

• All commits are signed off (git commit -s) per the DCO.
• I read CONTRIBUTING.md and the change is scoped to one logical concern.
• Tests are updated — 19 added, all written before the implementation they cover.
• The PR title is in TML-NNNN: <sentence-case title> form — not applicable, there is no Linear ticket for this work. Titled in the repo's conventional-commit form instead, matching ci: and docs(skills): in the recent log. Happy to retitle if a ticket is opened.
• The Skill update section above is filled in.