pgvector in Postgres
pgvector in Postgres is an extension that adds an embedding-friendly vector column type, distance operators, and indexing options to PostgreSQL. It lets an application store text chunks, metadata, permissions, and embedding vectors in ordinary tables, then retrieve nearest neighbours with SQL queries that also apply relational filters.
Vector search often becomes awkward when embeddings live in a separate system from the data that decides whether a result is valid. A retrieval query usually needs semantic similarity, but also tenant boundaries, document type, language, timestamps, and access control. Splitting those concerns across a vector store and a relational database creates synchronisation problems and extra application logic. pgvector is useful when the embedding is just another attribute of records already governed by Postgres.
In practice, the application creates embeddings outside Postgres, usually with an embedding model, and writes them into a vector column beside the source text and metadata. A query embeds the user’s question, compares that vector with stored vectors using a distance operator, orders by the smallest distance or strongest similarity, and returns the nearest rows. Because it is SQL, the same query can include ordinary WHERE clauses for permissions, accounts, dates, languages, and other structured constraints.
The main trade-off is that Postgres is still doing database work, not magic. Without a suitable index, nearest-neighbour search can require comparing against a large part of the table. Approximate indexes such as HNSW or IVFFlat reduce that cost, but introduce tuning choices around recall, latency, memory, build time, and update patterns. The right distance metric also depends on how the embeddings were produced and whether they were normalised.
Engineers usually meet pgvector in RAG systems, semantic search features, recommendation prototypes, and internal tools where the existing source of truth is already PostgreSQL. It is commonly misunderstood as a complete replacement for every specialised vector database. Sometimes it is enough, especially when metadata filtering matters and operational simplicity is valuable. Sometimes it is not, particularly when scale, write volume, or retrieval tuning requirements outgrow the comfort zone of a general-purpose database.
Common questions
- Does pgvector create embeddings inside Postgres?
- Usually no. pgvector stores vectors and provides operators and indexes for comparing them. The embeddings are normally generated by application code or a separate model service, then inserted into Postgres. Postgres handles persistence, filtering, joins, ordering by distance, and index-assisted retrieval over those stored vectors.
- Can pgvector combine semantic search with permissions and metadata filters?
- Yes, that is one of its strongest reasons to exist. A query can order by vector distance while also filtering on tenant, user permissions, document type, language, timestamps, or any other relational column. The exact execution plan depends on indexes, selectivity, table size, and how the SQL is written.
- Is pgvector always slower than a dedicated vector database?
- It depends on workload and constraints. A dedicated vector database may offer more specialised scaling and retrieval controls, but pgvector avoids moving data into another system and benefits from SQL, transactions, backups, and existing metadata. For many product features, operational simplicity and correct filtering matter as much as raw nearest-neighbour performance.