Vector Search

Polypheny offers vector similarity search for Cypher, MQL and SQL.

For data on PostgreSQL stores with installed pgvector extension Polypheny offers, in addition to internal computation, the direct execution of vector similarity search queries on the data store. Local execution on capable data stores provides considerable speedup. Additionally to delegated query execution, Polypheny exposes the creation of vector indexes. To benefit from this, queries have to be written in SQL.

SQL

A vector column is declared with the VECTOR data type. This is the precondition for delegating a distance computation to a data store.

SQL offers three ways to write a distance:

• The parameterized DISTANCE function,
• dedicated functions per metric, such as COSINE_DISTANCE,
• infix operators per metric, such as <=>, that lean on the pgvector syntax.

SELECT id, DISTANCE(embedding, ARRAY[1.0, 2.0, 3.0], 'COSINE') AS dist FROM listings;

SELECT id, COSINE_DISTANCE(embedding, ARRAY[1.0, 2.0, 3.0]) AS dist FROM listings;

SELECT id, embedding <=> ARRAY[1.0, 2.0, 3.0] AS dist FROM listings;

These three queries are equivalent. The exceptions are listed on the DISTANCE page.

SQL is, besides the web interface, the only surface to create a vector index.

MQL and Cypher

When writing vector similarity search queries in MQL, the vector search stage can be used.

For vector similarity search queries in Cypher there is a dedicated vector_distance function.

Note: Similarity search queries written in Cypher or MQL are currently not delegated to the data store.

Example: Semantic Search of Articles

It is possible to store metadata on a document store and vector data on a relational store. Assume the following example:

• We have a collection of articles that consist of paragraphs.
• We want to perform a semantic search on the articles to find an article which has a content closest to a statement.
• The articles have some metadata that is well suited for an unstructured store.

In this case we have a MongoDB store for our article metadata and a PostgreSQL store with the pgvector extension installed for our paragraph data.

CREATE TABLE "public"."paragraph" (
    id BIGINT NOT NULL,
    article_id BIGINT NOT NULL,
    content TEXT,
    embedding REAL VECTOR(768),
    PRIMARY KEY (id)
) ON STORE "postgres";

We first create our table that holds a row per paragraph. Each row has attributes id, article_id, allowing us to link the paragraph to the article’s metadata, the content of the paragraph and embedding of the content as a vector.

CREATE DOCUMENT NAMESPACE "article_meta";

We then create a document namespace for our article metadata.

db.createCollection("articles").store("mongodb")

And the collection consisting of our articles.

Afterwards we simply insert our article metadata and the paragraph rows.

Since we want to perform a semantic search we can simply embed a prompt as a vector using the same model we used for the paragraph embedding. The ARRAY[0.2276921570301056, ... ] is the embedded prompt.

SELECT article_id, content, DISTANCE(embedding, ARRAY[0.2276921570301056, ... ], 'COSINE') AS dist
FROM "public"."paragraph"
ORDER BY dist ASC
LIMIT 5;

We can write the same query in Cypher,

MATCH (n:paragraph)
RETURN n.article_id AS article_id,
vector_distance(n.embedding, [0.2276921570301056, ... ], 'COSINE') AS dist
ORDER BY dist ASC
LIMIT 5

db.paragraph.aggregate([{
    "$vectorSearch": {
        "path": "embedding",
        "queryVector": [0.2276921570301056, ... ],
        "metric": "COSINE",
        "limit": 5
    }
}])

or in MQL. But here it is important that only the SQL query statement benefits from computational delegation to the data store.

Unlike the previous examples, the following query combines data across different models, joining paragraphs from the relational store with article metadata from the document store.

Here is how the query is structured:

• The inner query executes a vector search on the relational paragraph table to find the five closest matches. Performing this step first is crucial: it reduces the join payload to just five rows, and it ensures the distance calculation is pushed down to pgvector.
• The outer query joins those five results with the articles document collection. When accessed via SQL, document collections expose their data in a single column named d. The query casts d to VARCHAR so JSON_VALUE can parse it, extracting the title using lax $.title (the lax modifier safely returns a null value instead of throwing an error if the field is missing).
• To link the two stores, the query extracts the $.article_id from the document and casts it to BIGINT to match the relational article_id column.
• Because SQL joins do not preserve the order of their inputs, an explicit ORDER BY clause is required at the end to restore the correct vector distance ranking.

SELECT closest.article_id,
    JSON_VALUE(CAST(m.d AS VARCHAR(2050)), 'lax $.title') AS title,
    closest.dist, closest.content
FROM (
    SELECT article_id, content,
    DISTANCE(embedding, ARRAY[0.2276921570301056, ... ], 'COSINE') AS dist
    FROM "public"."paragraph"
    ORDER BY dist ASC
    LIMIT 5
) closest
JOIN article_meta.articles m
ON closest.article_id =
    CAST(JSON_VALUE(CAST(m.d AS VARCHAR(2050)), 'lax $.article_id') AS BIGINT)
ORDER BY closest.dist ASC;

Vector Indexes

Polypheny itself does not provide vector indexes in its engine. However, if an underlying data store, such as PostgreSQL with the pgvector extension, supports them, they can be used.

Syntax

ALTER TABLE table_name ADD INDEX index_name ON (column_name) USING method ON STORE store_name [WITH (metric = metric_value, key = value, ... )];

Differences to the syntax used when altering a table or materialized view:

• vector indexes can only span one column,
• ON STORE is mandatory and it needs to be a store supporting vector indexes (e.g. a PostgreSQL store with pgvector extension),
• USING method is required and can be either of the two listed in the table below,
• and WITH ( metric = metric_value ) is optional and defaults to the L2 metric.

The default parameters are not always a good fit for all data. For example when using the IVFFlat method and having many rows and high variance data, leaving lists at default results in a poor recall.

Example 1: Create an HNSW index

ALTER TABLE listing ADD INDEX l1_hnsw_index ON (embedding) USING hnsw ON STORE postgres WITH (metric = L1, m = 32, ef_construction = 128);

Notice that we changed m and ef_construction which are set to values other than their defaults.

Example 2: Create an IVFFlat index

ALTER TABLE listing ADD INDEX l2_ivfflat_index ON (embedding) USING ivfflat ON STORE postgres WITH (metric = L2, lists = 1000);

In this example you see that we do not build an index for the L1 metric since the IVFFlat method does not provide it. Furthermore, we set the lists parameter to 1000.

Previous

Data Types