Cross-Schema Fan-Out Elimination

Maintainer directive (Roland, 2026-08-31): "we should not be doing any cross schema stuff … we need to stop these fan-outs — find them and eliminate." This page is the census, the measured mechanism, and the elimination plan. It exists because the evidence was expensive to assemble — live pg_stat_activity sampling from inside a production portal, Loki correlation across two meshes, and a call-site hunt across two repos — and must not be re-derived.

What a cross-schema fan-out is

Every mesh partition (top-level space: Store, Doc, rbuergi, …) is its own PostgreSQL schema with its own mesh_nodes and satellite tables (access, notifications, threads, activities, …). A query whose path:/namespace: first segment names a partition is pinned to that one schema. A query with no concrete first segment cannot know where its answer lives, so PostgreSqlCrossSchemaQueryProvider generates one UNION ALL over every row of public.searchable_schemas — 188 schemas on memex-cloud as of 2026-08-31 — and pays for all of them regardless of where the rows are.

Routing detail that matters for the census: PostgreSqlPartitionedMeshQuery.NeedsFanOut routes every satellite-table query through the fan-out provider even when anchored (the pedestrian provider cannot see satellite tables); an anchored query then runs against a one-element schema list (the pinned fast path). So an anchored satellite query is fine — the population to eliminate is exactly the queries with no concrete first segment.

Why it is a lock bomb, not just a slow query (measured 2026-08-31)

