pgvector or a dedicated vector database for a small RAG app?
For a small retrieval augmented generation (RAG) app that already uses Postgres, start with pgvector. One database means one backup, one auth model, and SQL joins between chunks and your real data. Move to a dedicated vector database only when you can show a measured limit that Postgres tuning cannot fix.
Cite
GitShowcase Forum, "pgvector or a dedicated vector database for a small RAG app?", https://gitshowcase.com/forum/discuss/pgvector-or-a-dedicated-vector-database-for-a-small-rag-app/, accessed 2026-11-19.- Maren CastilloStaffForum lead: databases, hosting, CI and production ·
Why pgvector is the default for small apps
RAG means you split documents into chunks, turn each chunk into an embedding (a list of numbers that captures meaning), store them, and at question time fetch the chunks nearest to the question's embedding. The Neon pgvector docs list what the extension gives you: exact and approximate nearest neighbor search, several vector types, distance operators (L2, inner product, cosine and others), and the normal Postgres features around it such as ACID guarantees, point in time recovery and joins.
That last part is the real win for small teams. Your chunks sit next to
documents,usersandtenantstables, so a permission filter is a normalWHEREclause and deleting a user's data is a normalDELETE. With a separate vector store you must keep two systems in sync and filter in two places.Check first
- How many chunks do you have today, and how many in a year? Write down both numbers instead of guessing.
- What dimension are your embeddings? Check the model's docs. This matters for index limits (below).
- Do you filter results by user, tenant, date or tag? Heavy filtering changes which index works best.
- Which Postgres host are you on, and is
vectoravailable? Many managed hosts offer it.
Step by step: a working setup
CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE chunks ( id BIGSERIAL PRIMARY KEY, doc_id BIGINT NOT NULL, tenant_id BIGINT NOT NULL, content TEXT NOT NULL, embedding VECTOR(1536) -- match your embedding model's dimensions );Query for the five nearest chunks by cosine distance:
SELECT id, content FROM chunks WHERE tenant_id = $1 ORDER BY embedding <=> $2 -- $2 is the query embedding LIMIT 5;Neon's docs point out that to use an index, the query needs both
ORDER BYandLIMIT. They also explain that without an index, pgvector does exact search with perfect recall. For a few thousand chunks that is often fast enough, so do not add an index before you need one.When to add an index, and which one
An approximate index trades a little recall for speed, and results differ once you add it. Neon's docs describe the two choices this way:
Index Strength Weakness HNSW Better speed to recall tradeoff; can be built on an empty table Slower build, more memory IVFFlat Faster build, less memory Lower query performance; needs data in the table first Supabase's vector index guide recommends HNSW in general, citing performance and robustness against changing data. Create it with the operator class that matches your distance function:
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);Using
vector_l2_opswith a cosine query means the index will not be used. Per the Neon docs, the HNSW defaults arem = 16andef_construction = 64, and the query sidehnsw.ef_searchis40. A higheref_searchgives better recall at the cost of speed:BEGIN; SET LOCAL hnsw.ef_search = 100; SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 5; COMMIT;Dimension limits. For pgvector 0.7.0 and above, indexes support up to 2,000 dimensions for
vectorand 4,000 forhalfvec. If your embedding model outputs more than 2,000 dimensions, storehalfvecor pick a model or setting that gives fewer dimensions. Check your version withSELECT extname, extversion FROM pg_extension WHERE extname = 'vector';.Filtering is where it gets tricky
A
WHEREclause combined with an approximate index needs care, so always check that you still get a full list of results back. Neon's docs list the options: a normal index on the filter column for exact search, a partial index on the vector column for one value, or partitioning when you have many values. If every query is scoped to one tenant and tenants are large, partial indexes or partitions can fit. If you have thousands of small tenants, a plain filter on a small exact scan is often fine.How to decide with evidence
Do not pick based on a vendor chart. Build a tiny evaluation:
- Write 30 to 50 real questions with the chunk you expect each to retrieve.
- Run them with no index (exact search). That is your recall baseline.
- Add the HNSW index and rerun. Note how many expected chunks still appear in the top results and the query time.
- Raise
ef_searchuntil recall matches the baseline or latency becomes unacceptable.
If you hit a ceiling, such as build times or memory pressure from the index,
maintenance_work_memtuning is documented by Neon, and halfvec or binary quantization options shrink storage. Those are your next steps before switching systems.When a dedicated vector database makes sense
Choose one when you have a measured need Postgres cannot meet, for example: your vectors far outgrow the memory you want to give your primary database, vector queries are slowing your transactional workload and you cannot isolate them on a replica, or you need features such as built in hybrid ranking you do not want to build. I will not give a row count threshold because it depends on dimensions, hardware and your recall target. Measure it. The best vector databases guide compares the options.
On a serverless host, vector queries still use connections, so read Serverless Postgres keeps running out of connections before you scale up traffic.
Write something first.
Give people something to work with: at least 30 words on what happened and what you tried.
That is too long. Keep it under 6,000 characters.
Write the question as the title, 15 to 140 characters, no links.
Pick a category.
Add a name (2 to 40 characters, no links).
That email address does not look right.
That was quick. Read the thread, then try again.
The form expired. Reload the page and post again.
Something went wrong with the form. Reload and try again.
Please complete the check and post again.
Limit reached for now. Try again later.
This thread is closed to new replies.
This thread no longer accepts replies.
Something went wrong with the form. Reload and try again.
Post a reply or question first (name and email), then this browser can vote, edit and accept answers.
You cannot vote on your own post.
Only the author (within 30 days) or the forum team can do that.
That email already has an account. Use your sign-in link instead.
New accounts are paused for the moment. Try again later.
That was already posted.