Skip to content
Discord Get Started

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

Section titled “Installation”
SQL
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS embedding;  -- optional: enables EMBEDDING() function

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

VECTOR Type

Section titled “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 32-bit floats (float4) internally, matching the pgvector storage format. Each element keeps roughly 7 significant decimal digits — e.g. [0.123456789] reads back as [0.12345679]. Dimensions must match the column definition for all operations.

Distance Operators

Section titled “Distance Operators”
OperatorMetricFunction equivalent
<->L2 (Euclidean) distancel2_distance(a, b)
<=>Cosine distancecosine_distance(a, b)
<#>Negative inner productvector_negative_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 NaN (cosine similarity is undefined for a zero vector).

The <#> operator returns the negative dot product (pgvector convention), so lower values indicate higher similarity — '[1,2,3]' <#> '[4,5,6]' is -32. The inner_product(a, b) function returns the plain (positive) dot product — inner_product('[1,2,3]', '[4,5,6]') is 32. Use the <#> operator (or vector_negative_inner_product) for distance ordering, and inner_product when you want the raw dot product.

Distance and Utility Functions

Section titled “Distance and Utility Functions”
FunctionSignatureDescription
l2_distance(a, b)(VECTOR, VECTOR) → FLOAT8Euclidean distance
cosine_distance(a, b)(VECTOR, VECTOR) → FLOAT8Cosine distance (1 - cosine similarity)
inner_product(a, b)(VECTOR, VECTOR) → FLOAT8Inner product (dot product), e.g. 32
vector_negative_inner_product(a, b)(VECTOR, VECTOR) → FLOAT8Negative dot product; backs the <#> operator
vector_dims(v)(VECTOR) → INT4Number of dimensions
vector_norm(v)(VECTOR) → FLOAT8L2 norm (magnitude)
l2_normalize(v)(VECTOR) → VECTORUnit 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

Section titled “HNSW Indexes”

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

Queries fall back to an exact sequential scan when no index is present, so all distance operators and functions (<->, <=>, <#>, l2_distance, cosine_distance, inner_product) return correct results either way — the index changes speed, not answers.

Creating an index

Section titled “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

Section titled “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);
ParameterDefaultDescription
m16Connections per layer. Higher = better recall, more memory. Range: 2–100.
ef_construction64Search width during build. Higher = better index quality, slower build. Range: 2 × m up to 1000.

The two are checked together: ef_construction must be at least twice m. The default ef_construction = 64 therefore allows any m up to 32; for a larger m, set ef_construction explicitly — WITH (m = 33) alone fails, WITH (m = 33, ef_construction = 66) works. A value outside either range fails CREATE INDEX with 22023 and a message naming the parameter. m is checked first (m must be between 2 and 100); with m in range, an ef_construction below 4 or above 1000 gives ef_construction must be between 4 and 1000, and one below twice m gives ef_construction must be >= 2*m.

Search-time tuning

Section titled “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, and the accepted range is 1 to 1000. Higher values improve recall at the cost of latency. A value outside the range is rejected rather than clamped: SET hnsw.ef_search = 2000 fails with hnsw.ef_search must be between 1 and 1000, got 2000 (22023), and so does 0.

When the index is used

Section titled “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, where the constant is a literal or a bound parameter
  • 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
  • The query has no WHERE clause
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;

The no-WHERE condition is about the presence of a filter, not how selective it is. A predicate on an unrelated column, on the primary key, one that matches no rows, and even a trivially true WHERE 1=1 all fall back to an exact scan — so pre-filtered vector search is not accelerated.

To combine a filter with an indexed search, apply the filter outside a k-NN subquery, which keeps the index on the inner scan:

SQL
-- The inner scan still uses the HNSW index; the filter runs on its results
SELECT * FROM (
  SELECT id, tenant_id FROM documents
  ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
  LIMIT 100
) t
WHERE t.tenant_id = 3;

Note that this filters the k nearest rows rather than finding the k nearest matching rows, so over-fetch: with LIMIT 100 and a selective filter you may get far fewer than 100 rows back, and a row that would have matched can fall outside the inner k entirely. Size the inner LIMIT by how selective your filter is.

Index constraints

Section titled “Index constraints”

  • Single vector column only — access method "hnsw" does not support multicolumn indexes

  • The indexed column must declare its dimensions (VECTOR(1024), not bare VECTOR). Declare the dimension when you create the table:

    SQL
    -- Works
    CREATE TABLE documents (id SERIAL PRIMARY KEY, embedding VECTOR(1024));
    CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
    

    -- Cannot be indexed: no declared dimension.
    -- If `bad` holds rows, this drops the connection instead of erroring.
    CREATE TABLE bad (id SERIAL PRIMARY KEY, embedding VECTOR);
    CREATE INDEX ON bad USING hnsw (embedding vector_cosine_ops);

  • The table must have a single-column primary key. A table with no primary key or a composite primary key fails with HNSW indexes require a single-column primary key (0A000).

  • If the primary key is INTEGER or BIGINT, its values must be non-negative. A negative key present at build time fails the CREATE INDEX with 22023 and a message naming the offending value; inserting one after the index exists fails the INSERT with a bare XX000 (internal error). UUID and TEXT primary keys have no such restriction.

  • No partial indexes — a WHERE clause fails with HNSW indexes do not support partial index predicates (WHERE clause) (0A000).

  • IVFFlat is not available at all (access method "ivfflat" does not exist); HNSW is the only vector index type.

