# pg_pinecone/pgvector ANN benchmark harness What it measures: filtered and unfiltered ANN search of pgvector (hnsw, ivfflat, forced exact seqscan) against pg_pinecone's native local engine (`pg_pinecone_native` = `CREATE INDEX ... USING pinecone ... WITH (mode = 'local')`), on synthetic clustered data and on real embeddings (SIFT1M 128d, wiki_dpr 768d): tie-aware recall against exact ground truth, latency percentiles, QPS under concurrency, build time and size, churn. The harness runs its **own throwaway Postgres** (`harness/pgctl.py`); it never touches a shared instance and makes no Pinecone call, except the `remote*` configs, which measure the Pinecone-backed lanes (`pg_pinecone_remote`, `pg_pinecone_remote_topk`) against a real serverless index (see "Pinecone-backed leg (box)" below). Evidence velocity over elegance, but reproducible: one entrypoint (`run.py --config ...`), configs as checked-in YAML, results as timestamped JSON plus a generated markdown summary. Write-ups: - `REPORT-residual-filter.md`: iterative scan vs pgvector hnsw for a pushed key plus a residual `WHERE`, 1M x 768d, 2026-10-07 (how to rerun: `configs/README-residual.md`). - `REPORT-halfvec.md`: pgvector FP16 (halfvec) against FP32 hnsw and pg_pinecone native from `main` (0.10.0), 1M x 768d, one AWS box, 2026-10-16 (`configs/halfvec_1m.yaml`; box facts `box/BOX-2026-10-16-halfvec.md`). - `REPORT-halfvec-filtered.md`: the same arms under 1% and 10% filters on the 0.10.0 release, 2026-10-22 (`configs/halfvec_filtered_1m_0.10.yaml`; box facts `box/BOX-2026-10-22-hvfilt.md`). - `REPORT-overram.md`: FP32 / FP16 hnsw and native under a 4 GB memory cap, 2026-10-23 (`configs/overram_1m.yaml`; box facts `box/BOX-2026-10-22-overram.md`). **Last verified:** 2026-10-14 on an AWS `r6id.4xlarge` (PostgreSQL 17.11 PGDG, pgvector 0.8.7, the pg_pinecone 0.9.0 release tarball, Python 3.12.3): `local_1m`, `residual-c1`, `residual-c2`, `residual-ext` (its mode-L system re-run as `residual-ext-model`), `remote_1m`, `remote_filtered`, `remote_partition` and both `remote_lanes_*` configs, all from clean commits (the residual cells' recall equals the 2026-10-07 run within 0.023). On 2026-10-12 (main dc3e29c, M4 Max, pgrx cassert Postgres 17.10, Python 3.14.6) `configs/smoke-native.yaml` ran 50,000 rows in 411 s, 44/44 sweep points valid: pg_pinecone recall 0.812 / 0.879 / 0.930 at `rescore_multiplier` 4 / 10 / 20, hnsw 0.646 / 0.843 at `ef_search` 10 / 40; `configs/ci_tiny_exact.yaml` 12 s, 8/8 points valid. On 2026-10-16, against pg_pinecone 0.10.0 built from main (d1b57ff) with PostgreSQL 17.11 / pgvector 0.8.7 in Docker on a laptop, `configs/smoke-halfvec-native.yaml` ran in 80 s, 76/76 points valid, every native point on its index: `target_recall` 0.90 / 0.95 / 0.98 / 0.99 calibrated (`nprobe`, pool) to (50, 200) / (75, 500) / (75, 1,000) / (75, 2,000) of 224 lists. Datasets via `scripts/download-datasets.sh`: `sift` (25 s; `sift1m.npz` byte-identical to the 2026-09 cache) and `wikidpr` (9 min on the laptop, 7 min on the box; 5.3 GB of shards became a 3.38 GB `wikidpr_768d_1m.npz` whose base set, dataset key and held-out query/bank cluster ids match the 2026-09 artifact's recorded fingerprints; its 160 warmup vectors have none). Not run in any of these: SIFT1M and every config not named here. ## Environment | variable | meaning | default | |---|---|---| | `PG_PINECONE_BENCH_PG` | Postgres 17 install prefix (`/bin/pg_ctl`) with pgvector and pg_pinecone installed | `~/.pgrx/17.10/pgrx-install` (`PGRX_PG_BIN`, the `bin/` dir, still works) | | `PG_PINECONE_BENCH_DATA` | datasets, ground-truth caches and scratch Postgres dirs, under `vector/` | `bench/.data` | | `BENCH_TUNED_CONF` | settings appended to `postgresql.conf` after `initdb` | `pg/tuned.conf` (sized for a 36 GB laptop; `pg/tuned.linux-aws.conf` is the r6id.4xlarge box) | | `BENCH_RESULTS_DIR` | where results JSON and markdown land | `bench/vector/results/` | | `PINECONE_API_KEY` or `EE_ENV` | the key (or a file holding `PINECONE_API_KEY=...`) of the `remote*` configs; never printed or stored in results | unset | | `STATE`, `RAW` | the Pinecone legs' spend tally, events, build state and backfill CSVs | `results/state`, `results/raw` | Results land in `bench/vector/results/` (gitignored): `_.json` plus the matching `.md`. Run every command from `bench/vector`. Only the `remote*` configs call Pinecone; `env -u PINECONE_API_KEY` costs nothing and is used below for the others. ## Setup ``` python3 -m venv .venv .venv/bin/pip install -r requirements.txt # numpy, psycopg[binary], pgvector, pyyaml export PG_PINECONE_BENCH_DATA="$(cd .. && pwd)/.data" ``` ### Install pg_pinecone The harness never builds the engine: it needs a Postgres 17 with pgvector and pg_pinecone installed, in which it runs `CREATE EXTENSION` inside its own throwaway instance. Use a PRIVATE pgrx home so other worktrees' `cargo pgrx install` / `test` cannot overwrite the build under test. Re-install after every engine-side change before trusting a `pg_pinecone_native` result. ``` export PGRX_HOME=/path/to/private-pgrx-home # any empty dir, e.g. $PG_PINECONE_BENCH_DATA/pgrx mkdir -p "$PGRX_HOME" && cp -cR ~/.pgrx/17.10 "$PGRX_HOME/17.10" # a pgrx PG 17.10 that has pgvector installed printf '[configs]\npg17 = "%s/17.10/pgrx-install/bin/pg_config"\n' "$PGRX_HOME" > "$PGRX_HOME/config.toml" (cd ../../pg_pinecone && env -u PINECONE_API_KEY cargo pgrx install --release \ --pg-config "$PGRX_HOME/17.10/pgrx-install/bin/pg_config") export PG_PINECONE_BENCH_PG="$PGRX_HOME/17.10/pgrx-install" ``` The `cp` line assumes `cargo pgrx init --pg17 download` was run once and pgvector installed into it with `scripts/install-pgvector.sh ` (the CI recipe; not run in this pass). That Postgres is a `--enable-cassert` build (stamped per run as `pg_debug_assertions`): fine for plumbing and recall, but absolute latencies carry the assertion tax. For timing runs use a release Postgres built from source (add pgvector with `../../scripts/install-pgvector.sh `). ### Datasets ``` scripts/download-datasets.sh sift # SIFT1M: 168 MB download -> sift1m.npz 521 MB scripts/download-datasets.sh wikidpr # wiki_dpr: 5.3 GB download (4 shards) -> wikidpr_768d_1m.npz 3.4 GB scripts/download-datasets.sh cohere # Cohere wikipedia-22-12 1M, cosine: 2.9 GB HDF5 -> cohere_768d_1m.npz (needs h5py) ``` All land in `$PG_PINECONE_BENCH_DATA/vector/datasets_cache`. The synthetic datasets (everything named smoke, sweep, spotcheck, full) are generated on first use; nothing to download. SIFT1M is only read by configs whose `sift1m:` block is enabled; wiki_dpr only by the `kind: real` configs (baselines_exact_real*, wikidpr_1m_pg_pinecone, followup_c1_wikidpr, residual-*, except residual-smoke). The Cohere 1M file is the one Tiger Data publishes on `s3://vector-datasets`; `download-datasets.sh cohere10m` converts its 10M sibling the same way. ## Run ``` env -u PINECONE_API_KEY .venv/bin/python run.py --config configs/smoke-native.yaml \ --data-dir "$PG_PINECONE_BENCH_DATA/vector/pgdata-smoke" -v ``` `results/_.json` holds the full data, the `.md` the summary (build stats, latency-at-recall tables, raw sweeps, churn, SIFT1M sanity check). A run of record must come from a clean committed tree; `save_results` refuses a dirty one (see "Provenance stamps" below) unless you pass `--allow-dirty`, which marks the file and report DIRTY. **Running two instances at once (e.g. a long baseline series plus a plumbing check) needs a distinct `--data-dir` each.** `PgInstance.initdb()` `rmtree`s whatever it finds at its data directory (default `bench/vector/.pgdata`) before creating a fresh one, so two runs sharing the path delete each other's live data, not just clash on a port (ports are always freshly OS-assigned; see `harness/util.free_port`). Pass an absolute path (the socket directory derived from it is written into `postgresql.conf`). `./cleanup.sh [data-dir ...]` stops leftover scratch instances. ### Configs | config | data | runs | wall time | |---|---|---|---| | `smoke-native` | 50k x 768d synthetic | pgvector hnsw + `pg_pinecone_native` (unfiltered, filtered, one residual cell); the pg_pinecone 0.8 port smoke | 411 s (verified 2026-10-12) | | `ci_tiny_exact` | tiny synthetic | exact-seqscan regression gate before a long run | 12 s (verified 2026-10-12) | | `smoke` | 50k x 768d synthetic + SIFT1M | pgvector hnsw/ivfflat only; the 2026-09 smoke | ~21 min (2026-09 record) | | `smoke_pg_pinecone_native` | 50k x 768d synthetic | trimmed plumbing proof with forced mode L / mode H | not run in this pass | | `residual-smoke` | 50k x 768d synthetic | iterative-scan grid, one residual cell; rehearsal of `residual-c1/c2` | ~7 min (its header) | | `residual-c1`, `residual-c2`, `residual-ext`, `residual-ext2`, `residual-ext-model` | wiki_dpr 1M x 768d | residual-filter benchmark (`REPORT-residual-filter.md`), run from `configs/README-residual.md`; `residual-ext-model` is the mode-L system of `residual-ext` alone | c1, c2: 55 min each on the laptop, 83 and 88 min on the box (ext: 23 min, ext-model: 6 min) | | `local_1m` | wiki_dpr 1M x 768d | unfiltered hnsw, ivfflat, `pg_pinecone_native` and forced exact at k = 10 and 100, 1 / 4 / 16 clients (`k_values`, `sweep_values_by_k`, `max_queries`) | box run 2026-10-14 | | `local_1m_0.10`, `residual-c1-0.10`, `residual-c2-0.10`, `residual-ext-0.10`, `remote_filtered_keep` | wiki_dpr 1M x 768d | the 0.10.0 versions of the configs above: the native grid is `scan_fraction` x `rescore_multiplier` with the calibrated default as its first point, `iterative_scan` `auto` (the new default) beside `off` and `relaxed_order`, `remote_filtered_keep` is `remote_filtered` without deleting the 1M index | 0.10.0 release run on the box | | `smoke-halfvec-native` | 50k x 768d synthetic | rehearsal of `halfvec_1m`: every arm kind in its order (FP32 hnsw, 5x rerank, the four `target_recall` native builds and the 0.95 grid, exact FP32, FP16 heap, exact FP16), k = 10 and 100, 1 / 4 clients; needs pg_pinecone from main | 80 s (verified 2026-10-16) | | `halfvec_1m` | wiki_dpr 1M x 768d | FP32 hnsw; FP16 expression hnsw with and without 2x / 5x / 10x FP32 rerank; FP16 heap hnsw; m16 and m32 builds; `pg_pinecone_native` from main at `target_recall` 0.90 / 0.95 / 0.98 / 0.99 (calibrated defaults, plus a probe x pool grid on 0.95); exact FP32 and FP16 scans; k = 10 and 100, 1 / 4 / 16 clients | 95 min on the box (2026-10-16, `REPORT-halfvec.md`) | | `smoke-halfvec-filtered`, `halfvec_filtered_1m_0.10` | 50k x 768d synthetic / wiki_dpr 1M x 768d | halfvec expression hnsw next to FP32 hnsw and native under the 1% and 10% correlated key with iterative scan; the smoke rehearses it (PostgreSQL 18 and the 0.10.0 pg18 tarball on the box) | box run 2026-10-22 (`REPORT-halfvec-filtered.md`) | | `smoke-overram`, `overram_1m` | 50k synthetic / wiki_dpr 1M x 768d | FP32 hnsw, FP16 hnsw and native on an external Postgres under a cgroup memory cap (`box/overram-pg.sh`), uncapped and 4 GB | box run 2026-10-23 (`REPORT-overram.md`) | | `residual-cohere-c1`, `residual-cohere-c2` | Cohere wikipedia-22-12 1M x 768d, cosine | the residual-filter cells of `residual-c1/c2` on Cohere: pushed 1% / 10% key plus a 0.3% / 1% residual, hnsw and native against the exact scan | box run 2026-10-24 | | `remote-smoke`, `remote-smoke-lanes` | 5k x 768d synthetic | rehearsals of the Pinecone lanes against a real index: AM with a filter key and residuals, direct REST, a partitioned AM index, the trigger lane | 3 to 5 min, about 15 to 30k write units each | | `remote_1m`, `remote_filtered`, `remote_partition`, `remote_lanes_am_100k`, `remote_lanes_topk_100k` | wiki_dpr 1M / 500k / 100k x 768d | the Pinecone-backed leg (staged: `--stage build`, then `--stage measure`); real money, see below | hours (backfill) plus about 1 h of queries | | `baselines_exact`, `baselines_exact_real`, `baselines_exact_real_r50` | synthetic / wiki_dpr | forced-seqscan exact baselines (recall asserted == 1.0) | hours | | `sweep_pg_pinecone_50k`, `spotcheck_pg_pinecone_50k` | 50k synthetic | `pg_pinecone_native` knob sweep and spot check | not run in this pass | | `full`, `full_1m`, `full_1m_mid`, `full_1m_mid_pg_pinecone`, `sift_hard_pg_pinecone`, `wikidpr_1m_pg_pinecone`, `followup_c1_mid`, `followup_c1_wikidpr`, `followup_c5_sift_k512` | 1M synthetic / SIFT1M / wiki_dpr | the 2026-09 records and follow-ups | hours; budget before launching | Only the configs named in the verification above were run. The rest load and generate valid DDL but are unverified on this tree, and the 1M configs also need the datasets above. ## Pinecone-backed leg (box) `pg_pinecone_remote` is `pinecone.create_index(tbl, col, engine => 'remote', metric, index_name[, filter_column][, partition_column])` (what `CREATE INDEX ... USING pinecone ... WITH (mode = 'remote')` runs): the build creates a serverless index on Pinecone (`pinecone.am_cloud` / `am_region`, default aws us-east-1, dimension and metric from the column and opclass), queues a backfill that the background worker drains (`pinecone.worker_naptime_ms`, default 2000), and every query is the plain `ORDER BY embedding <-> $1 LIMIT k` served by the access method. `pg_pinecone_remote_topk` is `pinecone.promote` plus `pinecone.topk(tbl, q, k)`. What the harness adds on top of the sweep machinery (`harness/remote.py`): - **Build and gate.** After `create_index` it polls `pinecone.status()` until the queue is idle in remote mode, counts the vectors Pinecone holds (`describe_index_stats`), calibrates the reported read units per query on the finished namespace, and runs the searchability gate: 100 sampled rows queried with their own vector through the lane under test, at least 99% must come back as their own nearest neighbour. Time-to-searchable is `create_index` to that gate. Backfill rows per second, the worker's CPU seconds and WAL are recorded; the number of upsert requests is **estimated** from the extension's chunking rule (at most 1,000 rows or 1.9 MB of record size per request, counted as Pinecone counts it: 4 bytes a value, the id, the metadata; one request per tenant per 5,000-row window for a partitioned index), because the extension logs none. - **Sweep points.** Same EXPLAIN gate as every system (a plan that does not use the pg_pinecone index invalidates the point), same tie-aware recall against exact ground truth (Pinecone's own recall, since it answers the query), plus `pinecone.last_scan_stats()` per query (`batches` = Pinecone `/query` calls per SQL query). Pinecone documents 100 queries per second per namespace and answers the excess with HTTP 429, which `pg_pinecone` raises as an error and never retries: a failed attempt is retried with backoff, its latency is not counted, and each point reports the rejected share. The box run saw about 245 per second on one namespace before queueing (1M) or 429s (100k). `remote.rest_seconds` pauses between points so the allowance refills. - **Knobs.** `pinecone.am_min_topk` (the first `topK`, also for `LIMIT 10`: default 50 in the 0.9.0 run, 100 from 0.10; a `LIMIT` above it re-queries at `topK` x 4), `pinecone.iterative_scan` and `pinecone.max_scan_tuples` (rows requested from Pinecone, bounded by `pinecone.am_max_topk`, default 10,000) for a residual `WHERE`. - **Direct lane.** The same `/query` over keep-alive HTTPS from Python (`remote.direct`), with Pinecone's own `x-pinecone-request-latency-ms`, the reported read units and the RTT (TCP, TLS, keep-alive GET). - **Partition.** `partition:` in the config adds a `tenant_id` column (100 tenants, each a group of k-means clusters) and a per-tenant workload; per-tenant p50 is the median over tenants. - **Spend.** Every request is tallied in `$STATE/cost-tally.jsonl` (write units from the logical record size, 1 WU per KB with 5 per request, which is what Pinecone reports in `x-pinecone-request-logical-size`; read units as the API reports them, rounded up per query, 429s counted as reads) and the run stops at 6M WU, 1M RU or $40 at the high end of Standard list ($4.50 per M WU, $18 per M RU). - **Cleanup.** `box/pc_indexes.py delete-all --yes` deletes every index whose name contains `pg-pinecone-bench-vec` (the access method names its index `pv--`, so the tables are called `pg_pinecone_bench_vec_*`) and lists what is left. Stages: `run.py --config configs/remote_1m.yaml --data-dir D --stage build` loads the table, builds the index, waits for the backfill and the gate, and stops; `--stage measure` (or `--reuse`) starts the cluster in `D` again, skips the load and the build, re-runs a short gate and measures. `remote_filtered.yaml` runs on the same `D`. Rehearse any change first with `configs/remote-smoke.yaml` and `remote-smoke-lanes.yaml` (about $0.10 each). The box recipe, phases and teardown are in `box/README.md`. ## Not reproducible from this tree - The 2026-09 production-build runs on a GCP VM and in Docker, including their Pinecone-backed leg, which never completed (the free tier's write quota ran out): their scripts are not in this tree. - Absolute latencies: they depend on the machine and on cassert vs release Postgres. The reports say which was used. - The over-RAM workload's Docker variant (`overram/RUNBOOK.md`) was only smoke-checked locally; the capped runs of `REPORT-overram.md` use `box/overram-pg.sh` on a Linux host. - The residual-filter write-up's table scripts and probe logs are not imported. ## Methodology guarantees These are the properties the harness enforces mechanically -- each was a concrete way an earlier version of this harness could lie, found in review and fixed: **One index at a time (no cross-system contamination).** Before each system's build+sweep, every other secondary index on the table is dropped (`harness/workloads.py:drop_all_secondary_indexes`); the results JSON records what was dropped. Churn likewise drops everything and rebuilds only the system-under-test's index (verified via EXPLAIN before the first cycle; hard-fails if the check doesn't hold). **Plan-shape integrity.** Every sweep point captures `EXPLAIN (FORMAT JSON)` at that point's exact session settings and tags the point with its plan shape (`index_scan:` / `seqscan_sort`) and the index actually used. If an index scan uses an *unexpected* index, the point is recorded as `valid: false` with an `abort_reason` and excluded from every derived number. Seqscan points are legitimate data ("the planner gives up here") and are reported as their own labeled series -- never blended into an index's recall/latency curve. This matters: in the clean smoke run, the planner flips hnsw filtered queries to a seqscan at `ef_search >= 80`, and those points score recall 1.000 at ~16ms -- an earlier harness version reported exactly those as the index "reaching recall 1.0". When *every* default-planner point for a filter goes seqscan, the harness re-runs the same grid with `enable_seqscan = off` as a clearly-labeled forced-plan companion series, so "the planner gives up" is told apart from "the index is incapable" instead of only the former being on record. **Iterative-scan budget is swept, not assumed.** Filtered `relaxed_order` scans are bounded by `hnsw.max_scan_tuples` (default 20000) / `ivfflat.max_probes`. That default is comparable to the row count and can cap filtered recall independent of `ef_search` -- so it is swept as its own stamped axis (default vs. `auto` = `max(100000, 10% of rows)`), the effective GUCs are `SHOW`-captured onto every point, and points where recall is flat while the knob rises *and* a higher budget still moves recall are labeled `budget_capped`. This is exactly the earlier "0.855 plateau": that number was the 20000-tuple budget binding, not pgvector's ceiling (see the results summary -- the fair cross-tenant ceiling is ~0.93). **No plan drift within a point.** All connections use psycopg `prepare_threshold=None` (no silent auto-prepare generic-plan switch), and session GUCs are set once per connection per sweep point, not per query. **Calibrated dataset difficulty.** The synthetic GMM (see `harness/datasets.py` and the `dataset:` comment in `configs/smoke.yaml`) is calibrated so the swept knob actually matters: mixed per-cluster scales, a 2% uniform background fraction, and `cluster_sigma` chosen empirically against real pgvector recall curves (naive tight clusters are trivially easy in 768d -- the curse of dimensionality makes "nearest-center impurity" a misleading target; local density is the real lever). Every run records geometry diagnostics (inter/intra distance ratio) and runs an automatic hardness check: if the cheapest sweep setting achieves unfiltered recall > 0.97, the run FAILS LOUD ("dataset too easy — recalibrate") instead of producing flattering numbers. The clean smoke run's cheapest-point recall: hnsw 0.670, ivfflat 0.545. **Honest concurrency.** Workers are OS processes (`ProcessPoolExecutor`, one psycopg connection per process, persistent across a sweep) -- not GIL-sharing threads. All workers barrier after their untimed warmup. `qps_wall` comes from a *separate* duration-based throughput pass: after a second barrier, each worker streams its query set in a loop for at least `min_throughput_seconds` (5s in full, 1.5s in smoke) and counts completed queries -- a fixed 500-query set split 8 ways otherwise finishes in tens of milliseconds, too short a window to measure throughput. Recall and the latency percentiles still come from the fixed measured set (one pass), kept separate from the throughput count. **Fixed measured query set.** The measured pool (`n_queries`, 500 in both configs) is identical at every concurrency; warmup queries come from a disjoint held-out pool (`n_conns * warmup_queries` extra vectors generated alongside the dataset) and are never counted. **Bounded interpolation.** latency@recall interpolates only between two points of the *same* plan shape whose recall gap is <= 0.15; otherwise the report shows the bracketing pair and `n/a` rather than a made-up number. Targets outside the achieved recall range are `n/a` (never extrapolated). **Provenance stamps, machine-checked.** Every results JSON records the git SHA, a `git_dirty` flag (`git status --porcelain` non-empty), a content hash over every tracked file under `bench/vector/`, a stable config hash, `SELECT extversion` for pgvector, per-point plan-shape tags + effective GUCs, and per-filter mask hashes; filtered ground-truth caches are keyed on the hash of the actual mask bytes, not a human label. `save_results` **refuses** to write a run of record from a dirty tree (dev iteration needs an explicit `--allow-dirty`, which tags the filename and banners the report DIRTY); the run of record must come from a clean commit. NaN/Inf are serialized as `null` (strict-JSON safe). ## What's implemented **Datasets** (`harness/datasets.py`, `harness/sift.py`): - Synthetic clustered Gaussian-mixture point cloud (fixed seed; `n_centers`, `cluster_sigma`, `cluster_scale_mix`, `background_frac` knobs), calibrated as described above. NOT uniform random. The GMM centers and per-cluster sigmas are persisted in the dataset cache -- churn and in-region query sampling draw from the same generative model. Held-out queries (measured + warmup pools) are drawn from the same distribution. - SIFT1M (128d, classic ann-benchmarks corpus, ~168MB download from `ftp.irisa.fr`, `scripts/download-datasets.sh sift`) as a real-data sanity check. Downloads, parses, and caches to `datasets_cache/sift1m.npz`; **skips gracefully** (logs why, returns `None`, run continues) if the download or parse fails for any reason -- see `sift1m.available` in the results JSON. - Real-embedding corpora (`kind: real`): a converted npz artifact in `datasets_cache/` (fp32 base vectors + held-out queries/warmup/query-bank + embedded acquisition provenance) plus a cached numpy k-means clustering that plays the GMM cluster-id role -- filter columns derive from k-means membership under the same correlated/uncorrelated rules, and in-region ("tenant") queries are REAL held-out bank vectors from the qualifying clusters (per-filter sampling info persisted, incl. the with-replacement fallback at thin selectivities where the finite bank can't cover the pool). First corpus: `wikidpr_768d_1m.npz` (facebook/wiki_dpr DPR embeddings, 768d x 1M; built by `scripts/download-datasets.sh wikidpr`) -- see `configs/baselines_exact_real.yaml`'s header for acquisition, holdout, and geometry notes. Churn hard-errors on real corpora (it needs the generative model). **Ground truth** (`harness/groundtruth.py`): exact top-100 via brute-force numpy (batched to bound peak memory), cached to disk keyed by a hash of the dataset + metric + (for filtered ground truth) the qualifying-mask bytes. Churn cycles always recompute against current live rows (the whole point is the ground truth moves as the data does). **Filtered search workloads** (`harness/datasets.py`, `harness/loader.py`): one boolean column per (selectivity, mode) pair in `filters.run`. `mode=correlated` picks whole clusters until their combined population hits the target selectivity (qualifying rows are spatially contiguous -- the hard case). `mode=uncorrelated` assigns independently at random. Selectivity is a *target*; realized selectivity is always reported. Correlated filters run **two query regimes** as separate sweeps: `out_region` (global queries, the cross-tenant case -- query lands far from the qualifying region) and `in_region` (queries sampled from the qualifying clusters, the tenant-style case). The clean smoke run shows why both matter: at 1% correlated selectivity with `relaxed_order` at the raised budget, hnsw reaches ~0.928 recall @ ~16ms for out-of-region queries but ~0.989 @ ~0.8ms for in-region ones -- same index, same filter, ~20x latency and a real recall gap. **Churn workload** (`harness/churn.py`): per cycle, re-embeds a % of rows (fresh draw from the row's own cluster's generative distribution -- "same topic, freshly encoded", not a random walk) and delete+reinserts another % with fresh ids drawn from the generative model, with all filter columns populated using the same assignment rules as the base load. Ground truth is recomputed against current live rows every cycle; recall measured before and after `VACUUM`. Churn runs with only the system-under-test's index present, and the EXPLAIN plan shape is re-verified **after every cycle's ANALYZE** (recorded per cycle; a non-index plan fails the run loud). **Systems** (`harness/systems.py`): pgvector hnsw (`m`/`ef_construction` pinned to pgvector docs' recommended defaults, `ef_search` swept) and ivfflat (`lists` via the docs' heuristic, `probes` swept). `hnsw.iterative_scan` / `ivfflat.iterative_scan` run as **separate configurations** on filtered sweeps (`off` vs `relaxed_order`), each with the scan-budget GUC swept as its own axis (default vs. table-scaled `auto`); not swept on unfiltered queries where they don't matter. Plus `type: exact`: the forced-seqscan baseline -- builds NO index, runs every point with `enable_indexscan/enable_bitmapscan = off`, EXPLAIN-verifies `plan_shape=seqscan_forced` per point, and **asserts** recall == 1.0 against the cached brute-force ground truth instead of merely reporting it (an FP16-heap exact scan against the FP16-rounded truth: "pgvector halfvec (FP16)" below) (`configs/baselines_exact.yaml`; these measured exact-scan p50s are the denominators for the filtered-search claims). **`pg_pinecone_native` (`harness/systems.py`'s `System(kind="pg_pinecone_native")`):** `CREATE INDEX ... USING pinecone (embedding [, filter_column]) WITH (mode = 'local', ...)` -- the native local engine. - *Install first, separately.* This harness never builds/installs pg_pinecone -- it assumes the extension is already installed into the Postgres install `PG_PINECONE_BENCH_PG` points at (the binaries `harness/pgctl.py`'s `PgInstance` launches its own throwaway `.pgdata` from); see "Install pg_pinecone" above. Re-install after every engine-side change before trusting a `pg_pinecone_native` result. `PgInstance` always `initdb`s a brand-new data directory per run, so a bench run itself never needs a stop/start restart. - *Filter columns are equality-typed, not boolean.* pg_pinecone's AM only accepts an int2/int4/int8/text/varchar filter column (`am.rs`'s `ambuild`: bare `boolean` has no pg_pinecone opclass and is rejected at DDL time), so `harness/loader.py` writes an int2 companion column per filter spec (`loader.filter_eq_column_name`, `1`/`0` mirroring the SAME mask the boolean column pgvector systems query) -- a `pg_pinecone_native` system's `build_params.filter_column` names that companion column, and `runner.py`'s per-filter loop queries it (`WHERE {eq_col} = 1`) instead of the boolean one. `am.rs` supports AT MOST ONE filter column per index, fixed at `CREATE INDEX` time, so a `pg_pinecone_native` build only ever gets swept against ITS OWN filter column -- `runner.py` records every OTHER filter spec as `not_applicable` for that system rather than silently skip it or (worse) run it anyway as an unfiltered-scan-plus-executor-postfilter and misreport it as a filtered result. Sweeping a different filter cell means a different `pg_pinecone_native` system entry (a different build) in the config, same as sweeping `m`/`ef_construction` would for hnsw. - *Protocol constraints, encoded as assertions:* - Query `k <= 100` (`workloads.run_sweep_point`): `local/scan.rs`'s `ASSUMED_TOP_K=100` fixes the exact-rescore pool size (no real `LIMIT` propagation into the AM), so a larger `k` doesn't error inside the engine -- it silently under-returns. The harness asserts this instead of reporting an artificially low "recall" that's actually a misconfiguration. - Per-measured-query `pinecone.last_scan_mode()` (`workloads._pool_worker_run`): every recall-pass query for a `pg_pinecone_native` system asserts the reported mode (`'flat'`/`'h'`/`'l'`) against `System.pg_pinecone_native_expected_mode`'s prediction (small-N bootstrap vs. filtered-vs-not vs. the estimate-vs-`mode_l_threshold` rule, using the harness's own already- measured realized selectivity) -- not just recorded, asserted, so a silently-wrong scan path shows up as a hard failure at the exact sweep point it happened, not a puzzling recall number three tables later. - Sweep knobs are session GUCs (`pinecone.rescore_multiplier <= 1000`, `pinecone.scan_fraction`/`pinecone.mode_l_threshold` in `[0, 1]`, `_assert_and_set_pg_pinecone_guc` bounds-checks every one before `SET`) -- `System.sweep_param` must name one of the three; `k_bits`/`seed`/etc. are build-time reloptions and can never be `sweep_param` (that would silently rebuild mid-sweep). A `k_bits` sweep is multiple `pg_pinecone_native` system entries (multiple builds), each sweeping a GUC -- same pattern as sweeping hnsw's `m` via multiple systems. `target_recall` (0.10+, a real in `[0.5, 0.999]`) passes through `build_params` the same way; every native build records `pinecone.index_calibration()` (`build.calibration`, `None` on a build without it), and every point `SHOW`s `pinecone.scan_prune`. - *Churn is REINDEX-shaped, not pgvector's in-place live-index churn* -- an HONEST comparability difference, documented at the call site (`harness/churn.py:run_churn_pg_pinecone_native`), not a like-for-like substitute. (Stale since 0.8.0: the engine now has an incremental `aminsert`, `local/insert.rs`; the protocol below was written for an earlier engine and was not re-validated.) Local mode's `aminsert` is a hard-stub `ERROR` (no incremental insert yet), so ANY `INSERT`/vector-column `UPDATE` raises while this system's index exists on the table -- churn can only reach the index via a full rebuild. Per cycle: `VACUUM` (measured; reclaims already- dead TIDs) -> `DROP INDEX` (measured) -> mutate the table exactly like pgvector's churn (safe now, no index to reject the writes) -> `CREATE INDEX` fresh (measured -- **the number to compare**: REINDEX time is counted fully) -> recall against the fresh build. There's no "pre-vacuum recall" here (no live index existed to drift); recall reflects a normal fresh build every cycle, by construction. - See `configs/smoke_pg_pinecone_native.yaml` for a runnable plumbing-proof config (unfiltered + forced-mode-L + forced-mode-H `pg_pinecone_native` systems alongside a trimmed `pgvector_hnsw` sweep, 50k rows) -- run it with `--data-dir` pointed at a scratch directory, never the default `bench/vector/.pgdata`, whenever another run might already own that path. **Incremental checkpoints** (`harness/runner.py:_write_checkpoint`): after every completed workload chunk the runner atomically writes `results/_.partial.json` (marked `partial: true`, no provenance stamps) so a multi-hour run can't lose everything to a crash before the final write; `save_results` removes the partials on success. **Metrics** (`harness/metrics.py`, `harness/workloads.py`): per sweep point, recall@k, p50/p95/p99 + mean latency (ms) from the recall pass; `qps_wall` from the separate duration-based throughput pass; plan-shape tag, effective GUCs, `budget_capped` label, validity. latency@recall per (iterative_scan, scan_budget, plan_shape, n_conns) group under the bounded-interpolation rule above. **Build stats** (`harness/workloads.py:build_index`): wall time, `pg_relation_size`, peak RSS of the leader backend sampled every 200ms (directional -- doesn't count parallel workers), plus any server NOTICEs raised during the build (`build_notices`) and a `graph_spilled_to_disk` flag derived from pgvector's "graph no longer fits into maintenance_work_mem" notice. ## Postgres tuning `pg/tuned.conf` is appended to `postgresql.conf` after `initdb`, pinned for the Mac the harness was developed on (Apple Silicon, 14 cores, 36GB RAM): 8GB `shared_buffers`, 2GB `maintenance_work_mem`, `fsync`/`synchronous_commit` off (data is 100% reproducible -- never worth durability), 7 parallel maintenance workers, autovacuum off (the harness controls VACUUM explicitly). `effective_io_concurrency` left at 0: nonzero fails startup on macOS. A config can override any of these per run via a `postgres:` block (layered last, so it wins) -- `full.yaml` raises `maintenance_work_mem` to 8GB so the 1M-row HNSW build doesn't spill (the spill is captured and labeled if it happens anyway). ## pgvector halfvec (FP16) pgvector's FP16 options against its FP32 hnsw and the native engine, on one table. In a system entry (`harness/systems.py`), `index_type: halfvec` builds hnsw with `halfvec_*_ops`: over the FP32 heap (`storage_type: vector`, the default) on the expression `embedding::halfvec(dim)`; with `storage_type: halfvec` the loader rounds the rows to IEEE FP16 on COPY, and the runner reloads the same rows whenever the next system needs the other heap (so a halfvec run needs `stage: all`). `rerank_multiplier: 2/5/10` fetches that many FP16 candidates per requested row and re-sorts them by the FP32 vector inside the measured query (FP32 heap only); timing, the EXPLAIN gate and the cost probe run the same SQL. An `ef_search` below the candidate count is refused at startup, before the load or any build. The top-level `fp32_column_storage` (`plain`, `main`, `external`, `extended`; unset = the type's default) is set on the FP32 heap's `embedding` column before the COPY; the halfvec configs use `plain`. pgvector's default (`external`) TOASTs a vector over about 2 KB, so at 768d every FP32 vector read by the rerank, native rescore or exact FP32 arms would cost a TOAST lookup the FP16 heap (1,544 bytes, inline) never pays. `plain` keeps 2 rows per 8 KB page, the same bytes on disk, and fits FP32 vectors up to about 1,990 dimensions; the build line stamps the column storage and the TOAST size. FP32 recall is the decision metric. A run with a halfvec system also records, on every point of every system, `recall_halfvec_truth` (the report's `FP16 truth` column: recall against exact search over FP16-rounded corpus and queries, what rounding alone costs) and, per build, heap, index and total relation size; the forced exact scan of an FP16 heap is asserted at 1.0 against that truth, not the FP32 one. A run without a halfvec system records and reports exactly what it did before. `halfvec_1m.yaml` sweeps `ef_search` through at least each rerank candidate count at k = 10 and 100, on m16 / ef_construction 64 and m32 / ef_construction 200 builds: report the recall-latency frontier and the plan shape, not one hand-picked setting. Its native arms are pg_pinecone from main (0.10), one build per `target_recall` (0.90, 0.95, 0.98, 0.99), each queried at the defaults its build calibrated (sweep value 0: no session override) and at `rescore_multiplier` 10 for k = 100; the 0.95 build also sweeps `pinecone.scan_fraction` x `pinecone.rescore_multiplier` to reach recall above its calibration (`docs/reference.md`, "Native engine: calibrated defaults"). Every native build stamps `pinecone.index_calibration()` into its build result and report line. Configs: `smoke-halfvec-native.yaml` (every arm kind of `halfvec_1m` at 50k x 768d, native included: run it on the same pg_pinecone build before the record run), `halfvec_1m.yaml` (the record run, on the box with pg_pinecone built from main: `box/README.md`, "Halfvec session"). The halfvec arms benchmark pgvector; they do not imply pg_pinecone supports a halfvec column. ## Over-RAM workload `configs/overram_1m.yaml` runs FP32 hnsw, the FP16 expression index and native on an external Postgres 17 that `box/overram-pg.sh` starts as a transient systemd unit, so `MemoryMax` (cgroup v2, page cache included) can drop after each uncapped build and lift after the sweep; the result is `REPORT-overram.md`. `overram/RUNBOOK.md` is the earlier Docker-based variant: in 2026-09 a local smoke check (macOS Docker VM, `pgvector/pgvector:pg17`, `--memory=512m`) confirmed that container config works. ## Smoke results (historical: 2026-09-09, clean run after the methodology fixes) Wall time 1261s (~21m) for `configs/smoke.yaml` (50k rows, 768d). The run stamps `git_dirty: False` against commit `8cc2a86`; 125/125 sweep points valid, the hardness gate passed (cheapest-point recall hnsw 0.644 / ivfflat 0.606). Headlines (all from plan-verified index-scan points unless labeled): - **The raised `hnsw.max_scan_tuples` budget reveals the real filtered ceiling.** On the 1%-correlated *cross-tenant* filter with `relaxed_order`, the default 20000-tuple budget plateaus recall at **0.861** (ef=40, ~16.7ms p50, every point auto-labeled `budget_capped`); raising the budget to `auto` (=100000, 10% of rows) lifts it to **0.928** at equal latency. The earlier "0.855 plateau" was the budget binding, not pgvector's capability. - **Tenant vs cross-tenant split** (same index, same 1% filter, `relaxed_order`, raised budget, ef=40, 1 conn): in-region **0.989 recall @ 0.8ms** vs out-of-region **0.928 @ 15.9ms** -- ~20x latency, and the tenant case nearly saturates recall while the cross-tenant case tops out at ~0.93 even with the budget lifted. `iterative_scan=off` collapses cross-tenant to 0.011-0.014 (planner keeps the index at ef<=40), then flips to seqscan at ef>=80 (recall 1.000, ~16ms -- its own labeled series, not index perf). - **Unfiltered, latency@recall (p50, 1 conn):** hnsw 1.24ms @ 0.90, 2.47ms @ 0.95 (recall 0.99 not reached within `ef_search<=160`: max 0.962). ivfflat 10.1ms @ 0.90, 15.2ms @ 0.99. hnsw build 26.9s / 204.8MB; ivfflat 5.1s / 205.2MB. - **Concurrency (8 process workers, duration-based throughput pass):** hnsw unfiltered `qps_wall` 1449 (1 conn) -> 10974 (8 conns) at `ef_search=10`; 480 -> 3013 at `ef_search=160`. This is a >=1.5s measured window per point, not the earlier ~50ms noise window. - **ivfflat under the 1% correlated filter:** the planner *does* pick the ivfflat index at probes=1,2 (recall 0.149-0.293) before flipping to seqscan at probes>=4 -- "low-probe index then planner fallback," not the earlier "all seqscan." (Because real index-scan points exist, the forced-plan companion series is correctly not triggered here.) - **Churn (3 cycles, 5% reembed + 5% delete/reinsert, hnsw, ef_search=40):** plan shape re-verified after every cycle's ANALYZE (all `index_scan`). Recall 0.836/0.824/0.822 pre-VACUUM vs 0.829/0.825/0.808 post-VACUUM -- no clear post-VACUUM recovery signal at this scale. VACUUM dominates cost: 156-162s per cycle for 5k dead tuples on a 50k-row HNSW table. **To re-verify at full scale** -- an observation from a 50k-row run, not a headline. - **SIFT1M sanity (1M x 128d real corpus):** hnsw build 49.4s, 820MB; latency@recall 0.64ms @ 0.90, 1.09ms @ 0.95, 1.95ms @ 0.99. Confirms the harness produces sane recall/latency curves on real data. ### Deltas vs the earlier clean run - The cross-tenant filtered ceiling moved from a **budget-capped 0.86** to a **fair 0.93** once `max_scan_tuples` is lifted -- the number the investment case needs. The earlier run reported the capped value as if it were the limit. - ivfflat filtered is "low-probe index then seqscan," not "all seqscan" as the earlier run stated -- the per-point plan-shape gate shows the planner does pick it at probes=1,2. - 8-conn QPS is now a duration-based measurement, not a ~50ms window. - Hardness 0.644/0.606 (earlier run 0.670/0.545): same regime, both well under the 0.97 gate; churn recall trend and VACUUM cost are consistent. ## Layout ``` bench/vector/ run.py single entrypoint requirements.txt configs/ run configs (*.yaml); README-residual.md = runbook of the residual-filter benchmark pg/tuned.conf appended to postgresql.conf after initdb (tuned.linux-aws.conf / tuned.linux-gcp.conf: the AWS and GCP boxes) harness/ the package (pgctl, datasets, sift, groundtruth, loader, systems, workloads, remote, churn, metrics, runner, report, config, util) scripts/ download-datasets.sh, convert_wikidpr.py, convert_cohere.py box/ the AWS boxes: provision.sh, upload.sh, teardown.sh, conn.sh, step.sh, user-data.sh, install-pg_pinecone.sh, install-pg18.sh, setup-box.sh, phase.sh, settings.py, pc_indexes.py, overram-pg.sh, tables_halfvec.py, README.md, BOX-2026-10-16-halfvec.md, BOX-2026-10-22-hvfilt.md, BOX-2026-10-22-overram.md and their settings-*.json overram/ docker-compose.yml + tuned conf + RUNBOOK.md (the Docker design) kernels/ standalone microbench crate, no dependencies: `cargo run --release` times the Hamming and 1-bit-code kernels (x86_64); `cargo run --release --bin distance` the engine's exact f32 distance kernels (any architecture) cleanup.sh stops leftover scratch Postgres instances REPORT-*.md write-ups results/ timestamped JSON + markdown (gitignored) bench/.data/vector/ datasets_cache/ (datasets + ground truth), pgdata-*/ scratch (gitignored) ```