How to Build a Secure Enterprise RAG Pipeline on PostgreSQL and pgvector

Most teams reach for a hosted vector database the moment someone says "RAG," then spend the next quarter explaining to compliance why embeddings of regulated documents live on a vendor's shared tenancy. If you already run PostgreSQL under your own change control, you have a shorter path. The pgvector extension turns the database you back up, audit, and patch into a vector store that can hold tens of millions of chunks without leaving the perimeter.

This is not a sales pitch for Postgres over purpose-built engines. It is a concrete build. Schema, index parameters, row-level security tied to SSO claims, audit logging to your SIEM, and the operational notes that keep the thing running in production. If you want the broader strategy conversation first, the case for owning the vector database covers the economics. What follows is the implementation.

Why pgvector Earns a Seat at the Enterprise Table

The practical argument is boring and durable: your DBAs already know how to run Postgres. Backup, PITR, replication, failover, encryption at rest, role management, and audit tooling are solved. Adding CREATE EXTENSION vector; adds a data type and two index methods, not a new system to operate.

The capability argument has caught up. pgvector supports vectors up to 2,000 dimensions for the standard type, 4,000 for halfvec, 64,000 for bit vectors, and 1,000 non-zero elements for sparsevec. That envelope covers every mainstream embedding model worth deploying on-prem, from BGE and E5 variants to the larger Qwen and NV-Embed families.

Performance is no longer the easy objection either. In a published benchmark on 50 million Cohere embeddings at 768 dimensions, PostgreSQL with pgvector and pgvectorscale achieved 28x lower p95 latency and 16x higher query throughput than Pinecone's storage-optimized index at 99% recall, at roughly 75% lower monthly cost on self-hosted EC2. Your mileage will differ. The point is that the gap people assumed in 2023 is not what it used to be.

Schema Design That Survives Contact With Legal

Design the schema around three questions auditors will ask: who can see this row, where did it come from, and when was it last touched. A workable baseline:

• documents — one row per source file. Columns for tenant_id, sensitivity (public, internal, restricted, regulated), source_system, source_uri, sha256, ingested_at, retention_class.
• chunks — one row per retrievable passage. Foreign key to documents, plus chunk_index, token_count, the raw text, and the embedding vector(1024) column sized to your model.
• acl — a bridge table mapping document_id to the SSO groups, matter numbers, or clearances allowed to read it. Keep ACLs out of the chunk row so a reclassification is one UPDATE, not millions.

Partition chunks by tenant_id or by month if ingestion is heavy. A dedicated ingest_events table recording every embedding run (model name, model version, chunker version, operator, source batch) is what you will hand to an EU AI Act assessor when they ask how a given answer was produced. The conformity assessment walkthrough on this site explains why that lineage matters.

Chunking Is a Schema Decision, Not an Afterthought

Pick the embedding model before you pick the chunk size, because the model picks it for you. The all-MiniLM-L6-v2 model, which still pulls roughly 254 million downloads per month, caps inputs at 256 tokens. Longer chunks are silently truncated by the embedder with no error raised, which is the sort of defect that produces confident wrong answers for months before anyone notices. Store token_count on every chunk and add a CHECK constraint against the model's limit.

HNSW Index Tuning Without the Guesswork

pgvector offers IVFFlat and HNSW. For most enterprise RAG workloads under a few hundred million vectors, HNSW is the right default: higher recall at a given latency, no training step, graceful behavior as data grows. The build defaults are m=16 and ef_construction=64, with ef_search defaulting to 40 at query time.

Those defaults are a starting point, not a destination. The three knobs that matter:

• m — connections per layer. Raising to 24 or 32 helps recall on high-dimensional embeddings (1024 and up) at the cost of index size and build time.
• ef_construction — candidate list during build. 128 to 256 is typical for production corpora; the index is built once, so pay the time.
• ef_search — candidate list at query time. This is the recall/latency dial you tune per workload. Measure, do not guess.

Build indexes with CONCURRENTLY on populated tables, bump maintenance_work_mem for the session (8 GB is reasonable on a dedicated builder), and set max_parallel_maintenance_workers to match the physical cores you can spare. Expect HNSW build on tens of millions of 1024-dim vectors to run for hours, not minutes.

