Home

Awesome

pg_embedding

IMPORTANT NOTICE:

As of Sept 29, 2023, Neon is no longer committing to pg_embedding.

Support will remain in place for existing users of the extension, but we strongly encourage migrating to pgvector.

For migration instructions, see Migrate from pg_embedding to pgvector, in the Neon documentation.

The pg_embedding extension enables the using the Hierarchical Navigable Small World (HNSW) algorithm for vector similarity search in PostgreSQL.

This extension is based on ivf-hnsw implementation of HNSW the code for the current state-of-the-art billion-scale nearest neighbor search system<sup>[1]</sup>.

Using the pg_embedding extension

This section describes how to use the pg_embedding extension with a simple example demonstrating the required statements, syntax, and options.

For information about migrating from pgvector to pg_embedding, see Migrate from pgvector to pg_embedding, in the Neon documentation.

Usage summary

The statements in this usage summary are described in further detail in the following sections.

CREATE EXTENSION embedding;
CREATE TABLE documents(id integer PRIMARY KEY, embedding real[]);
INSERT INTO documents(id, embedding) VALUES (1, '{0,1,2}'), (2, '{1,2,3}'),  (3, '{1,1,1}');
SELECT id FROM documents ORDER BY embedding <-> ARRAY[3,3,3] LIMIT 1;

Enable the extension

To enable the pg_embedding extension, run the following CREATE EXTENSION statement:

CREATE EXTENSION embedding;

Create a table for your vector data

To store your vector data, create a table similar to the following:

CREATE TABLE documents(id INTEGER, embedding REAL[]);

This statement generates a table named documents with an embedding column for storing vector data. Your table and vector column names may differ.

Insert data

To insert vector data, use an INSERT statement similar to the following:

INSERT INTO documents(id, embedding) VALUES (1, '{0,1,2}'), (2, '{1,2,3}'),  (3, '{1,1,1}');

Query

The pg_embedding extension supports Euclidean (L2), Cosine, and Manhattan distance metrics.

Euclidean (L2) distance:

SELECT id FROM documents ORDER BY embedding <-> array[3,3,3] LIMIT 1;

Cosine distance:

SELECT id FROM documents ORDER BY embedding <=> array[3,3,3] LIMIT 1;

Manhattan distance:

SELECT id FROM documents ORDER BY embedding <~> array[3,3,3] LIMIT 1;

where:

In summary, the query retrieves the ID of the record from the documents table whose value is closest to the [3,3,3] query vector according to the specified distance metric.

Create an HNSW index

To optimize search behavior, you can add an HNSW index. To create the HNSW index on your vector column, use a CREATE INDEX statement as shown in the following examples. The pg_embedding extension supports indexes for use with Euclidean, Cosine, and Manhattan distance metrics.

Euclidean (L2) distance index:

CREATE INDEX ON documents USING hnsw(embedding) WITH (dims=3, m=3, efconstruction=5, efsearch=5);
SET enable_seqscan = off;
SELECT id FROM documents ORDER BY embedding <-> array[3,3,3] LIMIT 1;

Cosine distance index:

CREATE INDEX ON documents USING hnsw(embedding ann_cos_ops) WITH (dims=3, m=3, efconstruction=5, efsearch=5);
SET enable_seqscan = off;
SELECT id FROM documents ORDER BY embedding <=> array[3,3,3] LIMIT 1;

Manhattan distance index:

CREATE INDEX ON documents USING hnsw(embedding ann_manhattan_ops) WITH (dims=3, m=3, efconstruction=5, efsearch=5);
SET enable_seqscan = off;
SELECT id FROM documents ORDER BY embedding <~> array[3,3,3] LIMIT 1;

Tuning the HNSW algorithm

The following options allow you to tune the HNSW algorithm when creating an index:

In summary, to prioritize search speed over accuracy, use lower values for m and efsearch. Conversely, to prioritize accuracy over search speed, use a higher value for m and efsearch. A higher efconstruction value enables more accurate search results at the cost of index build time, which is also affected by the size of your dataset.

How HNSW search works

HNSW is a graph-based approach to indexing multi-dimensional data. It constructs a multi-layered graph, where each layer is a subset of the previous one. During a search, the algorithm navigates through the graph from the top layer to the bottom to quickly find the nearest neighbor. An HNSW graph is known for its superior performance in terms of speed and accuracy.

The search process begins at the topmost layer of the HNSW graph. From the starting node, the algorithm navigates to the nearest neighbor in the same layer. The algorithm repeats this step until it can no longer find neighbors more similar to the query vector.

Using the found node as an entry point, the algorithm moves down to the next layer in the graph and repeats the process of navigating to the nearest neighbor. The process of navigating to the nearest neighbor and moving down a layer is repeated until the algorithm reaches the bottom layer.

In the bottom layer, the algorithm continues navigating to the nearest neighbor until it can't find any nodes that are more similar to the query vector. The current node is then returned as the most similar node to the query vector.

The key idea behind HNSW is that by starting the search at the top layer and moving down through each layer, the algorithm can quickly navigate to the area of the graph that contains the node that is most similar to the query vector. This makes the search process much faster than if it had to search through every node in the graph.

References