pgvector in Production: Lessons from a Real Search Feature

Why not a dedicated vector database

When building semantic vector search features into an existing application, the default recommendation is often to deploy a dedicated vector database like Pinecone, Qdrant, or Milvus. However, standing up a separate vector database introduces significant architectural complexity:

1. Dual-Source-of-Truth Syncing: Maintaining data consistency between PostgreSQL primary records and a external vector database requires building Change Data Capture (CDC) pipelines or dual-write transactional outboxes.
2. Distributed Query Failures: Executing a query that filters metadata in SQL (e.g., WHERE tenant_id = 42 AND is_published = true) while ranking vectors in an external service requires complex two-phase fetches, increasing network round-trips.
3. Operational Overhead: Managing secondary cluster backups, access controls, and SaaS subscription costs for a dedicated vector store adds friction.

Because our primary application stack was already running on PostgreSQL 16, we chose pgvector — an open-source extension that adds vector data types, distance metrics, and vector index types directly to PostgreSQL.

pgvector allows us to store 1536-dimensional embeddings alongside relational model attributes and query both in a single ACID SQL transaction.

Indexing tradeoffs: IVFFlat vs HNSW

pgvector supports two primary vector index algorithms, each suited for different workload profiles:

• IVFFlat (Inverted File Flat): Divides vector space into lists via k-means clustering.
  • Pros: Fast index construction time and low memory footprint.
  • Cons: Poor recall on dynamic datasets. As new vectors are inserted, recall accuracy degrades unless the index is periodically retrained (REINDEX INDEX) using updated centroid clusters.
• HNSW (Hierarchical Navigable Small World): Constructs a multi-layer graph where nodes represent vectors linked by similarity.
  • Pros: Superior search recall (95%+ accuracy) and robust incremental insert performance without requiring periodic offline retraining.
  • Cons: Higher RAM usage and longer initial index build times.

As our table scaled beyond 500,000 document vectors (1536 dimensions), we selected HNSW. We could not afford scheduled offline reindexing windows, making HNSW's incremental insert support essential for production reliability.

-- Enabling extension and adding HNSW index for cosine distance
CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE document_chunks 
ADD COLUMN embedding vector(1536);

-- HNSW Index creation using cosine distance
CREATE INDEX idx_document_chunks_embedding 
ON document_chunks 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

Hybrid Search: Combining BM25 Full-Text with Vector Embeddings

Pure vector search (dense retrieval) excels at understanding semantic concepts and synonyms, but struggles with exact keyword matches — such as part numbers (e.g. SKU-9941), email addresses, or error codes (ERR_502_TIMEOUT).

To achieve optimal search relevance, we combined PostgreSQL's native tsvector full-text search (sparse retrieval) with pgvector cosine similarity using Reciprocal Rank Fusion (RRF):

WITH text_search AS (
    -- Sparse BM25 keyword search
    SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(text_vector, query) DESC) AS rank
    FROM document_chunks, plainto_tsquery('english', :search_keyword) query
    WHERE tenant_id = :tenant_id
    LIMIT 50
),
vector_search AS (
    -- Dense HNSW vector search
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> :query_embedding ASC) AS rank
    FROM document_chunks
    WHERE tenant_id = :tenant_id
    LIMIT 50
)
-- Reciprocal Rank Fusion (RRF) algorithm combining both result sets
SELECT 
    COALESCE(t.id, v.id) AS chunk_id,
    (COALESCE(1.0 / (60 + t.rank), 0.0) + COALESCE(1.0 / (60 + v.rank), 0.0)) AS rrf_score
FROM text_search t
FULL OUTER JOIN vector_search v ON t.id = v.id
ORDER BY rrf_score DESC
LIMIT 10;

graph LR
    Query["Search Query"] --> BM25["Sparse BM25 Search<br/>(tsvector)"]
    Query --> Vector["Dense Vector Search<br/>(HNSW Cosine)"]
    BM25 --> RRF["Reciprocal Rank Fusion<br/>Score = 1/(60+r1) + 1/(60+r2)"]
    Vector --> RRF
    RRF --> Results["Top 10 Hybrid Results<br/>(&lt; 18ms p99)"]

This RRF SQL pattern runs entirely within PostgreSQL in under 18ms, producing search relevance superior to either vector search or keyword search operating alone.

Chunking Strategy: The Real Relevance Factor

In production, index tuning mattered less than document chunking strategy. Early iterations used large 2,000-token document chunks, which diluted semantic embeddings with irrelevant context.

We achieved a 34% boost in search precision by implementing smaller, semantically aligned chunks:

1. Chunk Size: Reduced chunk size to 384 tokens with a 64-token overlap window.
2. Contextual Headers: Prepended document title and section headers to each chunk text before generating embeddings (Doc Title > Section H2 > Chunk Text).
3. Asynchronous Ingestion: Generating embeddings is handled via queued background jobs (GenerateChunkEmbeddingJob) using a worker pool, preventing HTTP request blocking during document uploads.

namespace App\Jobs;

use App\Models\DocumentChunk;
use App\Services\EmbeddingService;
use Illuminate\Contracts\Queue\ShouldQueue;

class GenerateChunkEmbeddingJob implements ShouldQueue
{
    public function __construct(public DocumentChunk $chunk) {}

    public function handle(EmbeddingService $embeddings): void
    {
        // Generate 1536-dim embedding vector via Gemini or OpenAI API
        $vector = $embeddings->embedText(
            $this->chunk->contextual_header . "\n" . $this->chunk->content
        );

        // Store directly into pgvector column
        $this->chunk->update([
            'embedding' => json_encode($vector),
        ]);
    }
}

Results and Key Lessons

Adding pgvector directly to our existing PostgreSQL infrastructure delivered enterprise vector search capabilities with minimal architectural complexity:

• Simplified Architecture: Eliminated the need for external vector databases, CDC pipelines, or dual-source sync logic.
• Single-Digit Latency: Hybrid RRF search queries execute in < 18ms p99 over 500,000 document vectors on standard cloud hardware.
• Transactional Consistency: Deleting or updating a document row immediately updates or deletes its vector embedding in the same SQL transaction.