PolarDB:Perform vector retrieval using the MySQL protocol

Define a vector column

Use VECTOR(N) in a CREATE TABLE or ALTER TABLE statement to define a vector column, where N is the number of dimensions (1–16,383). Each dimension is a single-precision floating-point number (4 bytes).

-- Define a 4-dimensional vector column
CREATE TABLE t1 (id INT PRIMARY KEY, v1 VECTOR(4));

Constraints:

• A vector column supports equality comparison only with another vector column. It cannot be compared with other data types.

• A vector column cannot be a primary key, foreign key, unique key, or partition key.

• If N exceeds 16,383, the following error is returned: Data size (xxx Bytes, xxx dimensions) exceeds VECTOR max (65532 Bytes, 16383 dimensions) for column: 'xxx'

Convert between string and binary formats

PolarDB provides two functions for converting vector data between text and binary formats.

`STRING_TO_VECTOR` — converts a string to the internal binary format.

• Input format: comma-separated floating-point numbers enclosed in square brackets, for example '[1.2, 3.4, 5.6]'.

• The output uses little-endian byte order. For example, 1.0 (hex 0x3F800000) is stored as 0000803F.

• If the input format is invalid, the error Data cannot be converted to a valid vector: 'xxx' is returned.

SELECT HEX(STRING_TO_VECTOR('[1,2,3,4]'));
-- Output: 0000803F000000400000404000008040

`VECTOR_TO_STRING` — converts binary vector data back to a readable string.

• Input: binary data where every 4 bytes represent one dimension in little-endian byte order.

• If the input is invalid, the error Data cannot be converted to a valid vector: '' is returned.

SELECT VECTOR_TO_STRING(0x0000803F000000400000404000008040);
-- Output: [1.00000e+00,2.00000e+00,3.00000e+00,4.00000e+00]

Work with vector data

Add a vector column to an existing table:

ALTER TABLE t1 ADD COLUMN v1 VECTOR(4);

Insert a vector:

-- As a string using STRING_TO_VECTOR
INSERT INTO t1 (id, v1) VALUES (1, STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]'));

-- In binary format directly
INSERT INTO t1 VALUES (2, 0x0000803F000000400000404000008040);

Upsert a vector:

INSERT INTO t1 VALUES (1, STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]'))
ON DUPLICATE KEY UPDATE v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]');

Update a vector:

UPDATE t1 SET v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]') WHERE id = 1;

Delete a row by vector value:

DELETE FROM t1 WHERE v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]');

View vector values as strings:

SELECT VECTOR_TO_STRING(v1) FROM t1;

Create a vector index

Define a vector index by setting the column COMMENT in a CREATE TABLE or ALTER TABLE statement.

Syntax:

COMMENT 'imci_vector_index=<algorithm>(<parameters>) [other_comments]'

• imci_vector_index= is a fixed prefix that declares the vector index.

• Always include parentheses after the algorithm name, even when using all defaults: HNSW().

• Separate multiple parameters with commas.

• Each parameter uses the format name=value or name:value.

Index algorithms:

Index build parameters:

All three algorithms share the following build-time parameters:

FAISS_HNSW_PQ has two additional parameters for controlling product quantization:

Example 1: Define a vector index when creating a table.

-- Method 1: Enable columnstore for the entire table
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    v1 VECTOR(4) COMMENT 'imci_vector_index=HNSW(metric=COSINE, max_degree=16)'
) COMMENT 'COLUMNAR=1';

-- Method 2: Enable columnstore only for the vector column
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    description TEXT,
    v1 VECTOR(4) COMMENT 'COLUMNAR=1, imci_vector_index=HNSW(metric=COSINE)'
);

Example 2: Add a vector index to an existing table.

-- Method 1: Enable table-level columnstore and define the vector index in the same statement
ALTER TABLE t1
  COMMENT "COLUMNAR=1",
  MODIFY COLUMN v1 VECTOR(4) COMMENT "imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)";

-- Method 2: Enable columnstore only for the vector column
-- The vector index is created automatically based on the column COMMENT
ALTER TABLE t1 MODIFY COLUMN v1 VECTOR(4)
  COMMENT "COLUMNAR=1 imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)";

Modify a vector index

Requires version 8.0.2.2.32 or later.

Change the index algorithm or parameters by modifying the column COMMENT.

-- Change the algorithm from HNSW to FAISS_HNSW_FLAT
ALTER TABLE t1 MODIFY COLUMN v1 VECTOR(4)
  COMMENT "imci_vector_index=FAISS_HNSW_FLAT(metric=COSINE,max_degree=16,ef_construction=300)";

-- Change max_degree from 16 to 32
ALTER TABLE t1 MODIFY COLUMN v1 VECTOR(4)
  COMMENT "imci_vector_index=HNSW(metric=COSINE,max_degree=32,ef_construction=300)";

Delete a vector index

Delete the vector index along with the columnstore index:

