A team adds a separate vector database to power internal support search, then spends the next quarter reconciling two truths: PostgreSQL says the customer document was deleted, while the vector store still returns the old chunk. The retrieval model was fast. The data model was wrong.

Short Version

pgvector is enough when the RAG corpus is already relational, the metadata filters matter, the write volume is moderate, and operational simplicity is worth more than specialized vector infrastructure.

Keeping embeddings inside Postgres gives you one transaction boundary, one backup system, one authorization model, and one place to join vectors with tenant, product, entitlement, and lifecycle fields. That does not make pgvector the right answer for every vector workload. It means many production RAG systems should start in Postgres and earn their way out.

Situation

Most RAG architecture diagrams start with chunks, embeddings, vector database, retriever, and LLM. They understate the data-management work around the vector store.

The Problem

A second vector system adds real obligations:

  • Source records need stable IDs.
  • Chunks need versioning.
  • Deletes need to propagate.
  • Tenant filters need to be enforced at retrieval time.
  • Embedding model changes need backfills.
  • Restore tests need to include the vector index.
  • Authorization cannot be delegated to the LLM layer.

If the source data already lives in PostgreSQL, a second vector system creates a synchronization problem before it solves a retrieval problem. Does the corpus need a dedicated vector store at all, or does it just need embeddings added to the database that already owns the data?

How It Works

A Postgres-first RAG design treats embeddings as an indexable representation of rows, not as a new source of truth.

flowchart TD
    App[application write] --> Tx[Postgres transaction]
    Tx --> Doc[documents table]
    Tx --> Job[embedding job row]
    Job --> Worker[embedding worker]
    Worker --> Emb[document embeddings table]
    Query[user question] --> Auth[tenant and entitlement filter]
    Auth --> SQL[SQL vector query]
    SQL --> Context[retrieved context]
    Context --> LLM[answer generation]

The base tables hold documents, ownership, lifecycle state, and permissions. A companion embeddings table holds chunk text, embedding vectors, model version, content hash, and timestamps.

CREATE TABLE document_chunks (
  id              bigserial PRIMARY KEY,
  document_id     bigint NOT NULL,
  tenant_id       bigint NOT NULL,
  chunk_ordinal   int NOT NULL,
  content_hash    text NOT NULL,
  embedding_model text NOT NULL,
  body            text NOT NULL,
  embedding       vector(1536),
  deleted_at      timestamptz
);

CREATE INDEX document_chunks_tenant_idx
  ON document_chunks (tenant_id, document_id);

CREATE INDEX document_chunks_embedding_hnsw
  ON document_chunks
  USING hnsw (embedding vector_cosine_ops);

The retrieval query can enforce authorization before ranking:

SELECT document_id, chunk_ordinal, body
FROM document_chunks
WHERE tenant_id = $1
  AND deleted_at IS NULL
ORDER BY embedding <=> $2
LIMIT 8;

That query is not magic. It is regular SQL with a vector operator. The value is that the same database enforces the same data boundaries the rest of the application already trusts.

Architecture and Operating Model

The Postgres-first operating model has four parts.

Ingestion. Store source documents first. Generate chunks deterministically. Write chunk rows with a content hash and model version. Generate embeddings asynchronously. Make the embedding job idempotent so a retry cannot create duplicate chunks.

Retrieval. Apply tenant, entitlement, lifecycle, and category filters in SQL. Rank by vector distance only after the candidate set is authorized. For hybrid retrieval, combine Postgres full-text search with vector search and rerank in the application when necessary.

Maintenance. Monitor index size, query plans, table bloat, autovacuum lag, and embedding backfill progress. Rebuild indexes during controlled windows. Keep embedding model version in the table so old and new vectors do not silently mix.

Recovery. Restore Postgres and the embeddings restore with it. If embeddings are treated as derived data, keep enough source and job metadata to rebuild them; if they are treated as operational data, include them in normal backup and restore drills.

In Practice

DBAs tend to prefer boring systems that fail predictably. pgvector is attractive because it keeps the retrieval feature inside known operational controls: roles, schemas, backups, PITR, extensions, connection pools, observability, and SQL plans.

The tradeoff is resource contention. Vector indexes consume memory. Approximate searches consume CPU. Large embedding backfills create write load and table churn. A Postgres-first design should isolate this with separate tables, controlled workers, statement timeouts, connection limits, and workload-specific indexes.

The platform question is not “can Postgres do vector search?” It can. The question is whether the vector workload will harm the OLTP workload that Postgres already serves.

Where It Breaks

LimitProduction symptomMitigation
High vector query volumeOLTP latency rises during retrieval peaksRead replica, workload isolation, or dedicated vector store
Weak hybrid search needsExact terms, SKUs, and names rank poorlyAdd full-text search and reranking, or move search-heavy workloads to OpenSearch
Selective filters with approximate indexesTop-k returns too few authorized rowsTune ef_search, iterative scans, partial indexes, or partitions
Large embedding backfillsVacuum pressure, WAL growth, replica lagBatch writes, throttle workers, schedule index rebuilds
Multi-region retrievalCross-region consistency and latency conflictsDefine regional ownership and rebuild strategy

Security, Cost, Observability, and Failure Notes

Security improves when authorization fields live beside vectors, but only if every retrieval query uses them. Do not rely on application-side filtering after retrieval; the retrieved text has already crossed the boundary.

Cost is often lower at the beginning because there is no extra cluster. It can become higher if vector search forces a larger Postgres instance for unrelated OLTP traffic. Measure the marginal cost of CPU, RAM, storage, WAL, backups, and replicas.

Observability should include EXPLAIN plans for representative retrieval queries, index hit behavior, query latency by tenant, rows removed by filters, embedding job lag, and empty-result rates.

Failure modes are usually stale embeddings, deleted documents still searchable, mixed model versions, or query plans that stop using the intended index after data distribution changes.

Decision Checklist

  • Is the corpus already in PostgreSQL?
  • Do retrieval filters depend on relational fields or joins?
  • Is read-after-write consistency more important than maximum ANN throughput?
  • Can the OLTP workload tolerate vector query CPU and memory pressure?
  • Can the team validate query plans with EXPLAIN?
  • Is the expected corpus size within the team’s Postgres operating comfort?
  • Are embedding jobs idempotent and observable?
  • Is there a clean path to move retrieval out later if load justifies it?

What to Do Next

Problem: Teams add a separate vector database before proving Postgres cannot handle the retrieval workload, creating a synchronization problem between two sources of truth.

Solution: Keep embeddings inside Postgres when the corpus is already relational — start there for consistency, tenant filtering, and operational simplicity, and leave only when vector traffic, hybrid relevance, memory pressure, or multi-stage retrieval requirements make Postgres the wrong failure boundary.

Proof: EXPLAIN ANALYZE on the production-shaped retrieval query shows the intended HNSW index in use, and a restore drill confirms deleted or unauthorized documents no longer appear in retrieval.

Action: This week, add content_hash, embedding_model, and deleted_at to the embeddings table if they are missing, and run the Decision Checklist above against the current corpus.

Operating Guardrails

Keep the first implementation deliberately narrow. Start with one corpus, one embedding model, one vector column, one retrieval endpoint, and one query set for regression testing. Add hybrid search, replicas, or a separate vector service only after the simple path has measurable pressure.

The guardrail that matters most is reversibility. Store source IDs, chunk hashes, model versions, and ingestion timestamps so the team can rebuild embeddings, compare old and new results, and prove that deleted or unauthorized records no longer appear in retrieval.

Sources to Verify