---
title: "How-To: Build and Use the Relational SQL Vector Index (Virtuoso)"
description: "Learn how to create, query, and maintain a native HNSW-backed vector index on a relational text column in Virtuoso using SQL, with local ONNX or remote HTTP embedding models."
url: urn:osdi:this
dateModified: 2026-10-30T15:45:34Z
language: en
---
One page of the Vector Index documentation, MERGED by build_vector_index_docs.py from this template and the markdown in fixture/vector-index/. Do not edit the generated file: change this template or the generator and rebuild. It opens on the documentation header (dxshell.py), not on the site hero.

[Home](urn:osdi:site)/[Virtuoso](urn:osdi:sitevirtuoso/)/[Vector index](urn:osdi:sitevirtuoso/docs/vector-index/)/How-To: Build and use the relational SQL vector index (Virtuoso)

# How-To: Build and use the relational SQL vector index (Virtuoso)

Learn how to create, query, and maintain a native HNSW-backed vector index on a relational text column in Virtuoso using SQL, with local ONNX or remote HTTP embedding models.

Start  [Overview](urn:osdi:sitevirtuoso/docs/vector-index/) [Quickstart](urn:osdi:sitevirtuoso/docs/vector-index/quickstart/) [Tutorial: FAQ semantic search](urn:osdi:sitevirtuoso/docs/vector-index/tutorial-faq-semantic-search/)  Build  SQL vector index how-to [SPARQL vector index how-to](urn:osdi:sitevirtuoso/docs/vector-index/howto-sparql-vector-index/) [Search RDF literals (knnSimilar)](urn:osdi:sitevirtuoso/docs/vector-index/knn-similar/)  Configure  [Embedding models](urn:osdi:sitevirtuoso/docs/vector-index/embedding-models/) [Index parameters](urn:osdi:sitevirtuoso/docs/vector-index/index-parameters/) [Similarity metrics](urn:osdi:sitevirtuoso/docs/vector-index/similarity-metrics/)  Operate  [VVec Console (web UI)](urn:osdi:sitevirtuoso/docs/vector-index/vvec-console/) [Troubleshooting](urn:osdi:sitevirtuoso/docs/vector-index/troubleshooting/)  Reference  [API reference](urn:osdi:sitevirtuoso/docs/vector-index/api-reference/)

This guide covers building and using the native, model-bound, HNSW-backed **relational SQL vector index** in Virtuoso Open Source. It indexes a raw text column, the server owns embedding generation via a local ONNX model or a remote HTTP embedding provider, and similarity is queried with `X_SIMILAR(...)` or `ORDER BY VECTOR_DISTANCE(...) TOP N`, both driven by the HNSW index.

This article covers:

- **Prerequisites and build**: Configuring Virtuoso with ONNX support
- **Creating a vector index**: DDL syntax, HNSW parameters, and model binding
- **Remote embedding providers**: OpenAI, Ollama, LM Studio, and OpenAI-compatible APIs
- **Querying**: Text-based search, ANN-driven search, filter/rerank mode, and accuracy tuning
- **Maintenance**: Automatic queue-based index updates
- **Troubleshooting**: Common issues and health checks

Note

For the RDF/SPARQL vector index surface, see [SPARQL vector index how-to](urn:osdi:sitevirtuoso/docs/vector-index/howto-sparql-vector-index/).

---

## Prerequisites

Before you start, make sure you have:

- **Virtuoso Open Source** built with the ONNX plugin (`--enable-onnxruntime`)
- **The `vonnx` plugin** loaded in your `virtuoso.ini`
- **An embedding model** registered in `DB.DBA.ONNX_MODELS` (auto-provisioned at startup for the bundled `granite-384-local` model)

**Verify the plugin and model at startup:**

SQL

```
-- The vonnx plugin should load at startup; check the log for:
--   "SUCCESS plugin N: loaded from .../vonnx.so"

-- Verify the bundled model auto-provisioned:
SELECT model_name, model_type, handle
FROM DB.DBA.ONNX_MODELS
WHERE model_name = 'granite-384-local';
-- Expect: model_type = text_embedding, handle = 1 (non-null means the model is loaded)

-- Test embedding:
SELECT length (DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL('granite-384-local', 'hello world'));
-- Expect: 384
```

