004. Reuse PostgreSQL JSON parsing and validation results

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

Problem and mechanism

fromjson() validates a complete JSON document before exposing its HFF value. The baseline repeated recursive numeric-validation SQL for repeated uses of the same projected receiver. The candidate caches the fully validated JSON result by exact receiver SQL text within the current projection scope. Every call keeps its own presence/no-value expression; callback and shadowed-alias scopes remain isolated. This is expression reuse, not value common-subexpression elimination across different SQL expressions.

The validation contract is unchanged. PostgreSQL checks one complete document for JSON syntax, decoded Unicode, and duplicate keys. A recursive walk validates every numeric node against HFF int64 and finite-float rules. An invalid nested number makes the whole parse ordinary null even if later access would not read that field. Boolean, string, and null roots skip the recursive numeric walk; a number root still validates its single number. Array/object recursion does not expand scalar leaf children. The validated result is projected once and reused within its scope.

Applicability

Reproduction

The baseline is f2421d84. The candidate was measured as a source snapshot; its SHA-256 inputs are listed below. Earlier candidate-run timings differed, so this record reports only the final baseline/candidate outputs.

The host was Darwin 25.2.0, darwin/arm64, Apple M3 Max with 64 GiB RAM. Go was 1.27.1 and benchmark output used the default GOMAXPROCS=16. PostgreSQL was 18.6 in a Debian aarch64 container using image sha256:5a5a84b19854a9ffaa54082c166ff4ec27473a361e496e5ea167f298f2da9722. The container had no explicit CPU, memory, or cpuset limits; its data directory used tmpfs, so these results do not measure persistent-disk I/O. JIT was disabled, max_parallel_workers_per_gather was zero, custom plans were forced, and the statement timeout was 60 seconds.

The same benchmark harness and fixtures were applied to baseline and candidate source snapshots. The harness used nine query shapes and 100- and 1,000-row temporary tables: scalar number/string/bool roots, a single object field, repeated object access, an array predicate, constant valid and invalid-nested documents, and repeated constants. Exact query shapes are defined by BenchmarkPostgreSQLJSONParsingBuild in json_parse_bench_test.go and BenchmarkPostgreSQLJSONParsingExecution in json_parse_execution_bench_test.go. Fixtures included nulls, malformed JSON, duplicate keys, and out-of-range nested numbers. Before each EXPLAIN, exact SQL result IDs were compared with local HFF matches. Server planning and execution came from EXPLAIN (ANALYZE, FORMAT JSON, TIMING OFF, BUFFERS, SETTINGS); fixture setup, Docker/psql startup, and validation-query time are not reported as database execution time.

Build measurements:

GOTOOLCHAIN=go1.27.1 go test ./backends/sql -run '^$' -bench '^BenchmarkPostgreSQLJSONParsingBuild$' -benchtime=200ms -count=3

PostgreSQL measurements (use a unique plan directory for each revision):

HFF_POSTGRES_CONTAINER=<container-name-or-id> HFF_POSTGRES_PLAN_DIR=/private/tmp/hff-parsing-plans GOTOOLCHAIN=go1.27.1 go test -tags=integration ./backends/sql -run '^$' -bench '^BenchmarkPostgreSQLJSONParsingExecution$' -benchtime=1x -count=3

Raw baseline/candidate logs, plans, SQL, and summary are outside Git in /private/tmp/hff-parsing-evidence/ on the measurement host.

Results

Values are medians of three samples. Time ranges show minimum–maximum. Build B/op is cumulative Go allocation, not peak or retained memory.

PostgreSQL execution

Planning and execution are reported separately. Matched is the result count for both baseline and candidate.

Build time increased in eight of nine workload medians. ScalarNumber rose from 21.201 to 31.930 µs/op (about 51%); RepeatedObject rose from 53.252 to 56.103 µs/op even though its allocation volume and SQL size fell. RepeatedConstants had a slightly lower Build median. A final baseline and candidate sample differed from an earlier candidate run, so these Build figures describe the captured run rather than isolate code-only cost.

Across the sampled PostgreSQL executions, candidate medians were lower for all nine query shapes at both row counts. The largest difference was RepeatedObject at 1,000 rows: 112.700 to 29.640 ms/op, SQL 41,460 to 33,716 bytes, and 220 to 164 plan nodes. These measurements describe one local host, PostgreSQL version, fixture, and three-sample run; they do not predict other workloads or isolate all run variability.

Correctness and tradeoffs

All benchmark samples matched local HFF result IDs, including malformed documents and nested invalid numeric values. Full PostgreSQL integration passed in 268.095 seconds; project formatting, race, vet, parser generation, examples, WebAssembly, and site checks also passed. PostgreSQL 16+ JSON syntax, Unicode, and unique-key checks remain in force. Reuse is limited to the exact SQL receiver expression in a projection scope; each call retains its own presence state, and callback/shadowed-alias scopes remain isolated. SQL execution does not enforce local MaxJSONDepth or step budgets. Decoded JSON strings and object keys must contain no NUL; see the PostgreSQL fromjson() contract.

Decision and limits

The candidate reduced generated SQL and measured PostgreSQL execution time most clearly for repeated object access, while Build time rose in eight of nine workload medians and SQL grew slightly for several single-use queries. The results do not establish a general speedup or a resource bound. Full document validation remains necessary for every root, including unused nested values, so an invalid document cannot be accepted by pruning its parsed fields. These results do not show that all database work is cached.