A vector index cannot exist independently of a columnstore index. Disabling columnstore deletes the vector index by cascade.

ALTER TABLE t1 COMMENT 'COLUMNAR=0';

Delete only the vector index (requires version 8.0.2.2.32 or later):

Clear the column COMMENT to remove the vector index while keeping the columnstore index.

ALTER TABLE t1 MODIFY COLUMN v1 VECTOR(4) COMMENT "";

Distance calculation

Use the DISTANCE function to calculate the similarity between two vectors.

DISTANCE(vector1, vector2, '<metric>')

SELECT DISTANCE(v1, STRING_TO_VECTOR('[1.2,2.3,3.4,4.5]'), 'COSINE') FROM t1;

ANN search

ANN search uses a vector index to retrieve approximate nearest neighbors efficiently. All of the following conditions must be met for the optimizer to use the vector index:

• The query includes both ORDER BY and LIMIT clauses.

• The first expression in ORDER BY is DISTANCE(...).

• The sort direction is ASC.

• The vector column in DISTANCE(...) has an active vector index.

• The metric in DISTANCE(...) matches the metric used to build the index.

• If the query includes a JOIN, the execution plan must use a Right Deep Join Tree, and the DISTANCE(...) field must come from the driving table.

• The query cannot contain GROUP BY. To aggregate results, wrap the vector search in a subquery.

Enable ANN search:

Run these two session-level commands before executing ANN queries.

-- Allow the optimizer to use vector indexes
SET imci_enable_vector_search = ON;

-- Force queries to use the column store execution path
SET cost_threshold_for_imci = 0;

Run an ANN query:

-- Find the top 5 most similar records
SELECT id, v1
FROM t1
WHERE category_id = 123
ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[...]'), 'COSINE') ASC
LIMIT 5;

Using `GROUP BY` with vector search:

GROUP BY cannot appear in the same query as ORDER BY DISTANCE(...). Run the vector search in a subquery first, then aggregate.

-- Incorrect: GROUP BY conflicts with ORDER BY DISTANCE()
-- SELECT category_id, COUNT(*) FROM t1
-- GROUP BY category_id
-- ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[...]'), 'COSINE') ASC
-- LIMIT 5;

-- Correct: perform ANN search in a subquery, then aggregate
SELECT category_id, COUNT(*)
FROM (
    SELECT id, category_id
    FROM t1
    ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[...]'), 'COSINE') ASC
    LIMIT 100
) AS nearest_neighbors
GROUP BY category_id;

ANN search parameters:

KNN (exact) search

KNN search traverses all data to calculate distances, guaranteeing 100% recall. It does not require a vector index and is suitable for small datasets.

-- Find the 10 most similar records
SELECT id, VECTOR_TO_STRING(v1)
FROM t1
ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[1.2,2.3,3.4,4.5]'), 'COSINE')
LIMIT 10;

Vector queries with predicates

Add WHERE conditions to filter results alongside vector similarity.

SELECT * FROM t1
WHERE id < 10
ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[1.2, 2.3, 3.4, 4.5]'), 'COSINE')
LIMIT 2;

The optimizer selects one of three execution strategies based on the estimated selectivity of the filter:

Adaptive execution: If the optimizer's row count estimate is inaccurate, the system can switch strategies at runtime. For example, when using the inline filter strategy, if the actual number of matching rows turns out to be less than imci_vector_search_prefilter_rows, the system switches to prefilter dynamically.

Partition optimization: For predicates based on tenant IDs or tags, use LIST DEFAULT HASH partitioning. The optimizer uses partition pruning to select the best strategy per partition — postfilter for large partitions, prefilter or inline filter for smaller ones.

Predicate parameters:

Index snapshot offset

Query INFORMATION_SCHEMA.IMCI_INDEX_STATS to see how many vectors the columnstore index has loaded into its snapshot.

SELECT schema_name, table_name, vector_rows
FROM information_schema.imci_index_stats;

A VECTOR_ROWS value of 0 means no vector index snapshot is available — either the table has no vector index, or a newly created index has not yet generated a valid snapshot.

Compare VECTOR_ROWS with ROW_ID in INFORMATION_SCHEMA.IMCI_INDEXES to estimate build latency. The vector index still works with some latency — any vectors not yet written to the index are handled automatically during a search.

Physical vector index status

Query INFORMATION_SCHEMA.IMCI_VECTOR_INDEX_STATS to inspect the physical state of each vector index.

SELECT schema_name, table_name, column_name, vectors, memory_usage, storage_usage
FROM information_schema.imci_vector_index_stats;

ANN search is unavailable for any column not returned by this view. A VECTORS value of 0 after a cluster restart does not mean the index is lost. The index is persisted to disk. The first query after a restart triggers the data to load into memory. For accurate snapshot information, use VECTOR_ROWS in INFORMATION_SCHEMA.IMCI_INDEX_STATS.