Sampling pg_stat_activity from inside the systemorph portal during ordinary evening load, with the 08-31 rebake (issue #2895) writing concurrently:

• Every slow SELECT was waiting on LWLock/LockManager — not I/O, not rows.
• The rebake's INSERT INTO "…"."mesh_nodes" … ON CONFLICT sat in Lock/relation, blocked by the fan-out SELECTs' shared relation locks (blocking pids listed four SELECT backends).
• Pinned point-reads (WHERE namespace=$1 AND LOWER(id)=$2 …) waited 2–5 s on LWLock/LockManager — innocent victims.

The mechanism: one 188-schema UNION ALL takes heavyweight locks on ~500+ relations (188 tables plus their indexes). A backend has 16 fast-path lock slots; everything beyond goes through the shared lock manager's partition LWLocks. A handful of concurrent fan-outs means thousands of colliding lock acquisitions — the LockManager serializes, and every other query on the database queues behind it, pinned or not. This is why "the portal is completely unresponsive" (memex, 2026-08-31 ~18:30Z: 399 SubscribeRequest 60 s timeouts in 40 min, Store/Plugin hub starved, instance-key resolution failing, /api/plugins 503) and "every page has a ~2 s floor" (#2640) are one defect. Eliminating the fan-outs removes both the direct cost and the collateral.

Loki, memex-cloud, 40 min, capped sample: 2 583 × [CrossSchema] SLOW — mesh_nodes ~1.7 s, access ~1.9 s, notifications ~2.0 s (2 286–4 203 rows), threads — each "188 of 188 partition schema(s)".

The census — who issues unanchored queries on a render path

🚨 Rows 6 and 7 were invisible to the static census, and that is the lesson of #3545. MeshWeaver.Plugins' UnanchoredQueryAllowFileTest and ShellQueryShapesTest scan src/ — the compiled projects. The Hosting callers in row 6 live in NodeType Source/*.cs trees, which compile at runtime in the portal and which no census reads, and the search box's free-text query starts with *, which the shell census does not recognise as a query. So they surfaced only as log lines. And the log incident folded every unanchored caller onto ONE fingerprint — the exception quotes the query and the masking rules ate the only token that told callers apart — so #3545 reopened under its *Post title whatever anybody fixed. The identity now keeps nodeType: terms (MeshWeaver.Plugins 8944f145e, 2026-09-13); a caller with no nodeType: term, like the search box, still shares one fingerprint with every other such caller. Read an unanchored incident's samples, never its title.

🚨 Merged and published is not running — measured 2026-09-16, and it is why #3545 still reopens. Admin/_LogIncident/d4c8f6f74ecfa422 is the pre-change identity (category + event id + exception type; its normalizedDetail is empty), and it was still advancing at 21:21Z with 5 723 occurrences. So the running log watcher still computes the old fingerprint, and every unanchored query from every caller still lands on #3545. None of its ten retained samples (20:49–21:21Z) is the row-4 shape:

• 7 are the FleetWatch roster from before MeshWeaver.Plugins#1766 (nodeType:Hosting/Deployment scope:subtree -status:Decommissioned select:path,id limit:500).
• 2 are a bare nodeType:Hosting/Deployment.
• 1 is an operator search (name:*Social* …). #4274 refuses that shape, but the incident's namespace is memex-cloud, whose image (core c84c6c05) predates that change.

None of the 75 lines the bot quoted in its 25 reopen comments since 2026-09-12 is nodeType:*Post either. Ten samples out of thousands cannot prove that one caller has gone quiet, and a pod that never loaded the fixed module would still report under the same id. So a #3545 reopen is inconclusive about row 4 either way: every unanchored caller shares it, so it cannot show a regression, and it cannot show the absence of one. Only a sample names the caller. Before acting on one:

1. Check whether the old id still advances. While it does, the per-caller incidents cannot exist.
2. Read the query quoted in the newest sample on the incident node. Do not go by the issue title or the lines in a reopen comment. Name the caller that sends that query.

Resolved 2026-09-19 — the site went quiet, and #3545 closed on that. Final tally over the issue's whole life: 114 query strings quoted across every fold and reopen comment since 2026-09-12 — 96 the FleetWatch roster query, 10 a bare nodeType:Hosting/Deployment, 8 others, and 0 containing Post. Admin/_LogIncident/d4c8f6f74ecfa422 stopped at lastSeen 2026-09-19T05:15:30Z on occurrences: 6415, and the stop is real rather than an addressing artefact: before ~11:41Z (when the portal began re-addressing reports) a burst would have advanced that node, after it a successor node with this category would exist, and content.category:*PartitionedMeshQuery* finds none — count: 2, truncated: false, coverage.partitions: ["admin"], this node plus the site's bodyless twin. Ingest liveness over the same window is evidenced by memex-cloud lines captured at 06:30:42Z and 08:54:16Z. So zero unanchored fan-out lines of any shape on that portal for ~14 h.

🚨 And the prediction this page recorded — "once the watcher rolls, per-caller ids appear" — is still unmet; do not read #4443 as evidence that it shipped. That ticket is the SAME log site's second bucket: the 2026-08-09 discriminator switches on (exceptionType, topFrame), so the exception-bearing lines hash to d4c8f6f74ecfa422 (#3545) and the bodyless ones to SHA256("{category}\n0\n")[..8] = 5d52ad4396af9a59 (#4443, titled after the nodeType:Skill sample it happened to be filed from). Two catch-alls, one site, neither per-caller. Mechanics, instruments and the traps in using them: Log-watch triage → "One log SITE holds TWO buckets" and "The portal RE-ADDRESSES a reported fingerprint".

Each of these small sets is tiny and rarely changing — the fold's global reads return under ~50 rows; the bell's thousands of rows are its own defect — fetched the most expensive way the storage layer has, per render.

What #3093 changed underneath this census

The fold's ANCHORED legs were per-SCOPE, and that made their count — not their fan-out — grow with the mesh's read volume: a node's own path is the leaf of its own scope chain, so every node ever permission-checked minted its own live $security-access:{path} + $security-policy:{path} query. They are now per-PARTITION (path:{partition} scope:descendants …), which is where _Access and _Policy actually live. Measured: RLS-filtering a 4-node listing opened 13 security queries and a 32-node listing 69; both are 5 now. Rows 2 and 3 of the census are unchanged — the global legs still fan out, and Unanchored Security Reads says why they must.

The 2026-09-02 census — memex-cloud on ci.7616, after #3125 (#2194)

Maintainer directive (2026-09-02 19:40Z): "profile it and improve" — the portal was still slow after rolling to ci.7616, which carries #3125's per-partition fold. Measured on the new pods: the portal pods were light (0.1–1.3 cores each) while Azure Postgres memexaks-pg (Standard_D8ds_v5) ran at 94–98 % CPU with 225–292 active connections; [CrossSchema] SLOW averaged 4.0 s (max 9.8 s), 2 917 lines in five minutes across 8 pods. So the bottleneck had moved entirely into the database, and the fan-outs were what it was doing. The shapes, by count in that window (the shape is what DescribeQueryShape logs — nodeType path scope), each attributed to its reader:

The multiplier — why a "process-wide cached subscription" shows up 170 times in five minutes. A fan-out live query re-runs when a change notification is relevant, and PostgreSqlPartitionedMeshQuery.FanOutQuery's relevance filter classifies a notification by its Entity: a MeshNode is matched against the query (_evaluator.Matches, node type included), but n.Entity is not MeshNode → return true — "unclassifiable — re-query rather than miss it". Every notification that arrives from ANOTHER process is unclassifiable by construction: PostgreSqlChangeListener.cs:211 builds new DataChangeNotification(path, kind, null, …) because the pg_notify('mesh_node_changes', …) payload carries only path and op. On an 8-pod portal 7/8 of all writes arrive that way, so every write anywhere in the fleet re-runs every unanchored live query on every pod — memberships, roles, gated types, the root policy leg, the user directory, the UI-contribution catalog, and every circuit's bell. That is the 172 / 179 / 53 / 39 above, and it is why the fold's globals were "the single biggest DB load" on code that reads them once per process. The lever is in MeshWeaver.Plugins: put node_type on the notify payload and classify the cross-process notification by it (a node type that no fan-out query names cannot enter its result set), keeping the fail-safe re-query for a payload without one. It removes the multiplier from every fan-out that survives, and it is independent of anchoring.

What moved (this change, core):

• The root policy leg is spelled path:_Policy nodeType:PartitionAccessPolicy (SecurityQueries.RootPolicy) instead of namespace: id:_Policy …. The two name the same node (namespace "" + id _Policy IS the path _Policy), but the old spelling had no first segment, so the router UNION-ed every partition schema for it — 179 times in the window, every one necessarily empty: an unregistered _-prefixed first segment is unroutable, so no write can land a root _Policy row on Postgres at all. With a first segment the router never fans out; today it answers empty (nothing registered for _Policy), and registering _Policy as a global satellite the way _Access is would make the read pinned without touching the query.
• The root grants leg is SecurityQueries.RootAssignments and is reclassified: _Access is a REGISTERED global satellite (DefaultPartitionProvider → schema system_access), and the router resolves a _-prefixed first segment through that registry — so this leg was served by one schema all along. The census on both pages called it a fan-out on the strength of a comment; it never appeared in the Loki shape counts, which is the measurement that should have been consulted.
• SecurityQueryShapesTest now classifies every fold shape into the router's three real outcomes (Pinned / Unroutable / FanOut), pins the four measured path:- shapes as never-the-fold's, and mirrors the global-satellite registry; SecurityQueryRootLegRegistryTest asserts that mirror against the real registry of a running mesh.

The 2026-09-16/17 content sweep, and the census that now covers it

Rows 8 and 9 came from a sweep of the NODE CONTENT of the six satellite repositories plus MeshWeaver.Plugins: every nodeType: string literal in a NodeType's Source/*.cs and in an executable Code node's content.code, judged by the same predicate the planner uses. That predicate is PostgreSqlPartitionedMeshQuery.Judge over ParsedQuery.IsSufficientlySpecified, and it SERVES a query on any of four grounds — a concrete path:/namespace: first segment, several path:s (ParsedQuery.Paths), a wildcard-namespace filter (ExtractNamespacePatterns), or partitions:all — plus a fifth outside it, a registered QueryRoutingRule that names the partition the text does not (nodeType:User → Auth). Content is the blind spot rows 6 and 7 named: it compiles at RUNTIME, so dotnet build never sees it and UnanchoredQueryAllowFileTest, which scans src/, never read it.

The census now covers it. UnanchoredContentQueryCensusTest (src/MeshWeaver.Hosting.PostgreSql.Test, MeshWeaver.Plugins) applies that predicate to every module folder's sources and scripts, and its negative control feeds it the exact pre-fix board and script lines — the script's inside a Code node's JSON — and asserts they are flagged while their anchored, declared and rule-pinned forms are not.

What the sweep found, beyond rows 8 and 9:

The picker resolves its own reach (2026-09-17)

The biggest population the sweep found was not a caller but a CLASS: 227 [MeshNode("nodeType:X")] declarations across the satellites. A picker is declared by naming the TYPE it offers, and that statement says nothing about WHERE those nodes live — so MeshNodePickerView sent the text verbatim and every dropdown open ran the lock-bomb shape. On a host whose policy REFUSES it, the view's own Catch turned the refusal into an empty dropdown: the picker offered nothing and said nothing.

Anchoring 227 attributes would have been the wrong shape, and {node.namespace} is not the fix either (it resolves to the edited node's own path, which searches one node's subtree). The attribute is a statement about the TYPE; where to look is the picker's question, and PickerQueryReach (MeshWeaver.Plugins, src/MeshWeaver.Blazor.Graph) answers it where the query is built — one anchored leg per reach:

1. every location the TYPE DECLARES (NodeTypeDefinition.InstanceLocations → INodeTypeInstanceLocations);
2. the partition of the node being EDITED;
3. the partition of the picked type (Reinsurance/Currency → Reinsurance).

Two shapes are left exactly as written: a query the planner already reads as specified (ParsedQuery.IsSufficientlySpecified), and one a QueryRoutingRule pins — nodeType:User is served from Auth, so anchoring it to the edited partition would break the user pickers rather than fix them. When nothing can be named at all, the query goes out unchanged: fail-open, slow, never silently partial, exactly as INodeTypeInstanceLocations promises.

🚨 It narrows what a picker OFFERS, and that is the point. A picker exists to offer what you can reference from HERE; a node living outside the three reaches is no longer offered, and an author who needs it has three ways to say so — declare the type's instance locations (best: it fixes every picker of that type at once and narrows the planner's fan-outs too), spell the reach in the attribute, or declare partitions:all where every partition genuinely is the answer. What is not on that list is keeping the silent fan-out, because a dropdown is a render path. Checked against the measured homes: Reinsurance/Currency, LineOfBusiness and Ifrs17/AocType live in the module partition (reach 3), ILS/Tranche under ILS/Deals (3), Reinsurance/Broker in ReinsuranceDemo/Brokers (reach 2 while editing there) and Reinsurance/Samples (3).

