pgvector: Build RAG Retrieval Inside the Postgres You Already Run
This week's AI tool: pgvector, an open-source Postgres extension that adds vector similarity search to the database you probably already run.
Why it's worth 15 minutes of your time: the retrieval step of a RAG app is "store embeddings, find the nearest ones to a query, filter by who is allowed to see what." If your documents, users and permissions already live in Postgres, pgvector lets you do all three in one SQL query, with the transactions, backups, JOINs and point-in-time recovery you already rely on. No second database to sync.
Here's exactly how to use it.
1. Install and enable the extension
pgvector supports Postgres 13+. It's available through Docker, Homebrew, APT, Yum, PGXN and conda-forge, and it comes preinstalled on many hosted Postgres providers (see the installation section of the README). Then, once in each database where you want it:
CREATE EXTENSION vector;
2. Add a vector column next to your text
A RAG table is just your chunks plus their embeddings. The vector(n) type takes the number of dimensions your embedding model outputs. OpenAI's text-embedding-3-small, for example, returns 1536 dimensions by default (OpenAI docs).
CREATE TABLE doc_chunks (
id bigserial PRIMARY KEY,
doc_id bigint NOT NULL,
team_id bigint NOT NULL, -- who may read this chunk
doc_type text NOT NULL, -- e.g. 'faq', 'runbook'
content text NOT NULL,
embedding vector(1536)
);
Already have a table? ALTER TABLE doc_chunks ADD COLUMN embedding vector(1536); works too.
3. Query by distance
pgvector adds distance operators you use in ORDER BY:
| Operator | Meaning |
|---|---|
| <-> | L2 (Euclidean) distance |
| <=> | cosine distance |
| <#> | negative inner product |
| <+> | L1 distance |
<#> returns the negative inner product because Postgres only supports ascending-order index scans on operators. Multiply by -1 if you need the actual value. For cosine similarity, use 1 - (embedding <=> query).
SELECT id, content, 1 - (embedding <=> $1) AS cosine_similarity
FROM doc_chunks
ORDER BY embedding <=> $1
LIMIT 5;
Without an index, this is an exact nearest-neighbor search with perfect recall. That's a fine place to start.
4. Add an approximate index when it gets slow
pgvector offers two approximate index types. Create the index with the operator class that matches the operator you query with (vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>).
HNSW (better speed-recall tradeoff, slower build, more memory, can be built on an empty table):
CREATE INDEX ON doc_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64); -- these are the defaults
m is the max connections per layer; a higher ef_construction gives better recall at the cost of build time and insert speed. At query time, hnsw.ef_search (40 by default) controls the candidate list size: higher means better recall, slower queries.
BEGIN;
SET LOCAL hnsw.ef_search = 100; -- only for this transaction
SELECT ...;
COMMIT;
IVFFlat (faster build, less memory, lower speed-recall tradeoff). Build it after the table has data. The README suggests starting with lists = rows / 1000 for up to 1M rows and sqrt(rows) above that, and probes = sqrt(lists) at query time:
CREATE INDEX ON doc_chunks
USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
SET ivfflat.probes = 10; -- 1 by default
One thing to expect: after adding an approximate index, the same query can return different results than the exact scan did. That's the recall-for-speed trade.
5. Call it from Python (psycopg 3 + pgvector-python)
pip install "psycopg[binary]" pgvector openai
import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector
from openai import OpenAI
client = OpenAI()
conn = psycopg.connect("dbname=app", autocommit=True)
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
register_vector(conn) # teaches psycopg the vector type
def embed(text: str) -> Vector:
r = client.embeddings.create(model="text-embedding-3-small", input=text)
return Vector(r.data[0].embedding)
# Index a chunk
chunk = "To reset 2FA, open Settings > Security and choose Reset."
conn.execute(
"INSERT INTO doc_chunks (doc_id, team_id, doc_type, content, embedding) "
"VALUES (%s, %s, %s, %s, %s)",
(42, 7, "faq", chunk, embed(chunk)),
)
# Retrieve top-k chunks the user is allowed to see
def retrieve(question: str, team_id: int, k: int = 5):
return conn.execute(
"""
SELECT content, 1 - (embedding <=> %(q)s) AS score
FROM doc_chunks
WHERE team_id = %(team)s
ORDER BY embedding <=> %(q)s
LIMIT %(k)s
""",
{"q": embed(question), "team": team_id, "k": k},
).fetchall()
Using a connection pool? pgvector-python's README shows passing a configure function that calls register_vector(conn) to ConnectionPool. For async connections there's register_vector_async.
6. Filtering: the caveat that bites RAG apps
That WHERE team_id = ... is where pgvector shines (permissions are just SQL) and where you need to understand one detail. From the README:
> With approximate indexes, filtering is applied after the index is scanned. If a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average.
So a LIMIT 5 query can quietly return fewer than 5 chunks. Your options, all from the README:
- Iterative index scans (pgvector 0.8.0+): keep scanning the index until enough rows match, up to hnsw.max_scan_tuples (20,000 by default) or ivfflat.max_probes.
SET hnsw.iterative_scan = strict_order; -- or relaxed_order for better recall
- A B-tree index on the filter column (CREATE INDEX ON doc_chunks (team_id);): exact search works well when the condition matches a low percentage of rows.
- Partial indexes when you filter by only a few distinct values, e.g. ... USING hnsw (embedding vector_cosine_ops) WHERE (doc_type = 'faq');
- List partitioning by tenant. The README notes that sharing one approximate index across tenants means one tenant's vectors can affect recall and speed for others.
Workflow: RAG over support docs (hypothetical)
Hypothetical scenario, to show how the pieces fit: a SaaS team already runs Postgres for its app and wants an assistant that answers from internal support docs.
1. Chunk and embed each doc into doc_chunks, storing team_id and doc_type from the same tables the app already uses for access control.
2. Retrieve with one query: vector distance in ORDER BY, permissions and metadata in WHERE, LIMIT 5. Turn on hnsw.iterative_scan so a narrow team filter still returns a full top-k.
3. Go hybrid if exact terms matter (error codes, SKUs): the README suggests combining with Postgres full-text search and merging results with Reciprocal Rank Fusion or a cross-encoder (examples in pgvector-python).
4. Re-embed on update in the same transaction that edits the doc, so the vector and the text never drift apart.
5. Measure recall on a small labeled set of questions before and after adding the index, then tune ef_search.
When to graduate to a dedicated vector database? Consider it when vector search becomes the main workload and starts competing with your transactional traffic for memory and CPU, or when you need features your Postgres setup doesn't give you. Before that, check what pgvector already offers for scale: halfvec (indexes up to 4,000 dimensions vs 2,000 for vector), binary quantization, and subvector indexing are all in the README. Until then, one database is one less thing to sync, secure and back up.
References
- pgvector README: pgvector/pgvector
- pgvector-python (psycopg, SQLAlchemy, Django and more): pgvector/pgvector-python
- pgvector-python hybrid search examples: github.com/pgvector/pgvector-python/tree/master/e…
- psycopg 3 docs: psycopg.org
- PostgreSQL partial indexes: postgresql.org
- PostgreSQL table partitioning: postgresql.org
- OpenAI embeddings guide: platform.openai.com
Do you already run Postgres for your app? Would you put your RAG retrieval in it, or reach for a dedicated vector DB from day one?
Mentions
pgvector: Build RAG Retrieval Inside the Postgres You Already Run This week's AI tool: pgvector, an open-source Postgres extension that adds vector similarity search to the database you probably already run. Why it's worth 15 minutes of your time: the retrieval step of a RAG app is "store embeddings, find the nearest ones to a query, filter by who is allowed to see what." If your documents, users and permissions already live in Postgres, pgvector lets you do all three in one SQL query, with the transactions, backups, JOINs and point-in-time recovery you already rely on. No second database to sync. Here's exactly how to use it. 1. Install and enable the extension pgvector supports Postgres 13+. It's available through Docker, Homebrew, APT, Yum, PGXN and conda-forge, and it comes preinstalled on many hosted Postgres providers (see the installation section of the README). Then, once in each database where you want it: CREATE EXTENSION vector; 2. Add a vector column next to your text A RAG table is just your chunks plus their embeddings. The vector(n) type takes the number of dimensions your embedding model outputs. OpenAI's text-embedding-3-small, for example, returns 1536 dimensions by default (OpenAI docs). CREATE TABLE doc_chunks ( id bigserial PRIMARY KEY, doc_id bigint NOT NULL, team_id bigint NOT NULL, -- who may read this chunk doc_type text NOT NULL, -- e.g. 'faq', 'runbook' content text NOT NULL, embedding vector(1536) ); Already have a table? ALTER TABLE doc_chunks ADD COLUMN embedding vector(1536); works too. 3. Query by distance pgvector adds distance operators you use in ORDER BY: | Operator | Meaning | |---|---| | <-> | L2 (Euclidean) distance | | <=> | cosine distance | | <#> | negative inner product | | <+> | L1 distance | <#> returns the negative inner product because Postgres only supports ascending-order index scans on operators. Multiply by -1 if you need the actual value. For cosine similarity, use 1 - (embedding <=> query). SELECT id, content, 1 - (embedding <=> $1) AS cosine_similarity FROM doc_chunks ORDER BY embedding <=> $1 LIMIT 5; Without an index, this is an exact nearest-neighbor search with perfect recall. That's a fine place to start. 4. Add an approximate index when it gets slow pgvector offers two approximate index types. Create the index with the operator class that matches the operator you query with (vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>). HNSW (better speed-recall tradeoff, slower build, more memory, can be built on an empty table): CREATE INDEX ON doc_chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- these are the defaults m is the max connections per layer; a higher ef_construction gives better recall at the cost of build time and insert speed. At query time, hnsw.ef_search (40 by default) controls the candidate list size: higher means better recall, slower queries. BEGIN; SET LOCAL hnsw.ef_search = 100; -- only for this transaction SELECT ...; COMMIT; IVFFlat (faster build, less memory, lower speed-recall tradeoff). Build it after the table has data. The README suggests starting with lists = rows / 1000 for up to 1M rows and sqrt(rows) above that, and probes = sqrt(lists) at query time: CREATE INDEX ON doc_chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); SET ivfflat.probes = 10; -- 1 by default One thing to expect: after adding an approximate index, the same query can return different results than the exact scan did. That's the recall-for-speed trade. 5. Call it from Python (psycopg 3 + pgvector-python) pip install "psycopg[binary]" pgvector openai import psycopg from pgvector import Vector from pgvector.psycopg import register_vector from openai import OpenAI client = OpenAI() conn = psycopg.connect("dbname=app", autocommit=True) conn.execute("CREATE EXTENSION IF NOT EXISTS vector") register_vector(conn) # teaches psycopg the vector type def embed(text: str) -> Vector: r = client.embeddings.create(model="text-embedding-3-small", input=text) return Vector(r.data[0].embedding) # Index a chunk chunk = "To reset 2FA, open Settings > Security and choose Reset." conn.execute( "INSERT INTO doc_chunks (doc_id, team_id, doc_type, content, embedding) " "VALUES (%s, %s, %s, %s, %s)", (42, 7, "faq", chunk, embed(chunk)), ) # Retrieve top-k chunks the user is allowed to see def retrieve(question: str, team_id: int, k: int = 5): return conn.execute( """ SELECT content, 1 - (embedding <=> %(q)s) AS score FROM doc_chunks WHERE team_id = %(team)s ORDER BY embedding <=> %(q)s LIMIT %(k)s """, {"q": embed(question), "team": team_id, "k": k}, ).fetchall() Using a connection pool? pgvector-python's README shows passing a configure function that calls register_vector(conn) to ConnectionPool. For async connections there's register_vector_async. 6. Filtering: the caveat that bites RAG apps That WHERE team_id = ... is where pgvector shines (permissions are just SQL) and where you need to understand one detail. From the README: > With approximate indexes, filtering is applied after the index is scanned. If a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average. So a LIMIT 5 query can quietly return fewer than 5 chunks. Your options, all from the README: - Iterative index scans (pgvector 0.8.0+): keep scanning the index until enough rows match, up to hnsw.max_scan_tuples (20,000 by default) or ivfflat.max_probes. SET hnsw.iterative_scan = strict_order; -- or relaxed_order for better recall - A B-tree index on the filter column (CREATE INDEX ON doc_chunks (team_id);): exact search works well when the condition matches a low percentage of rows. - Partial indexes when you filter by only a few distinct values, e.g. ... USING hnsw (embedding vector_cosine_ops) WHERE (doc_type = 'faq'); - List partitioning by tenant. The README notes that sharing one approximate index across tenants means one tenant's vectors can affect recall and speed for others. Workflow: RAG over support docs (hypothetical) Hypothetical scenario, to show how the pieces fit: a SaaS team already runs Postgres for its app and wants an assistant that answers from internal support docs. 1. Chunk and embed each doc into doc_chunks, storing team_id and doc_type from the same tables the app already uses for access control. 2. Retrieve with one query: vector distance in ORDER BY, permissions and metadata in WHERE, LIMIT 5. Turn on hnsw.iterative_scan so a narrow team filter still returns a full top-k. 3. Go hybrid if exact terms matter (error codes, SKUs): the README suggests combining with Postgres full-text search and merging results with Reciprocal Rank Fusion or a cross-encoder (examples in pgvector-python). 4. Re-embed on update in the same transaction that edits the doc, so the vector and the text never drift apart. 5. Measure recall on a small labeled set of questions before and after adding the index, then tune ef_search. When to graduate to a dedicated vector database? Consider it when vector search becomes the main workload and starts competing with your transactional traffic for memory and CPU, or when you need features your Postgres setup doesn't give you. Before that, check what pgvector already offers for scale: halfvec (indexes up to 4,000 dimensions vs 2,000 for vector), binary quantization, and subvector indexing are all in the README. Until then, one database is one less thing to sync, secure and back up. References - pgvector README: pgvector/pgvector - pgvector-python (psycopg, SQLAlchemy, Django and more): pgvector/pgvector-python - pgvector-python hybrid search examples: github.com/pgvector/pgvector-python/tree/master/e… - psycopg 3 docs: psycopg.org - PostgreSQL partial indexes: postgresql.org - PostgreSQL table partitioning: postgresql.org - OpenAI embeddings guide: platform.openai.com Do you already run Postgres for your app? Would you put your RAG retrieval in it, or reach for a dedicated vector DB from day one?