SQL inteface for pgvector and pgvectorscale

Installing the pgvector and pgvectorscale extensions

If not already installed, install the vector and vectorscale extensions on your Tiger Data database.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS vectorscale;

Creating the table for storing embeddings using pgvector

Vectors inside of the database are stored in regular Postgres tables using vector columns. The vector column type is provided by the pgvector extension. A common way to store vectors is alongside the data they are embedding. For example, to store embeddings for documents, a common table structure is:

CREATE TABLE IF NOT EXISTS document_embedding  (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    document_id BIGINT FOREIGN KEY(document.id)
    metadata JSONB,
    contents TEXT,
    embedding VECTOR(1536)
)

This table contains a primary key, a foreign key to the document table, some metadata, the text being embedded (in the contents column) and the embedded vector.

You may ask why not just add an embedding column to the document table? The answer is that there is a limit on the length of text an embedding can encode and so there needs to be a one-to-many relationship between the full document and its embeddings.

The above table is just an illustration, it's totally fine to have a table without a foreign key and/or without a metadata column. The important thing is to have a column with the data being embedded and the vector in the same row, enabling you to return the raw data for a given similarity search query

The vector type can specify an optional number of dimensions (1,538) in the example above). If specified, it enforces the constraint that all vectors in the column have that number of dimensions. A plain VECTOR (without specifying the number of dimensions) column is also possible and allows a variable number of dimensions.

Query the vector embeddings

The canonical query is:

SELECT *
FROM document_embedding
ORDER BY embedding <=> $1
LIMIT 10

Which returns the 10 rows whose distance is the smallest. The distance function used here is cosine distance (specified by using the <=> operator). Other distance functions are available, see the [discussion][distance-functions].

The available distance types and their operators are:

Distance type	Operator
Cosine/Angular	<=>
Euclidean	<->
Negative inner product	<#>

If you are using an index, you need to make sure that the distance function used in index creation is the same one used during query (see below). This is important because if you create your index with one distance function but query with another, your index cannot be used to speed up the query.

Indexing the vector data using indexes provided by pgvector and pgvectorscale

Indexing helps speed up similarity queries of the basic form:

SELECT *
FROM document_embedding
ORDER BY embedding <=> $1
LIMIT 10

The key part is that the ORDER BY contains a distance measure against a constant or a pseudo-constant.

Note that if performing a query without an index, you always get an exact result, but the query is slow (it has to read all of the data you store for every query). With an index, your queries are an order-of-magnitude faster, but the results are approximate (because there are no known indexing techniques that are exact see [here for more][vector-search-indexing]).

Nevertheless, there are excellent approximate algorithms. There are 3 different indexing algorithms available on TimescaleDB: StreamingDiskANN, HNSW, and ivfflat. Below is the trade-offs between these algorithms:

Algorithm	Build Speed	Query Speed	Need to rebuild after updates
StreamingDiskANN	Fast	Fastest	No
HNSW	Fast	Fast	No
ivfflat	Fastest	Slowest	Yes

You can see benchmarks in the blog.

For most use cases, the StreamingDiskANN index is recommended.

Each of these indexes has a set of build-time options for controlling the speed/accuracy trade-off when creating the index and an additional query-time option for controlling accuracy during a particular query.

You can see the details of each index below.

StreamingDiskANN index

The StreamingDiskANN index is a graph-based algorithm that was inspired by the DiskANN algorithm. You can read more about it in How We Made Postgres as Fast as Pinecone for Vector Data.

To create an index named document_embedding_idx on table document_embedding having a vector column named embedding, with cosine distance metric, run:

CREATE INDEX document_embedding_cos_idx ON document_embedding
USING diskann (embedding vector_cosine_ops);

Since this index uses cosine distance, you should use the <=> operator in your queries. StreamingDiskANN also supports L2 distance:

CREATE INDEX document_embedding_l2_idx ON document_embedding
USING diskann (embedding vector_l2_ops);

For L2 distance, use the <-> operator in queries.

These examples create the index with smart defaults for all parameters not listed. These should be the right values for most cases. But if you want to delve deeper, the available parameters are below.