012. Read validated duration fractions as ASCII bytes

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

Problem and mechanism

The PostgreSQL duration parser sums fractional components one decimal digit at a time so that nanosecond integrality and int64 bounds remain exact for long fractions. The candidate converts each already-validated ASCII fraction to UTF-8 bytes once, then reads digits by byte offset in the recursive carry query. It retains the existing grammar, component summation, integrality and overflow checks, and null propagation.

Applicability

Reproduction

The baseline is ef9945b. The candidate backends/sql/duration.go source snapshot has SHA-256 9eba6b1d5ed1e28c2596766baeefa503d0cbf430bf1b0f09838cd00b918124aa. The same benchmark harness was used for both source versions:

• Build benchmark, SHA-256 e5c4dd065e2adb2a430c216c5615cc8f33bd91537d7205ed1e4bcf7c62dcb04b
• Execution benchmark, SHA-256 ed55515e863a11c5cc436e35536038fd1ed2d8d61cc6299456310f7660e169c9

Build covers four query shapes—StringControl, IntegerUnits, Repeated512, and Serialized512—for native text, JSON, and JSONB. Its inputs do not vary by stored fraction length. Database execution also covers CompoundUnits, fractions with 16, 512, and 4,096 digits, and a 20,000-digit fraction. The 20,000-digit database case has one positive row. Two-row fixtures contain positive and negative values; 25-row fixtures also include invalid, zero, and null values. Fractional fixtures combine tiny decimal components that sum exactly to one nanosecond, with alternating negative cases. Each execution sample compares SQL-matched IDs with local HFF matches before collecting PostgreSQL planning and execution fields.

Build command:

TMPDIR=/private/tmp GOMAXPROCS=16 GOTOOLCHAIN=go1.27.1 \
go test ./backends/sql -run '^$' \
  -bench '^BenchmarkPostgreSQLDurationBuild$' -benchmem \
  -benchtime=200ms -count=3

PostgreSQL execution command (use a separate plan directory for each stage):

TMPDIR=/private/tmp GOMAXPROCS=16 \
HFF_POSTGRES_CONTAINER=hff-pg-conformance-20261012 \
HFF_POSTGRES_PLAN_DIR=/private/tmp/hff-duration-evidence/baseline-plans \
GOTOOLCHAIN=go1.27.1 \
go test -tags=integration ./backends/sql -run '^$' \
  -bench '^BenchmarkPostgreSQLDurationExecution$' -benchtime=1x -count=3

Change the plan directory for the candidate and repeated stages. Use the same PostgreSQL 18.6 profile when reproducing the comparison.

Runs were serialized in this order: baseline, candidate, baseline repeat, candidate repeat. Each stage has three samples, for six measurements per side. The host was Darwin 25.2.0 arm64, Apple M3 Max with 64 GiB RAM. Go was 1.27.1 with GOMAXPROCS=16. PostgreSQL was 18.6 in a Debian aarch64 container using image digest sha256:5a5a84b19854a9ffaa54082c166ff4ec27473a361e496e5ea167f298f2da9722. The container had no network, explicit CPU, memory, or cpuset limits; its database directory used tmpfs. JIT was off, parallel workers were disabled, custom plans were forced, and statement timeout was 60 seconds. The local parity oracle used MaxSteps=math.MaxInt; that is not a PostgreSQL execution budget. Raw logs, plans, IDs, and aggregate data remain outside Git in /private/tmp/hff-duration-evidence/ on the measurement host.

Results

Build time is median ns/op with six-sample min–max ranges. Allocations are cumulative B/op and allocs/op; SQL bytes are generated predicate length. Allocation counts were unchanged in all 12 Build cases. B/op ranged from effectively unchanged to 3.33% higher, and the candidate added 53 SQL bytes per duration expression. Build timing was mixed and does not show a general reduction.

The execution table reports six-sample medians and min–max ranges in milliseconds. Planning medians are also shown because execution alone does not represent all measured server cost. All 51 workloads kept the same plan-node count. Planning changes were mixed, from 17.1% lower to 18.6% higher across the matrix, with no systematic reduction. The six-sample string-control execution medians drifted from 11.1% lower to 8% higher across tiny timings; their SQL was byte-identical.

For 25-row compound inputs, execution increased 6.6% to 10.8%. The two-row compound samples were mixed, from 4.2% lower to 15.7% higher. For 512-digit fractions, the 25-row samples were about 71–72% lower; for 4,096 digits, they were about 94% lower. The single-row 20,000-digit samples were about 97.5% lower. These are measurements of the tested inputs and local PostgreSQL container, not a guarantee for other data or deployments.

Correctness and tradeoffs

The candidate passed 8,930 duration comparisons, eight long-decimal boundary cases, six SQL string escaping probes, and 87 shared conformance pairs. These cover exact fractional summation, integral-nanosecond checks, int64 bounds, invalid and null inputs, and parameter escaping. No parser grammar or result-conversion behavior changed.

The byte-index path materially reduced execution time for tested long fractional strings but added generated SQL and cumulative Build allocation. The short compound-path regressions are retained above. The database timing is from a single host/container profile with six samples per side; it is not a production latency or service-level claim. Peak and retained memory were not measured. PostgreSQL does not enforce the local parser's step budget; database timeouts remain caller-owned.

Decision and limits

The byte-index candidate is retained for its measured long-fraction results, with the short compound overhead and Build tradeoff documented. Continue to compare representative ordinary inputs alongside long fractions; do not extrapolate the measured reduction to other PostgreSQL versions, plans, or workloads.