pgvector: vector search inside the Postgres you already run

If your application already stores its data in PostgreSQL, adding semantic search doesn't have to mean adding another database. pgvector is an open-source extension that gives Postgres a vector column type, distance operators, and approximate nearest-neighbour indexes. Your embeddings live next to the rows they describe, inside the same transactions, backups and permissions.

Enable it

pgvector is available on most managed Postgres services and in common Docker images. Once installed on the server:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id         bigserial PRIMARY KEY,
  doc_id     bigint NOT NULL,
  content    text NOT NULL,
  embedding  vector(1536)      -- match your embedding model's dimension
);

Query by distance

pgvector adds three distance operators:

Operator	Meaning
<->	Euclidean (L2) distance
<=>	cosine distance (1 − cosine similarity)
<#>	negative inner product

A nearest-neighbour query is just ORDER BY plus LIMIT:

SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM chunks
WHERE doc_id = ANY($2)              -- normal SQL filters still work
ORDER BY embedding <=> $1
LIMIT 10;

Because it's ordinary SQL, you can join to your users, documents and permissions tables in the same query. That's the biggest practical advantage over a separate store.

From Python

import numpy as np
import psycopg
from pgvector.psycopg import register_vector

with psycopg.connect(DSN) as conn:
    register_vector(conn)
    conn.execute(
        "INSERT INTO chunks (doc_id, content, embedding) VALUES (%s, %s, %s)",
        (42, text, np.array(vec, dtype=np.float32)),
    )
    rows = conn.execute(
        "SELECT id, content FROM chunks ORDER BY embedding <=> %s LIMIT 5",
        (np.array(query_vec, dtype=np.float32),),
    ).fetchall()

Indexes: exact first, then HNSW

Without an index, pgvector does an exact scan. For tens of thousands of rows that is often fast enough, and it gives perfect recall.

As data grows, add an approximate index. pgvector offers two kinds:

-- HNSW: better speed/recall trade-off, slower to build, more memory
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

-- IVFFlat: faster to build, smaller; build it after loading representative data
CREATE INDEX ON chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Match the operator class to your distance: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. An index built for one operator won't be used for queries ordered by another.

At query time you can trade speed for recall:

SET hnsw.ef_search = 100;     -- higher = better recall, slower

Measure recall against an exact search on a sample of your real queries before settling on settings.

Filtering and approximate indexes

A common surprise: with an approximate index, a selective WHERE filter is applied to the candidates the index returns, so you can get fewer results than your LIMIT. Options include raising ef_search, using partial indexes per tenant or category, or (for highly selective filters) letting Postgres choose an exact scan on the filtered rows. Recent pgvector versions also add iterative scans to help with this; check the documentation for your version.

Hybrid search in one database

Postgres full-text search sits right next to pgvector, so keyword and semantic results can be combined in SQL, for example with Reciprocal Rank Fusion. That combination usually beats pure vector search on exact terms like error codes and function names.

When a dedicated vector database still makes sense

• Hundreds of millions of vectors with tight latency requirements.
• Very high write rates of vectors that would compete with your transactional workload.
• Features you need that Postgres doesn't offer, like built-in multi-region vector replication.

For most applications, from prototypes to a few million vectors, pgvector keeps the architecture simpler: one database, one backup, one access-control model.

Verdict

If you already run Postgres, start with pgvector. Use exact search while data is small, add HNSW when queries slow down, measure recall on your own queries, and only reach for a separate vector store when you have evidence you've outgrown it.