Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Vectors (extension)

Specialized Data

A recipe site keeps an embedding of each recipe, a vector a model made of its text, so recipes alike in kind have vectors near each other. It needs the recipes nearest one a reader likes, and the nearest a query's vector. This guide stores the embeddings in a VECTOR column (extension), finds the nearest by distance, compares the metrics, works with the vectors' arithmetic, and defines a distance of its own. The reference gives every rule.

The recipes

Real embeddings have hundreds of dimensions. These have four, so the numbers can be read:

CREATE SCHEMA AUTHORIZATION ANN;
CREATE TABLE RECIPES (ID INTEGER PRIMARY KEY, TITLE VARCHAR(40), EMBEDDING VECTOR(4));
INSERT INTO RECIPES VALUES
    (1, 'Tomato soup', '[0.9, 0.1, 0.0, 0.2]'),
    (2, 'Minestrone', '[0.8, 0.3, 0.1, 0.1]'),
    (3, 'Chocolate cake', '[0.0, 0.1, 0.9, 0.3]'),
    (4, 'Brownies', '[0.1, 0.0, 0.8, 0.4]'),
    (5, 'Green salad', '[0.4, 0.9, 0.0, 0.0]');
COMMIT;
OK
OK
OK
OK

The text is made a vector of the column's type as it is stored. Text of another dimension count is refused, never cut or padded:

INSERT INTO RECIPES VALUES (6, 'Bread', '[0.3, 0.3, 0.3]');
turborel: SQLCODE -301 (SQLSTATE 22000): dimension mismatch: expected 4, got 3

Finding the nearest

A reader liked the tomato soup. The recipes nearest it, by the Euclidean distance the <-> operator gives, are the ones to show next. ORDER BY the distance and FETCH FIRST read every recipe and keep the nearest:

SELECT TITLE, EMBEDDING <-> (SELECT EMBEDDING FROM RECIPES WHERE ID = 1) AS DISTANCE FROM RECIPES
    WHERE ID <> 1 ORDER BY DISTANCE FETCH FIRST 3 ROWS ONLY;
 TITLE                                    |                 DISTANCE
------------------------------------------+--------------------------
 Minestrone                               |       2.6457513166967E-1
 Green salad                              |     9.643650180005846E-1
 Brownies                                 |     1.1532562263894588E0
(3 rows)

A search box's text is embedded by the model too, and its vector is given as a parameter or written as text:

SELECT TITLE FROM RECIPES ORDER BY EMBEDDING <=> '[0.1, 0.1, 0.9, 0.2]' FETCH FIRST 2 ROWS ONLY;
 TITLE
------------------------------------------
 Chocolate cake
 Brownies
(2 rows)

Indexing

A site of millions of recipes cannot read each one for every query. A vector index finds the nearest without reading them all. It is approximate, and may miss a near row to answer sooner, so a query asks for its answer with FETCH APPROX, and ORDER BY the distance the index measures by:

COMMIT;
CREATE VECTOR INDEX RECIPES_V ON RECIPES (EMBEDDING) USING hnsw WITH (METRIC = 'COSINE');
SELECT TITLE FROM RECIPES ORDER BY VECTOR_DISTANCE(EMBEDDING, '[0.1, 0.1, 0.9, 0.2]', COSINE) FETCH APPROX FIRST 2 ROWS ONLY;
OK
OK
 TITLE
------------------------------------------
 Chocolate cake
 Brownies
(2 rows)

FETCH FIRST without APPROX is always exact, and reads every row as before. CREATE VECTOR INDEX has the methods and how they are tuned.

Choosing a metric

VECTOR_DISTANCE names its metric. COSINE, the default, compares the vectors' directions alone, so it suits a model whose vectors' lengths mean nothing. EUCLIDEAN compares their positions, and DOT, the negated dot product, suits vectors made of length 1. A smaller distance is always the nearer:

SELECT TITLE, VECTOR_DISTANCE(EMBEDDING, '[0.9, 0.2, 0.0, 0.1]', COSINE) AS COSINE,
    VECTOR_DISTANCE(EMBEDDING, '[0.9, 0.2, 0.0, 0.1]', EUCLIDEAN) AS EUCLIDEAN,
    VECTOR_DISTANCE(EMBEDDING, '[0.9, 0.2, 0.0, 0.1]', DOT) AS DOT
    FROM RECIPES WHERE ID IN (1, 2, 5) ORDER BY ID;
 TITLE                                    |                   COSINE |                EUCLIDEAN |                      DOT
