[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > [Tutorials](index.md) > Running a vector search with `pgvector` in Yandex Managed Service for OpenSearch

# Running a vector search with `vector` in Yandex Managed Service for OpenSearch

# Running a vector search with `vector` in Yandex Managed Service for OpenSearch

With the [vector](../operations/extensions/vector.md) extension, you can store vector data and run a vector search in [Yandex Managed Service for PostgreSQL](../index.md).

The extension supports:

* Exact and approximate nearest neighbor search.
* Various vector types: single-precision, half-precision, binary, and sparse vectors.
* Several vector comparison metrics: L2 (Euclidean distance), cosine distance, dot product, L1 (Manhattan distance), Hamming and Jaccard distance.
* Any programming language that supports a connection to PostgreSQL.

Vector search can be combined with familiar PostgreSQL and SQL features.

By default, `vector` performs exact search where it compares all vectors. For large data volumes, you can create vector indexes and use approximate search. This search is faster, but the results may prove less accurate.

`vector` supports two index types:

* HNSW, which organizes vectors into a data structure which allows you to go from one similar vector to the next and find nearest neighbors without going through the entire table. This structure is based on a multilayer graph.
* IVFFlat, which breaks all vectors up into several groups (lists). The search does not scan all groups but only those that are closest to the target vector. Compared to HNSW, this index is built faster and uses less memory; however, it is inferior in terms of quality and search speed.

The `vector` extension is available in Managed Service for PostgreSQL clusters of version `13` and higher. For more information about `vector`, see [this tutorial](https://github.com/pgvector/pgvector).

To run a vector search in Managed Service for PostgreSQL:

1. [Set up your infrastructure](#infra).
1. [Prepare your test data](#prepare-test-data).
1. [Run search queries](#execute-queries).
1. [Create an index to make your search faster](#add-index).

If you no longer need the resources you created, [delete them](#clear-out).


## Getting started {#before-you-begin}

Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).

### Required paid resources {#paid-resources}

* Managed Service for PostgreSQL cluster: use of computing resources allocated to hosts, as well as the storage and backup size (see [Managed Service for PostgreSQL pricing](../pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Yandex Virtual Private Cloud pricing](../../vpc/pricing.md)).


## Set up your infrastructure {#infra}

1. [Create a cloud network](../../vpc/operations/network-create.md) named `demo-network`.

   
   When creating a network, three subnets in different availability zones are created automatically.



1. In `demo-network`, [create a security group](../../vpc/operations/security-group-create.md) named `mpg-sg` for the Managed Service for PostgreSQL cluster and [add](../../vpc/operations/security-group-add-rule.md) the following inbound rule to the group to allow internet access to the cluster:

    * **Port range**: `6432`.
    * **Protocol**: `TCP`.
    * **Destination name**: `Address range`.
    * **IPv4 CIDR**: `0.0.0.0/0`.


1. [Create a Managed Service for PostgreSQL cluster](../operations/cluster-create.md) of any suitable configuration with the following settings:

    * **Version**: `13` or higher.
    * **Network**: `demo-network`.

    
    * **Security group**: `mpg-sg`


    * **Database**: `pgdb`.

    
   When creating your cluster, enable host public access.

    {% note info %}
    
    Public access to cluster hosts is required if you plan to connect to the cluster via the internet. This connection option is simpler and is recommended for the purposes of this guide. You can connect to non-public hosts as well but only from Yandex Cloud virtual machines located in the same cloud network as the cluster.
    
    {% endnote %}


1. In the Managed Service for PostgreSQL cluster, [add](../operations/extensions/cluster-extensions.md#update-extensions) the `vector` extension to the `pgdb` database.

## Prepare your test data {#prepare-test-data}

1. [Connect to the Managed Service for PostgreSQL cluster](../operations/connect/clients.md).
1. Create a column vector of size `3`:

   ```sql
   CREATE TABLE items (
     id bigserial PRIMARY KEY, 
     embedding vector(3)
   );
   ```

1. Add vectors: 

   ```sql
   INSERT INTO items (embedding) 
   VALUES
   ('[1,2,3]'),
   ('[4,5,6]'),
   ('[7,8,9]'),
   ('[10,11,12]'),
   ('[13,14,15]'),
   ('[16,17,18]');
   ```

## Run search queries {#execute-queries}

1. Find the three nearest neighbors using L2 distance:

   ```sql
   SELECT *
   FROM items
   ORDER BY embedding <-> '[3,1,2]'
   LIMIT 3;
   ```

   To get a distance, the `vector` extension uses the following functions:

   * `<->`: L2 distance (Euclidean distance).
   * `<#>`: Negative dot product.
   * `<=>`: Cosine distance.
   * `<+>`: L1 distance (Manhattan distance).
   * `<~>`: Hamming distance (for binary vectors).
   * `<%>`: Jaccard distance (for binary vectors).

1. Find three lines that bear the closest resemblance to the first one:

   ```sql
   SELECT *
   FROM items
   WHERE id != 1
   ORDER BY embedding <->
   (
   SELECT embedding
   FROM items
   WHERE id = 1
   )
   LIMIT 3;
   ```

1. Find the lines that lie within the boundaries of the set distance:

   ```sql
   SELECT *
   FROM items
   WHERE embedding <-> '[3,1,2]' < 15;
   ```

1. Get the distance between each vector and vector `[3,1,2]`:

   ```sql
   SELECT embedding <-> '[3,1,2]'
   AS distance
   FROM items;
   ```

1. To calculate the dot product, multiply the result by `-1`, since the `<#>` returns the negative dot product:

   ```sql
   SELECT (embedding <#> '[3,1,2]') * -1 AS inner_product
   FROM items;
   ```

1. To find out how similar each vector is to `[3,1,2]` using cosine similarity, use `1 - cosine distance`:

   ```sql
   SELECT 1 - (embedding <=> '[3,1,2]') AS cosine_similarity
   FROM items;
   ```

1. Find the mean vector:

   ```sql
   SELECT AVG(embedding)
   FROM items;
   ```

## Create an index to make your search faster {#add-index}

Create an index of each type: [HNSW](#hnsw-index) and [IVFFlat](#ivfflat-index).

### HNSW {#hnsw-index}

1. Create an HNSW index for L2 distance search:

   ```sql
   CREATE INDEX ON items
   USING hnsw (embedding vector_l2_ops)
   WITH (
   m = 16,
   ef_construction = 64
   );
   ```

   Where:

   * `m`: Maximum number of connections a graph node can have at each layer. The default value is `16`.
   * `ef_construction`: Number of candidates considered when building the graph. The default value is `64`. The higher the value, the greater the probability of finding the most relevant records; however, index build time increases, and insertion operations become slower.

   These parameters are set one time when creating an index. To modify their values, re-create the index.

   You need to create a dedicated HNSW index for each distance calculation function. To learn more, see the `vector` extension [guide](https://github.com/pgvector/pgvector#hnsw).

1. Configure the search settings:

   ```sql
   SET hnsw.ef_search = 3;
   ```

   Where `ef_search` is the number of vectors the algorithm considers during the search. The default value is `40`. The higher the value, the greater the probability of finding the most relevant records; however, the query runs slower.

   Unlike the `m` and `ef_construction` index parameters, the `ef_search` parameter can be modified before running queries.

1. Run a search for the nearest neighbors:

   ```sql
   SELECT *
   FROM items
   ORDER BY embedding <-> '[3,1,2]'
   LIMIT 3;
   ```

   If the search results are not accurate enough, increase the `ef_search` value and repeat the query.

### IVFFlat {#ivfflat-index}

1. Create an IVFFlat index for L2 distance search:

   ```sql
   CREATE INDEX ON items
   USING ivfflat (embedding vector_l2_ops)
   WITH (
   lists = 3
   );
   ```

   Where `lists` is the number of groups (lists) into which the vectors will be divided. The recommended value depends on the amount of data. The more groups there are, the faster the search, but the search quality may decrease.

   This parameter is set one time when creating an index. To change its value, re-create the index.

   You need to create a dedicated IVFFlat index for each distance calculation function. To learn more, see the `vector` extension [guide](https://github.com/pgvector/pgvector#ivfflat).

1. Configure the search settings:

   ```sql
   SET ivfflat.probes = 1;
   ```

   Where `probes` is the number of groups (lists) viewed when running a search. The default value is `1`. The higher the value, the greater the probability of finding the most relevant records; however, the query runs slower.

   Unlike the `lists` index parameter, the `probes` search parameter can be modified before running queries.

1. Run a search for the nearest neighbors:

   ```sql
   SELECT *
   FROM items
   ORDER BY embedding <-> '[3,1,2]'
   LIMIT 3;
   ```

   If the search results are not accurate enough, increase the `probes` value and repeat the query.

## Delete the resources you created {#clear-out}


If you no longer intend to use the Managed Service for PostgreSQL cluster, [delete](../operations/cluster-delete.md) it to avoid paying for it.