What Changed in pgvector 0.8

The 0.8 line fixed the filter problem that embarrassed every real RAG deployment on earlier versions. Version 0.8.0 added iterative index scans, which keep walking the HNSW graph until enough post-filter rows are found, controlled by hnsw.iterative_scan and a hnsw.max_scan_tuples ceiling. It also improved the planner's cost estimates so a plain B-tree is chosen when it would actually be faster at 100% recall. On Aurora, AWS measured up to 5.7x query improvement over 0.7.4 on specific filtered patterns.

Row-Level Security Tied to SSO Identity

The vector database is where document permissions become query-time filters. Done casually, RAG leaks: a user asks about a merger and gets back a chunk from a deal they are walled off from. OWASP added Vector and Embedding Weaknesses as LLM08 in the 2025 Top 10 for LLM Applications specifically to flag this, and the guidance is to implement permission-aware vector stores with strict logical and access partitioning between classes of users.

PostgreSQL has had the mechanism since 2016. Row-Level Security attaches a Boolean predicate to each table that runs on every SELECT, even when the application forgets a WHERE clause. The pattern for RAG:

1. Issue each application role a connection as a non-superuser. Set current_setting('app.user_groups') at the start of every session from the user's SSO group claims, inside a transaction, with set_config(..., true) so it dies with the connection.
2. Write a policy on chunks that joins through acl and returns true only when the user's groups overlap the document's allowed groups.
3. Declare ALTER TABLE chunks FORCE ROW LEVEL SECURITY;. Without FORCE, the table owner bypasses policies entirely, which is the mistake that turns an audit finding into a breach notification.

Pair this with a connection pooler that cannot be bypassed and application code that never, under any circumstance, issues a raw SQL query without first setting the identity GUCs. The healthcare build in the HIPAA private LLM walkthrough uses the same pattern for PHI partitioning.

pgaudit, SIEM, and Evidence You Can Hand to an Auditor

Enable pgaudit by adding it to shared_preload_libraries, restarting, and running CREATE EXTENSION pgaudit;. For a RAG store, pgaudit.log = 'ddl, role, write' is a reasonable floor; add read on the ACL and documents tables via object-level auditing rather than globally, because SELECT volume on chunks will swamp a SIEM if logged in full.

Set log_destination = 'jsonlog', ship via syslog or a sidecar to Splunk, Elastic, or whatever your security team already runs, and keep an immutable copy in object storage with retention tied to your framework (seven years for most financial regimes, six for HIPAA). Log every schema change to the embeddings tables, every GRANT, every role assumption, and every connection. Those lines are the evidence package when an auditor asks who could have read what, when.

Backups, HA, and the Boring Parts That Keep You Employed

Treat the vector store like any other primary database. Streaming replication to at least one hot standby, physical backups with pgBackRest or Barman, PITR verified by actual restore drills on a schedule, and a documented RPO and RTO signed by the business owner. HNSW indexes are rebuilt from heap on restore, so budget restore-time index builds into your RTO or ship base backups that include the index files.

A note on sizing: a 1024-dim vector is roughly 4 KB on disk before index overhead. Ten million chunks is around 40 GB of raw vector data plus a comparable amount for HNSW. That fits comfortably on a single well-specified node. Reach for sharding and pgvectorscale when a single instance stops making the latency SLO, not before.

Where This Fits in the Larger Stack

A PostgreSQL vector store is one component. In front of it sit the embedder, the retriever, the reranker, and the model serving the final answer. Behind it sit the ingestion pipelines, the ACL sync from your identity provider, and the observability stack. For small corpora, you may not need any of this: Anthropic's own guidance puts the no-RAG threshold near 200,000 tokens, roughly 500 pages, below which prompt caching over a stuffed context will outperform a retrieval pipeline. Build the vector store when the corpus genuinely exceeds what long context can hold.

Teams that want help wiring this into existing systems can look at our custom LLM deployment services or the appliance path for shops that would rather not operate the GPU tier themselves. Either way, the pgvector choice buys you one thing worth defending in a compliance committee: the embeddings of your regulated documents live in a database your DBAs already back up, your SIEM already watches, and your auditors already know how to inspect.