------------------------------------------+--------------------------+--------------------------+--------------------------
 Tomato soup                              |     1.162801023930149E-2 |     1.414213612422477E-1 |    -8.499999046325684E-1
 Minestrone                               |    1.6335059691790832E-2 |    1.7320507344417657E-1 |    -7.899999618530273E-1
 Green salad                              |      4.08767021850181E-1 |     8.660253693716803E-1 |    -5.399999618530273E-1
(3 rows)

Vectors have no order and no equality, so a query cannot sort, group or compare them directly:

SELECT TITLE FROM RECIPES ORDER BY EMBEDDING;
turborel: SQLCODE -103 (SQLSTATE 2A000): ORDER BY orders, groups, compares or keys values of VECTOR(4), which have no order and no equality; VECTOR_EQUALS compares vectors

Working with vectors

The centre of a group of vectors is their average. The site's "soups" are the recipes near the centre of the two soups' vectors:

SELECT FROM_VECTOR(AVG(EMBEDDING)) AS SOUPS FROM RECIPES WHERE ID IN (1, 2);
SELECT R.TITLE FROM RECIPES AS R, (SELECT AVG(EMBEDDING) FROM RECIPES WHERE ID IN (1, 2)) AS S (CENTRE)
    ORDER BY R.EMBEDDING <-> S.CENTRE FETCH FIRST 3 ROWS ONLY;
 SOUPS
--------------------------------------------------------------
 [0.85,0.2,0.05,0.15]
(1 row)
 TITLE
------------------------------------------
 Tomato soup
 Minestrone
 Green salad
(3 rows)

Arithmetic moves a vector: the tomato soup, less what makes it a soup and more what makes a dessert, is nearest the desserts. A distance operator binds less tightly than + and -, so the sum is measured:

SELECT R.TITLE FROM RECIPES AS R, RECIPES AS SOUP WHERE SOUP.ID = 1
    ORDER BY R.EMBEDDING <-> SOUP.EMBEDDING - '[0.8, 0, 0, 0]' + '[0, 0, 0.8, 0]' FETCH FIRST 1 ROW ONLY;
 TITLE
------------------------------------------
 Chocolate cake
(1 row)

A vector of length 1 keeps its direction alone. VECTOR_NORMALIZE makes one, and VECTOR_QUANTIZE keeps a vector in a smaller format, here a byte of each element:

SELECT FROM_VECTOR(VECTOR_NORMALIZE(EMBEDDING)) AS UNIT,
    FROM_VECTOR(VECTOR_QUANTIZE(EMBEDDING, INT8, 0.01, 0)) AS BYTES FROM RECIPES WHERE ID = 3;
 UNIT                                                         | BYTES
--------------------------------------------------------------+--------------------------------------------------------------
 [0,0.104828484,0.94345635,0.31448546]                        | [0,10,90,30]
(1 row)

A distance of one's own

The site weighs the first dimension, savoury, twice the others. A distance function of SQL measures that way, and VECTOR_DISTANCE names it as it names a metric:

CREATE VECTOR DISTANCE FUNCTION SAVOURY (A VECTOR, B VECTOR) RETURNS FLOAT64 DETERMINISTIC
    PROPERTIES (SYMMETRIC, NON_NEGATIVE) LANGUAGE SQL
    AS 'VECTOR_NORM((A - B) * ''[2, 1, 1, 1]'')';
SELECT TITLE, VECTOR_DISTANCE(EMBEDDING, '[0.9, 0.1, 0.0, 0.2]', SAVOURY) AS D FROM RECIPES ORDER BY D, ID;
OK
 TITLE                                    |                        D
------------------------------------------+--------------------------
 Tomato soup                              |                      0E0
 Minestrone                               |     3.162277340888977E-1
 Green salad                              |     1.2961480617523193E0
 Brownies                                 |      1.802775502204895E0
 Chocolate cake                           |      2.014944076538086E0
(5 rows)

CREATE VECTOR DISTANCE FUNCTION has every rule. The reference has sparse vectors, the other formats, every function and the catalog's views.