> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL inteface for pgvector and pgvectorscale

> Use the SQL interface to work with pgvector and pgvectorscale, including installing the extensions, creating a table, querying the vector embeddings, and more

export const TIMESCALE_DB = 'TimescaleDB';

export const PG = 'Postgres';

export const COMPANY = 'Tiger Data ';

## Installing the pgvector and pgvectorscale extensions

If not already installed, install the `vector` and `vectorscale` extensions on your {COMPANY} database.

```sql theme={"dark"}
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 {PG} 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:

```sql theme={"dark"}
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:

```sql theme={"dark"}
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 | `<#>`    |

<Info>
  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.
</Info>

## Indexing the vector data using indexes provided by pgvector and pgvectorscale

Indexing helps speed up similarity queries of the basic form:

```sql theme={"dark"}
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 {TIMESCALE_DB}: 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](https://www.timescale.com/blog/how-we-made-postgresql-the-best-vector-database/) 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](https://github.com/microsoft/DiskANN) algorithm.
You can read more about it in
[How We Made {PG} as Fast as Pinecone for Vector Data](https://www.timescale.com/blog/how-we-made-postgresql-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:

```sql theme={"dark"}
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:

```sql theme={"dark"}
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.

#### StreamingDiskANN index build-time parameters

These parameters can be set when an index is created.

| Parameter name           | Description                                                                                                                                                                                      | Default value                               |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------- |
| `storage_layout`         | `memory_optimized` which uses SBQ to compress vector data or `plain` which stores data uncompressed                                                                                              | memory\_optimized                           |
| `num_neighbors`          | Sets the maximum number of neighbors per node. Higher values increase accuracy but make the graph traversal slower.                                                                              | 50                                          |
| `search_list_size`       | This is the S parameter used in the greedy search algorithm used during construction. Higher values improve graph quality at the cost of slower index builds.                                    | 100                                         |
| `max_alpha`              | Is the alpha parameter in the algorithm. Higher values improve graph quality at the cost of slower index builds.                                                                                 | 1.2                                         |
| `num_dimensions`         | The number of dimensions to index. By default, all dimensions are indexed. But you can also index less dimensions to make use of [Matryoshka embeddings](https://huggingface.co/blog/matryoshka) | 0 (all dimensions)                          |
| `num_bits_per_dimension` | Number of bits used to encode each dimension when using SBQ                                                                                                                                      | 2 for less than 900 dimensions, 1 otherwise |

An example of how to set the `num_neighbors` parameter is:

```sql theme={"dark"}
CREATE INDEX document_embedding_idx ON document_embedding
USING diskann (embedding) WITH(num_neighbors=50);
```

#### StreamingDiskANN query-time parameters

You can also set two parameters to control the accuracy vs. query speed trade-off at query time. We suggest adjusting `diskann.query_rescore` to fine-tune accuracy.

| Parameter name                   | Description                                                             | Default value |
| -------------------------------- | ----------------------------------------------------------------------- | ------------- |
| `diskann.query_search_list_size` | The number of additional candidates considered during the graph search. | 100           |
| `diskann.query_rescore`          | The number of elements rescored (0 to disable rescoring)                | 50            |

You can set the value by using `SET` before executing a query. For example:

```sql theme={"dark"}
SET diskann.query_rescore = 400;
```

Note the [SET command](https://www.postgresql.org/docs/current/sql-set.html) applies to the entire session (database connection) from the point of execution. You can use a transaction-local variant using `LOCAL` which will
be reset after the end of the transaction:

```sql theme={"dark"}
BEGIN;
SET LOCAL diskann.query_search_list_size= 10;
SELECT * FROM document_embedding ORDER BY embedding <=> $1 LIMIT 10
COMMIT;
```

#### StreamingDiskANN index-supported queries

You need to use the cosine-distance embedding measure (`<=>`) in your `ORDER BY` clause. A canonical query would be:

```sql theme={"dark"}
SELECT *
FROM document_embedding
ORDER BY embedding <=> $1
LIMIT 10
```

### pgvector HNSW

Pgvector provides a graph-based indexing algorithm based on the popular [HNSW algorithm](https://arxiv.org/abs/1603.09320).

To create an index named `document_embedding_idx` on table `document_embedding` having a vector column named `embedding`, run:

```sql theme={"dark"}
CREATE INDEX document_embedding_idx ON document_embedding
USING hnsw(embedding vector_cosine_ops);
```

This command creates an index for cosine-distance queries because of `vector_cosine_ops`. There are also "ops" classes for Euclidean distance and negative inner product:

| Distance type          | Query operator | Index ops class     |
| ---------------------- | -------------- | ------------------- |
| Cosine / Angular       | `<=>`          | `vector_cosine_ops` |
| Euclidean / L2         | `<->`          | `vector_ip_ops`     |
| Negative inner product | `<#>`          | `vector_l2_ops`     |

Pgvector HNSW also includes several index build-time and query-time parameters.

#### pgvector HNSW index build-time parameters

These parameters can be set at index build time:

| Parameter name    | Description                                                                                                                                                                                                                                                              | Default value |
| ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------- |
| `m`               | Represents the maximum number of connections per layer. Think of these connections as edges created for each node during graph construction. Increasing m increases accuracy but also increases index build time and size.                                               | 16            |
| `ef_construction` | Represents the size of the dynamic candidate list for constructing the graph. It influences the trade-off between index quality and construction speed. Increasing `ef_construction` enables more accurate search results at the expense of lengthier index build times. | 64            |

