Vector search

Vector search ranks rows by how close a stored vector is to a query vector, where a traditional search compares keywords for an exact match. The values it compares are vector embeddings, the numeric representations of text, images, or other data. SQL Database implements vector search through libSQL, which stores each vector in the native SQLite BLOB storage class. The vector type sets how many bits represent each floating-point number in the vector.

Vector search serves four kinds of workload:

• Recommendations that find items with similar characteristics, such as related products in an ecommerce catalog or content in a streaming platform.
• Semantic text search, where a text embedding represents a word or a phrase as a vector.
• Voice assistants and chatbots that apply natural language processing (NLP).
• The retrieval step of a retrieval-augmented generation (RAG) application built on AI Inference, with a framework such as LangChain or LangGraph.

The vectors sit in the same database as the rest of the rows, so one statement filters on ordinary columns and ranks by vector distance. No separate vector service holds a copy of the data. For how a query reaches the data, refer to How SQL Database works.

Vector columns

A vector column is declared as a blob type carrying the number of dimensions each vector holds. A F32_BLOB column holds an array of 32-bit floating-point numbers as a binary large object (BLOB). This statement declares a three-dimensional vector column:

The examples on this page use three dimensions so that every vector is readable in full. A real embedding column declares the dimension count of the model that fills it. The text-embedding-3-small model returns 1,536 dimensions, so its column is declared F32_BLOB(1536).

Each type has an alias, and the two spellings declare the same column:

FLOAT32 is the recommended starting point.

Functions

SQL Database adds six SQL functions for vector data:

vector builds the value a vector column stores, and vector_extract reads it back. The text form vector_extract returns carries no spaces between the elements, so a column holding vector('[85, 25, 65]') returns [85,25,65].

Distance

A distance function compares two vectors and returns a number. A smaller number means the two vectors are closer.

vector_distance_cos returns the cosine distance, which is 1 - cosine similarity. The value ranges from 0 to 2:

• A distance close to 0 means the vectors are nearly identical, or exactly matching.
• A distance close to 1 means the vectors are orthogonal, at a right angle to each other.
• A distance close to 2 means the vectors point in opposite directions.

vector_distance_l2 returns the Euclidean distance instead. It is not supported for FLOAT1BIT vectors.

Order by the distance to rank rows from nearest to furthest. This query returns the three teams whose stats are closest to a season of 82 goals scored, 25 conceded, and 63% possession:

A query written this way does not consult a vector index. The index is stored as a separate table, and a statement reaches it only through vector_top_k. To rank a large table without comparing the query vector with every row, refer to Indexing.

Indexing

A vector index answers an approximate-nearest-neighbor (ANN) search without comparing the query vector with every row. SQL Database builds the index with the DiskANN algorithm. Wrap the vector column in libsql_vector_idx to create one:

The column inside libsql_vector_idx is the vector column of the table the index is created on. A second argument sets the distance metric, as in libsql_vector_idx(stats_embedding, 'metric=cosine').

Creating a vector index adds a shadow table named after the index, teams_idx_shadow. A table listing returns it beside the tables you created. The .tables command of the EdgeSQL Shell and the getTables call of the azion/sql library both show it.

A vector index requires a table with a ROWID or with a single-column PRIMARY KEY. A composite PRIMARY KEY without a ROWID is not supported.

Querying

Using a vector index is not automatic. Select from vector_top_k and join the table on rowid to make a statement consult the index.

vector_top_k takes the index name, the query vector, and the number of rows to return. It returns those rows as one column named id, which holds the ROWID or the PRIMARY KEY of each nearest neighbor.

This sequence creates the table, stores four vectors, indexes them, and returns the two teams nearest to the query vector:

A WHERE clause filters the rows vector_top_k already returned. Adding WHERE year >= 2023 keeps the 2023 rows among those two, and it never makes the index return more than k rows.

Generate the query vector with the same embedding model that produced the stored column. For a walkthrough that builds a semantic search end to end, refer to Build a semantic search with vector embeddings.

Limits

Three bounds apply to vector data. A vector holds at most 65,536 dimensions. vector_distance_l2 is not supported for FLOAT1BIT vectors. A vector index requires a table with a ROWID or a single-column PRIMARY KEY.

The dimension ceiling is enforced when a vector is built, not when a column is declared. The statement CREATE TABLE t (v F32_BLOB(65537)); succeeds, because SQLite does not validate the parameter of a column type. The statement that calls vector with more than 65,536 dimensions is the one that fails, and it answers {"error":"vector: max size exceeded 65536"} inside an HTTP 200 response.

For every other bound SQL Database applies, refer to SQL Database limits.

Related resources