Vec1

Vec1 Reference Guide
Login

Contents:

This page is intended to be a complete reference to the interface exported by the vec1 extension. It is not a user manual. For that, please see this page.

1. Vector Format

Vec1's native vector format is an SQL BLOB containing a packed array of 32-bit IEEE floating point values in machine byte order. All values must be finite, NaN and infinite values are not allowed.

2. Scalar Functions

Vec1 provides the following SQL scalar functions:

vec1_info()

This function returns a human-readable string describing the version of the vec1 extension currently in use.

vec1_l2_distance(VECTOR1, VECTOR2)

Both arguments must be vectors in native format. Both must be the same size. This function returns the square of the euclidean distance between the two vectors (SQL type real), calculated as: 

vec1_l2_distance(a, b) = ∑i(ai - bi)2

If either argument is not an SQL BLOB, or if the two arguments are of different sizes, or if the size in bytes of the two arguments is not divisible by 4, an exception is thrown.

vec1_cos_distance(VECTOR1, VECTOR2)

Like vec1_l2_distance(), except that it returns the cosine distance between the two vectors (SQL type real), calculated as follows: 

vec1_cos_distance(a, b) = 2.0 - ∑i(ai bi) / √((∑i ai²)(∑i bi²))

This function does not require its arguments to be normalized.

vec1_to_json(VECTOR)

The argument to this function must be a vector in native format. A JSON array containing the elements of the vector is returned (SQL type TEXT).

vec1_from_json(JSON)

The argument to this function must be a JSON array of numeric values. It returns the equivalent vector in native format (a BLOB).

vec1_config(PARAMETER) / vec1_config(PARAMETER, VALUE)

The first form returns the current value of parameter PARAMETER (type TEXT). The second form sets the value of PARAMETER to VALUE, then returns a copy of the new value. Supported parameters are currently:

Example

To configure vec1 to use 16 threads when possible: 

    SELECT vec1_config('nthread', 16);

To query for the current default value of nprobe: 

    SELECT vec1_config('nprobe');

3. Training

The IVF+OPQ algorithm used by vec1 requires a trained model. This model is built by training on a representative set of vectors (i.e. vectors with the same data distribution as those that will be indexed).

There is a single aggregate SQL function used for training:

vec1_train(VECTOR, JSON-PARAMETERS)

The aggregate should be run over the set of training vectors, each vector passed as the first argument. It returns a BLOB containing the trained model, which may be used with a vec1 virtual table. The second argument is a JSON object (type TEXT) containing parameters to configure the model and training process. The following parameters are supported to configure the model:

And the following parameters are also supported to configure the training process itself:

Models may be generated on one machine and used on another. However, the two machines must use the same byte-order for 32-bit IEEE floating point values.

Example

Assume table "learn" contains one training vector per row in column "vec". The following query generates a model: 

    -- Returns a model (an SQL BLOB) for use with a vec1 virtual table.
    --
    -- Configuration:
    --   distance = "cos"   -> use cosine distance
    --   quantizer = "opq"  -> use OPQ to compress vectors
    --   codesize = 32      -> compress each vector to 32 bytes
    --   nbucket = 1024     -> use 1024 IVF buckets
    --
    SELECT vec1_train(learn.vec, '{
        distance: "cos",
        quantizer: "opq",
        codesize: 32,
        nbucket: 1024
    }')
    FROM learn;

4. Virtual Table

4.1. Creation and Population of Tables

To create a vec1 virtual table:

    CREATE VIRTUAL TABLE tbl USING vec1(vector_column, metadata_column...);

The first argument specifies the name of the column used to store vectors. Each subsequent argument specifies the name of a metadata column.

A vec1 table must contain exactly one vector column, and may contain between 0 and 255 metadata columns. All vec1 tables have a unique integer rowid that identifies each row. Additionally, all vec tables have a hidden column named distance populated dynamically by nearest-neighbor queries.

Column names may be enclosed in single or double quotes. If unquoted, they must consist only of ASCII alphanumeric characters and underscores. The names "rowid" and "distance" are reserved and may not be used.

It is not possible to specify types or other column constraints for vec1 table columns.

vec1 virtual tables may be modified using standard SQL INSERT, UPDATE, and DELETE statements. The vector column accepts only BLOB values whose length is a multiple of 4 bytes (each element is a 32-bit floating-point value). Once the vector size for a table has been fixed, all inserted vectors must have exactly that size.

The vector size is fixed when either:

Metadata columns may store values of any type.

4.2. Configuration

A vec1 table may be configured to use a model using the following SQL command:

    INSERT INTO tbl(cmd, arg) VALUES('rebuild', MODEL);

MODEL must either be a BLOB returned by vec1_train(), or else a JSON object (type TEXT) specifying model parameters. Supported model parameters for JSON models are:

The 'rebuild' command automatically rebuilds the index using the new model. Subsequent 'rebuild' commands replace the existing index. 'rebuild' may be run before or after vectors are added to the table.

Example

To configure a vec1 table to store full vectors in packed BLOBs and to use cosine distance when queried:

    INSERT INTO tbl(cmd, arg) VALUES('rebuild', '{
        index: "flat", 
        distance: "cos"
    }');

4.3. Nearest-Neighbor Queries

A vec1 table is queried for the nearest-neigbours of a vector by using the table as a table-valued-function, where the first argument is the query vector, and the second, optional, argument the query parameters. As follows:

    SELECT ... FROM tbl(VECTOR, PARAMETERS)

If PARAMETERS is passed a TEXT value, it is interpreted as a JSON object containing query parameters. The following query parameters are supported:

The hidden distance column is populated with the distance between the query vector and the row vector for each row returned. If the table is configured with a model that uses compressed vectors (i.e. was trained with a non-zero codesize parameter), then the distances returned in this column are based on the compressed version of the vector. For non-streaming queries, rows are always returned in ascending order of this column.

For streaming queries, rows are usually returned in ascending order of distance, but some results may also be returned slightly out of order. This happens when the SQL engine requests so many rows that the query has to begin scanning more than the number of buckets suggested by the nprobe parameter.

If PARAMETERS is passed an INTEGER value instead of TEXT, it is equivalent to specifying the integer as the K value and leaving all other query parameters unset.

WHERE clause processing

If a nearest-neighbor query has a WHERE clause that specifies one or more constraints connected by AND operators that meet the following criteria, then they are evaluated internally by vec1. This changes the query results because this filtering occurs before the best K results are accumulated. The criteria are:

LIMIT clause processing

Normally, a nearest-neighbor query requires a K value to be specified. However, if a query against a vec1 table as a visible LIMIT clause, then the value passed to the LIMIT clause is used in place of an explicit K. Or, if a query has both an explicit K and a visible LIMIT clause, then the smaller of the two values is selected at runtime. Whether or not an SQL LIMIT clause is visible is determined by the SQL engine. In general, a LIMIT clause is visible if the query (or sub-query) that uses the vec1 table: