pgvector and pgvectorscale

Overview

pgvector adds vector similarity search to PostgreSQL, letting you store embeddings from OpenAI, Cohere, Hugging Face, or any other model alongside your relational data. pgvectorscale builds on pgvector with StreamingDiskANN indexes for larger, disk-oriented vector workloads. Both extensions are enabled by default on new Rivestack databases. Their SQL extension names are vector and vectorscale.

Verify the extension

Confirm both extensions are active on your database:

SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('vector', 'vectorscale')
ORDER BY extname;

Store embeddings

Create a table with a vector column

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding VECTOR(1536)  -- OpenAI ada-002 dimensions
);

The VECTOR(n) type stores n-dimensional vectors. Common dimension sizes:

Model	Dimensions
OpenAI text-embedding-ada-002	1536
OpenAI text-embedding-3-small	1536
OpenAI text-embedding-3-large	3072
Cohere embed-english-v3.0	1024
BGE-small-en	384

Insert vectors

INSERT INTO documents (content, embedding)
VALUES (
    'Rivestack is a managed PostgreSQL service',
    '[0.1, 0.2, 0.3, ...]'  -- your embedding vector
);

From Python with OpenAI

import psycopg2
import openai

# Generate embedding
response = openai.embeddings.create(
    model="text-embedding-ada-002",
    input="Rivestack is a managed PostgreSQL service"
)
embedding = response.data[0].embedding

# Store in Rivestack
conn = psycopg2.connect("postgresql://user:pass@host:6432/mydb?sslmode=verify-full&sslrootcert=system")
cur = conn.cursor()
cur.execute(
    "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
    ("Rivestack is a managed PostgreSQL service", str(embedding))
)
conn.commit()

Query vectors

Similarity search

Find the 5 most similar documents using cosine distance:

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

Distance operators

Operator	Distance Metric	Use Case
`<->`	L2 (Euclidean)	General purpose
`<=>`	Cosine	Text embeddings (most common)
`<#>`	Inner product	When vectors are normalized

Create an index

For tables with more than a few thousand rows, create an index to speed up queries:

HNSW index (recommended)

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

HNSW provides fast, approximate nearest-neighbor search with good recall. Tuning parameters:

Parameter	Default	Description
`m`	16	Max connections per node. Higher = better recall, more memory.
`ef_construction`	64	Build-time search width. Higher = better recall, slower builds.

Set the search parameter at query time. Higher ef_search means better recall and higher latency (the default is 40):

-- Per query, and the only form that survives transaction pooling (see warning):
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id FROM documents ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

-- Or set a persistent default for the whole database (applies to new connections):
ALTER DATABASE appdb SET hnsw.ef_search = 100;

A plain session SET hnsw.ef_search is silently ignored. Every dedicated plan (Solo, Starter, Growth, Scale) connects through PgBouncer in transaction-pooling mode on port 6432, so a bare SET hnsw.ef_search = 100; runs in its own transaction and is reset before your next query, leaving you with the default (40) instead. Always either use SET LOCAL inside the same transaction as the query, or set a persistent default with ALTER DATABASE appdb SET hnsw.ef_search = … (or ALTER ROLE appdb SET …).Free databases connect directly to PostgreSQL on port 5432, where a plain session SET works normally. This holds on HA clusters too. The load balancer and source-filtering HAProxy gateway pass the TLS stream to PgBouncer rather than to PostgreSQL directly, so scaling up does not turn session-level SET back on.

IVFFlat index

CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

IVFFlat requires a training step and works best when you set lists to rows / 1000 for tables up to 1M rows.

SET ivfflat.probes = 10;  -- default is 1, higher = better recall

StreamingDiskANN index (pgvectorscale)

For datasets that should not keep the entire graph in memory, use pgvectorscale’s StreamingDiskANN index:

CREATE INDEX documents_embedding_diskann_idx
ON documents
USING diskann (embedding vector_cosine_ops);

The table still uses pgvector’s VECTOR type and distance operators; pgvectorscale supplies the diskann index access method. Test recall, build time, memory, and latency with your own embeddings before choosing an index for production.

Which index to use?

	HNSW	IVFFlat	StreamingDiskANN
