---
title: "Build a semantic search with AI Endpoints and Public Cloud Databases for PostgreSQL"
description: "Find out how to turn your texts into vectors with AI Endpoints, store them in a managed PostgreSQL service with pgvector, and search them by meaning"
url: https://docs.ovhcloud.com/it/guides/public-cloud/databases/postgresql-tuto-vector-search-ai-endpoints
lang: en
lastUpdated: 2026-11-19
---
> For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/it/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/it/llms-full.txt.

# Build a semantic search with AI Endpoints and Public Cloud Databases for PostgreSQL

## Objective

A semantic search finds texts by their meaning rather than by their exact words. Each text is turned into a vector, called an _embedding_, by an embedding model. Texts with similar meanings get similar vectors, so a question can be matched with the passages that answer it, even when they share no words.

This tutorial builds such a search end to end, entirely on OVHcloud:

- [AI Endpoints](https://docs.ovhcloud.com/it/guides/public-cloud/ai-machine-learning/ai-endpoints-getting-started.md) turns your texts and your questions into vectors;
- a Public Cloud Databases for PostgreSQL service stores them with the `vector` extension (pgvector) and finds the closest ones with an HNSW index.

It also covers what you need to know before you start: the rights needed in PostgreSQL, the number of texts per request, and the column type to choose according to the model.

**Warning:** OVHcloud provides services for which you are responsible for their configuration and management. You are therefore responsible for their proper functioning.

This tutorial is designed to help you as much as possible with common tasks. If you are having difficulty performing these actions, please contact a specialised service provider and/or discuss it with our community of users on [https://community.ovhcloud.com/](https://community.ovhcloud.com/). OVHcloud can't provide you with technical support in this regard.

## Requirements

- A [Public Cloud project](https://www.ovhcloud.com/it/public-cloud/) in your OVHcloud account.
- A [Public Cloud Databases for PostgreSQL](https://www.ovhcloud.com/it/public-cloud/databases/) service, version 15 or later, that accepts connections from your machine (the [Configure incoming connections](https://docs.ovhcloud.com/it/guides/public-cloud/databases/postgresql-prepare-for-incoming-connections.md) guide explains how to authorise your IP address).
- Command-line access to your service with the `avnadmin` user (the [Connect using the CLI](https://docs.ovhcloud.com/it/guides/public-cloud/databases/postgresql-connect-cli.md) guide can help you to meet this requirement).
- An AI Endpoints API key (see [Getting started with AI Endpoints](https://docs.ovhcloud.com/it/guides/public-cloud/ai-machine-learning/ai-endpoints-getting-started.md)).
- A Python 3 environment with the `psycopg2-binary` package.

**Note:** On a project shared through IAM, only users with administrator rights can create an AI Endpoints API key. If you cannot create one, ask your project administrator.

Without a key, AI Endpoints accepts 2 requests per minute per model: enough to try the examples below, not to process a corpus. Beyond that, calls fail with `API rate limit exceeded` (HTTP 429).

***

### OVHcloud Console Access

- **Direct link:** "Projects"
- **Navigation path:** "Public Cloud" > Select your project > "Databases"

***

## Instructions

### Step 1 - Preparing the database

#### Installing the extension

Connect as `avnadmin` to the database you will use, for example `defaultdb`, and install the extension. Its name in SQL is `vector`:

```sql
CREATE EXTENSION IF NOT EXISTS vector;
```

#### Creating an application user with its own schema

Do not use `avnadmin` in your application: create a dedicated user **in SQL, as `avnadmin`**, and give it its own schema:

```sql
CREATE ROLE "rag_app" LOGIN;
CREATE SCHEMA "rag" AUTHORIZATION "rag_app";
```

**Note:** On PostgreSQL 15, `CREATE SCHEMA` fails with `must be member of role "rag_app"`. Make `avnadmin` a member of the role for the time of the command:

```sql
GRANT "rag_app" TO avnadmin;
CREATE SCHEMA "rag" AUTHORIZATION "rag_app";
REVOKE "rag_app" FROM avnadmin;
```

From PostgreSQL 16, `avnadmin` is a member of the roles it creates, so this step is not needed.

Then set its password with psql's `\password` meta-command:

```console
defaultdb=> \password rag_app
```

**Warning:** A user created from the `Users
` tab of the OVHcloud Console or with the API cannot create tables: it gets `permission denied for schema public`
, including in a database created from the OVHcloud Console. The schema owned by `rag_app`
 above solves this. For more ways to grant privileges, see [Manage users, roles and privileges](https://docs.ovhcloud.com/it/guides/public-cloud/databases/postgresql-users-roles-grants.md)
.

### Step 2 - Choosing the model and the column type

AI Endpoints offers several embedding models. The size of their vectors, called the _dimension_, determines which column type you can index:

| Model                     | Dimension                                      | Context       | Column type to index                                  |
| ------------------------- | ---------------------------------------------- | ------------- | ----------------------------------------------------- |
| `bge-m3`                  | 1,024                                          | 8,192 tokens  | `vector(1024)`                                        |
| `bge-multilingual-gemma2` | 3,584                                          | 8,192 tokens  | `halfvec(3584)`                                       |
| `Qwen3-Embedding-8B`      | 4,096, or less with the `dimensions` parameter | 32,768 tokens | `vector(N)` with `dimensions` set to N, at most 2,000 |

The HNSW index of pgvector accepts:

- up to **2,000 dimensions** on a `vector` column. Beyond that, index creation fails with `column cannot have more than 2000 dimensions for hnsw index`;
- up to **4,000 dimensions** on a `halfvec` column, which stores each value on 2 bytes instead of 4.

A `vector(d)` value takes 4 × d + 8 bytes and a `halfvec(d)` value 2 × d + 8 bytes. In our test with 2,000 rows of 1,024 dimensions, the table took 11 MB as `vector` and 5.6 MB as `halfvec`, and the HNSW index 5.6 MB and 4.3 MB.

**Warning:** Choose the column type before loading your data. The dimension limit only shows when you create the index, after the vectors are stored.

`Qwen3-Embedding-8B` returns 4,096 dimensions by default, above both limits. Add the `dimensions` parameter to the request, for example `"dimensions": 1024`, to receive shorter vectors that fit a `vector` column. It accepts a value from 32 to 4,096.

Fewer dimensions mean a smaller table and index, at the cost of some precision. In our test with three passages and three questions, each question still found the right passage at 1,024 and at 256 dimensions, with a smaller lead over the next passage at 256. Compare a few values on your own data before choosing: up to 2,000 dimensions in a `vector` column, up to 4,000 in a `halfvec` column.

This tutorial uses `bge-m3`, which is multilingual and the fastest of the three.

#### Creating the table

Connect as `rag_app` and create the table in its schema (for `bge-multilingual-gemma2`, declare `emb halfvec(3584)` instead of `emb vector(1024)`):

```sql
CREATE TABLE rag.docs (
  id   int PRIMARY KEY,
  body text,
  emb  vector(1024)
);
```

### Step 3 - Turning your texts into vectors

AI Endpoints exposes an OpenAI-compatible embeddings route. Send at most **25 texts per request**: beyond that, the request is rejected with an HTTP 400 error, `given batch size overflow maximal one`.

```python
import json
import os
import urllib.request

URL = "https://oai.endpoints.kepler.ai.cloud.ovh.net/v1/embeddings"
KEY = os.environ["AI_ENDPOINT_API_KEY"]
BATCH = 25  # maximum number of texts per request

def embed(texts, model="bge-m3", dimensions=None):
    body = {"model": model, "input": texts}
    if dimensions:  # Qwen3-Embedding-8B only
        body["dimensions"] = dimensions
    req = urllib.request.Request(
        URL,
        data=json.dumps(body).encode(),
        headers={"Content-Type": "application/json", "Authorization": "Bearer " + KEY},
    )
    return [e["embedding"] for e in json.load(urllib.request.urlopen(req))["data"]]

def embed_all(texts, **kwargs):
    vectors = []
    for i in range(0, len(texts), BATCH):
        vectors += embed(texts[i:i + BATCH], **kwargs)
    return vectors
```

With an API key, AI Endpoints accepts 400 requests per minute per model. To embed a large corpus faster, send several batches in parallel within this limit.

### Step 4 - Loading the vectors into PostgreSQL

```python
import psycopg2
from psycopg2.extras import execute_values

texts = [
    "The museum opens every day from 9 am to 6 pm, except on Mondays.",
    "Children under 12 enter for free when accompanied by an adult.",
    "Guided tours of the medieval wing start at 10 am and 3 pm.",
]

conn = psycopg2.connect(
    host="<service-host>", port=20184, dbname="defaultdb",
    user="rag_app", password=os.environ["PG_PASSWORD"], sslmode="require",
)
cur = conn.cursor()

vectors = embed_all(texts)
execute_values(
    cur,
    "INSERT INTO rag.docs (id, body, emb) VALUES %s",
    [(i, t, str(v)) for i, (t, v) in enumerate(zip(texts, vectors))],
)
conn.commit()
```

Replace `<service-host>` and the port with the values shown in the **Connection information** section of your service.

Once the data is loaded, create the HNSW index for the cosine distance (for a `halfvec` column, use `halfvec_cosine_ops`):

```sql
CREATE INDEX ON rag.docs USING hnsw (emb vector_cosine_ops);
```

**Note:** Building the index after loading is faster than updating it row by row. Rows inserted later are added to the index automatically.

### Step 5 - Searching by meaning

Turn the question into a vector with the **same model**, then sort the rows by cosine distance (`<=>`):

```python
question = "Is it free for kids?"
q = embed([question])[0]

cur.execute(
    "SELECT id, body, emb <=> %s::vector(1024) AS distance "
    "FROM rag.docs ORDER BY distance LIMIT 3",
    (str(q),),
)
for row in cur.fetchall():
    print(row)
```

The passage about children comes first, even though it shares no words with the question.

To check that the index is used, prefix the query with `EXPLAIN ANALYZE`. On a table of a few rows, as here, PostgreSQL prefers to read the whole table and the plan shows a `Seq Scan` followed by a `Sort`. On a real corpus, or after `SET enable_seqscan = off;` in your session, the plan shows an `Index Scan` on the HNSW index, here `docs_emb_idx`.

**Note:** Always use the same model, and the same `dimensions` value for `Qwen3-Embedding-8B`, for your texts and for your questions. Vectors from two different models cannot be compared.

## Troubleshooting

| Error                                                         | Cause and fix                                                                                                                                                        |
| ------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `permission denied for schema public`                         | The user was created from the Console or the API. Create the application role and its schema in SQL as `avnadmin`, as in [Step 1](#step-1---preparing-the-database). |
| `must be member of role "rag_app"`                            | PostgreSQL 15 only. Grant the role to `avnadmin` before `CREATE SCHEMA`, then revoke it.                                                                             |
| `column cannot have more than 2000 dimensions for hnsw index` | The vectors have more than 2,000 dimensions. Use a `halfvec` column, or ask `Qwen3-Embedding-8B` for fewer dimensions.                                               |
| `column cannot have more than 4000 dimensions for hnsw index` | Even a `halfvec` column is limited to 4,000 dimensions. Use the `dimensions` parameter of `Qwen3-Embedding-8B`.                                                      |
| HTTP 400 `given batch size overflow maximal one`              | More than 25 texts in one request. Send them in batches of 25.                                                                                                       |
| HTTP 400 `` `dimensions` field must be between 32 and 4096 `` | The `dimensions` value is outside the range the model accepts.                                                                                                       |
| HTTP 403 `Forbidden: authentication failed`                   | The API key is invalid, expired or revoked. Generate a new one.                                                                                                      |
| HTTP 429 `API rate limit exceeded`                            | Without a key, 2 requests per minute per model. Use an API key, or wait between requests.                                                                            |

## Go further

[Available extensions on Public Cloud Databases for PostgreSQL](https://docs.ovhcloud.com/it/guides/public-cloud/databases/postgresql-extensions.md)

[Manage users, roles and privileges](https://docs.ovhcloud.com/it/guides/public-cloud/databases/postgresql-users-roles-grants.md)

[Getting started with AI Endpoints](https://docs.ovhcloud.com/it/guides/public-cloud/ai-machine-learning/ai-endpoints-getting-started.md)

For training or technical assistance implementing our solutions, contact your sales representative or visit our [Professional Services](https://www.ovhcloud.com/it/professional-services/) page to request a quote and have your project analysed by our experts.

## We want your feedback!

We would love to help answer questions and appreciate any feedback you may have.

Are you on Discord? Connect to our channel at [https://discord.gg/ovhcloud](https://discord.gg/ovhcloud) and interact directly with the team that builds our databases service!

Join our [community of users](https://community.ovhcloud.com/).
