pgvector

pgvector is a PostgreSQL extension for vector search. It stores embeddings in the database and queries text, images, or other unstructured data by similarity.

CloudBase PostgreSQL can work with Cloud Functions, CloudBase Run, or AI model services to build RAG applications. The application generates embeddings, PostgreSQL stores vectors and metadata, and queries retrieve relevant content by similarity.

Enable the extension

Enable the vector extension in the DMC SQL window. Actual support depends on the console and instance version.

create extension if not exists vector;

Create a vector table

Vector fields must declare a fixed dimension. The dimension must match the embedding model output.

create table public.documents (
  id bigint generated by default as identity primary key,
  title text not null,
  content text not null,
  metadata jsonb not null default '{}',
  embedding vector(1536),
  created_at timestamptz not null default now()
);

If the model outputs 768-dimensional vectors, use vector(768).

Insert vectors

After your service generates embeddings, write them through a server-side connection or HTTP API.

insert into public.documents (title, content, embedding)
values (
  'CloudBase PostgreSQL',
  'CloudBase PostgreSQL supports relational data and vector search.',
  '[0.012, -0.023, 0.034]'::vector
);

The vector in the example is truncated. Pass the full dimension in real writes.

Similarity query

pgvector supports several distance operators:

Operator	Description
<->	L2 distance
<#>	Negative inner product
<=>	Cosine distance

select id, title, content
from public.documents
order by embedding <=> '[0.011, -0.020, 0.030]'::vector
limit 5;

Smaller distance means higher similarity. In RAG, you usually retrieve Top K documents first, then send their content to the model as context.

Create indexes

For larger data sets, create an approximate index on the vector field. Supported index types and parameters depend on the pgvector version.

create index documents_embedding_ivfflat_idx
on public.documents
using ivfflat (embedding vector_cosine_ops)
with (lists = 100);

After creating an ivfflat index, run analyze.

analyze public.documents;

RAG flow

1. Split knowledge base documents into chunks.
2. Generate embeddings for each chunk.
3. Store chunk text, source, permission metadata, and vectors in PostgreSQL.
4. Generate a query embedding for the user question.
5. Retrieve relevant chunks by vector distance.
6. Send the chunks to the model as context to generate an answer.

For a complete RAG example, see RAG with CloudBase PostgreSQL and pgvector.

Permissions and filters

If users or teams can only access their own knowledge bases, keep user_id, team_id, or visibility fields in the vector table and combine vector search with RLS or server-side permission filters.

select id, title, content
from public.documents
where team_id = $1
order by embedding <=> $2
limit 5;

Do not filter only in the application after retrieval. Otherwise unauthorized documents may be sent to the model context.

Performance recommendations

• The dimension must match the model output. Do not mix embeddings with different dimensions.
• Small data sets can use exact ordering. Large data sets should use vector indexes.
• Vector search usually needs metadata filters such as tenant, knowledge base, status, and update time.
• Embedding fields are large. Import in batches and pay attention to index build time.