Vector Search

DB9 provides pgvector-compatible vector storage and HNSW indexing, plus a built-in embedding service that generates vectors directly in SQL without external API calls.

Two extensions work together:

• vector — the VECTOR type, distance operators, and HNSW indexes
• embedding — the embedding() function for server-side text embedding

Installation

SQL

CREATE EXTENSION vector;
CREATE EXTENSION embedding;  -- optional: enables EMBEDDING() function

Both extensions require superuser privileges (the default admin role).

VECTOR Type

Store fixed-dimension vectors using the VECTOR(n) type:

SQL

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  embedding VECTOR(1024)
);

Vectors can be inserted as text literals or cast from arrays:

SQL

-- Text literal
INSERT INTO documents (content, embedding)
VALUES ('hello world', '[0.1, 0.2, 0.3, ...]');

-- Array cast
INSERT INTO documents (content, embedding)
VALUES ('hello world', ARRAY[0.1, 0.2, 0.3]::vector);

Vectors are stored as 64-bit floats internally. Dimensions must match the column definition for all operations.

Distance Operators

Operator	Metric	Function equivalent
<->	L2 (Euclidean) distance	l2_distance(a, b)
<=>	Cosine distance	cosine_distance(a, b)
<#>	Negative inner product	inner_product(a, b)

All operators return FLOAT8. Both operands must have the same number of dimensions.

SQL

-- Cosine similarity search
SELECT content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 5;

Cosine distance returns 0 for identical vectors and approaches 2 for opposite vectors. If either vector has zero norm, it returns 1.0.

Inner product returns the negative dot product (pgvector convention), so lower values indicate higher similarity.

Distance and Utility Functions

Function	Signature	Description
l2_distance(a, b)	(VECTOR, VECTOR) → FLOAT8	Euclidean distance
cosine_distance(a, b)	(VECTOR, VECTOR) → FLOAT8	Cosine distance (1 - cosine similarity)
inner_product(a, b)	(VECTOR, VECTOR) → FLOAT8	Negative dot product
vector_dims(v)	(VECTOR) → INT4	Number of dimensions
vector_norm(v)	(VECTOR) → FLOAT8	L2 norm (magnitude)
l2_normalize(v)	(VECTOR) → VECTOR	Unit vector (v / ‖v‖)

SQL

SELECT vector_dims(embedding) FROM documents LIMIT 1;
-- Returns: 1024

SELECT vector_norm('[3, 4]'::vector);
-- Returns: 5.0

SELECT l2_normalize('[3, 4]'::vector);
-- Returns: [0.6, 0.8]

HNSW Indexes

HNSW (Hierarchical Navigable Small World) indexes enable fast approximate nearest-neighbor search. Without an index, distance queries require a full table scan.

Creating an index

SQL

-- Cosine distance (most common for text embeddings)
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_cosine_ops);

-- L2 distance
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_l2_ops);

-- Inner product
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_ip_ops);

Tuning parameters

SQL

-- Custom build parameters
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Parameter	Default	Description
m	16	Connections per layer. Higher = better recall, more memory. Range: 4–32.
ef_construction	64	Search width during build. Higher = better index quality, slower build. Range: 40–512.

Search-time tuning

SQL

-- Increase search accuracy (higher = better recall, slower)
SET hnsw.ef_search = 100;

-- Query uses the index automatically
SELECT content, embedding <=> query_vec AS distance
FROM documents
ORDER BY distance
LIMIT 10;

The default ef_search is 40. Higher values improve recall at the cost of latency.

When the index is used

The optimizer uses an HNSW index when all of these are true:

• The query has ORDER BY distance_function(vector_column, constant) ASC
• A LIMIT k clause is present
• An HNSW index exists on the vector column with the matching distance metric
• The index is in Ready state

SQL

-- This uses the HNSW index (ORDER BY + LIMIT + matching metric)
SELECT * FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

-- This does NOT use the index (no LIMIT)
SELECT * FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector;

Index constraints

• Single vector column only (no multi-column HNSW indexes)
• Table must have an INTEGER or BIGINT primary key with non-negative values
• No partial indexes (WHERE clause not supported on HNSW)
• No UNIQUE constraint on vector columns

Built-in Embedding Generation

The embedding extension provides server-side text embedding. The default model is text-embedding-v4 with 1024 dimensions.

embedding()

SQL

-- Default model and dimensions
SELECT embedding('hello world');

