009. Reduce PostgreSQL canonical JSON lowering work

• Status: Measured
• Date: 2026-10-13

Problem and mechanism

Canonical serialization of dynamic JSON values used a recursive token walk that expanded scalar nodes as though they could have children. Float tokens also decoded the same lexical number in more than one expression. The candidate keeps scalar/null rows as token sources but filters them out of the recursive child-expansion term. It projects the decoded Float value once in the local Float-token CASE/LATERAL scope before formatting canonical Float text. Array order, object key sorting, numeric subtype and signed-zero output, null, and no-value behavior remain unchanged.

Applicability

Reproduction

The baseline is 96286b2. The candidate backends/sql/json_canonical.go source snapshot has SHA-256 5031dda6326eb33e0631f55e50ba996a5f71c2e2a3f21a964df398e4f983d9b5. The same harness was applied to baseline and candidate:

• Build benchmark, SHA-256 76fcb9398b09ad4310e1e4eafe0281ed21e2ebaf794567f649232240c75b809c
• Execution benchmark, SHA-256 11fdd635962bffe4ccee4ab791478ffb002f49a66970e36d286ff18b19c96484

The seven shapes were TypedStringControl, TypedListControl, AnyScalar, AnyNested, AnyWide, AnyDeep, and Callback. The first two serialize a schema-known string and string list. AnyScalar cycles through null, a large integer, Float values including negative zero, a string, and a boolean. AnyNested contains an object with a list of numeric, null, and scalar values. AnyWide has 64 object keys and 16 Float leaves; AnyDeep nests 16 alternating arrays and objects. Callback serializes the results of an identity map over a list. All dynamic cases use value:json=expected, except the callback case, which uses value:map(x=>x):json=expected. Both native JSON and JSONB with numeric metadata were measured; database fixtures had 50 and 250 rows.

The baseline and candidate were run in serialized three-sample pairs. Build sub-benchmarks ran for 200 ms; each database sample executed one query after checking result IDs against local HFF. The final tables use only the final baseline/candidate pair. Earlier baseline, prune-only, and projected-Float experiments are exploratory and are not mixed into these results.

The host was Darwin 25.2.0, arm64, Apple M3 Max with 64 GiB RAM. Go was 1.27.1 with default GOMAXPROCS=16. PostgreSQL was 18.6 in a Debian aarch64 container using image digest sha256:5a5a84b19854a9ffaa54082c166ff4ec27473a361e496e5ea167f298f2da9722. There were no explicit CPU, memory, or cpuset limits. JIT was off, parallel workers were disabled, custom plans were forced, statement timeout was 60 seconds, and the database directory used tmpfs. No retained-heap measurement was made.

Build command:

GOTOOLCHAIN=go1.27.1 go test ./backends/sql -run '^$' \
  -bench '^BenchmarkPostgreSQLSerializationBuild$/(json|jsonb)/(TypedStringControl|TypedListControl|AnyScalar|AnyNested|AnyWide|AnyDeep|Callback)$' \
  -benchmem -benchtime=200ms -count=3

Database execution command (use separate plan directories for baseline and candidate):

HFF_POSTGRES_CONTAINER=<container-name-or-id> \
HFF_POSTGRES_PLAN_DIR=/private/tmp/hff-serialization-plans \
GOTOOLCHAIN=go1.27.1 go test -tags=integration ./backends/sql -run '^$' \
  -bench '^BenchmarkPostgreSQLSerializationExecution$/(json|jsonb)/(50Rows|250Rows)/(TypedStringControl|TypedListControl|AnyScalar|AnyNested|AnyWide|AnyDeep|Callback)$' \
  -benchtime=1x -count=3

Raw logs, SQL, plans, and IDs remain outside Git in /private/tmp/hff-serialization-evidence/ on the measurement host.

Results

Build time is median ns/op with the three-sample min–max. Allocation is cumulative B/op, not peak or retained memory. SQL bytes are the generated predicate length.

Database timings are median milliseconds from three one-execution samples, with min–max ranges shown. Plan-node counts and SQL bytes are baseline → candidate; matched rows are the unchanged expected count. The controls' SQL and plan-node counts are unchanged.

The eight typed controls retain identical SQL; their timings vary, including a JSON TypedListControl execution increase from 0.476 ms to 0.545 ms at 250 rows. All ten dynamic 250-row cases measured lower candidate execution times. For example, AnyWide changed from 219.1 ms to 172.3 ms for JSON and from 254.1 ms to 209.4 ms for metadata-backed JSONB. These are measurements for the listed fixtures, not a general latency or memory guarantee.

Correctness and tradeoffs

The canonical serialization differential suite passed 324 comparisons for each candidate implementation across native JSON and metadata-backed JSONB. It covered scalar, nested, deep, identity-map output, whitespace and escapes, Float subtype, and negative-zero cases. The 64-key wide shape is a benchmark workload, not part of that 324-comparison suite. A separate 64-case guard matrix covered null, type, list, and path guards across JSON and JSONB. The final change preserved array order, canonical object-key order, numeric representation, null-token treatment, and the caller's JSONB metadata contract. No full PostgreSQL package integration suite was rerun for this isolated token-expression change; the database benchmark checked SQL IDs against local results for every sample. No peak or retained-memory measurement was made.

Build B/op fell for dynamic workloads while allocs/op increased by two or three; these are separate cumulative allocation measures. The 1-second CPU/allocation profiles for native-JSON AnyNested did not identify a compiler-specific CPU hotspot: sampled CPU time was dominated by runtime and GC waits. Allocation profiles locate cumulative allocation in Float text, canonical JSON, and expression construction. Profile totals are not compared because the runs completed different iteration counts.

The focused profile command was:

GOTOOLCHAIN=go1.27.1 go test ./backends/sql -run '^$' \
  -bench '^BenchmarkPostgreSQLSerializationBuild$/json$/AnyNested$' \
  -benchtime=1s -count=1 -cpuprofile=<path> -memprofile=<path> \
  -o=<binary-outside-repository>

The canonical differential test and project checks passed:

TMPDIR=/private/tmp HFF_POSTGRES_CONTAINER=<container> GOTOOLCHAIN=go1.27.1 \
  go test -tags integration ./backends/sql \
  -run '^TestPostgreSQL(DynamicCanonicalJSON|CanonicalSerializationGuards)$' \
  -count=1 -v
TMPDIR=/private/tmp HFF_WASM_NODE=<node-path> GOTOOLCHAIN=go1.27.1 sh hack/check.sh
TMPDIR=/private/tmp GOTOOLCHAIN=go1.27.1 go vet -tags integration ./backends/sql

The focused tests passed 324 canonical comparisons and 64 guard cases in 6.479 seconds. The project check covered formatting, race tests, vet, generation, examples, WASM tests, and site checks. No full PostgreSQL package integration run was performed for this isolated change.

Decision and limits

Retain scalar-leaf child-expansion pruning and one-time local Float-token decoding. The candidate reduces generated SQL and plan nodes for the dynamic token-CTE workloads; controls do not use that CTE and keep byte-identical SQL. The results do not establish faster PostgreSQL execution for every query, physical evaluation order, or compliance with local runtime budgets.