An example of how to set the m parameter is:

```sql theme={"dark"}
CREATE INDEX document_embedding_idx ON document_embedding
USING hnsw(embedding vector_cosine_ops) WITH (m = 20);
```

#### pgvector HNSW query-time parameters

You can also set a parameter to control the accuracy vs. query speed trade-off at query time. The parameter is called `hnsw.ef_search`. This parameter specifies the size of the dynamic candidate list used during search. Defaults to 40. Higher values improve query accuracy while making the query slower.

You can set the value by running:

```sql theme={"dark"}
SET hnsw.ef_search = 100;
```

Before executing the query, note the [SET command](https://www.postgresql.org/docs/current/sql-set.html) applies to the entire session (database connection) from the point of execution. You can use a transaction-local variant using `LOCAL`:

```sql theme={"dark"}
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT * FROM document_embedding ORDER BY embedding <=> $1 LIMIT 10
COMMIT;
```

#### pgvector HNSW index-supported queries

You need to use the distance operator (`<=>`, `<->`, or `<#>`) matching the ops class you used during index creation in your `ORDER BY` clause. A canonical query would be:

```sql theme={"dark"}
SELECT *
FROM document_embedding
ORDER BY embedding <=> $1
LIMIT 10
```

### pgvector ivfflat

Pgvector provides a clustering-based indexing algorithm. The [blog post](https://www.timescale.com/blog/nearest-neighbor-indexes-what-are-ivfflat-indexes-in-pgvector-and-how-do-they-work) describes how it works in detail. It provides the fastest index-build speed but the slowest query speeds of any indexing algorithm.

To create an index named `document_embedding_idx` on table `document_embedding` having a vector column named `embedding`, run:

```sql theme={"dark"}
CREATE INDEX document_embedding_idx ON document_embedding
USING ivfflat(embedding vector_cosine_ops) WITH (lists = 100);
```

This command creates an index for cosine-distance queries because of `vector_cosine_ops`. There are also "ops" classes for Euclidean distance and negative inner product:

| Distance type          | Query operator | Index ops class     |
| ---------------------- | -------------- | ------------------- |
| Cosine / Angular       | `<=>`          | `vector_cosine_ops` |
| Euclidean / L2         | `<->`          | `vector_ip_ops`     |
| Negative inner product | `<#>`          | `vector_l2_ops`     |

Note: *ivfflat should never be created on empty tables* because it needs to cluster data, and that only happens when an index is first created, not when new rows are inserted or modified. Also, if your table undergoes a lot of modifications, you need to rebuild this index occasionally to maintain good accuracy. See the [blog post](https://www.timescale.com/blog/nearest-neighbor-indexes-what-are-ivfflat-indexes-in-pgvector-and-how-do-they-work) for details.

Pgvector ivfflat has a `lists` index parameter that should be set. See the next section.

#### pgvector ivfflat index build-time parameters

Pgvector has a `lists` parameter that should be set as follows:
For datasets with less than one million rows, use lists =  rows / 1000.
For datasets with more than one million rows, use lists = sqrt(rows).
It is generally advisable to have at least 10 clusters.

You can use the following code to simplify creating ivfflat indexes:

```python theme={"dark"}
def create_ivfflat_index(conn, table_name, column_name, query_operator="<=>"):
    index_method = "invalid"
    if query_operator == "<->":
        index_method = "vector_l2_ops"
    elif query_operator == "<#>":
        index_method = "vector_ip_ops"
    elif query_operator == "<=>":
        index_method = "vector_cosine_ops"
    else:
        raise ValueError(f"unrecognized operator {query_operator}")

    with conn.cursor() as cur:
        cur.execute(f"SELECT COUNT(*) as cnt FROM {table_name};")
        num_records = cur.fetchone()[0]

        num_lists = num_records / 1000
        if num_lists < 10:
            num_lists = 10
        if num_records > 1000000:
            num_lists = math.sqrt(num_records)

        cur.execute(f'CREATE INDEX ON {table_name} USING ivfflat ({column_name} {index_method}) WITH (lists = {num_lists});')
        conn.commit()
```

#### pgvector ivfflat query-time parameters

You can also set a parameter to control the accuracy vs. query speed tradeoff at query time. The parameter is called `ivfflat.probes`. This parameter specifies the number of clusters searched during a query. It is recommended to set this parameter to `sqrt(lists)` where lists is the parameter used above during index creation. Higher values improve query accuracy while making the query slower.

You can set the value by running:

```sql theme={"dark"}
SET ivfflat.probes = 100;
```

Before executing the query, note the [SET command](https://www.postgresql.org/docs/current/sql-set.html) applies to the entire session (database connection) from the point of execution. You can use a transaction-local variant using `LOCAL`:

```sql theme={"dark"}
BEGIN;
SET LOCAL ivfflat.probes = 100;
SELECT * FROM document_embedding ORDER BY embedding <=> $1 LIMIT 10
COMMIT;
```

#### pgvector ivfflat index-supported queries

You need to use the distance operator (`<=>`, `<->`, or `<#>`) matching the ops class you used during index creation in your `ORDER BY` clause. A canonical query would be:

```sql theme={"dark"}
SELECT *
FROM document_embedding
ORDER BY embedding <=> $1
LIMIT 10
```

[distance-functions]: /agentic-postgres/key-vector-database-concepts#vector-distance-types

[vector-search-indexing]: /agentic-postgres/key-vector-database-concepts#vector-search-indexing-approximate-nearest-neighbor-search