Vector Database PGSQL

Steps to setup and use PGVector with PGSQL

1. Create the database

Create the database with the additional configuration, this example assumes the database is being created with terraform. So you would just append the hcl below to pgsql-flexible_server.tf matching the correct server id.

resource "azurerm_postgresql_flexible_server_configuration" "cb-psql-extensions" {
  name      = "azure.extensions"
  server_id = azurerm_postgresql_flexible_server.cb-psql.id
  value     = "vector"
}

If you needed multiple extensions, list them comma-separated: value = "vector,pg_stat_statements,uuid-ossp" ect

2. Connect to the database and enable the plugin

CREATE EXTENSION IF NOT EXISTS vector;

3. Create a test table

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(512)
);

4. Persist data

you can also select data with

SELECT '[0.1,0.2,0.3,...]'::vector;

Distance metrics

Cosine similarity (recommended for LLM embeddings)

SELECT
    id,
    content,
    embedding <=> '[0.1,0.2,0.3,...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1,0.2,0.3,...]'::vector
LIMIT 10;

Smaller distance = more similar.

You can also calculate a similarity score:

SELECT
    id,
    content,
    1 - (embedding <=> '[0.1,0.2,0.3,...]'::vector) AS similarity
FROM documents
ORDER BY similarity DESC
LIMIT 10;

This produces values roughly between 0 and 1.

Euclidean (L2) distance

SELECT
    id,
    content,
    embedding <-> '[0.1,0.2,0.3,...]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1,0.2,0.3,...]'::vector
LIMIT 10;

Again, smaller is better.

Dot Product / Inner Product

This one catches people out.

<#> returns the negative inner product because PostgreSQL indexes sort in ascending order.

SELECT
    id,
    content,
    embedding <#> '[0.1,0.2,0.3,...]'::vector AS negative_inner_product
FROM documents
ORDER BY embedding <#> '[0.1,0.2,0.3,...]'::vector
LIMIT 10;

If you want the actual dot product:

SELECT
    id,
    content,
    -(embedding <#> '[0.1,0.2,0.3,...]'::vector) AS dot_product
FROM documents
ORDER BY embedding <#> '[0.1,0.2,0.3,...]'::vector
LIMIT 10;

Creating indexes

You’ll get much better performance by matching the index type to the operator you use.

Cosine

CREATE INDEX documents_embedding_cos_idx
ON documents
USING hnsw (embedding vector_cosine_ops);

Euclidean

CREATE INDEX documents_embedding_l2_idx
ON documents
USING hnsw (embedding vector_l2_ops);

Dot Product

CREATE INDEX documents_embedding_ip_idx
ON documents
USING hnsw (embedding vector_ip_ops);

Which should you use?

For modern embedding models (such as OpenAI’s text-embedding-3-large, Gemini embeddings, Voyage AI, or most sentence-transformer models), the usual recommendation is:

Cosine (<=>): Best default choice for semantic search and Retrieval-Augmented Generation (RAG).
Dot product (<#>): Best if your embeddings are already normalized to unit length. It’s often slightly faster and mathematically equivalent to cosine on normalized vectors.
Euclidean (<->): Useful for embeddings where the magnitude of the vector carries meaning, but it’s less common for text retrieval.

For a chatbot or semantic search application, I’d recommend cosine distance with an HNSW index using vector_cosine_ops. It’s the most common configuration and works well across most embedding models without requiring you to worry about whether the vectors have been normalized.