Disposable PostgreSQL dedup contention (CORE-202)¶
Run from the checkout with PostgreSQL optional dependencies installed and Docker available:
The benchmark requires the VM-local Docker endpoint
unix:///var/run/docker.sock. It checks both DOCKER_HOST and the effective
configured current context before creating a container and before SQL access.
Remote TCP/SSH endpoints and alternative sockets fail closed. Docker operations
and readiness checks are pinned to that Unix socket with routing overrides
removed from their subprocess environment, so a later context switch cannot
redirect operations to a different daemon.
The CLI and imported run() helper accept no DSN. A process-local opaque
ownership capability is minted only after inspecting the freshly created Docker
container. Before pool opening and immediately before graph truncation, helpers
recheck its exact container ID, generated owner label, image ID, running state,
and loopback port mapping. The backend must be the exact native backend/pool and
its DSN must match that attested target. Forged or revoked capabilities and
unrelated backends fail before connection acquisition; localhost alone is not
ownership evidence. It creates a uniquely named, loopback-only
pgvector/pgvector:pg16 container with an ephemeral host port and removes exactly
that container and its anonymous data volume in a finally block. Cleanup also
inspects the exact generated name if Docker run creates a container and then
fails, removing only a matching owner-labeled container by its ID. Cleanup
failures preserve an original exception and emit a warning; without an original
exception they fail the run. No staging or production connection is used.
The image tag is mutable; JSON records the exact image ID for comparison.
Retain VM load alongside results. The script verifies PostgreSQL major 16.
Each repeat truncates both graph tables in its owned disposable database, then seeds a fresh tenant corpus of procedural nodes with deterministic 24-character labels and ten percent exact duplicate pairs, plus two episodic writer targets and an edge in both the dedup tenant and an unrelated tenant. The private CORE-196 pass runs unchanged. Four independent thread/event-loop writers update the separate node/edge targets after the pass acquires its table locks. A barrier requires all four writers to be observed waiting on PostgreSQL locks before CPU planning starts. This prevents synchronous Python planning from merely postponing writer submission on the same event loop.
sequenceDiagram
participant B as Benchmark / private pass
participant P as Disposable PG16
participant W as Four independent writers
B->>P: Acquire both SHARE ROW EXCLUSIVE table locks
B->>W: Release ready writer barrier
W->>P: UPDATE node/edge (same and other tenant)
P-->>B: pg_stat_activity confirms four Lock waits
B->>B: Real read / O(n²) plan / apply / final read
B->>P: Commit and release table locks
P-->>W: Complete UPDATE and return server timestamp
B->>B: Record samples and nearest-rank percentiles
JSON reports p50/p95/p99 writer SQL elapsed and observed lock-wait lower bounds for each tenant/table combination, lock hold upper bounds, sample counts, actual merge/survivor counts, and raw trials. Writer SQL elapsed is measured between server timestamps before UPDATE submission and after its conflicting lock is acquired. It includes submission, execution and lock wait; it is not a pure lock wait. The server observations use PostgreSQL pg_locks.waitstart, and the second observation after unchanged plan application gives a sustained lock-wait lower bound before the final read/commit. Lock hold is an upper envelope measured from before lock acquisition to private-pass return after commit, so it includes acquisition, coordination, server observation, and post-commit acknowledgement. Writer timings also include instrumentation overhead. Coordination overhead is reported separately.
The four-writer burst is deliberately contended rather than a sustained production workload. Default five repeats produce only five samples per group, so p95 and p99 are the maximum sample; neither is a statistical tail-latency guarantee. Corpus labels, duplicate ratio, hardware, concurrent VM load, network, and real tenant data all affect results. The 5s lock_timeout limits lock acquisition, not time holding locks through planning.
Every report declares disposable-pg16 evidence, no production/staging latency proof, and an operational gate that remains blocked. The script checks the expected duplicate merge and survivor counts. Unit negative tests reject production and staging evidence labels. This benchmark supplies investigation evidence only; public PostgreSQL dedup and the beat schedule remain closed.
VM observation on 2026-10-05¶
Raw JSON records PG 16.15 on development VM 10.0.10.61, three repeats per size and per writer group. This is synthetic disposable evidence; unrelated active VM agents affected load. At 1,000 nodes each pass merged 100 duplicates and returned 900 survivors.
| Corpus | Same tenant node wait lower bound p50 / p95 / p99 | Other tenant node wait lower bound p50 / p95 / p99 | Lock hold envelope p50 / p95 / p99 |
|---|---|---|---|
| 100 | 115 / 157 / 157 ms | 115 / 157 / 157 ms | 121 / 165 / 165 ms |
| 1,000 | 9,441 / 9,942 / 9,942 ms | 9,441 / 9,942 / 9,942 ms | 9,469 / 9,976 / 9,976 ms |
Node and edge writer results are similar because both graph tables have global table locks. The other-tenant comparison demonstrates interference, not tenant isolation. With three samples per group, p95/p99 equal the observed maximum; the groups share each repeat's lock hold and are not independent samples. The observed waits above five seconds demonstrate why the acquisition timeout cannot justify opening the operational gate.
Ownership review follow-up¶
The original three-repeat observation above remains historical evidence. After adding ownership checks, the same CLI ran successfully on a new disposable PG16 container at 100 and 1,000 nodes with one repeat each. The 1,000-node pass merged 100 and returned 900; server lock-wait lower bounds were about 10.183s. This smoke run verifies the guarded path, not another latency distribution. Refusal tests cover arbitrary/loopback DSNs, forged/revoked capabilities, unrelated backends, changed container attestation, partial Docker creation and cleanup errors. Removing the destructive trial's ownership check makes the pre-connection refusal test fail before any SQL is executed.
The subsequent daemon-locality repair passed 28 focused tests and a fresh 100-node disposable PG16 smoke run. Remote DOCKER_HOST, a configured remote context (including with a local DOCKER_HOST), and daemon changes after token creation are refused before container creation or SQL pool opening. A mutation removing the SQL-boundary daemon check made the configured-context refusal test fail at its pre-pool sentinel, and restoring it returned the suite to green.