# pgvector or a dedicated vector database for a small RAG app?

Source: https://gitshowcase.com/forum/discuss/pgvector-or-a-dedicated-vector-database-for-a-small-rag-app/
Site: GitShowcase Forum, category Databases and Vector Stores
Published: 2026-11-19. Updated: 2026-11-19. Replies: 0.

## Question by Maren Castillo (staff), 2026-11-19

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.

## 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](https://neon.com/docs/extensions/pgvector) 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`, `users` and `tenants` tables, so a permission filter is a normal `WHERE` clause and deleting a user's data is a normal `DELETE`. With a separate vector store you must keep two systems in sync and filter in two places.

## Check first

1. How many chunks do you have today, and how many in a year? Write down both numbers instead of guessing.
2. What dimension are your embeddings? Check the model's docs. This matters for index limits (below).
3. Do you filter results by user, tenant, date or tag? Heavy filtering changes which index works best.
4. Which Postgres host are you on, and is `vector` available? Many managed hosts offer it.

## Step by step: a working setup

```sql
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:

```sql
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 BY` and `LIMIT`. 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](https://supabase.com/docs/guides/ai/vector-indexes) recommends HNSW in general, citing performance and robustness against changing data. Create it with the operator class that matches your distance function:

```sql
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
```

Using `vector_l2_ops` with a cosine query means the index will not be used. Per the Neon docs, the HNSW defaults are `m = 16` and `ef_construction = 64`, and the query side `hnsw.ef_search` is `40`. A higher `ef_search` gives better recall at the cost of speed:

```sql
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 `vector` and 4,000 for `halfvec`. If your embedding model outputs more than 2,000 dimensions, store `halfvec` or pick a model or setting that gives fewer dimensions. Check your version with `SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';`.

## Filtering is where it gets tricky

A `WHERE` clause 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:

1. Write 30 to 50 real questions with the chunk you expect each to retrieve.
2. Run them with no index (exact search). That is your recall baseline.
3. Add the HNSW index and rerun. Note how many expected chunks still appear in the top results and the query time.
4. Raise `ef_search` until 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_mem` tuning 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](https://gitshowcase.com/best/vector-databases/) compares the options.

On a serverless host, vector queries still use connections, so read [Serverless Postgres keeps running out of connections](/forum/discuss/serverless-postgres-keeps-running-out-of-connections-what-should-i/) before you scale up traffic.

---
Cite as: 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.
