Contents
- pg_pinecone/pgvector ANN benchmark harness
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 (<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): <name>_<timestamp>.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 <pg_config> (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 <pg_config>).
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/<name>_<timestamp>.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() rmtrees 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_indexit pollspinecone.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 iscreate_indexto 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/querycalls per SQL query). Pinecone documents 100 queries per second per namespace and answers the excess with HTTP 429, whichpg_pineconeraises 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_secondspauses between points so the allowance refills. - Knobs.
pinecone.am_min_topk(the firsttopK, also forLIMIT 10: default 50 in the 0.9.0 run, 100 from 0.10; aLIMITabove it re-queries attopKx 4),pinecone.iterative_scanandpinecone.max_scan_tuples(rows requested from Pinecone, bounded bypinecone.am_max_topk, default 10,000) for a residualWHERE. - Direct lane. The same
/queryover keep-alive HTTPS from Python (remote.direct), with Pinecone’s ownx-pinecone-request-latency-ms, the reported read units and the RTT (TCP, TLS, keep-alive GET). - Partition.
partition:in the config adds atenant_idcolumn (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 inx-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 --yesdeletes every index whose name containspg-pinecone-bench-vec(the access method names its indexpv-<schema>-<table>, so the tables are calledpg_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 ofREPORT-overram.mdusebox/overram-pg.shon 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:<index_name> / 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 <opclass>[, 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_PGpoints at (the binariesharness/pgctl.py’sPgInstancelaunches its own throwaway.pgdatafrom); see “Install pg_pinecone” above. Re-install after every engine-side change before trusting apg_pinecone_nativeresult.PgInstancealwaysinitdbs 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’sambuild: barebooleanhas no pg_pinecone opclass and is rejected at DDL time), soharness/loader.pywrites an int2 companion column per filter spec (loader.filter_eq_column_name,1/0mirroring the SAME mask the boolean column pgvector systems query) – apg_pinecone_nativesystem’sbuild_params.filter_columnnames that companion column, andrunner.py’s per-filter loop queries it (WHERE {eq_col} = 1) instead of the boolean one.am.rssupports AT MOST ONE filter column per index, fixed atCREATE INDEXtime, so apg_pinecone_nativebuild only ever gets swept against ITS OWN filter column –runner.pyrecords every OTHER filter spec asnot_applicablefor 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 differentpg_pinecone_nativesystem entry (a different build) in the config, same as sweepingm/ef_constructionwould for hnsw. - Protocol constraints, encoded as assertions:
- Query
k <= 100(workloads.run_sweep_point):local/scan.rs’sASSUMED_TOP_K=100fixes the exact-rescore pool size (no realLIMITpropagation into the AM), so a largerkdoesn’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 apg_pinecone_nativesystem asserts the reported mode ('flat'/'h'/'l') againstSystem.pg_pinecone_native_expected_mode’s prediction (small-N bootstrap vs. filtered-vs-not vs. the estimate-vs-mode_l_thresholdrule, 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_thresholdin[0, 1],_assert_and_set_pg_pinecone_gucbounds-checks every one beforeSET) –System.sweep_parammust name one of the three;k_bits/seed/etc. are build-time reloptions and can never besweep_param(that would silently rebuild mid-sweep). Ak_bitssweep is multiplepg_pinecone_nativesystem entries (multiple builds), each sweeping a GUC – same pattern as sweeping hnsw’smvia multiple systems.target_recall(0.10+, a real in[0.5, 0.999]) passes throughbuild_paramsthe same way; every native build recordspinecone.index_calibration()(build.calibration,Noneon a build without it), and every pointSHOWspinecone.scan_prune.
- Query
- 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 incrementalaminsert,local/insert.rs; the protocol below was written for an earlier engine and was not re-validated.) Local mode’saminsertis a hard-stubERROR(no incremental insert yet), so ANYINSERT/vector-columnUPDATEraises 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 INDEXfresh (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.yamlfor a runnable plumbing-proof config (unfiltered + forced-mode-L + forced-mode-Hpg_pinecone_nativesystems alongside a trimmedpgvector_hnswsweep, 50k rows) – run it with--data-dirpointed at a scratch directory, never the defaultbench/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/<name>_<started>.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_tuplesbudget reveals the real filtered ceiling. On the 1%-correlated cross-tenant filter withrelaxed_order, the default 20000-tuple budget plateaus recall at 0.861 (ef=40, ~16.7ms p50, every point auto-labeledbudget_capped); raising the budget toauto(=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=offcollapses 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_wall1449 (1 conn) -> 10974 (8 conns) atef_search=10; 480 -> 3013 atef_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_tuplesis 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)