pgvector extends PostgreSQL with vector similarity search, letting you store embeddings alongside your regular relational data and query them with standard SQL. For teams already running PostgreSQL, it is the lowest-friction path to adding vector search to an existing system — no new database to operate, no data synchronisation between services, and familiar tooling. But dedicated vector databases exist for good reasons. This guide covers when pgvector is sufficient and when you should reach for Qdrant, ChromaDB, or Weaviate instead.
What pgvector Adds to PostgreSQL
pgvector is a PostgreSQL extension that adds a vector data type and two index types for approximate nearest-neighbour search: IVFFlat (inverted file with flat quantization) and HNSW (Hierarchical Navigable Small World). Install it, create a vector column, insert embeddings as part of regular INSERT statements, and query with the <-> (L2 distance), <#> (inner product), or <=> (cosine distance) operators. The vector column lives in a regular PostgreSQL table alongside your text, timestamps, user IDs, and other relational data.
-- Install the extension
CREATE EXTENSION IF NOT EXISTS vector;
-- Create a table with a vector column
CREATE TABLE documents (
id BIGSERIAL PRIMARY KEY,
content TEXT,
embedding vector(1536), -- dimension matches your embedding model
source VARCHAR(255),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Create HNSW index for fast ANN search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- Insert with embedding
INSERT INTO documents (content, embedding, source)
VALUES ('Your document text', '[0.1, 0.2, ...]'::vector, 'wiki');
-- Nearest neighbour query
SELECT content, source,
1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
WHERE source = 'wiki'
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;The query combines a metadata filter (WHERE source = ‘wiki’) with vector similarity ordering — the same operation that dedicated vector databases perform. The result is returned as a standard SQL result set that joins naturally with other tables.
Where pgvector Wins
You already run PostgreSQL. If your application uses PostgreSQL for relational data, adding pgvector means zero new infrastructure — no additional service to deploy, monitor, back up, or scale. The operational overhead of adding a vector column is minimal compared to introducing a dedicated vector database service alongside your existing database. For teams without dedicated platform engineering capacity, this simplicity is often decisive.
Joining vectors with relational data. The native SQL join is pgvector’s strongest advantage. “Find the 10 most similar documents to this query, written by users who joined in the last 6 months, in categories the user has marked as favourites” is a single SQL query with pgvector. With a dedicated vector database, this requires fetching candidates from the vector DB, then joining with your relational database — two round trips, more code, potential consistency issues. Complex filtering that mixes vector similarity with relational conditions is where pgvector’s integrated approach shines most clearly.
Transactional consistency. Embeddings in pgvector participate in PostgreSQL transactions. If your application creates a document and stores its embedding in the same operation, both either commit or roll back together. Dedicated vector databases are separate services — keeping vectors in sync with your relational data requires explicit coordination, and failures in the vector insert can leave the systems inconsistent.
Collections under ~1 million vectors. For most RAG applications and semantic search features, the dataset fits within the range where pgvector’s HNSW index delivers sub-millisecond query times adequate for production use. In this range, query performance is not a meaningful differentiator — all options are fast enough.
Where Dedicated Vector DBs Win
Very large collections. At tens of millions or hundreds of millions of vectors, dedicated vector databases — particularly Qdrant — outperform pgvector in query throughput and latency. They use more sophisticated indexing and memory management tuned specifically for vector workloads. pgvector’s HNSW implementation is good but not competitive with Qdrant at extreme scale.
High query throughput. Dedicated vector databases handle thousands of concurrent vector queries per second more efficiently than PostgreSQL, which is a general-purpose database competing for compute and memory with all other query types. If vector search is a high-frequency path in your system, dedicated infrastructure isolates that workload.
Advanced vector features. Qdrant supports quantization (reducing memory usage 4-16x by compressing vectors), multiple vector spaces per record (useful for late interaction models like ColBERT), sparse vector support, and more sophisticated filtering. Weaviate provides built-in hybrid search, generative search, and multi-tenant isolation. ChromaDB provides the simplest developer experience for local development. These capabilities are either absent or less mature in pgvector.
No existing PostgreSQL dependency. If you are building a greenfield service without an existing PostgreSQL instance, there is less reason to introduce PostgreSQL just for pgvector — a dedicated vector database is simpler for the specific use case.
Figure 1 — pgvector vs Dedicated Vector DB: Decision Guide
Setting Up pgvector with Python
pip install psycopg2-binary pgvector
import psycopg2
from pgvector.psycopg2 import register_vector
import numpy as np
conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
register_vector(conn)
cur = conn.cursor()
# Insert an embedding
embedding = np.array([0.1, 0.2, 0.3]) # your actual embedding
cur.execute(
"INSERT INTO documents (content, embedding) VALUES (%s, %s)",
("Document text here", embedding)
)
conn.commit()
# Query nearest neighbours
query_embedding = np.array([0.1, 0.25, 0.28])
cur.execute(
"SELECT content, 1 - (embedding <=> %s) AS similarity "
"FROM documents ORDER BY embedding <=> %s LIMIT 5",
(query_embedding, query_embedding)
)
results = cur.fetchall()
for content, similarity in results:
print(f"{similarity:.3f}: {content}")The pgvector Python library handles the serialization between NumPy arrays and the PostgreSQL vector type. The same connection and cursor work for both vector queries and regular relational queries, which is the key workflow advantage.
HNSW vs IVFFlat in pgvector
pgvector supports two index types. HNSW (added in pgvector 0.5) is the better choice for most workloads: it provides better recall at equivalent query speed and does not require knowing the number of vectors in advance. IVFFlat (the original index type) is faster to build but requires setting the lists parameter based on the expected dataset size and generally delivers lower recall at equivalent speed settings. Always use HNSW unless you have a specific reason not to (very large collections where build time matters, or integration with tools that pre-date HNSW support). The HNSW index in pgvector uses the same algorithm as standalone HNSW implementations in dedicated vector databases, and the recall-latency trade-off is competitive for collections in the millions-of-vectors range.
pgvector Limitations to Know
A few current limitations of pgvector worth knowing before committing to it. Dimensions: pgvector supports vectors up to 2000 dimensions in current versions — this covers most embedding models (OpenAI text-embedding-3-small is 1536-dim, nomic-embed-text is 768-dim) but not all. HNSW index builds: building an HNSW index on a large existing table is slow and locks the table — plan for this during off-peak hours or build on a replica. Vacuum and HNSW: frequent updates to rows with vector columns can cause HNSW index bloat requiring VACUUM REINDEX — less of an issue for append-mostly embedding tables. No quantization: pgvector stores full-precision float32 vectors, using 4 bytes per dimension. Qdrant’s int8 quantization stores vectors at 1 byte per dimension, a 4x reduction in storage and memory at modest recall cost — useful for very large collections where memory matters. These limitations are known to the pgvector team and are being addressed in successive releases.
The Supabase and Neon Angle
Supabase and Neon both offer managed PostgreSQL with pgvector pre-installed, making managed pgvector as operationally easy as Pinecone. Supabase in particular has invested in pgvector tooling, documentation, and AI feature integration — their edge functions and storage features combine with pgvector for a full managed AI-ready backend. For teams using Supabase for their application backend, pgvector is the natural choice for vector storage: everything is in one managed service, the pricing is reasonable, and the integration with Auth, Storage, and Realtime is tight. This managed angle significantly changes the pgvector vs dedicated vector DB calculus — “pgvector” no longer means “self-manage PostgreSQL” for these users, it means “use the managed service you are already paying for.”
Recommendation: Start with pgvector, Graduate When Needed
The practical recommendation: if you have a PostgreSQL database (hosted anywhere), use pgvector first. The integration simplicity and SQL join capability are real advantages, and the performance is adequate for most production RAG applications at the scale most teams operate. Monitor query latency and throughput as your collection grows. If queries start exceeding acceptable latency thresholds (typically above 50-100ms for interactive applications) or your query volume grows to thousands per second, that is the signal to evaluate Qdrant or Weaviate. The migration path is straightforward — export your embeddings from pgvector, import to Qdrant, update your query code. Starting with pgvector does not lock you in; it buys you time to understand your actual scale requirements before committing to the operational overhead of a dedicated service.
Hybrid Search with pgvector and PostgreSQL Full-Text Search
pgvector does not include hybrid search (BM25 + vector) natively, but PostgreSQL’s built-in full-text search (tsvector/tsquery) provides the keyword search component. Combining the two requires running separate queries and merging results, typically using Reciprocal Rank Fusion (RRF) to combine the rankings:
-- Hybrid search: combine vector similarity and full-text search
WITH vector_results AS (
SELECT id, content,
ROW_NUMBER() OVER (ORDER BY embedding <=> '[0.1,...]'::vector) AS rn
FROM documents
LIMIT 20
),
fts_results AS (
SELECT id, content,
ROW_NUMBER() OVER (ORDER BY ts_rank(to_tsvector(content),
plainto_tsquery('your query')) DESC) AS rn
FROM documents
WHERE to_tsvector(content) @@ plainto_tsquery('your query')
LIMIT 20
),
rrf AS (
SELECT COALESCE(v.id, f.id) AS id,
COALESCE(v.content, f.content) AS content,
(COALESCE(1.0/(60 + v.rn), 0) + COALESCE(1.0/(60 + f.rn), 0)) AS rrf_score
FROM vector_results v
FULL OUTER JOIN fts_results f ON v.id = f.id
)
SELECT id, content, rrf_score FROM rrf ORDER BY rrf_score DESC LIMIT 5;This approach delivers hybrid search from pure PostgreSQL without any additional services. The query is more complex than dedicated hybrid search in Qdrant or Weaviate, but it works well and keeps everything in a single database. For teams comfortable with SQL, this pattern is a viable production-grade hybrid search implementation that requires no additional infrastructure investment.