Vector Overview

MariaDB Vector is a feature that allows MariaDB Server to perform as a relational vector database. Vectors generated by your favourite AI model can be stored and searched for in MariaDB.

It is only available in the Vector Preview release, for source download only, or through the the pre-built docker image on quay.io.

In this initial preview, there is not yet a dedicated VECTOR data type, as we've focused on implementing a usable indexed search.

Any data type that can store a sequence of bytes can be used, for example VARBINARY or BLOB. The index is implemented in a way that supports different storage engines, and InnoDB and MyISAM at least have been tested and known to work.

The initial implementation uses the Modified HNSW algorithm for searching in the vector index (to solve the so-called Approximate Nearest Neighbor problem), and defaults to Euclidean distance.

Creating

Vectors are defined using VECTOR INDEX for the index definition in the CREATE TABLE statement. For now, for testing purposes, avoid the CREATE VECTOR INDEX statement, which hasn't been fully implemented. Declare the index as a part of your CREATE TABLE statement:

CREATE TABLE v (
     id INT PRIMARY KEY,
     v BLOB NOT NULL,
     VECTOR INDEX (v)
);

Inserting

Vector columns store 32-bit IEEE 754 floating point numbers.

INSERT INTO v VALUES 
     (1, x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
     (2, x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
     (3,x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
     (4,x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
     (5,x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
     (6,x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
     (7,x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
     (8,x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
     (9,x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
     (10,x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');

Querying

VEC_Distance is a function that calculates a Euclidean (L2) distance between two points.

SELECT id FROM v 
  ORDER BY VEC_Distance(v, x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
+----+
| id |
+----+
| 10 |
|  7 |
|  3 |
|  9 |
|  2 |
|  1 |
|  5 |
|  4 |
|  6 |
|  8 |
+----+

Most commonly, this kind of query is done with a limit, for example to return vectors that are closest to a given vector, such as from a user search query, image or a song fragment.

SELECT id FROM v 
  ORDER BY VEC_Distance(v, x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e') LIMIT 2;
+----+
| id |
+----+
| 10 |
|  7 |
+----+

System Variables

There are a number of system variables used for vectors:

• mhnsw_cache_size - size of the cache for the MHNSW vector index.
• mhnsw_limit_multiplier - Number of result candidates to look for in the vector index for ORDER BY ... LIMIT N queries.
• mhnsw_max_edges_per_node - Larger values means slower INSERT, larger index size and higher memory consumption, but better search results. Should not be changed once vector indexes have been created.

See Also

• MariaDB Vector: A storage engine for LLMs - Kaj Arnö and Jonah Harris (video)
• AI first applications with MariaDB Vector - Vicentiu Ciorbaru (video)