Playbook: PostgreSQL 16 RLS + pgvector HNSW Hybrid BM25 Search
Architecting single-query multi-tenant hybrid search without external vector database dependencies or synchronization drift.
1. Why Co-Locating Vectors in PostgreSQL Prevents State Drift
External vector databases introduce synchronization lag, split-brain consistency bugs, and duplicate access control implementations. By keeping vector embeddings in PostgreSQL 16 alongside relational tenant records, Row-Level Security automatically guarantees that an organization can never retrieve chunks outside its security perimeter.
2. Production HNSW & GIN Index Provisioning
Provision an HNSW index with m = 16 and ef_construction = 64 for sub-45ms cosine distance searches (<=>), paired with a STORED tsvector column and GIN index for lexical search.
CREATE INDEX idx_knowledge_chunks_hnsw
ON knowledge_chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
CREATE INDEX idx_knowledge_chunks_tsv
ON knowledge_chunks USING gin (search_tsv);3. Reciprocal Rank Fusion (RRF) SQL Query
Execute both vector and text searches in CTEs with a 60-rank constant, combining dense semantic search with exact keyword matching in a single network round-trip.
Need to implement this architecture in your codebase?
We ship this exact system in a deterministic 2-week rapid engineering sprint.