For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/de/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/de/llms-full.txt, and this page is available as Markdown at https://docs.ovhcloud.com/de/guides/public-cloud/databases/postgresql-tuto-vector-search-ai-endpoints.md.
Zum Inhalt springen

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

Als Markdown ansehen

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

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 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/. OVHcloud can't provide you with technical support in this regard.

Requirements

Info

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:
  • 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:

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:

CREATE ROLE "rag_app" LOGIN;
CREATE SCHEMA "rag" AUTHORIZATION "rag_app";
Info

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:

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:

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.

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:

ModelDimensionContextColumn type to index
bge-m31,0248,192 tokensvector(1024)
bge-multilingual-gemma23,5848,192 tokenshalfvec(3584)
Qwen3-Embedding-8B4,096, or less with the dimensions parameter32,768 tokensvector(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)):

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.

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

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):

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

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 (<=>):

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.

Info

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

ErrorCause and fix
permission denied for schema publicThe user was created from the Console or the API. Create the application role and its schema in SQL as avnadmin, as in Step 1.
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 indexThe 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 indexEven a halfvec column is limited to 4,000 dimensions. Use the dimensions parameter of Qwen3-Embedding-8B.
HTTP 400 given batch size overflow maximal oneMore than 25 texts in one request. Send them in batches of 25.
HTTP 400 `dimensions` field must be between 32 and 4096The dimensions value is outside the range the model accepts.
HTTP 403 Forbidden: authentication failedThe API key is invalid, expired or revoked. Generate a new one.
HTTP 429 API rate limit exceededWithout 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

Manage users, roles and privileges

Getting started with AI Endpoints

For training or technical assistance implementing our solutions, contact your sales representative or visit our 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 and interact directly with the team that builds our databases service!

Join our community of users.

War diese Seite hilfreich?