If the query returns no row, or `handle` is null, see [Troubleshooting](#troubleshooting).

---

## Build Virtuoso with ONNX support

### Configure and build

From the repository root:

Shell

```
./autogen.sh
./configure \
  --prefix=/opt/virtuoso-opensource \
  --with-layout=openlink \
  --enable-onnxruntime=/path/to/onnxruntime \
  --enable-vvec-vad
make
make install
```

- **`--enable-onnxruntime=<dir>`** builds the `vonnx` plugin and its `onnx_helper` program. `<dir>` is an ONNX Runtime installation prefix that holds the **static** library `lib/libonnxruntime.a` and the C API headers (in `include/` or `include/onnxruntime/`). ONNX Runtime is linked into the plugin and the helper, so nothing from it has to be installed on the server at run time. `README.Building.md` lists ONNX Runtime 1.29.0 or later. Given without a directory, `--enable-onnxruntime` asks `pkg-config` for `libonnxruntime`. If no usable `libonnxruntime.a` is found, `configure` warns and the server is built without the plugin.
- **`--enable-vvec-vad`** also builds `vvec_dav.vad`, the [VVec console](urn:osdi:sitevirtuoso/docs/vector-index/vvec-console/) package (`--enable-all-vads` includes it).
- On macOS with Homebrew, add `--enable-openssl=$(brew --prefix openssl@3)`.

The bundled model's weights (`model.onnx`, about 60 MB) are not kept in the source repository. The first build downloads them from `https://nexus.openlinksw.com/repository/onnx-models/` and checks them, together with the tokenizer files that ship with the source, against the recorded `SHA256SUMS`; a mismatch fails the build. A tree that already holds a verified copy never touches the network, so for an offline build place `model.onnx` in `binsrc/hosting/onnx/onnx-models/granite-embedding-30m-english/` first. `make install` puts the model under `<prefix>/hosting/onnx-models/granite-embedding-30m-english/`.

### Enable the plugin in virtuoso.ini

The `virtuoso.ini` that `make install` writes for the default database already loads the plugin when it was built, and says where the models live:

INI

```
[ONNX]
ModelRoot   = /opt/virtuoso-opensource/hosting/onnx-models
;HelperPath = /opt/virtuoso-opensource/hosting/onnx_helper

[Plugins]
LoadPath    = /opt/virtuoso-opensource/hosting
Load17      = plain, vonnx
```

For another database, add the `vonnx` line under `[Plugins]` (any free `LoadN` number) and the `[ONNX]` section. With `HelperPath` commented out the helper is found next to the plugin in `LoadPath`. On startup, confirm the log shows lines of this form:

Output

```
SUCCESS plugin N: loaded from <hosting directory>/vonnx.so }
PL LOG: ONNX bootstrap refreshed 1 model(s)
```

---

## Models

Embeddings come from models resolved through `DB.DBA.ONNX_MODELS`. The bundled `granite-384-local` model (IBM granite-embedding-30m-english, 384 dimensions) is **auto-provisioned at startup** — you do not create or populate the table by hand.

### Available model types

| Model type | Description | Handle required? |
| --- | --- | --- |
| `text_embedding` | Local ONNX model (e.g. the bundled `granite-384-local`) | Yes — loaded at startup |
| `openai` | OpenAI API (`text-embedding-3-small`, etc.) | No — HTTP provider |
| `ollama` | Local Ollama server | No — HTTP provider |
| `lmstudio` | Local LM Studio server | No — HTTP provider |
| `openai_compatible` | Any OpenAI-compatible API | No — HTTP provider |

### Embed text manually (for testing)

SQL

```
-- By model name (recommended):
SELECT DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL('granite-384-local', 'hello world');

-- By handle (lower-level plugin BIF):
SELECT ONNX_EMBED_TEXT(
  (SELECT handle FROM DB.DBA.ONNX_MODELS WHERE model_name = 'granite-384-local'),
  'hello world'
);
```

Tip

You normally never call these for queries. To search, pass the query **text** directly to `X_SIMILAR` / `VECTOR_DISTANCE` — the index embeds it with its own bound model automatically.

---

## Create a vector index

The source table needs a **single-column integer primary key** (`INTEGER`, `BIGINT`, `SMALLINT` or `NUMERIC`) and a character column (`VARCHAR`, `NVARCHAR`, `LONG VARCHAR` or `LONG NVARCHAR`).

SQL

```
CREATE TABLE DB.DBA.DOCS (
  id      INTEGER NOT NULL PRIMARY KEY,
  content LONG VARCHAR
);

INSERT INTO DB.DBA.DOCS VALUES (1, 'How do I Install Virtuoso on Linux?');
INSERT INTO DB.DBA.DOCS VALUES (2, 'How do I install virtuoso on Windows?');
INSERT INTO DB.DBA.DOCS VALUES (3, 'How do I install virtuoso on macOS?');
INSERT INTO DB.DBA.DOCS VALUES (4, 'How do I change the dba password?');

CREATE VECTOR INDEX DOCS_VEC
ON DB.DBA.DOCS (content)
USING 'granite-384-local'
WITH (
  dimensions      = 384,
  distance_metric = 'cosine',
  hnsw_m          = 16,
  ef_construction = 200
);
```

### The short form

Every `WITH` option above has a default, and `dimensions` is knowable from the model, so the same index can be created with none of them. The column can be left out too when the table has exactly one string column:

SQL

```
CREATE VECTOR INDEX DOCS_VEC ON DB.DBA.DOCS USING 'granite-384-local';
```

That gives `dimensions` 384 from the model, `cosine`, `hnsw_m` 16 and `ef_construction` 200 — identical to the explicit statement above. Spell the options out when you want something other than those defaults.

If the table has more than one character column, naming it is required: the server refuses rather than choosing for you, because indexing the wrong column succeeds quietly and only shows up later as poor search results:

Output

```
VEC11: Cannot infer a source column for vector index LIM_TWO_VEC: table DB.DBA.LIM_TWO
has 2 candidates (title, body). Name one explicitly: ON DB.DBA.LIM_TWO (<column>).
```

This query runs as follows:

1. **`CREATE TABLE`** defines a table with an integer PK and a text column.
2. **`INSERT`** populates rows before index creation so they get backfilled.
3. **`CREATE VECTOR INDEX`** binds the index to the `granite-384-local` model and builds the HNSW graph.

**Key concepts:**

- **`USING 'model_name'`** binds the index to a specific model in `ONNX_MODELS`.
- **`dimensions`** must match the model's output dimension exactly (unless `reduce = 'truncate'` is used, in which case it's the reduced dimension). It can also be **left out** — see the short form above.
- **`distance_metric`** can be `cosine` (default), `l2`, or `l1`.
- **`hnsw_m`** and **`ef_construction`** control HNSW graph quality.
- **`reduce`** can be `'none'` (default) or `'truncate'` — enables Matryoshka dimension reduction for MRL-trained models like OpenAI text-embedding-3-small. See [Index parameters: Matryoshka reduction](urn:osdi:sitevirtuoso/docs/vector-index/index-parameters/#matryoshka-dimension-reduction).

### What CREATE VECTOR INDEX does

1. Validates the model binding and dimension match.
2. Creates a hidden managed embedding table.
3. Creates the native HNSW index over the managed embeddings.
4. Creates `AFTER INSERT/UPDATE/DELETE` maintenance triggers.
5. **Backfills existing rows** — every row of the table, embedded inside the `CREATE` statement; the index is populated and searchable when `CREATE` returns.

The backfill reads the table in key order and embeds and inserts it in batches of 32 (`__vec_embed_batch_size`). An HTTP provider gets each batch in one request. A local model gets one text per call — its helper is no faster per text on a batch — so for a local model `CREATE` takes as long as embedding the table: 4,000 FiQA passages (704 characters on average) took 180 s with `granite-384-local`, about 22 a second, on a 10-core arm64 machine (Apple M1 Pro), 2026-10-10. Give the model more threads to go faster — see [Helper thread counts](urn:osdi:sitevirtuoso/docs/vector-index/embedding-models/#helper-thread-counts). A row that cannot be embedded fails the whole `CREATE`, naming the rows of its batch.

By default the backfill links each row into the graph as it goes, and an incremental graph is one 8 KB page per node: once it outgrows the buffer pool, every link is a disk read. For a large table, create the index with `build = 'bulk'` — every vector is staged first and the graph derived in one parallel pass, in packed pages — or size the buffer pool to hold the graph (Maintenance). Measured with a synthetic 384-dimension test model (no embedding cost) and the stock `virtuoso.ini` (10,000 buffers): 40,000 rows took 4.3 s with `build = 'bulk'`; the default build was still reading graph pages from disk after 12 minutes and was stopped. See [Index parameters: build](urn:osdi:sitevirtuoso/docs/vector-index/index-parameters/#build-default-auto-which-means-incremental).

Important

`CREATE VECTOR INDEX` is **atomic**: if any step fails, the whole index is rolled back — no key, hidden table, or triggers are left behind. A row that cannot be embedded fails the statement and names its batch, for example `VECQ1: Vector index ATOM_V_IDX backfill failed in rows 1 to 2: 22023: VEC31: Embedding dimension 3 does not match vector index dimension 384.`

### Tuning HNSW parameters for accuracy

| Parameter | Default | Accuracy-focused | Effect |
| --- | --- | --- | --- |
| `hnsw_m` | 16 | 32 | Max neighbors per node per layer |
| `ef_construction` | 200 | 400 | Search depth during index build |

For production use with 1536-dimensional OpenAI embeddings, `hnsw_m = 32` and `ef_construction = 400` are recommended.

---

## Bring your own vectors (precomputed embeddings)

If your table column **already holds embedding vectors** — you compute them in your own pipeline — you can index the column directly, with **no model**. This is the `source_kind = 'vector'` (passthrough) mode. `CREATE VECTOR INDEX` builds the HNSW graph from the column's vectors, and ordinary `INSERT` / `UPDATE` / `DELETE` keep the index in sync from the column value.

### Enable the mode (DBA-only)

Passthrough is opt-in, behind a registry flag that a DBA sets once:

SQL

```
registry_set ('__vvec_passthrough_column', 'on');
```

With the flag off, a `source_kind = 'vector'` create fails with **`VEC83`**:

Output

```
VEC83: Precomputed-vector passthrough column mode (source_kind='vector') is not enabled.
A DBA can enable it with: registry_set ('__vvec_passthrough_column', 'on').
```

### The source column

The source column must be able to hold a boxed vector value — **`ANY`**, **`LONG ANY`**, or **`(LONG) VARBINARY`**. A plain `VARCHAR`/`NVARCHAR` column is rejected at create time with **`VEC87`** (it cannot faithfully hold a vector). Each row's value may be either:

- a **native float array** — the canonical, most compact form, which is what the embedding functions return, or a `vector (0.1, 0.2, …)` of numbers; **or**
- a **serialized blob/string** — a stored `serialize (vector (…))`; the index deserializes it for you.

Prefer the native float-array form in storage (smallest, no per-element boxing).

### Create the index

Omit the `USING '<model>'` clause entirely and pass `source_kind = 'vector'`. In this example the vectors come from the bundled model, standing in for whatever pipeline produces yours:

SQL

```
CREATE TABLE DB.DBA.DOCS_V (
  id  INTEGER NOT NULL PRIMARY KEY,
  emb ANY                       -- holds a 384-d embedding per row
);

INSERT INTO DB.DBA.DOCS_V VALUES (1, DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL ('granite-384-local', 'How do I Install Virtuoso on Linux?'));
INSERT INTO DB.DBA.DOCS_V VALUES (2, DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL ('granite-384-local', 'How do I install virtuoso on Windows?'));
INSERT INTO DB.DBA.DOCS_V VALUES (3, DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL ('granite-384-local', 'Steps to reset a forgotten password'));

CREATE VECTOR INDEX DOCS_V_IDX
ON DB.DBA.DOCS_V (emb)
WITH (
  dimensions      = 384,
  distance_metric = 'cosine',
  hnsw_m          = 16,
  ef_construction = 200,
  source_kind     = 'vector'    -- no USING clause; the column IS the vector
);
```

Rules:

- **`source_kind = 'vector'` replaces `USING`** — supplying both is contradictory and fails with **`VEC84`**; omitting both fails with **`VEC12`**.
- **`dimensions`** is the vector length; every row's vector must match it.
- `reduce = 'truncate'` is **not** available (it needs a model) → **`VEC86`**.
- Everything else (`distance_metric`, `hnsw_m`, `ef_construction`, `rebuild`) works exactly as in the model path, and existing rows are **backfilled** on create.

### Maintenance (upsert from the column)

`INSERT` / `UPDATE` / `DELETE` on the base table maintain the index from the column value — no model is invoked:

- a new/changed vector is upserted into the graph;
- a **`NULL`** column value removes the row from the index;
- a **malformed** value (`VEC30`) or a **wrong-dimension** value (`VEC31`) is a hard, deterministic per-row error: the queue row is marked `error` with the message (for example `22023: VEC31: Embedding dimension 2 does not match vector index dimension 384.`), counted in `DB.DBA.VEC_SQL_WORKER_STATUS ()`, and **not** retried. At create time such a row **fails the `CREATE`** with an actionable message; fix the row and recreate.

The hidden managed embedding table stays server-owned exactly as in the model path — the `VEC82` guard still blocks any non-DBA write to it.

### Querying a passthrough index

Query **by vector** — the index has no model, so it cannot embed query text:

SQL

```
SELECT TOP 3 id,
       X_SIMILAR (emb, DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL ('granite-384-local', 'I forgot my password'), 10) AS score
  FROM DB.DBA.DOCS_V
 WHERE X_SIMILAR (emb, DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL ('granite-384-local', 'I forgot my password'), 10) > 0.5
 ORDER BY 2 DESC;
```

| id | score |
| --- | --- |
| 3 | 0.925 |
| 1 | 0.750 |
| 2 | 0.746 |

A **text** query against a passthrough index raises a catchable **`VEC88`**:

Output

```
VEC88: Vector index DOCS_V_IDX has no embedding model (source_kind='vector'); query it
by vector with X_SIMILAR (col, <vector>, k), not by text.
```

`__VEC_INDEX_REBUILD` (including `'fast'`) works normally.

---

## Using a remote HTTP embedding provider

Instead of the local ONNX model, you can bind an index to a remote embedding API.

### Step 1: Store the API key (DBA-only)

SQL

```
DB.DBA.ONNX_API_KEY_SET ('ONNX_API_KEY_OPENAI', '<your-secret-key>');
```

### Step 2: Register the provider model

SQL

```
DB.DBA.ONNX_REGISTER_HTTP_EMBED_MODEL (
  _model_name,     -- alias for CREATE VECTOR INDEX
  _provider,       -- 'openai', 'ollama', 'lmstudio', or 'openai_compatible'
  _remote_model,   -- the provider's model name
  _dimensions,     -- must match the provider model's output dimension
  _endpoint        := NULL,   -- null = provider default
  _api_key_name    := NULL,   -- the registry name from step 1 (NOT the key itself)
  _timeout         := 30,     -- seconds, 1 to 300
  _description     := NULL,
  _insecure        := 0);
```

Caution

The 6th parameter (`_api_key_name`) is the **registry name** of the key (e.g. `'ONNX_API_KEY_OPENAI'`), **not** the key itself. For the `openai` provider, passing `null` auto-defaults to `ONNX_API_KEY_OPENAI`.

The endpoint must be `https://` unless it is on a loopback or private-network host; a plain-`http` public endpoint is refused at registration so an API key is never sent in cleartext. See [Embedding models](urn:osdi:sitevirtuoso/docs/vector-index/embedding-models/#registering-a-provider-model) for every parameter.

Examples:

SQL

```
-- OpenAI text-embedding-3-small (1536 dimensions)
DB.DBA.ONNX_REGISTER_HTTP_EMBED_MODEL (
  'openai-small', 'openai',
  'text-embedding-3-small', 1536,
  NULL, 'ONNX_API_KEY_OPENAI', 30);

-- Local Ollama (no key needed)
DB.DBA.ONNX_REGISTER_HTTP_EMBED_MODEL (
  'ollama-mxbai', 'ollama',
  'mxbai-embed-large', 1024);

-- Any OpenAI-compatible endpoint
DB.DBA.ONNX_REGISTER_HTTP_EMBED_MODEL (
  'my-remote', 'openai_compatible',
  'bge-large-en', 1024,
  'https://my-host/v1/embeddings', 'ONNX_API_KEY_MYHOST', 30);
```

### Step 3: Create an index over it

SQL

```
CREATE VECTOR INDEX DOCS_VEC ON DB.DBA.DOCS (content)
USING 'openai-small'
WITH (dimensions = 1536, distance_metric = 'cosine',
      hnsw_m = 32, ef_construction = 400);
```

Note

A remote-backed index makes an HTTP call to embed the query text, so query latency includes the provider round-trip. See [Avoiding duplicate embedding calls](#avoiding-duplicate-embedding-calls-with-remote-providers).

---

## Automatic maintenance

After creation, base-table writes keep the index current **without** running embedding generation in the write transaction:

- **INSERT / UPDATE / DELETE** triggers enqueue work into `DB.DBA.VEC_SQL_QUEUE`
- **Hash-skip** detects unchanged text and skips re-embedding
- **Deletes** remove the managed row and the HNSW node

### Draining the queue

SQL

```
-- One-shot manual drain (up to N items); returns the number processed:
SELECT DB.DBA.VEC_SQL_QUEUE_DRAIN(1000);

-- Or schedule a worker (the argument is the scheduler interval, in minutes):
DB.DBA.VEC_SQL_WORKER_START(1);
SELECT obj2json (DB.DBA.VEC_SQL_WORKER_STATUS());
-- ["pending",0,"error",0,"last_run",...,"last_count",...,"last_error",null]
DB.DBA.VEC_SQL_WORKER_STOP();
```

`VEC_SQL_WORKER_START` registers a scheduled event, `VEC_SQL_WORKER`, that drains up to 100 queued rows per run (and feeds new RDF literals to any [RDF literal index](urn:osdi:sitevirtuoso/docs/vector-index/knn-similar/)). Virtuoso's scheduler counts the interval in **minutes** and only wakes every `SchedulerInterval` minutes (`[Parameters]` in `virtuoso.ini`, 10 in the shipped file), so a scheduled drain can lag a write by that long. Call `VEC_SQL_QUEUE_DRAIN` yourself when a write has to be searchable at once.

Tip

A freshly inserted row becomes searchable after the next drain. `CREATE` backfills inline, so create-then-search works immediately.

---

## Querying

### Query with text (primary form)

`X_SIMILAR` and `VECTOR_DISTANCE` accept the query as **plain text**. The server resolves the column's vector index, embeds the text with that index's own bound model, and drives the HNSW search.

SQL

```
SELECT TOP 10 id, content,
       VECTOR_DISTANCE(content, 'how to reset my password') AS distance,
       X_SIMILAR(content, 'how to reset my password')       AS score
FROM DB.DBA.DOCS
ORDER BY VECTOR_DISTANCE(content, 'how to reset my password');
```

Example output:

| id | content | distance | score |
| --- | --- | --- | --- |
| 4 | How do I change the dba password? | 0.252 | 0.874 |
| 1 | How do I Install Virtuoso on Linux? | 0.473 | 0.763 |
| 2 | How do I install virtuoso on Windows? | 0.481 | 0.759 |
| 3 | How do I install virtuoso on macOS? | 0.495 | 0.753 |

The password question ranks first although it shares only one word with the query. The three install questions match the query poorly, yet each still scores about 0.75: scores are on the model's own scale, and with the bundled model text that shares little with the query lands well above 0.5. Judge relevance by the gap — here 0.87 against 0.75 — and set any threshold from your own data. See [Similarity metrics](urn:osdi:sitevirtuoso/docs/vector-index/similarity-metrics/).

**Key concepts:**

- **`VECTOR_DISTANCE`** = raw distance (lower = closer)
- **`X_SIMILAR`** = similarity in `[0, 1]` (higher = closer)
- For **cosine**: `score = 1 - distance / 2`. Cosine distance runs 0..2, so halving maps it onto `[0, 1]`: identical vectors score 1.0, orthogonal vectors 0.5, and only opposed vectors approach 0.0. Where unrelated *text* lands depends on the model — about 0.75 for the install rows above.
- For **L1/L2**: `score = 1 / (1 + distance)`

Note

The text must be written into the statement as a literal. Text bound as a parameter (`?`, as ODBC, JDBC and `exec` pass it) is not embedded, and the search fails with `VEC33: Malformed query vector`. Embed a parameter yourself and pass the vector:

SQL

```
SELECT TOP 10 id, content, X_SIMILAR (content, qv.v) AS score
  FROM DB.DBA.DOCS
 CROSS JOIN (SELECT DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL ('granite-384-local', ?) AS v) qv
 ORDER BY VECTOR_DISTANCE (content, qv.v);
```

### Leading-index (HNSW-driven) search

When the table's key is otherwise unbound and the query states how many rows it wants — a `TOP n`, or `X_SIMILAR`'s third argument `k` — `ORDER BY VECTOR_DISTANCE(...)` or `X_SIMILAR(...)` in the `WHERE` clause drives the HNSW ANN node directly:

SQL

```
SELECT TOP 10 id, content
FROM DB.DBA.DOCS
WHERE X_SIMILAR(content, 'reset password') > 0.8;
-- 4  How do I change the dba password?
```

With a threshold of 0.7 the same query would also return the three install rows, which score about 0.74 against `'reset password'`.

### Filter / rerank mode

When the table is already constrained by another selective predicate, or the query gives no bound (no `TOP`, no `k`), `X_SIMILAR` scores the incoming rows exactly instead of leading — every row that clears the threshold is returned, with no approximation:

SQL

```
SELECT id, content
FROM DB.DBA.DOCS
WHERE id < 1000
  AND X_SIMILAR(content, 'reset password') > 0.8;
-- 4  How do I change the dba password?
```

### Using the k parameter for ANN search

The third argument to `X_SIMILAR` is `k` — the number of candidates to retrieve from the HNSW graph, which also sets how widely the search explores. Without it, the query's `TOP n` is used; with neither, the query is answered by exact scoring rather than the ANN search. A higher `k` improves recall:

SQL

```
-- k=100 retrieves 100 candidates from HNSW, then TOP 10 picks the best:
SELECT TOP 10 id, content,
       X_SIMILAR(content, 'reset password', 100) AS score
FROM DB.DBA.DOCS
WHERE X_SIMILAR(content, 'reset password', 100) > 0.0
ORDER BY X_SIMILAR(content, 'reset password', 100) DESC;
```

### Tuning accuracy at query time

| Goal | k value | Effect |
| --- | --- | --- |
| Fast, approximate | about the `TOP n` you want (e.g. 10) | Quick search, may miss some matches |
| Balanced | 50–100 | Good recall for most use cases |
| Maximum accuracy | 200+ | Explores more of the graph, highest recall |
| Exact | omit both `k` and `TOP` | Every row is scored; no approximation, cost grows with the table |

Tip

For maximum accuracy, set `k` much higher than your actual result count (e.g. `k=200` for `TOP 10` results). If results are still poor, rebuild the index with higher `hnsw_m` / `ef_construction`.

### Avoiding duplicate embedding calls with remote providers

With a **local** model, embedding a short query text costs a few milliseconds, so repeating the text literal is cheap. With a **remote** model every embedding is an HTTP request, and a `TOP n` query that ranks with `ORDER BY VECTOR_DISTANCE (col, 'text')` makes two of them, with or without a projected distance or score (`WHERE X_SIMILAR (col, 'text') > …` makes one, and two once it also projects the score). Embed once and reuse the vector:

SQL

```
SELECT TOP 10 id, content,
       VECTOR_DISTANCE(content, qv.v) AS distance,
       X_SIMILAR(content, qv.v)       AS score
FROM DB.DBA.DOCS
CROSS JOIN (SELECT DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL('openai-small',
  'how to reset my password') AS v) qv
ORDER BY VECTOR_DISTANCE(content, qv.v);
```

### Confirm which mode (EXPLAIN)

SQL

```
EXPLAIN('SELECT TOP 10 id, content,
  VECTOR_DISTANCE(content, ''reset password'') AS distance
FROM DB.DBA.DOCS
ORDER BY VECTOR_DISTANCE(content, ''reset password'')');
```

- **Leading mode**: output contains `vvec_search_node` with `mode=ann`
- **Filter mode**: output contains a call to `__VEC_INDEX_ROW_SIMILARITY`, `mode=row_distance`, and no `vvec_search_node`

---

## Inspect and operate

Each of these returns a name/value vector; `obj2json` prints it readably in `isql`, and `get_keyword ('graph_nodes', …)` reads a single field.

SQL

```
-- Index metrics (node/insert/deleted counts, dimension, hnsw_m, state):
SELECT obj2json (__VEC_INDEX_METRICS ('DOCS_VEC'));
-- ["dimension",384,"distance_metric","cosine","hnsw_m",16,"managed_rows",4,
--  "graph_nodes",4,"deleted_nodes",0,"insert_count",4,"search_count",4,
--  "search_usec",0,"bytes_written",65536,"layer0_hit_rate",1,
--  "state","READY_FOR_INTERNAL_LOAD"]

-- Page stats (node pages, entry point, max layer, page format):
SELECT obj2json (__VEC_INDEX_PAGE_STATS ('DOCS_VEC'));
-- ["metadata_pages",1,"node_pages",4,"deleted_pages",0,"entry_point_dp",38864,
--  "max_layer",0,"inline_dim_limit",1742,"overflow_pages",0,
--  "deleted_overflow_pages",0,"physical_node_pages",4,"format","legacy"]

-- Exact-vs-ANN recall validation and graph/embedding consistency:
SELECT obj2json (__VEC_INDEX_VALIDATE ('DOCS_VEC'));
-- ["queries_tested",4,"recall_at_10",1,"distance_metric","cosine","dimension",384,
--  "consistent",1,"consistency_detail","graph agrees with the stored embeddings",
--  "graph_epoch",4,"data_epoch",4,"active_nodes",4,"embedding_rows",4]

-- Queue/worker health:
SELECT obj2json (DB.DBA.VEC_SQL_WORKER_STATUS ());
```

Important

The `__VEC_*` functions are **BIFs** — call them **unqualified** (no `DB.DBA.` prefix). The `VEC_SQL_*` and `ONNX_*` functions are stored procedures — call them with the `DB.DBA.` prefix.

### Rebuild and compact

`__VEC_INDEX_REBUILD` rebuilds the HNSW graph from the durable stored embeddings — no re-embedding or model call is needed. Use it to recover an index that was marked `needs_rebuild` after an unclean shutdown (a `VEC80` error on search), or to **compact** an index that has grown into the legacy page format through many incremental inserts:

SQL

```
-- Check the current page format:
SELECT get_keyword ('format', __VEC_INDEX_PAGE_STATS ('DOCS_VEC'));   -- legacy

-- Fast rebuild → compacts to the packed page format (~1.3× amplification at 384-d):
SELECT __VEC_INDEX_REBUILD ('DOCS_VEC', 'fast');                       -- 4 (embeddings)
SELECT get_keyword ('format', __VEC_INDEX_PAGE_STATS ('DOCS_VEC'));   -- packed

-- Confirm health:
SELECT __VEC_INDEX_HEALTH_RECHECK ('DOCS_VEC');  -- 1 = ok
```

Name the method: called with only the index name, `__VEC_INDEX_REBUILD` uses the index's `rebuild` option, whose default `auto` chooses the fast builder only from 10,000 embeddings and otherwise replays the graph incrementally — which leaves a small index in the legacy format.

For the rebuild methods (`replay`/`fast`/`auto`), durability epochs, and crash recovery, see Maintenance: durability and recovery.

---

## Drop an index

SQL

```
DROP INDEX DOCS_VEC;
```

This removes the hidden managed table, maintenance triggers, and the native HNSW index. It does not drop the base table or the registered model.

---

## Permissions

| To | A non-DBA account needs |
| --- | --- |
| Create or drop a vector index | A DBA account. A non-DBA is refused even on a table it owns — the create reads the model registry and runs DBA-owned maintenance procedures. |
| Insert, update or delete rows of an indexed table | The ordinary table privileges. The maintenance triggers enqueue the change on the account's behalf; draining the queue is a DBA operation. |
| Search with `X_SIMILAR` / `VECTOR_DISTANCE` by text | `SELECT` on the table, and read access to the model registry: the `SPARQL_SELECT` role, or `GRANT SELECT (model_id, model_name, model_type, handle) ON DB.DBA.ONNX_MODELS TO "<account>"`. Without the latter, a text search fails with `SQ033: SELECT access denied for column model_id of table DB.DBA.ONNX_MODELS`. |

---

## Deployment and sizing

A server starts **two ONNX helper processes per loaded local model**; a SPARQL `refresh vector index` raises the pool to its embedding concurrency (`__vec_embed_concurrency`, default 4), and a pool does not shrink while the server runs. With the bundled model each helper holds **265–285 MB** at rest (265 MB freshly loaded, 276–285 MB after a few short texts), and its inference arenas grow with long inputs — 547–620 MB after one batch of 32 long passages. On top of that sits Virtuoso's buffer pool (`NumberOfBuffers * 8 KB`).

### Swap recommendations

| Instance RAM | Swap | Notes |
| --- | --- | --- |
| 0.5 GB | 2 GB (essential) | Below the practical floor: a stock server and its two helpers measured ~840 MB at rest |
| 1 GB | 2 GB (recommended) | Covers model-load spike + batch drain |
| 2 GB | 2–4 GB (safety net) | Fine with tuned RAM |

Caution

EC2 instances ship **no swap by default**. Without swap, RAM exhaustion triggers the Linux OOM killer, which will likely kill the `onnx_helper` process.

### Tuning for small instances

1. **Cut `NumberOfBuffers`** — budget ~64 MB per 8000 buffers
2. **Limit ONNX Runtime threads** on 1–2 vCPU nodes
3. **Budget two or more helpers per local model** — up to about 285 MB each at rest with the bundled model
4. **Keep embedding batches and `__vec_embed_concurrency` modest**

---

## Limitations

- The table needs a single-column integer primary key (`INTEGER`, `BIGINT`, `SMALLINT` or `NUMERIC`); composite and character keys are refused with `VEC11`
- The source column must be a character column (`VARCHAR`, `NVARCHAR`, `LONG VARCHAR`, `LONG NVARCHAR`), or `ANY` / `LONG ANY` / `(LONG) VARBINARY` for [precomputed vectors](#bring-your-own-vectors-precomputed-embeddings)
- `dimensions` must match the model's output dimension exactly (or be the reduced dimension with `reduce = 'truncate'`)
- The choice between leading ANN search and filter/rerank is the optimizer's: another selective predicate on the table keeps a query in filter mode, and otherwise the ANN path is costed from the index's own search statistics. `VectorANNCostBias` in the `[Parameters]` section of `virtuoso.ini` (default `1.0`) scales that cost; values below 1 favour ANN plans. Check the choice with [EXPLAIN](#confirm-which-mode-explain)

---

## Troubleshooting

| Symptom | Cause & fix |
| --- | --- |
| `SR185: Undefined procedure DB.DBA.ONNX_EMBED_TEXT` | The `vonnx` plugin isn't loaded. Add it to `[Plugins]` in `virtuoso.ini`, restart. The server log then shows `ONNX bootstrap disabled granite-384-local: …` and `DB.DBA.ONNX_MODELS` has no row for it. |
| `VEC18: Model granite-384-local is not present in DB.DBA.ONNX_MODELS` | The plugin did not load, or — on a new database — the bootstrap could not find the model files. Check the server log for the `ONNX bootstrap` line, which names the paths it tried, and set `[ONNX] ModelRoot` / `HelperPath` to absolute paths. |
| `CREATE VECTOR INDEX` fails | Verify ONNX health: `SELECT model_name, handle FROM DB.DBA.ONNX_MODELS;` (handle must be non-null). |
| `dimensions` mismatch on create | `WITH (dimensions = N)` must equal the model's output (bundled `granite-384-local` = 384, OpenAI `text-embedding-3-small` = 1536). |
| Query returns 0 rows | The HNSW graph may be empty. Check `get_keyword ('graph_nodes', __VEC_INDEX_METRICS ('DOCS_VEC'))` — it should be > 0. If 0, drop and recreate. |
| HTTP provider answers `401` | The key is missing, or the 6th registration argument was the key value instead of its registry name. Store the key with `DB.DBA.ONNX_API_KEY_SET` and register with its name (or `null` for `openai`). |
| New rows don't appear in search | Drain the queue: `SELECT DB.DBA.VEC_SQL_QUEUE_DRAIN(1000);` |
| `VEC33: Malformed query vector` | The query text was bound as a parameter; only a text literal is embedded. Embed the parameter with `DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL` and pass the vector — see [Query with text](#query-with-text-primary-form). |
| `DROP VECTOR INDEX` syntax error | Use `DROP INDEX <name>;` — no `ON <table>` clause. |

### Health check

SQL

```
SELECT model_name, handle FROM DB.DBA.ONNX_MODELS WHERE handle IS NOT NULL;
SELECT length (DB.DBA.ONNX_EMBED_TEXT_FOR_MODEL('granite-384-local', 'health check'));  -- 384
SELECT obj2json (__VEC_INDEX_METRICS ('DOCS_VEC'));
```

---

## Next steps

- **[SPARQL vector index how-to](urn:osdi:sitevirtuoso/docs/vector-index/howto-sparql-vector-index/)** — Use vector indexes with RDF data and SPARQL queries
- **[Vector index overview](urn:osdi:sitevirtuoso/docs/vector-index/)** — Architecture and concepts

## Related content

**Reference:**

- [ONNX Runtime](https://github.com/microsoft/onnxruntime) — Microsoft ONNX Runtime project
- [granite-embedding-30m-english](https://huggingface.co/ibm-granite/granite-embedding-30m-english) — Bundled embedding model
- [HNSW algorithm](https://github.com/nmslib/hnswlib) — Hierarchical Navigable Small World graphs

[PreviousTutorial: Semantic search over OpenLink FAQs with a SPARQL vector index](urn:osdi:sitevirtuoso/docs/vector-index/tutorial-faq-semantic-search/) [NextHow-To: Build and use the SPARQL vector index (Virtuoso)](urn:osdi:sitevirtuoso/docs/vector-index/howto-sparql-vector-index/)

On this page

1. [Prerequisites](#prerequisites)
2. [Build Virtuoso with ONNX support](#build-virtuoso-with-onnx-support)
3. [Models](#models)
4. [Create a vector index](#create-a-vector-index)
5. [Bring your own vectors (precomputed embeddings)](#bring-your-own-vectors-precomputed-embeddings)
6. [Using a remote HTTP embedding provider](#using-a-remote-http-embedding-provider)
7. [Automatic maintenance](#automatic-maintenance)
8. [Querying](#querying)
9. [Inspect and operate](#inspect-and-operate)
10. [Drop an index](#drop-an-index)
11. [Permissions](#permissions)
12. [Deployment and sizing](#deployment-and-sizing)
13. [Limitations](#limitations)
14. [Troubleshooting](#troubleshooting)
15. [Next steps](#next-steps)
16. [Related content](#related-content)