pgvector for PostgreSQL — A Field Guide

pgvector adds a vector data type and similarity search operators to Postgres, which means teams that already run Postgres for their application data can store embeddings — for semantic search, RAG retrieval, recommendation systems — right next to the relational data those embeddings describe, instead of standing up a separate specialized vector database. It's not free of tradeoffs, and the decision to stay on Postgres versus move to a dedicated vector store is one worth making deliberately rather than by default.

Storing embeddings as a column

pgvector introduces a vector column type with a fixed dimension — you declare vector(1536) for OpenAI's text-embedding-3-small output, for instance — and you insert embeddings as plain arrays of floats. Because it's a real Postgres column, it lives in the same table as whatever the embedding describes, so a similarity search can be a single SQL query with a normal WHERE clause filtering on other columns, rather than a separate round trip to a different system followed by a join in application code.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    content TEXT NOT NULL,
    embedding VECTOR(1536)
);

-- Cosine distance: <=> operator (lower = more similar)
SELECT id, content, embedding <=> :query_embedding AS distance
FROM documents
WHERE tenant_id = :tenant_id
ORDER BY embedding <=> :query_embedding
LIMIT 10;

The three distance metrics

pgvector supports three ways to compare vectors, each with its own operator: <-> for Euclidean (L2) distance, <=> for cosine distance, and <#> for negative inner product. Which one is correct depends on how the embedding model was trained — most modern text embedding models (OpenAI's, most sentence-transformer models) are trained and normalized for cosine similarity, so cosine distance is the right default for those; some models are tuned for inner product instead, and using the wrong metric silently produces worse-than-expected search results rather than an error, since all three operators return numeric distances that "look" valid regardless of whether they match how the model was trained.

IVFFlat vs HNSW indexing

Without an index, pgvector does an exact nearest-neighbor scan — correct, but linear in table size, which gets slow past a few hundred thousand rows. IVFFlat (Inverted File with Flat compression) partitions vectors into clusters at index build time and only searches the nearest clusters at query time — faster than a full scan, but it needs to be built after there's representative data in the table (an IVFFlat index built on an empty or tiny table clusters poorly), and it needs periodic rebuilding as data grows. HNSW (Hierarchical Navigable Small World) builds a multi-layer graph structure that gives better query performance and doesn't degrade as new rows are added without an index rebuild, at the cost of slower index builds and higher memory use. For most new projects, HNSW is the better default — its query performance and lack of a "must rebuild periodically" requirement usually outweigh the slower build time.

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

-- IVFFlat alternative -- needs a representative row count before
-- building, and periodic REINDEX as the table grows substantially
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

pgvector vs. a dedicated vector database

Dedicated vector databases (Pinecone, Weaviate, Qdrant, Milvus) are purpose-built for this workload and generally out-perform pgvector at very large scale — hundreds of millions of vectors, high query-per-second requirements, or workloads needing advanced filtering combined with vector search at speed pgvector's indexing hasn't fully matched yet. For a team already running Postgres with embeddings in the tens of millions of rows or fewer, pgvector avoids the operational cost of running and paying for an entirely separate database, keeps embeddings transactionally consistent with the relational data describing them, and lets you filter on relational columns and vector similarity in the same query without stitching results from two systems together. The tradeoff flips once query volume or vector count grows large enough that a dedicated store's specialized indexing and horizontal scaling actually matter more than the operational simplicity of staying on one database.

Wrapping up

pgvector is the right choice for most teams that already run Postgres and need vector search at small-to-medium scale — it keeps embeddings next to the data they describe, and a well-chosen index (HNSW, by default) keeps queries fast without adding an entire new database to operate. Match the distance metric to how the embedding model was actually trained, and revisit the decision to move to a dedicated vector database only once scale — not architectural purity — makes it necessary.