🚨 It narrows only a query that NAMES a type, and the copy/move picker is why. A destination picker asks context:create — no type, no anchor — and somewhere else is precisely what a destination means; narrowing it to the partition being edited would have broken the one picker whose purpose is to leave it. So the resolution rewrites only the declaration class it exists for, and the three destination pickers (CopyViews, MoveViews, MeshDataSourceLayoutAreas) now DECLARE partitions:all instead of being unanchored — which also takes them out of the refuse-at-runtime class they were in before any of this. A wildcard or alternation type (nodeType:*Post, nodeType:(A OR B)) names no one type either, so the reach stays out of it rather than guessing.

The guards are PickerQueryReachTest (the project that owns the picker; every leg asserted against the planner's own predicate, the pre-fix shapes as the negative control) and, in the content census, ThePickerResolvesItsReach — the picker exemption is only sound while the view sends the RESOLVED queries, so the census checks that rather than trusting its own comment.

Two blind spots of the content census, stated rather than hidden. The predicate is a line window, so an unrelated path:/namespace: within four lines hides a literal: MeshWeaver.Plugins' AiSettingsAreas "visible models" read (nodeType:LanguageModel sort:name limit:200, beside an anchored settings read) and MailboxAreas.Query's empty-scope branch are both unanchored and both invisible to it. And a query assembled without a "nodeType:…" literal is not a candidate at all.

Corrections to the unanchored-queries.allow rows, from the same sweep (each still listed, so each is still debt): DeviceSeed's instances live at Instance/{id}, not "the device user's partition", so that row anchors rather than declares; TokenUsageSettingsTab is admin-only and groups by person, so anchoring it to the viewer's partition — what its line suggests — would break it, and it should declare; and nothing in MeshWeaver.Plugins calls ChatHistorySelector's query any more, so that row's caller can be deleted rather than fixed.

The elimination plan

1. The bell: deliver notifications to the RECIPIENT's partition

Today NotificationService.CreateNotification writes {mainNodePath}/_Notification/{id} — under the entity that notified, scattered mesh-wide — and the bell reads every notification the viewer can see with an unanchored, unbounded, live query. There is no recipient-side store (NotificationRule/NotificationChannel under the user feed the triage agent, not routing).

The fix is the data model: a notification is addressed — deliver a copy (or the record itself) to {recipient}/_Notification/{id} at creation, and the bell becomes namespace:{viewer}/_Notification nodeType:Notification — pinned, one schema, small, and the live subscription's change feed narrows with it. Open questions the implementation must settle: who the recipients of an entity-scoped notification are (watchers? grant-holders?), migration of existing rows, and the panel's grouping (it already groups by source path, which survives).

🚨 This plan is now worked out in full, with the write-side measurement it was missing: Addressed Notifications (#3156). What it adds — the live distribution (of the newest 200 notifications on memex-cloud, 124 are plugin-update notices under Plugins/{pkg}, 60 are startup-import failures under a space, 12 are thread completions under a thread's context partition, and six are in a user's own partition), the fact that Notification has no SatelliteAccessRule so visibility is path-based and not MainNode-derived, the two-namespace anchor ({viewer} + Admin) the alternation resolver can narrow, the four product rulings the change needs, and the derivable migration. It also records that the shape is no longer merely expensive: Plugins #1231 refuses an unanchored query at runtime, and Plugins #1263 grandfathers this one as the FIRST line of the shrink-only src/MeshWeaver.Hosting.PostgreSql/unanchored-queries.allow — deleting that line is the acceptance test.

2. The security fold: one materialized global set, invalidated by the change feed

SecurityQueries' own doc explains why its globals are path-less (a GroupMembership lives under the group, the grant that names the group elsewhere) and why they must never be truncated (a paged membership read makes a group deny fail open — #2011). So neither anchoring nor paging is available; caching/materializing is the only lever, and it must be invalidation-correct or a revoked viewer keeps their old permissions.

The precedent already in the schema: public.partition_access — a public, write-maintained table the per-schema access clause reads on every query. The same shape serves the fold: a public materialization of the security-relevant rows (Roles, GroupMemberships, root-scope AccessAssignments/_Policys, gated-type identity rows), maintained on the owning hubs' writes, read by the fold as ONE single-table query. Invalidation is the write path itself (the table IS the store), so there is no staleness window to reason about — the delicate part is backfill and the write-path coverage test: every code path that writes one of those node types must also land in the materialization, pinned by a test that enumerates the types (SecurityQueries.AllShapes already exists as the census of shapes to cover).

3. The guardrail so the population never grows back

Once 1–3 land: the fan-out log line (which since Plugins #1035 names the query shape) feeds a ratchet — a periodic check (or a pg_stat_statements-based gate once the extension is enabled) that fails loudly when a NEW unanchored shape appears on a render path. The provider's own FanOutQuery live re-query on change notifications multiplies every surviving fan-out by the mesh's write rate — which is how the 08-31 rebake turned a floor into an outage — so the ratchet is not optional hygiene.

Order of work

1. Plugins #1035 — the shape on the log line (landed; identifies population #4 and any stragglers).
2. Plan 2 (fold materialization) — core; removes the per-render access + mesh_nodes globals.
3. Plan 1 (addressee delivery) — core NotificationService + Plugins bell/panel (landed 2026-09-03, #3156/#3216/#3238): a notification is delivered to its ADDRESSEE's partition, so the bell is two pinned reads instead of a 201-schema UNION — and the platform bell, which the fan-out could never reach because Admin is excluded from searchable_schemas, is finally readable by an operator. Addressed Notifications.
4. The ratchet (plan 3).

🚨 Read Unanchored Security Reads before touching plan 2. It is the companion to this page and it says which of these fan-outs must NOT be eliminated the obvious way: anchoring the fold's global reads to the viewer's partition is truncation, which makes a group-derived permission vanish AND a group-scoped deny fail open, with nothing logged and nothing failing. It also carries the per-lever verdicts (what is tractable, what needs a decision) and the executable census SecurityQueryShapesTest pins.

Declared instance locations (#3039) — enabling infrastructure, NOT the fix for the seven

Plugins#1127 added a fourth fan-out narrowing to PostgreSqlPartitionedMeshQuery: a NodeType DECLARES where its instances live, and an unanchored nodeType:X query intersects the declared partitions with the schemas it was going to UNION. Core #3039 supplies what that planner was missing — the declaration and its projection:

• NodeTypeDefinition.InstanceLocations (MeshWeaver.Graph.Contract): namespace:/path: query strings, authored on the type, shipped with the package that owns it, round-tripping through JSON and a package install like every other field.
• INodeTypeInstanceLocations (MeshWeaver.Graph.Contract) and its in-box implementation NodeTypeInstanceLocations (MeshWeaver.Graph, registered by AddGraph()): the static fold over the builder-registered definitions plus a dynamic lane fed by each definition node's OWN hub while it is live on the process — an entry follows every edit and is forgotten with the hub, so the projection can never serve a stale (under-stated) declaration. Undeclared, unknown, or another-silo definitions answer null: fail-open, the query fans out in full — slow, never partial.
• The authoring gate. NeverNarrowedNodeTypes (MeshWeaver.Mesh.Contract, hoisted from Plugins so there is ONE list) names the fold's types — Role, GroupMembership, AccessAssignment, PartitionAccessPolicy, plus every MeshConfiguration.NodeTypeGates type. InstanceLocationDeclarationValidator refuses a declaration on any of them at Create/Update, and the static fold throws for an in-process one, both naming the reason: in the fold a short read is a vanished grant (#2011) or a deny that fails OPEN (Unanchored Security Reads). The planner refuses the same set again at query time, so a declaration that slipped past both would still be inert — the gate exists so it is a red import instead.

🚨 This does not remove any of the fan-outs in the census above, and cannot. The seven shapes measured on 2026-09-01 (Plugins Hosting/NodeTypeInstanceLocations) live in per-partition satellite containers — {any}/_Access, {owner}/_Thread, {mainNode}/_Notification, per-user _Email — or ARE the fold (refused outright). No honest declaration narrows them: namespace:*/_X correctly resolves to "cannot narrow". What removes them is still this page's own plan, in its order: the fold materialization (plan 2), addressee-side notification delivery (plan 1, landed), then anchoring Thread/UiContribution/Email at their call sites. The declaration serves the types that DO have a home — an Admin/Menu entry, a package's own dimension types — and any future type whose author can say where it lives.

Plugins follow-up (the planner already resolves the interface with GetService, so nothing is wired until this lands): src/MeshWeaver.Hosting.PostgreSql/NodeTypeInstanceLocations.cs drops its own INodeTypeInstanceLocations and NeverNarrowedNodeTypes in favour of core's (using MeshWeaver.Graph.Configuration; / using MeshWeaver.Mesh.Security;), keeping only DeclaredNodeTypeInstanceLocations, the test fixture.

Related: issue #2640 (the per-page floor this eliminates), #2876 (a transient connect timeout took a whole area render down — the same fan-out, from the render side), #2895 (the rebake write storm whose mutual blocking with the fan-outs produced the 08-31 outages), #2011/#2048 (why the fold must never be paged), Plugins #1035 (the shape log).