What each implementation actually does, and the traps in each. Everything here was read out of the source trees or the engines' own test suites, not out of marketing material.
| MariaDB 11.8 MHNSW | AliSQL 8.0.44-2 VIDX | pgvector 0.8 | |
|---|---|---|---|
| Enable | on by default | SET GLOBAL vidx_disabled=OFF and session READ-COMMITTED |
CREATE EXTENSION vector |
| Column type | VECTOR(N) |
VECTOR(N), N ≤ 16383 |
vector(N) |
| Binary literal | raw LE float32 | raw LE float32 | '[…]'::vector |
| Text helper | VEC_FromText / VEC_ToText |
VEC_FROMTEXT / VEC_TOTEXT |
cast from text |
| Index DDL | VECTOR INDEX vi (v) M=n DISTANCE=cosine |
VECTOR INDEX vi (v) M=n DISTANCE=COSINE |
USING hnsw (v vector_cosine_ops) WITH (m, ef_construction) |
| M | index option (or mhnsw_default_m) |
index option (or vidx_hnsw_default_m) |
WITH (m = …) |
| ef_construction | not exposed | not exposed | WITH (ef_construction = …) |
| Search width | mhnsw_ef_search |
vidx_hnsw_ef_search (default 20) |
hnsw.ef_search |
| Graph cache | mhnsw_max_cache_size (default 16 MiB) |
vidx_hnsw_cache_size (default 16 MiB) |
none — shared_buffers |
| Distance fn | VEC_DISTANCE_COSINE / _EUCLIDEAN |
same names | <=> / <-> |
| Storage engine | InnoDB or MyISAM | InnoDB only | heap |
| Isolation | any | READ COMMITTED required | any |
| Graph location | companion table <t>#i#<nn> |
InnoDB aux table vidx_%016lx_%02x |
index relation |
| Build timing | incremental, on INSERT | incremental, on INSERT | bulk, after load |
Source: sql/vector_mhnsw.cc, sql/vector_mhnsw.h. GA in the 11.7/11.8 series.
Configuration surface. Four plugin variables, declared at the bottom of
vector_mhnsw.cc:
MYSQL_SYSVAR_ULONGLONG(max_cache_size, …) // global
MYSQL_THDVAR_UINT(ef_search, …) // session
MYSQL_THDVAR_UINT(default_m, …) // session
MYSQL_THDVAR_ENUM(default_distance, …) // sessionplus two index options that default from the session variables:
HA_IOPTION_SYSVAR("m", M, default_m),
HA_IOPTION_SYSVAR("distance", metric, default_distance),So M can be given per index in DDL, which is what this framework does — it
matches how AliSQL takes M and keeps the two engines configured alike.
Graph storage. The HNSW graph lives in a hidden "high-level index"
companion table named <table>#i#<nn>, one row per node. Consequences: index
size is a filesystem measurement rather than a catalog lookup, and the graph
inherits the storage engine's page cache behaviour.
Caching. One cache per TABLE_SHARE, bounded by mhnsw_max_cache_size,
shared across sessions. Under concurrency all clients contend for the same
structure, which is what the concurrency workload is designed to expose.
One writer. Concurrent INSERTs into an MHNSW index deadlock (errno 1213):
each X-locks the neighbour rows it relinks in <table>#i#<nn>, and two writers
take them in opposite orders. Extra writers buy no throughput either. Measured
on 11.8.9, InnoDB, with 16,000 784-d rows in 500-row INSERTs:
| Threads | 1 | 2 | 4 | 8 |
|---|---|---|---|---|
| rows/s | 1,141 | 1,214 | 1,196 | 1,217 |
| deadlocks | 0 | 0 | 6 | 46 |
So both measurement paths load MariaDB with one thread
(MARIADB.max_insert_threads; the ops harness always did). Every other
MySQL-family engine's ann load follows the resource pass's build_threads
(ann_pass.ann_insert_threads): one writer under normalized, like MariaDB's,
and up to the tuned pass's cpuset-capped figure there, where MariaDB alone
stays at one.
MyISAM. MariaDB supports MyISAM for vector tables, and MariaDB's own published benchmark uses it. AliSQL cannot, so this framework's headline comparison is InnoDB for both, with MyISAM reported as a MariaDB-only extra curve in the tuned pass.
Not exposed: ef_construction. Build quality is tunable only through M.
Verified on 11.8.8 — the only mhnsw* variables the server has are
mhnsw_default_distance, mhnsw_default_m, mhnsw_ef_search and
mhnsw_max_cache_size, and supplying it as an index option fails outright:
CREATE TABLE t (..., VECTOR INDEX vi (v) M=6 EF_CONSTRUCTION=200);
ERROR 1911 (HY000): Unknown option 'EF_CONSTRUCTION'
This is the most load-bearing claim in the methodology — it is why the
normalized pass pins pgvector's ef_construction — so it is stated with
evidence rather than asserted.
Source: sql/vidx/vidx_hnsw.cc, sql/vidx/vidx_index.cc, sql/vidx/vidx_field.cc,
headers under include/vidx/. Shipped in AliSQL-8.0.44-2, based on MySQL 8.0.44.
vidx_disabled defaults to ON. Any VECTOR column or VECTOR INDEX fails
with ER_VECTOR_DISABLED until you set it OFF. The runtime image passes
--vidx-disabled=OFF at startup.
Every vector operation raises ER_NOT_SUPPORTED_YET at any other isolation
level. This is a session setting and must be applied on every connection,
including every connection in a connection pool.
AliSQL costs the vector index against a table scan and picks the scan when the
LIMIT is large relative to the row count. A full scan returns exact
results, so the symptom is recall ≈ 1.0 with collapsed throughput —
indistinguishable from "very accurate but slow" unless you check the plan.
The threshold is a fraction of the table, not a fixed LIMIT, and it does not
depend on ef_search. Measured on AliSQL-8.0.44-2 (tests/verify-alisql-traps.sh):
| Table size | index used | falls back to a scan |
|---|---|---|
| 100 rows | LIMIT ≤ 25 |
LIMIT ≥ 40 |
| 1000 rows | LIMIT ≤ 250 |
LIMIT ≥ 290 |
Identical results at vidx_hnsw_ef_search = 20, 40 and 80 — the search width
plays no part in the decision. The crossover sits around 25–28% of rows.
AliSQL's own mysql-test/suite/rds/t/vidx_dml.test appears to show a fixed
LIMIT 16 / LIMIT 17 boundary, and an earlier version of this document
repeated that reading. It is the same rule: that test deletes a third of its 100
rows first, so 17 of the ~67 remaining is ~25%.
What this means in practice. At benchmark scale the fallback does not
trigger — k=10 against 60,000 rows is 0.017% of the table, three orders of
magnitude below the crossover. The plan guard is therefore cheap insurance
rather than a live hazard for these workloads. It stays on because the failure
is silent and would invalidate a whole run: every driver runs EXPLAIN per
configuration and records vector_index_used, and the report lists any
measurement where it was false in its Validity section, above the charts.
Mitigation when you need the index regardless of cost: FORCE INDEX (vi).
vidx_hnsw_cache_size defaults to 16777216 bytes — far below the graph size of
any realistic corpus, so an out-of-the-box AliSQL will thrash.
This is not an AliSQL-specific failing, and an earlier draft of this document
was wrong to imply it was. MariaDB ships the identical default: SHOW VARIABLES LIKE 'mhnsw%' on 11.8.8 reports mhnsw_max_cache_size = 16777216. Both engines
degrade badly on their defaults. The framework sets both from the resource
profile so neither is judged on a value its vendor plainly intended you to
change.
Field_vector::store(const char*, size_t, cs) accepts a binary string of
exactly 4 × dim bytes — it validates the length and rejects NaN and Inf. So
binary binding works, and VEC_FROMTEXT() text parsing is avoidable. This
matters for fairness: charging AliSQL for text parsing that MariaDB does not
pay would be a client-side artefact, not an engine difference.
VECTOR(N) is stored as varbinary(4N) and presented that way in
INFORMATION_SCHEMA.COLUMNS — the data dictionary rewrites the type into a
versioned comment so tools that do not know VECTOR still see something valid.
Two caches with different lifetimes (vidx_hnsw.cc):
- MHNSW Share — attached to the auxiliary table's
TABLE_SHARE, shared by read-only transactions. - MHNSW Trx — attached to the session via
thd_set_ha_data; a read-write transaction keeps accessed and modified nodes in its own cache and updates the shared cache at commit.
This split is the most interesting structural difference from MariaDB's single shared cache, and it should show up under mixed read/write concurrency.
- InnoDB only.
- Vector index DDL cannot use
ALGORITHM=INPLACE. - Vector indexes cannot be
INVISIBLE. - Rows with
NULLvectors are excluded from the index and sort last. - HNSW layer assignment is randomised: replicas built from identical rows are not guaranteed to have identical graph topology.
Mrange is 3–200;ef_searchrange is 1–10000.- Not exposed:
ef_construction.
Source: the pgvector extension, built against PostgreSQL 17.
Bulk build. The defining difference: pgvector builds its HNSW graph as one
operation after the data is loaded, parallelisable across
max_parallel_maintenance_workers and sized by maintenance_work_mem. MHNSW
and VIDX maintain their graphs on every INSERT and have no equivalent.
Comparing pgvector's bulk build against the others' incremental build compares
two different operations. This framework measures both, via build_mode:
post— load, thenCREATE INDEX. pgvector's idiomatic path.incremental—CREATE INDEXon the empty table, then load. Mirrors what MHNSW and VIDX are forced to do.
No vector-specific cache. Graph pages come through shared_buffers like
any other index pages. Mature and well-understood, but not vector-aware — there
is no equivalent of pinning the graph in memory. In the normalized pass
pgvector's buffer budget therefore absorbs the graph-cache fraction the other
two get separately; otherwise it would be handed strictly less resident memory
for the same container limit.
ef_construction. The one build-quality knob only pgvector has. Pinned to
its default of 200 in the normalized pass so pgvector is not given a tuning axis
the others lack; swept in the tuned pass and reported separately.
Iterative index scan (0.8+). hnsw.iterative_scan = off /
relaxed_order / strict_order. Without it, a selective WHERE can exhaust
the ef_search candidate list before k qualifying rows are found, and the query
returns fewer than k results. That is legitimate behaviour, but it makes
filtered recall ambiguous unless the mode is stated — so the framework sets it
explicitly and records it with every filtered measurement.
Operator/opclass must match. <=> requires vector_cosine_ops; <->
requires vector_l2_ops. A mismatch produces a sequential scan with no error
and no warning — pgvector's equivalent of AliSQL's silent fallback, and just
as capable of turning a benchmark into a brute-force measurement. Verified on
pgvector 0.8.6 against a cosine index over 5,000 rows:
-- matching operator: index used
EXPLAIN SELECT id FROM m ORDER BY embedding <=> '[…]' LIMIT 10;
Limit
-> Index Scan using m_cos on m
Order By: (embedding <=> '[…]'::vector)
-- mismatched operator against the same index: silently brute force
EXPLAIN SELECT id FROM m ORDER BY embedding <-> '[…]' LIMIT 10;
Limit
-> Sort
Sort Key: ((embedding <-> '[…]'::vector))
-> Seq Scan on m
The driver's plan check catches this: it requires the index name to appear in
the plan, and records vector_index_used=false otherwise.
Storage. SET STORAGE PLAIN on the vector column keeps vectors inline;
TOASTed vectors add a detoast to every distance comparison.
valkey-search describes two strategies for a hybrid query: pre-filtering (resolve the filter from the secondary index, then brute-force the survivors by distance) and inline filtering (walk the HNSW graph, testing the predicate as you go). Its documentation says the planner picks between them, and Google's Memorystore documentation — which runs this module — says pre-filtering is chosen "when the filtered search space is much smaller than the original".
Measured, it does not. Latency rises as the predicate tightens, which is the signature of the graph walk: with one node in a hundred qualifying, the walk travels much further to find ten of them.
| Selectivity | p50 | QPS |
|---|---|---|
| unfiltered | — | 479 |
| 10% | 18.16 ms | 53.5 |
| 1% | 120.56 ms | 8.2 |
9,900 vectors out of 990,000 is a search space where an exact scan takes a couple of milliseconds. It took 120.
There is no way to ask for the other strategy. RediSearch exposes exactly
this choice as HYBRID_POLICY ADHOC_BF | BATCHES; valkey-search's parser
rejects the keyword outright:
Error parsing vector similarity parameters:
`[KNN 10 @embedding $vec EF_RUNTIME 100 HYBRID_POLICY ADHOC_BF]`.
Unexpected argument `HYBRID_POLICY`
Two things about our own query were ruled out before concluding that. Dropping
EF_RUNTIME does not reach the pre-filter — it lowers search effort, and recall
falls with it. Replacing the open [-inf (threshold] bound with a bounded
[0 max] range, in case an unbounded side defeated cardinality estimation,
changes nothing: 136.72 ms against 136.72 ms.
Reproduce all of it in about five minutes:
./run-benchmark.sh lab --engine valkey probe-valkey-filter.py --rows 200000The probe generates random vectors, so its recall is not comparable to a run's — random high-dimensional vectors are nearly equidistant and any HNSW graph over them is poor. Only the comparison between shapes, and the direction across selectivities, carries over. Both reproduce the run: 5.2x slower at 1% than at 10% in the probe, 6.6x in the run.
Source: askdba/myvector release v1.26.9, commit 384d9df7, built in-tree
into MySQL mysql-9.7.2 (the server line Percona Server's 9.7 series was
based on at its 9.7.2-2 pin; the current Percona pin is 9.7.4-4). Symbols below are in that tree's src/myvector.cc,
src/myvector_binlog.cc, src/myvector_plugin.cc, include/hnswdisk.h and
sql/myvectorplugin.sql. Driver: harness/drivers/myvector.py.
The only engine here whose index is not part of the server's storage layer.
The graph is an hnswlib graph held in the plugin's memory and saved to files
in myvector_index_dir; the table is an ordinary InnoDB table with a native
VECTOR(n) column. How each piece works, and the traps in it:
| MyVector v1.26.9 | |
|---|---|
| Declared by | column COMMENT 'MYVECTOR Column |type=HNSW,dim=…,size=…,M=…,ef=…,dist=…,track=updatets' |
| Built by | CALL mysql.myvector_index_build('db.t.col','pk'), after the load |
| Searched by | WHERE MYVECTOR_IS_ANN('db.t.col','pk', vec, 'nn=k,ef_search=e'), rewritten before parsing |
| M / ef_construction | M= / ef= column options (exact case) |
| Search width | ef_search= inside each call; hnswlib searches at max(ef, k) |
| Cosine | dist=Cosine → AngularDistanceSpace, a scalar loop (computeCosineDistance); L2 uses hnswlib's SIMD L2Space |
| Capacity | size= is hnswlib's fixed max_elements; addPoint throws past it, inside mysqld |
| Writes after the build | myvector_index_refresh over a track= timestamp — adds only (below) |
| Online (binlog) maintenance | documented; exits after its first idle second on this server (below) |
| Filtered search | none |
HNSWMemoryIndex::HNSWMemoryIndex reads getIntOption("M", 16) and compares
getOption("dist") == "Cosine". m=32 builds M=16; dist=cosine builds L2.
Nothing warns. MYVECTOR_INDEX_STATUS prints M as the effective value but
Distance as written, and does not print ef at all. The driver writes
the exact spellings, then reads status back and refuses a build whose Type,
M, Dimension, Distance, capacity or row count is not what it declared.
myvector_index_build opens its own connection back to the server using
myvector.cnf (BuildMyVectorIndexSQL); the file must be mode 0600/0400 or it
is refused. Every failure — no file, refused connection, failed save — comes
back as text in the procedure's result set and the CALL succeeds. The driver
requires the text to start SUCCESS and to report the loaded row count.
docs/ONLINE_INDEX_UPDATES.md describes online=Y as real-time maintenance
from the binary log, applying inserts, updates and deletes. Two things are
true instead, the first observed in this image and the second read from the
source:
- It dies.
myvector_binlog_loopopens the stream with a 1-secondMYSQL_OPT_READ_TIMEOUTand treats any fetch error whose text lackstimed outas fatal. Against MySQL 9.7.2 the idle-timeout error text is empty, so the server log showsBinlog fetch failed:andExiting binlog funcabout a second after startup, noBinlog Dumpthread remains, and a row inserted after that never reaches the graph. Nothing restarts it. - It only inserts. The loop dispatches
TABLE_MAP_EVENTandWRITE_ROWS_EVENTand nothing else, andmyvector_table_oponly ever callsinsertVector. (Its row parser also reads the key from aMYSQL_TYPE_LONGcolumn only, so a BIGINT key would be indexed as 0.)
So the framework does not use online=Y and the engine gets no binlog
requirement: its server flags log exactly as Percona Server's do. Writes after
the build reach the index through the release's other documented incremental
path, a track= timestamp column and myvector_index_refresh, which the
churn workload's wait_index_synced hook runs after each re-insert and times
(index_sync_s). The refresh selects rows changed since the last build or
refresh at one-second resolution, so the driver waits out the watermark
second after each build and refresh — at most one second, inside the timed
maintenance — or a row written in that second would never be selected.
index_sync_s is therefore not an incremental sync, and is not comparable
to another engine's. A refresh is myvector_open_index_impl (action
refresh) → BuildMyVectorIndexSQL, which inserts the changed rows and then
calls saveIndex(myvector_index_dir, "build") — a full rewrite of the graph
file — after which myvector_open_index_impl calls saveIndex(…, "refresh"),
an incremental checkpoint, on top. So the figure is the re-inserted rows plus
one whole-graph write plus a checkpoint, and it grows with the corpus rather
than with the churn. It is one procedure call, so the parts cannot be timed
apart from SQL. The driver stamps this on every record
(extra.index_sync_semantics) and the report prints it under §9 Churn and in
Validity.
Neither path removes a deleted row. It stays in the graph; the search
still returns its id; the rewritten id IN (…) drops it, and the query
returns fewer than k rows. That is the degradation churn measures. Every
re-insert also consumes a graph slot for good, so the driver sizes the graph
at 1.5× the loaded rows and refuses a re-insert that would overflow it rather
than let hnswlib throw inside mysqld. The repair is a full
myvector_index_build, which takes LOCK TABLES … READ for its duration.
The audit-plugin rewrite (myvector_query_rewrite) fires only on a statement
whose first character is S/E/C/A and that matches ^SELECT\s+ or
^EXPLAIN\s+ — a leading comment or space disables it. It finds the end of
MYVECTOR_IS_ANN(…) by counting parentheses over the raw text, quotes
included, so an escaped binary vector can derail it with the bytes 0x28/0x29.
Worse, a long statement kills the server: with the query vector inline as a
hex literal, a 100-dim search (a 0.9 KB statement) works and a 784-dim one
(6.4 KB) makes mysqld exit with nothing in its error log — reproduced on this
image, and the cause of the first smoke run's failure. The same search with
the vector in a user variable works. So every query is two statements, SET @vb_q = X'…' (no MYVECTOR in it, so the rewrite returns at its first
strstr) and the search over @vb_q — a second round trip per query that no
other engine here pays, charged by the plugin's design. ef_search mutates the shared index
(setSearchEffort), so it is sent on every query; omitting it searches at
whatever another session last asked for.
Both query-path asymmetries — the second round trip, and Cosine's scalar
distance loop (AngularDistanceSpace → computeCosineDistance; L2 runs
hnswlib's SIMD L2Space) — are recorded on every record
(extra.round_trips_per_query, extra.distance_impl, set by
MyVectorDriver.query_path_facts on the ops path and by the overlay module's
get_additional on the ann path) and footnoted by the report under §4, §7 and
in Validity.
DROP DATABASE leaves the plugin's in-memory index object alive under the
same db.table.col name, and the next build reuses that object — with the
dimension, M and ef it was constructed with. The driver calls
myvector_index_drop first, and the status check above would catch a stale
object anyway.
myvector_ann_set_init accepts three or four arguments and the options string
carries nn and ef_search only. A predicate beside MYVECTOR_IS_ANN
filters the k results after the search (the release's own
docs/LIMITATIONS.md says so). The driver declares the filtered workload
declined (EngineDriver.declined_workloads); the harness skips it, prints the
reason and stamps extra.declined_workloads on every record of the run.
post only: load, then one myvector_index_build. There is no incremental
mode to compare it with, because the only write-path maintenance is the dead
binlog listener; the driver refuses build_mode: incremental with that
reason. myvector_index_bg_threads is the build's parallelism — 1 in the
normalized pass, {build_threads} in the tuned one; the plugin only builds in
parallel at 2 or more (HNSWMemoryIndex::startParallelBuild).
The graph is hnswlib's, in mysqld's own heap: it is not InnoDB pages, it is not
bounded by innodb_buffer_pool_size or by any MyVector variable, and it cannot
be paged out — the whole graph is resident or the search does not run. It IS
charged the same way as every other engine's memory: it is mysqld's address
space, inside the server container, so the container memory limit bounds it
and the run's mem-*.jsonl sampler and peak-RSS figures include it. What
differs is how the same budget is split. The normalized pass gives every
MySQL-family server a buffer pool of 30% of 16 GiB (4.8 GiB) and MariaDB/AliSQL
a graph cache of another 30%; MyVector's buffer pool serves only the table and
the k primary-key lookups per query, and its graph takes whatever is left.
Size, per element, is MYVECTOR_INDEX_STATUS's Element Data Size — the
vector, the level-0 links (2·M 4-byte ids plus a count) and the 8-byte label;
3,276 B at 784 dims and M=16, measured — plus an allowance of 100 B for the
per-element lock, level and pointer arrays and the label map (an allowance,
not a measurement). The build additionally keeps the WHOLE (id, vector)
result set client-side inside mysqld while it inserts (BuildMyVectorIndexSQL:
mysql_store_result), counted as 4·dim·rows. So the build peak is buffer pool
- graph + that copy, with no further margin.
harness/memory_fit.pycomputes it; these rows are its output at M=16 and the normalized pass's 16 GiB limit (30% buffer pool), andtests/test_e25_myvector_memory_fit.pyfails if they drift from it:
| Corpus | Graph | Build transient | Buffer pool | Build peak | Against 16 GiB |
|---|---|---|---|---|---|
| gist-960 | 3.8 GiB | 3.6 GiB | 4.8 GiB | 12.2 GiB | fits |
| dbpedia-openai-1000k | 5.9 GiB | 5.7 GiB | 4.8 GiB | 16.4 GiB | does not fit |
(dbpedia-openai-1000k is 1536-d and 990,000 training rows; gist-960 is 960-d and 1,000,000.) Nothing beyond those parts is added — mysqld's own baseline, the ann-benchmarks client sharing the container on the ann path, and page cache are not in the figure — so a peak near the limit is a peak that will not survive; a refusal at 16.4 GiB against 16.0 is not a close call.
The orchestrator refuses a unit whose estimated peak is over the run's
resolved server limit (cli._memory_refusal, gated on
capabilities.memory_fit: myvector), before the load, for both the ann and the
ops phase, with the rows that phase loads (ops.subset_rows for ops) and the
largest M it builds. The manifest records the phase as refused with the
reason — does not fit: estimated peak X GiB > limit Y GiB at M=32 (largest of [8, 16, 32]) — and the report lists it in Validity under "Units refused before
they ran", separately from failures, and the run's finish banner counts them.
The ann grid is ann_pass.ann_m_values, the function the ann render itself
uses, so a tuned m_values_by_engine override is honoured; the ops grid is
ops.m_values. A unit is refused as a whole when its largest M does not
fit, even if its smaller M values would: one unit is one phase over the
whole grid, and splitting it would change what a checkpoint means. That is
conservative, and the reason text names the M and the grid so the reader can
see it. A phase the profile disables (ann.enabled: false, ops.enabled: false) is never checked and never shows a refusal. The check compares
against the server container's limit only, not the client's and not the
host's: the graph and the build's copy of the vectors live in mysqld, so the
server's memory cgroup is the one that OOM-kills it, and the ann-benchmarks
client that shares the container on the ann path is sized separately by
ann_pass.client_memory_bytes. No default budget changed: the normalized pass stays at 16 GiB.
dbpedia under mariadb-blog's own 64 GiB, or under the tuned pass's host
fraction, fits and runs. Other engines are never refused: their graphs are
caches or InnoDB pages that degrade instead of killing the server.
The build record carries extra.myvector_graph_bytes_floor (level-0 bytes,
measured from the server's status) and every record carries
extra.memory_note; the report states both under §5 and in Validity. One
budget question remains the user's to decide: whether MyVector's normalized
buffer pool should give up the graph-cache share MariaDB and AliSQL get, so
its graph has the same headroom.
Server villagesql/villagesql-server tag publish/mysql-9.7_0.0.8 (9999524a,
VillageSQL 0.0.8 on MySQL 9.7.2) with the villagesql/vsql-vector-pk extension
at release 0.0.4 (3c798c74). vsql-vector-pk is the build of vsql-vector
whose HNSW index stores each row's primary key; it installs as vsql_vector,
and a server runs one or the other. It builds against the server's build tree
(VillageSQL_BUILD_DIR), -O3, at x86-64-v3, with -DVSQL_PRE_RELEASE_VERSION=
so the server reports 0.0.8 rather than the 0.0.8-dev its VSQL_VERSION file
still carries at the tag. villagesql84 (below) builds the same extension commit
against the MySQL 8.4 line.
0.0.8 / 0.0.4 is the first pin at which the driver binds vectors as float32 ("Value input" below). vsql-vector-pk 0.0.4 is 0.0.3 plus the binary SVECTOR form (#39), the column store's root-page deadlock fix (#42) and its version bump; its HNSW options and their bounds are 0.0.3's, and it does not have the vector cache.
Unless a row says otherwise, "this pin" in the rows below is the previous one,
vsql-vector-pk 0.0.3 with VillageSQL 0.0.7 (publish/mysql-9.7_0.0.7,
533ab4b5). Its measurements were
taken on the 8.4 build (villagesql84's server, 689c74a9), whose KNN
recognition, custom-index DML layer and storage ABI are the same files as 9.7's
(see the 8.4 section). On 9.7 this framework built vsql-vector-pk d23b87c4
(0.0.3 without its version bump) against 533ab4b5 and ran the gate profile
through both phases with the plan check passing, and the 8.4-built
vsql_vector.veb installed into the 9.7 server and planned the distance scan.
The engine's first pin was server publish/mysql-9.7_0.0.7 (533ab4b5) with
plain vsql-vector bac885dd, measured on 2026-10-30. Where a row below says
"first pin", that is the build it was measured on.
| VillageSQL + vsql_vector (vsql-vector-pk) | |
|---|---|
| Enable | INSTALL EXTENSION vsql_vector once (the entrypoint's bootstrap), and --vsql-allow-preview-extensions=ON on every start: on the first pin, once the extension was installed a server started without it refused to start (Failed to load VEF extension 'vsql_vector': extension requires preview capabilities but vsql_allow_preview_extensions is OFF, then Aborting), and SET GLOBAL of it was refused (must be set with SET PERSIST) |
| Column type | SVECTOR(N), stored by the extension in its own InnoDB pages |
| Value input | packed little-endian float32, bound as a parameter on the load and in every query, as for every MySQL-family engine here. The server hands a binary value to the type's from_binary (villagesql-server #1499); SVECTOR's takes the raw float32 array and refuses a length that is not 4 × the column's dimension (svector_from_binary, src/vector.cc, vsql-vector-pk #39). Needs VillageSQL 0.0.8 and vsql-vector-pk 0.0.4 or later. The text literal '[a,b,…]' (strtof per element) is still accepted, and was this engine's input through 0.0.7 |
| Index DDL | INDEX vi (v hnsw_l2) USING EXTENDED(hnsw) WITH (M = 16, ef_construction = 200) inline, or CREATE INDEX vi ON t1 (…) |
| Metric | chosen by the key part's profile: hnsw_l2, hnsw_cosine, hnsw_l1, hnsw_inner_product |
| ef_construction | WITH (ef_construction = …), default 200, rejected above 32 × M and below M (Options::validate, src/index/hnsw/storage.cc; M itself is 2..128; the same constants as plain vsql-vector's). On the first pin a refusal said only InnoDB: Custom type operation failed. See server error log for details. (M=4: 128 accepted, 129 refused). So 200 is not buildable at M=6 or below. The engine's first version clamped to 192 and recorded both values, but the result file and every label downstream still said 200; the combination is now dropped from the render by name (orchestrator/ann_knobs.py), and the driver and module refuse it if anything bypasses that. m-sweep gives VillageSQL M 8..32 for this reason |
| Search width | SET SESSION vsql_vector.ef_search = n (default 40, 1..65536); the scan searches at max(k, ef) |
| Distance fn | L2_DISTANCE, COSINE_DISTANCE, L1_DISTANCE, INNER_PRODUCT |
| Graph location | extension segments inside t1.ibd; on the first pin the catalog reported one page for vi |
| Build timing | incremental on INSERT, or bulk CREATE INDEX — see the third trap |
- The index is used only if it stores row references. The server
drives a distance scan ("Custom index distance scan on vi" in EXPLAIN
FORMAT=TREE) only from an index whose storage props include
HAS_ROW_REF(FindCustomKnnIndexOnFieldinvillagesql/sql/custom_index_knn_recognition.cc, gate from server PR #1269). Plain vsql-vector's HNSW declaresHAS_COLUMN_REF | REF_LOOKUP: on the first pinSELECT id FROM t1 ORDER BY L2_DISTANCE(v, '[…]') LIMIT 10planned asSortoverTable scan on t1(TRADITIONAL:type ALL,Using temporary; Using filesort), with the vector inlined or bound, underhypergraph_optimizer=offand=on, and recall was 1.000 at ef_search 10, 40, 160 and 400 alike — exact brute force. vsql-vector-pk declaresHAS_ROW_REF, and on this pin the plan isCustom index distance scan on viunder the default (classic) optimizer; on a 300 × 8 probe its top 5 matched exact brute force.VillageSQLDriverand the ann module still check the plan and raise instead of timing the query without it, so a pin that loses the scan fails loudly into the report's failed phases rather than publishing brute force as ANN search. Through 0.0.7 the query vector had to be a constant (BindFieldAndQuery), so it was inlined; since villagesql-server #1298 the recognition accepts a bound parameter, and the query vector is bound. - DELETE is rejected.
DELETE FROM t WHERE id = 1→DELETE on a table with a custom index (USING EXTENDED) is not supported yet.Churn is declined (declined_workloads,capabilities.churn: false). An UPDATE ofv, and oftag(which carries its own B-tree index), is rejected too:UPDATE that changes an indexed column on a table with a custom index (USING EXTENDED) is not supported yet.— any indexed column, not only the vector. Nothing here issues an UPDATE. All three measured on this pin, as on the first. - The bulk build can kill the server. On the first pin,
CREATE INDEX vi ON t1 (v hnsw_…) USING EXTENDED(hnsw)over a loaded table worked and was fast, and in about one build in seven it deadlocked: two threads X-latched extension pages at the storage ABI's page get (storage/innobase/villagesql/storage_abi.cc) and waited on each other, InnoDB's monitor printedDEADLOCK of threads detected!and aborted ([FATAL],sync0arr.cc, signal 6); never in 12 incremental builds. vsql-vector-pk's README says building the index over a populated table "is not safe yet and can crash the server". Socapabilities.bulk_vector_index_build: falseand the build mode isincremental; a profile may still ask forpostto re-test a new pin. - A WHERE beside the KNN ORDER BY is a post-filter. The scan returns its
top
max(ef_search, LIMIT)candidates and the predicate filters those (vsql-vector-pk README, "Filtering with a WHERE clause"), so a selective predicate returns fewer than k rows even when enough matching rows exist. Filtered search is declined: measuring it would publish post-filtered recall as filtered search. - Through 0.0.7 the vectors were text, a cost only this engine paid on
the load and inside every timed query (
extra.vector_bindingcarried it and the report stated it). From VillageSQL 0.0.8 and vsql-vector-pk 0.0.4 they are bound packed float32 like every other MySQL-family engine's, so the records no longer carry a binding of their own. - Ingest slowed within one server lifetime on the first pin. Repeated drop-and-reload cycles of a 3,000 × 16 incremental build on one server ran at ~290, ~330, ~330, ~150, ~120, ~125 rows/s; a restarted server started fast again. Unexplained, and not re-measured on this pin; the framework restarts the server per configuration, so it does not reach a measurement, but a hand-run loop may see it.
VillageSQL's team asked for their 8.4 build to be benchmarked rather than 9.7, so
villagesql84 compiles vsql-vector-pk against VillageSQL's MySQL 8.4 line (MySQL 8.4.11
base — not the Percona Server 8.4 based line). Its config is alias_of: villagesql: the
same Dockerfile, entrypoint, init.sql, driver, cmake flags, server flags and capabilities,
so the server line is the only difference between the two curves. It has its own
ann_constructor (VillageSQL84) and its own image names (vector-bench/villagesql84-*),
because both engines' images are tagged with the extension commit and would otherwise
overwrite each other.
It pins server tag publish/mysql-8.4_0.0.8 (6c49b098, the same commit as
release/0.0.8) with the same vsql-vector-pk release as villagesql, 0.0.4
(3c798c74).
What was checked in the 8.4 tree at the 0.0.7 pin (publish/mysql-8.4_0.0.7, 689c74a9f88c91ae035f57e0ae8915a168274112, with vsql-vector-pk 93ed0087), and found the same as 9.7:
- The build. Every cmake flag in the 9.7 config exists in 8.4, Boost is bundled in
extra/boost(1.84) as it is in 9.7, andvillagesql/sdk— the headers the extension includes, dev ABI and all — is byte-identical between the two tags (all 69 blobs). The extension therefore needs no change, and both engines pin the same commit. - The server surface the driver uses.
vsql_allow_preview_extensions, the DELETE / UPDATE refusal text,EXPLAIN FORMAT=TREE,information_schema.INNODB_TABLESPACESand the performance_schema wait instruments all exist.L2_DISTANCE/COSINE_DISTANCEare the extension's functions, not the server's, so 8.4's lack of a native vector type does not matter. 8.4 still shipsmysql_native_password, disabled; the bench account stays oncaching_sha2_passwordas on 9.7. - Trap 1 holds on 8.4.
FindCustomKnnIndexOnFieldinvillagesql/sql/custom_index_knn_recognition.ccis the same file on both tags and skips any index withoutVEF_INDEX_STORAGE_HAS_ROW_REF. The only differences invillagesql/between the tags are build scripts and the 8.4 iterator API (Init/Readwhere 9.7 hasDoInit/DoReadincustom_index_knn_scan.cc). vsql-vector-pk declaresHAS_ROW_REF, and the measurements in the rows above were taken on this 8.4 build: the KNN query plans asCustom index distance scan on vi. At the previous pin, plain vsql_vectorbac885d(HAS_COLUMN_REF | REF_LOOKUP,src/vector.cc), the KNN query was a filesort over a full scan on 8.4 as on 9.7 and the plan check refused it. Measured at that pin on the built image (2026-11-04,8.4.11-villagesql-0.0.7-dev-vector-bench, 2,000 × 16 rows, index declared inline at M=16, ef_construction=200):EXPLAIN FORMAT=TREE SELECT id FROM t1 ORDER BY L2_DISTANCE(v, '[…]') LIMIT 10isLimit→Sort: vsql_vector.l2_distance(…)→Stream results→Table scan on t1, TRADITIONAL istype ALL … Using temporary; Using filesort, the same atvsql_vector.ef_search = 10, andDELETEfails with the 9.7 text. One difference from 9.7: the hypergraph optimizer cannot be switched on at all in a non-debug 8.4 build (ERROR 3999 … does not yet support 'use in non-debug builds'), so "both optimizers" is one optimizer here. Thedevprofile on this engine produced itsingestandindex_buildrecords (incremental, 595 and 1,130 rows/s on the 4,000 × 16 tiny corpus in the normalized and tuned passes) and the concurrency workload raisedBruteForceRefusedin both passes, as designed. - Trap 3 (the bulk-build deadlock) was measured on 9.7 only. Every file under
storage/innobase/villagesql/—storage_abi.cc, where the two latches were taken, among them — is byte-identical between the tags, sobulk_vector_index_build: falseis kept rather than assumed fixed.
- The optimizer can always decline the index. Every one of them will fall back to exact brute force under some conditions, and every one makes that look like high recall at low throughput. Check the plan, every time.
- Metric is fixed at index creation. You cannot query a cosine index with a Euclidean distance function and get index acceleration.
- Recall is not deterministic across rebuilds. HNSW construction involves randomised layer assignment; two indexes built from identical data will not have identical graphs, and recall will differ slightly.
- Warm-up matters. The first queries after startup pay for cold graph caches. Measuring them as steady state understates every engine, and understates the ones with larger caches most.