Research: use ZODB Connection.prefetch to batch object loads (relations, contentlisting, catalog results) — eliminate common N+1 patterns

[jensens]

Summary

ZODB has had a batch-load hint for years: Connection.prefetch(objects_or_oids) calls storage.prefetch(oids); storages that implement the hook can fetch all not-yet-cached objects in one backend round-trip and warm their caches, so the per-object setstate loads that follow become cache hits. When a storage does not implement it, ZODB installs a no-op — calling it is safe everywhere, no feature flag needed (ZODB/Connection.py, prefetch).

Plone never calls it. As a result, the most common object-access patterns in core and ecosystem code are sequential N+1 loads: one storage round-trip per object, per relation target, per brain-getObject(). On fast local storage that is masked; on any networked storage (RelStorage/pg, ZEO, zodb-pgjsonb) it is the dominant render cost — and under thread concurrency each round-trip additionally pays GIL re-acquire latency, which stretches ~1 ms round-trips to 20–160 ms wall (profiled: bluedynamics/zodb-pgjsonb#98).

This issue proposes research into where Plone core should prefetch, not a specific patch.

Real-world numbers (production, 15-site Plone 6.2, PostgreSQL-backed storage)

Batching the relation-target wake-ups in three of our tiles (a ~10-line helper, see below):

pattern	before	after
"pins" tile: 44 relation targets	44 sequential loads	1 batch query
footer sponsor logos: 18 relation targets	171 object fetches, ~260 ms cold per page render	1 batch + residuals
imageset tiles: 6–13 relation targets per content page	6–13 sequential loads	1 batch query

Per-span tracing (objects fetched vs. real queries) made these visible; the same instrumentation shows the classic shapes everywhere: objects/queries ≈ 1 = N+1, ≫ 1 = batched.

The wake-free helper pattern (relations)

For z3c.relationfield relations you do not even need to wake anything to learn the target oids — the intid utility's KeyReferenceToPersistent stores .oid:

def prefetch_relation_targets(relation_values, jar, intids=None):
    if intids is None:
        intids = getUtility(IIntIds)
    refs = intids.refs
    oids = []
    for relation in relation_values or []:
        to_id = getattr(relation, "to_id", None)
        if to_id is None:
            continue
        keyref = refs.get(to_id)
        oid = getattr(keyref, "oid", None) if keyref is not None else None
        if oid is not None:
            oids.append(oid)
    for start in range(0, len(oids), 500):
        jar.prefetch(oids[start:start + 500])
    return len(oids)

For catalog result sets the oid is directly derivable per brain in some catalog implementations; plone-pgcatalog already auto-prefetches the next 100 brains' objects on the first brain.getObject() of a result set — prior art that this works transparently.

Research questions

1. Storage support matrix. Which storages implement IStorage.prefetch today? (RelStorage documents cache prefetch; ZEO/FileStorage presumably no-op; zodb-pgjsonb ≥ 1.15 implements it.) A no-op is harmless, but the matrix determines who benefits.
2. Highest-value core call sites. Candidates:
   • plone.app.contentlisting / ZCatalog result sets: prefetch a window of getObject() targets (mirror plone-pgcatalog's result-set behaviour storage-agnostically).
   • Relation fields (z3c.relationfield accessors, plone.app.relationfield widgets/views, related-items viewlet): the wake-free helper above.
   • plone.restapi serializers (relations, blocks resolveuid, batched listings).
   • Folder listings / navigation building.
3. API shape. A plone.base/plone.api helper (prefetch(objects_or_relations_or_brains))? Automatic-with-window in contentlisting? Both?
4. Semantics to document. prefetch warms the storage cache, not the ZODB pickle cache — setstate still runs per object, but hits the cache; the win is round-trips. Only prefetch what will actually be rendered (batching/pagination).

Context / prior art

• zodb-pgjsonb prefetch hook + measurements: feat(instance): implement the ZODB prefetch hook for batch loading bluedynamics/zodb-pgjsonb#94, Expose a real PG query counter (_pg_query_count) — _pg_load_count counts objects, so batching/prefetch is invisible to observability bluedynamics/zodb-pgjsonb#96, Live workers: shared (L2) cache almost never serves; per-load setstate waits ~19ms despite ~1.5ms PG latency bluedynamics/zodb-pgjsonb#98 (GIL-convoy analysis: why round-trip count matters more than round-trip time under load)
• plone-pgcatalog result-set auto-prefetch (load_multiple window on first getObject())

[jensens]

Research plan — decomposed into sub-issues

Based on a code scan of a Plone 6.2 coredev tree (2026-07-06). The plan is split into six sub-issues (attached below as GitHub sub-issues):

Sub-issue	Scope	Depends on
#4351	Storage support matrix + measurement harness (loads vs. round-trips baseline)	—
#4352	Shared prefetch() helper API in plone.base	—
#4353	Relations track — wake-free batch loads via five.intid keyref oids	#4351, #4352
#4354	Catalog result sets / contentlisting — oid strategy decision	#4351, #4352
#4355	Folder children — orderObjects wakes every child	#4352
#4356	Blocks resolveuid + linkintegrity — catalog-query N+1 (partly orthogonal)	#4351

Suggested order: #4351 → #4352 → #4353 (proven in production, wake-free) → #4355 (small, bounded) → #4354 (biggest surface, needs a strategy decision) → #4356.

Key facts established by the scan

• Connection.prefetch is safe everywhere (no-op self-replacement without storage support, ZODB/Connection.py:1081, ZODB 6.3) and accepts oids, persistent objects, or iterables.
• Relations are wake-free: five.intid's KeyReferenceToPersistent stores the target oid as a plain attribute (five/intid/keyreference.py:103) — intids.refs[to_id].oid never wakes the target. Fallback for plain zope.keyreference: keyref.object._p_oid (ghost _p_oid access does not activate).
• Catalog brains carry no oid — getObject() is path traversal, the RID is catalog-internal. Result sets need a separate strategy (Prefetch research: oid strategy for catalog result sets / contentlisting #4354); ghosts collected via parents are the leading candidate.
• Folder children are ghosts with free _p_oid — wake-free prefetch (Prefetch research: folder children — orderObjects wakes every child #4355).
• Navigation/navtree building is already brain-native (no object waking) — verified, nothing to do there.
• No prefetch call exists anywhere in scanned Plone core today.

Semantics to document (docs task, folded in here rather than a sub-issue)

Once helpers land, document in plone.base and the docs.plone.org performance guide:

• prefetch warms the storage cache, not the ZODB pickle cache — setstate still runs per object but hits the cache; the win is round-trip count, which dominates under thread concurrency (GIL-convoy analysis: bluedynamics/zodb-pgjsonb#98).
• It is a hint, never load-bearing for correctness; helpers must not raise on odd input.
• Only prefetch what will actually be rendered — respect batching/pagination windows.
• Prefetch is connection-bound (MVCC tid); cross-DB mounts must be skipped (five.intid keyrefs carry dbname).