Recall	High	Tunable	Tunable
Build speed	Slower	Faster	Workload dependent
Query speed	Fast	Fast	Designed for scalable disk-oriented search
Memory	More	Less	Lower in-memory graph pressure
Recommendation	General default	Simple, trained approximate index	Benchmark for larger vector datasets

RAG example

A complete retrieval-augmented generation (RAG) pattern:

import psycopg2
import openai

def search(query: str, top_k: int = 5):
    # 1. Generate embedding for the query
    response = openai.embeddings.create(
        model="text-embedding-ada-002",
        input=query
    )
    query_embedding = response.data[0].embedding

    # 2. Search Rivestack for similar documents
    conn = psycopg2.connect("postgresql://user:pass@host:6432/mydb?sslmode=verify-full&sslrootcert=system")
    cur = conn.cursor()
    cur.execute("""
        SELECT content, embedding <=> %s::vector AS distance
        FROM documents
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """, (str(query_embedding), str(query_embedding), top_k))

    results = cur.fetchall()
    conn.close()
    return results

# 3. Use results as context for your LLM
results = search("How does Rivestack handle backups?")
context = "\n".join([row[0] for row in results])

Benchmarks

Measured pgvector performance across the tiers (AMD CPX, shared vCPU, local NVMe), 250,000 vectors at 1536 dimensions, HNSW m=16 / ef_construction=64, cosine, from a same-region client over PgBouncer (:6432) and TLS, recall@10 vs exact KNN:

Tier	vCPU / RAM	Hot capacity	Throughput	Recall@10
Solo	1 / 2 GB	~250K	~980 QPS (4 clients), p50 3.8 ms	0.90
Starter	2 / 4 GB	~350K	~1,600 QPS (16 clients)	0.93
Growth	4 / 8 GB	~600K	~2,950 QPS (16 clients)	0.94
Scale	8 / 16 GB	~1M	~4,465 QPS (16 clients)	0.95

Scale also builds and serves a 1M × 1536 index (~6 GB) hot: ~3,600 QPS at recall@10 0.74, p50 4.2 ms, 16 clients (index builds in ~9 min).

CPX is AMD EPYC shared vCPU (not CPU-isolated); the gain over the older Intel CX line is faster cores and far better availability. QPS and p50 latency trade off across the concurrency curve, so each figure carries its recall and client count. Same-region network floor is ~0.3 to 0.5 ms per round-trip. Every operating point, with one-command reproduce steps: measured pgvector benchmarks.

Performance tips

Always create an index for tables over a few thousand rows. Without an index, pgvector scans every row.
Use HNSW as your default index type. It offers the best recall/speed trade-off.
Benchmark StreamingDiskANN when the vector graph is too large to keep comfortably in memory.
Increase ef_search if recall is too low. Start with 100 and increase as needed, but set it with SET LOCAL inside the query’s transaction or via ALTER DATABASE … SET — every plan pools on :6432, and a plain session SET is dropped by transaction pooling. The same applies to ivfflat.probes.
Batch inserts for loading large datasets. Use COPY or multi-row INSERT for best throughput.
Choose the right distance operator for your use case. Cosine (<=>) is the most common for text embeddings.

Getting Started

Features

Demos

Resources

Changelog

pgvector and pgvectorscale

Overview

Verify the extension

Store embeddings

Create a table with a vector column

Insert vectors

From Python with OpenAI

Query vectors

Similarity search

Distance operators

Create an index

HNSW index (recommended)

IVFFlat index

StreamingDiskANN index (pgvectorscale)

Which index to use?

RAG example

Benchmarks

Performance tips

​Overview

​Verify the extension

​Store embeddings

​Create a table with a vector column

​Insert vectors

​From Python with OpenAI

​Query vectors

​Similarity search

​Distance operators

​Create an index

​HNSW index (recommended)

​IVFFlat index

​StreamingDiskANN index (pgvectorscale)

​Which index to use?

​RAG example

​Benchmarks

​Performance tips

Overview

Verify the extension

Store embeddings

Create a table with a vector column

Insert vectors

From Python with OpenAI

Query vectors

Similarity search

Distance operators

Create an index

HNSW index (recommended)

IVFFlat index

StreamingDiskANN index (pgvectorscale)

Which index to use?

RAG example

Benchmarks

Performance tips