Service: Postgres Vector - EyevinnOSC/community GitHub Wiki

Postgres Vector

Postgres Vector (pgvector, serviceId pgvector-pgvector, https://app.osaas.io/dashboard/service/pgvector-pgvector) is a PostgreSQL database with the pgvector extension, which adds vector similarity search. It lets you store and query high-dimensional embeddings, for example from OpenAI, Cohere or Sentence Transformers, alongside your relational data. In Eyevinn Open Source Cloud (OSC) it is a long-running service instance with up to 20 GiB of storage per instance.

What it can do

  • Store embeddings in vector columns next to relational data.
  • Run nearest-neighbour queries with L2, inner product, cosine and L1 distance.
  • Back AI-powered search, semantic recommendations and retrieval-augmented generation (RAG) applications.
  • Speed up queries on large datasets with HNSW or IVFFlat indexes.

Creating an instance

Postgres Vector has no dependencies on other OSC services. Store the password with create-service-secret (serviceId pgvector-pgvector) and create the instance with create-service-instance, serviceId: "pgvector-pgvector":

{ "name": "myvectordb", "PostgresPassword": "{{secrets.pgvectorpwd}}", "PostgresDb": "vectors" }

Wait for it to be running (wait-for-service-ready or get-instance-status). Use describe-service-instance to get the IP and port. The connection URL is postgres://<user>:<password>@<IP>:<PORT>/<db>, for example postgres://postgres:<password>@<IP>:<PORT>/postgres with the defaults. Test it with psql "<url>".

With the OSC CLI:

osc create pgvector-pgvector myvectordb \
  -o PostgresPassword="<password>" \
  -o PostgresDb="vectors"

Options

Option Required Type Meaning
name Yes string Name of the instance. Alphanumeric, at most 20 characters.
PostgresPassword Yes string, sensitive Password of the PostgreSQL superuser.
PostgresUser No string Superuser name. Defaults to postgres.
PostgresDb No string Name of the default database created at startup. Defaults to the user name.
PostgresInitDbArgs No string Extra arguments for initdb at cluster initialization (for example encoding or locale).
PostgresInitDbSql No string SQL executed during initialization, for example CREATE EXTENSION vector; to enable pgvector automatically.

Store PostgresPassword as a service secret and pass it as {{secrets.NAME}} (whole field value only). Overwriting a secret does not restart a running instance.

Using the instance

Enable the extension in each database where you want vector search (not needed if you enabled it with PostgresInitDbSql):

CREATE EXTENSION IF NOT EXISTS vector;

Create a vector column and insert embeddings:

-- 384 dimensions, for example for all-MiniLM-L6-v2
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(384)
);

-- Example with 3 dimensions (use a table with matching dimensions)
INSERT INTO documents (content, embedding)
VALUES ('Hello world', '[0.1, 0.2, 0.3]');

Query by similarity (cosine distance):

SELECT id, content, 1 - (embedding <=> '[0.1, 0.15, 0.25]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.15, 0.25]'
LIMIT 5;
Operator Distance metric
<-> L2 (Euclidean) distance
<#> Negative inner product
<=> Cosine distance
<+> L1 (Manhattan) distance

Index for fast approximate nearest neighbour search on large datasets:

-- HNSW index (no training required)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- IVFFlat index (needs rows to train on)
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

From an application:

import openai
import psycopg2

conn = psycopg2.connect("postgres://postgres:<password>@<IP>:<PORT>/postgres")
cur = conn.cursor()

response = openai.embeddings.create(input="What is OSC?", model="text-embedding-3-small")
embedding = response.data[0].embedding

cur.execute(
    "SELECT content FROM documents ORDER BY embedding <=> %s::vector LIMIT 5",
    (str(embedding),)
)
results = cur.fetchall()

Limitations

  • Storage is limited to 20 GiB per instance.
  • The vector dimension of a column must match the embeddings you insert.

Resources

⚠️ **GitHub.com Fallback** ⚠️