ApsaraDB RDS:Vector storage

Step 1: Create a table with a vector index

The following example creates a table with a 5-dimension embedding column and an HNSW index that uses cosine distance.

-- Create a table with a 5-dimension vector column and an HNSW index
CREATE TABLE product_embeddings (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(255),
  embedding VECTOR(5) NOT NULL,
  -- Specify M (graph connectivity) and the distance metric
  VECTOR INDEX idx_embedding(embedding) M=16 DISTANCE=COSINE
);

Step 2: Insert vector data

Use VEC_FROMTEXT to convert a JSON-formatted array string to the VECTOR type.

INSERT INTO product_embeddings (product_name, embedding) VALUES
('product_A', VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.5]')),
('product_B', VEC_FROMTEXT('[0.6, 0.7, 0.8, 0.9, 1.0]')),
('product_C', VEC_FROMTEXT('[0.11, 0.22, 0.33, 0.44, 0.55]'));

Step 3: Query nearest neighbors

VEC_DISTANCE auto-detects the distance metric from the index when the column has a vector index. Order results by the score in ascending order and use LIMIT to retrieve the top-k results.

-- Return the 2 products most similar to the query vector
SELECT
  id,
  product_name,
  VEC_DISTANCE(embedding, VEC_FROMTEXT('[0.1, 0.2, 0.3, 0.4, 0.51]')) AS similarity_score
FROM product_embeddings
ORDER BY similarity_score ASC  -- smaller cosine distance = more similar
LIMIT 2;

Parameter reference

Parameter descriptions:

• `vidx_default_distance` — The default distance metric for vector operations.

  • EUCLIDEAN: Straight-line distance in multi-dimensional space. Use this when absolute magnitude matters.

  • COSINE: Cosine of the angle between two vectors. Use this when directional similarity matters and vector length is irrelevant.

• `vidx_hnsw_default_m` — Maximum number of outgoing edges per node in the HNSW graph. Higher values improve recall at the cost of more memory and slower index builds.

• `vidx_hnsw_ef_search` — Search scope during ANN queries. Higher values improve recall but increase query latency.

• `vidx_hnsw_cache_size` — Maximum memory the HNSW index cache may use, in bytes.

Modify parameters

1. Go to the Instances page, select the region, and click the instance ID.

2. In the left navigation pane, click Parameter Settings.

3. On the Editable Parameters tab, search for the parameter and set its value.

4. Click OK, then click Submit Parameters. In the dialog that appears, choose when to apply the changes.

Vector index requirements

• Vector indexes are supported only on tables using the InnoDB storage engine.

• The primary key of a table with a vector index cannot exceed 256 bytes.

• The inplace syntax cannot be used to create, modify, or delete vector indexes.

• Vector indexes cannot be set to INVISIBLE.

• Tables with vector indexes do not support the Recycle Bin feature.

Query and isolation level

• Data modifications and queries on vector indexes support only the Read Committed (RC) isolation level.

Primary/standby consistency

• Because the HNSW algorithm involves randomness (random levels and heuristic graph construction), the index graph structures on the primary and standby instances are not guaranteed to be identical. Query results may differ slightly between the two.

Data migration

• If stored procedures or functions in the source database use the vector data type, synchronization or migration to a destination database that does not support vectors will fail.