Prefetch research: storage support matrix + measurement harness (loads vs. round-trips baseline)

[jensens]

Part of the research plan for #4350 (see anchor comment there for the full decomposition).

Two prerequisites before touching any core code path: know which storages actually honor the hint, and be able to measure loads vs. round-trips so call-site choices are driven by numbers, not guesses.

All findings below are from a code scan of a Plone 6.2 coredev tree (2026-07-06); line numbers refer to the noted released versions.

Verified ground facts

• ZODB.Connection.prefetch(*args) (ZODB 6.3, ZODB/Connection.py:1081) accepts oids (bytes), persistent objects (uses _p_oid), or iterables thereof. If the storage lacks prefetch, the method replaces itself with a no-op — calling it is unconditionally safe, no feature flag needed.
• The MVCC adapter forwards with the connection's tid (ZODB/mvccadapter.py:192) — prefetch is connection-bound.

Part A — storage support matrix

Verify by code inspection plus one smoke test per storage:

• RelStorage (documents cache prefetch) — ✅ implemented, bulk-fetch of uncached oids; smoke-tested on sqlite backend
• ZEO ClientStorage — ✅ implemented, per-oid loadBefore but pipelined concurrently; smoke test: 50 cold misses → 0
• FileStorage — ✅ confirmed no-op (self-replacing lambda in mvccadapter; MappingStorage/DemoStorage likewise)
• zodb-pgjsonb — ✅ implemented since 1.15 (bluedynamics/zodb-pgjsonb#94), single ANY(...) query (code inspection; local smoke pending, coredev pins 1.13.0)
• Confirm tid semantics per storage — consistent with the connection's MVCC view, see matrix comment

Deliverable: support table posted to #4350 done — see the matrix comment below.

Part B — measurement harness and baseline

• Instrumentation counting object materializations (Connection.setstate calls) vs. actual storage round-trips (wrap storage.load* / use storage-side tracing; zodb-pgjsonb per-span tracing is prior art — bluedynamics/zodb-pgjsonb#96). The ratio objects/queries ≈ 1 identifies N+1; ≫ 1 identifies batched.
• Fixture site: folder with N items, content with N relations, blocks with N resolveuid references.
• Cold-cache vs. warm-cache runs against a networked storage (RelStorage and/or zodb-pgjsonb ≥ 1.15). FileStorage numbers are meaningless for round-trip analysis.
• Baseline the hotspot inventory from the anchor comment: loads-per-render before/after per call site.

Deliverable: hotspot list ranked by measured wins — this ordering drives the sibling sub-issues.

Note for coredev: the current constraints pin zodb-pgjsonb 1.13.0; local benchmarking needs ≥ 1.15 (or RelStorage).

[jensens]

Part A done — storage support matrix

Verified by code inspection of released versions plus smoke tests (Python 3.14, ZODB 6.3).

Storage	prefetch	Mechanism	Verified how
FileStorage / MappingStorage / DemoStorage (ZODB 6.3)	❌ no-op	Neither implements the hook. First call raises AttributeError inside MVCCAdapterInstance.prefetch (ZODB/mvccadapter.py:192-198), which then self-replaces with lambda *a: None. Subsequent calls are attribute-lookup-cheap.	Smoke test: no-op lambda lands in vars(adapter) after first call, second call raises nothing. Safe everywhere, confirmed.
ZEO 6.2	✅	ClientStorage.prefetch(oids, tid) (ClientStorage.py:613) → asyncio client prefetch_co (asyncio/client.py:766-776): filters oids already in the client cache, then issues one loadBefore server call per missing oid, but concurrently on the IO thread (asyncio.gather), populating the persistent client cache.	Smoke test against a live ZEO.server(): 50 cold-cache misses → 0 after conn.prefetch(oids); the call itself returned in 0.02 ms (fire-and-forget).
RelStorage 4.3.0	✅	MVCC-native (no mvccadapter): Connection._storage is the RelStorage instance. prefetch(oids) (storage/load.py:137) → cache.prefetch (cache/storage_cache.py:473): bulk-fetches only the oids missing from the local cache and indexes them; exceptions are deliberately swallowed (it is a hint). Skips work entirely when the local cache is disabled (cache_local_mb = 0) or the instance has not polled yet.	Smoke test on the sqlite3 backend: prefetch resolves to the real StaleAware_prefetch implementation (not a no-op), call succeeds, subsequent loads fine.
zodb-pgjsonb ≥ 1.15	✅	prefetch(oids) → load_multiple: one WHERE zoid = ANY(...) query for the not-yet-cached oids, warms L1/L2 (src/zodb_pgjsonb/instance.py:361-373 @ v1.16.0).	Code inspection + upstream changelog micro-benchmark (bluedynamics/zodb-pgjsonb#94): 151 objects, 151 → 1 round-trips; ~3.1 s → ~24 ms at 20 ms per-query latency. Local smoke pending — coredev currently pins 1.13.0.

tid semantics — confirmed consistent

Connection.prefetch goes through MVCCAdapterInstance.prefetch(oids), which forwards (oids, self._start) — the connection's snapshot tid — to IPrefetchStorage.prefetch(oids, tid). ZEO honors it via loadBefore(oid, tid), so the prefetched state matches the connection's MVCC view. RelStorage and zodb-pgjsonb are MVCC-instance storages (one-arg prefetch(oids)) already polled to the connection's view. No stale-state hazard; and since prefetch only warms caches (never materializes objects), a miss or race degrades to a normal load.

Practical consequences for the other sub-issues

• Who benefits from fewer round-trips: RelStorage and zodb-pgjsonb (true batch query). ZEO benefits differently — same number of server requests, but pipelined concurrently instead of serialized behind each setstate, so wall-clock latency still drops on high-latency links.
• Who pays nothing: FileStorage/MappingStorage users — after the first call the hint is a single no-op attribute lookup. No feature flag needed, as the parent issue assumed.
• RelStorage detail worth documenting later: prefetch is skipped when cache_local_mb = 0; deployments that disabled the local cache get no benefit.