-- Custom dimensions
SELECT embedding('hello world', 'text-embedding-v4', 512);

Argument	Type	Required	Description
text	TEXT	Yes	Text to embed (must not be empty)
model	TEXT	No	Model name (default: server-configured)
dimensions	INT	No	Output dimensions (default: server-configured)

Returns VECTOR. Requires superuser privileges.

embed_text()

An alternative with explicit model selection:

SQL

SELECT embed_text('text-embedding-v4', 'hello world');
SELECT embed_text('text-embedding-v4', 'hello world', '{"dimensions": 512}');

The third argument is a JSON string with options.

Auto-embedding distance functions

These functions embed text and compute distance in one call — useful for search queries where you don’t want to manage the embedding step:

SQL

-- Embed query text and compute cosine distance against stored vectors
SELECT content
FROM documents
ORDER BY vec_embed_cosine_distance(embedding, 'database for AI agents')
LIMIT 5;

Function	Equivalent to
vec_embed_l2_distance(vec, text)	l2_distance(vec, embedding(text))
vec_embed_cosine_distance(vec, text)	cosine_distance(vec, embedding(text))
vec_embed_inner_product(vec, text)	inner_product(vec, embedding(text))

These functions work with HNSW index scans — the embedding is computed once at plan time, not per row.

Embedding usage tracking

SQL

SELECT * FROM embedding_usage();

Returns a single row with:

• tokens_used (BIGINT) — cumulative tokens consumed today (UTC)
• resets_at (TIMESTAMPTZ) — next UTC midnight

Session configuration

Override the server-configured embedding settings per session:

SQL

SET embedding.provider = 'openai';
SET embedding.model = 'text-embedding-3-small';
SET embedding.api_key = 'sk-...';
SET embedding.dimensions = 1536;

Setting	Default	Description
embedding.provider	openai	API provider (openai or bedrock)
embedding.endpoint	(server-configured)	API endpoint URL
embedding.api_key	(server-configured)	API key
embedding.model	text-embedding-v4	Model name
embedding.dimensions	1024	Output dimensions
embedding.max_calls	100	Max embedding calls per SQL statement
embedding.concurrency	5	Max concurrent API calls per tenant
hnsw.ef_search	40	HNSW search expansion factor

End-to-End Example

SQL

-- Setup
CREATE EXTENSION vector;
CREATE EXTENSION embedding;

-- Create table
CREATE TABLE docs (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  vec VECTOR(1024)
);

-- Insert with server-side embedding
INSERT INTO docs (content, vec) VALUES
  ('PostgreSQL is a relational database', embedding('PostgreSQL is a relational database')),
  ('DB9 provides serverless Postgres for AI agents', embedding('DB9 provides serverless Postgres for AI agents')),
  ('Vector search finds similar documents', embedding('Vector search finds similar documents'));

-- Create HNSW index
CREATE INDEX idx_docs_vec ON docs USING hnsw (vec vector_cosine_ops);

-- Semantic search
SELECT content,
       vec <=> embedding('database for AI')::vector AS distance
FROM docs
ORDER BY distance
LIMIT 3;

-- Or use the auto-embedding shortcut
SELECT content
FROM docs
ORDER BY vec_embed_cosine_distance(vec, 'database for AI')
LIMIT 3;

Limits

Limit	Value
Embedding calls per statement	100 (configurable)
Concurrent embedding API calls per tenant	5 (configurable)
HNSW default m	16
HNSW default ef_construction	64
HNSW default ef_search	40
Embedding token usage	Tracked daily, resets at UTC midnight

Caveats

• IVFFlat is not supported. Only HNSW indexes are available for vector similarity search.
• Embedding requires superuser. Regular database users cannot call embedding() or embed_text().
• HNSW requires non-negative integer primary keys. Tables with negative or non-integer PKs cannot have HNSW indexes.
• No partial HNSW indexes. WHERE clauses on HNSW index creation are not supported.
• Embedding results are cached per (model, dimensions, text) within a statement to avoid redundant API calls.
• Distance operators require matching dimensions. Comparing vectors of different sizes raises an error.

Related Docs

• RAG with Built-in Embeddings — tutorial: build a complete RAG pipeline
• Compatibility Matrix — full PostgreSQL compatibility surface
• Extensions Overview — all 9 built-in extensions
• Limits and Quotas — all operational limits
• SQL Reference: Functions — built-in function reference