pgvector: Keeping Your Embeddings in PostgreSQL
pgvector adds vector similarity search to PostgreSQL. For most local RAG projects that is one fewer service to run, one fewer thing to back up, and SQL filters that actually work. Here is where it wins and where it stops.
Key takeaways
- pgvector is a PostgreSQL extension that stores embeddings and searches them by similarity, using ordinary SQL.
- Its decisive advantage is not speed, it is having one database instead of two: same backups, same transactions, same access control, same joins.
- Metadata filtering comes free and correct. Restricting a search by owner, date, department or permission is a WHERE clause, not a bolted-on feature.
- Two index types matter: HNSW (fast queries, slower builds, more memory) and IVFFlat (cheaper to build, needs data before it can be created properly).
- It stops making sense at large scale or high query volume, where a dedicated engine's memory tricks and distributed features earn their extra service.
The case for not adding a service
Your documents, your AI: a reliable local RAG over your PDFs, notes and mail — nothing leaves your machine.
- Lifetime online access
- PDF + files
- 30-day refund
A local RAG stack already runs a model server, an embedding step and something that stores documents. Adding a dedicated vector database means another container, another port, another backup job, another thing that can be out of sync with your relational data when a document is deleted.
If PostgreSQL is already in the picture — and in most business applications it is — pgvector removes that whole category of problem. Your chunks live in a table next to the documents they came from, with foreign keys that keep them honest, and a deletion cascades the way you expect.
How it works in practice
You store a vector column alongside your normal columns. A query orders rows by distance to the query embedding, and returns the nearest. Three distance operators cover the usual cases — cosine, inner product and L2 — and the one you choose must match how your embedding model was trained, which is the single most common source of quietly poor results.
| Piece | What it is | What to watch |
|---|---|---|
| Vector column | A fixed-dimension array of floats | The dimension is set by your embedding model and cannot change without re-embedding |
| Distance operator | Cosine, inner product or L2 | Must match the model's convention |
| HNSW index | Graph index, fast queries | Builds slowly and wants memory; the usual default today |
| IVFFlat index | Partition index, cheap to build | Should be created after representative data exists |
| No index at all | Exact scan of every row | Perfectly fine up to a few tens of thousands of rows |
The filtering argument
Real questions are rarely asked against an entire corpus. You want passages from this department, after that date, in documents this user is allowed to read. In a dedicated vector store that is a payload filter with its own syntax and its own edge cases. In PostgreSQL it is a WHERE clause next to a similarity ordering, joined to your users table if needed.
The permissions case deserves emphasis: when several people query one index, the filter on access rights is what stops a model from quoting someone a document they should not see. No prompt instruction substitutes for it, and expressing it in SQL against your existing authorisation model is far safer than reimplementing it.
Where it stops
- Memory at scale. Millions of full-precision vectors are large, and dedicated engines offer aggressive quantisation that keeps them resident in memory. pgvector has options here, but this is the clearest gap.
- Index build time. HNSW on a very large table is slow and resource-hungry.
- Query concurrency. Heavy vector search alongside your transactional load puts both on the same server. Read replicas help; separating concerns helps more.
- Dimension limits. Indexed vectors have an upper bound on dimensions. Common embedding models fit comfortably; exotic large-dimension models may not.
- Hybrid search. PostgreSQL has full-text search and you can combine it with vector distance, but the ergonomics are yours to build.
Changing the embedding model invalidates everything. The dimension and the geometry both belong to the model. A new model means a new column or table and a full re-embedding — a GPU job, not a migration script. Decide early, and prefer a model whose language coverage matches your corpus.
pgvector or a dedicated vector database
| Situation | Choice |
|---|---|
| You already run PostgreSQL, under a few hundred thousand chunks | pgvector, comfortably |
| Vectors must stay consistent with relational data | pgvector — transactions do it for free |
| Fine-grained permission filtering against existing tables | pgvector |
| Millions of vectors, high query rate | A dedicated engine |
| No server to administer, embedded in an app | An embedded vector store |
| Prototype in a notebook | Anything; this choice is reversible |
Verdict
pgvector is the pragmatic default for local RAG in any project that already has a relational database. It trades peak performance for one fewer moving part and for filters that are correct by construction. Start here, measure when your corpus grows, and move to a dedicated engine when the numbers — not the architecture diagrams — say so.
Frequently asked questions
Is pgvector fast enough for RAG?
For typical local corpora — tens of thousands to a few hundred thousand chunks — yes, with an HNSW index, and often even without one at the low end. Retrieval is rarely the slow part of a local pipeline; generation is.
pgvector or Qdrant?
pgvector if PostgreSQL is already there and your data is relational: fewer services, transactional consistency, SQL filters. Qdrant when scale, memory-efficient quantisation or a dedicated service model matter more.
Which index should I use?
HNSW is the sensible default: better query performance, at the cost of slower builds and more memory. IVFFlat is cheaper to build but should be created once representative data exists. Below a few tens of thousands of rows, no index is fine.
Can I filter by metadata?
Yes, with ordinary SQL, including joins to other tables. This is one of the strongest reasons to choose it, especially for per-user permission filtering.
What happens if I change embedding model?
Everything must be re-embedded: the dimension and the vector geometry belong to the model. Plan that as a GPU batch job, not a schema migration.
Does pgvector need a GPU?
No. Similarity search is CPU and memory work. The GPU is used to compute embeddings and to run the language model, both outside the database.
A current option for local AI: GMKtec EVO-X2 64GB / 1TB (Ryzen AI Max+ 395). Match memory to your model and software. A mini PC is a complete PC alternative; Mac/MLX and CUDA instructions require compatible hardware.
Amazon Check GMKtec EVO-X2 64GB / 1TB (Ryzen AI Max+ 395) price →As an Amazon Associate, BestLLMfor earns from qualifying purchases, at no extra cost to you. It does not influence our independent rankings.
Found an error or have feedback? Let us know — it helps everyone who reads this guide.