Built-in Embedding Generation

Section titled “Built-in Embedding Generation”

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

embedding()

Section titled “embedding()”
SQL
-- Default model and dimensions
SELECT embedding('hello world');


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


-- Custom dimensions: 256, 512 or 1024
SELECT embedding('hello world', 'text-embedding-v4', 512);
ArgumentTypeRequiredDescription
textTEXTYesText to embed (must not be empty)
modelTEXTNoModel name (default: server-configured)
dimensionsINTNoOutput width: 256, 512 or 1024. Defaults to the embedding.dimensions session setting, which itself defaults to 1024.

Returns VECTOR. Requires superuser privileges.

embed_text()

Section titled “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

Section titled “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;
FunctionEquivalent 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))
vec_embed_negative_inner_product(vec, text)vector_negative_inner_product(vec, embedding(text))

vec_embed_l2_distance, vec_embed_cosine_distance and vec_embed_negative_inner_product can use an HNSW index (vector_l2_ops, vector_cosine_ops and vector_ip_ops respectively) when the text is written inline — see When the index is used. vec_embed_inner_product always scans the table; to rank by inner product through an index, order by vec_embed_negative_inner_product, where lower is more similar. Each function embeds its text once per statement, not per row, unlike embedding(text) written into the query yourself.

Embedding usage tracking

Section titled “Embedding usage tracking”
SQL
SELECT * FROM extensions.embedding_usage();

Returns a single row with:

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

Session configuration

Section titled “Session configuration”

Override the server-configured embedding settings per session:

SQL
SET embedding.provider = 'openai';
SET embedding.model = 'text-embedding-v4';
SET embedding.api_key = 'sk-...';
SET embedding.dimensions = 1024;
SettingDefaultDescription
embedding.providerbedrockAPI provider (openai or bedrock)
embedding.endpoint(server-configured)API endpoint URL
embedding.api_key(server-configured)API key
embedding.modeltext-embedding-v4Model name
embedding.dimensions1024Output dimensions
embedding.max_calls100Max embedding calls per SQL statement
embedding.concurrency5Max concurrent API calls per tenant
hnsw.ef_search40HNSW search expansion factor (accepted range 1-1000)

Document Chunking with CHUNK_TEXT

Section titled “Document Chunking with CHUNK_TEXT”

For long documents, split text into overlapping chunks before embedding using the built-in CHUNK_TEXT table-valued function:

SQL
-- Chunk a document and embed each chunk
INSERT INTO chunks (doc_id, chunk_index, chunk_text, embedding)
SELECT
  1,
  c.chunk_index,
  c.chunk_text,
  embedding(c.chunk_text)
FROM CHUNK_TEXT(
  content => (SELECT content FROM documents WHERE id = 1),
  max_chars => 1500,
  overlap_chars => 200,
  title => (SELECT title FROM documents WHERE id = 1)
) AS c;

CHUNK_TEXT is markdown-aware — it prefers to break at paragraph boundaries, headings, and list items. See CHUNK_TEXT for full reference.

End-to-End Example

Section titled “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. `vec` is declared VECTOR(1024) above, which is what
-- makes it indexable. Note the CTE search below does NOT use it (the index
-- does not take its probe from a CTE) — only the vec_embed_cosine_distance()
-- form further down does.
CREATE INDEX idx_docs_vec ON docs USING hnsw (vec vector_cosine_ops);


-- Semantic search: embed the query once in a CTE. Calling embedding()
-- directly in the query runs it once per row and fails past 100 rows.
WITH q AS (SELECT embedding('database for AI') AS v)
SELECT content,
       vec <=> q.v AS distance
FROM docs, q
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

Section titled “Limits”
LimitValue
Embedding calls per statement100 (configurable)
Concurrent embedding API calls per tenant5 (configurable)
HNSW default m16
HNSW default ef_construction64
HNSW default ef_search40
Embedding token usageTracked daily, resets at UTC midnight

Caveats

Section titled “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 a single-column primary key. UUID and TEXT keys work; INTEGER/BIGINT keys must be non-negative. No-PK and composite-PK tables are rejected.
  • HNSW requires a dimensioned column. Index the column as VECTOR(n); a bare VECTOR column that holds rows drops the connection at CREATE INDEX, and an empty one yields an index the planner never uses.
  • No partial HNSW indexes. WHERE clauses on HNSW index creation are not supported.
  • Only VEC_EMBED_* caches its query text. Within one statement, each distinct text passed to a VEC_EMBED_* function is embedded once and counts once toward embedding.max_calls. embedding() and embed_text() are not cached: every call is a separate model request that counts toward the limit, even when the text is identical.
  • Distance operators require matching dimensions. Comparing vectors of different sizes raises an error.

Next Steps

Section titled “Next Steps”