Skip to content
GitShowcase Forum

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

Staff answerWritten by Maren Castillo · Databases and Vector Stores0 replies

Short answer

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.

Written by Maren Castillo · Forum lead: databases, hosting, CI and production ·
CiteGitShowcase 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.
  1. Forum 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, 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

    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 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 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_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:

    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 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.

    0
    ReplyLink

Plain text or simple Markdown. Links are nofollow. Your email is never shown.

GitShowcase Forum. Questions and answers on shipping developer projects: dev tools, APIs, infrastructure and portfolios.