Postgres Full-Text vs. pgvector Hybrid Search: What Our Civic Intelligence App Actually Needed

We spent three months building a civic intelligence app that lets citizens search public records, meeting minutes, and policy documents across multiple municipalities. The core requirement: fast, relevant search over 2 million+ documents with mixed content types (structured tables, long-form text, scanned PDFs). We needed to find specific clauses, dates, and names, but also surface semantically related documents.

This is the story of why we started with pure PostgreSQL full-text search, then added pgvector, and finally settled on a hybrid approach. No fluff, just the trade-offs we encountered and the decisions we made.

The Data and Query Patterns

Our dataset: 2.3 million documents from 12 city governments, ranging from 50-word meeting minutes to 500-page zoning codes. Documents are stored in a PostgreSQL 16 database with a documents table:

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    source_city TEXT NOT NULL,
    doc_date DATE,
    doc_type TEXT NOT NULL, -- 'minutes', 'ordinance', 'report', etc.
    created_at TIMESTAMPTZ DEFAULT now()
);

Users query these documents in two distinct ways:

1. Keyword search: Find exact phrases, dates, ordinance numbers. "Show me all meeting minutes from 2023 that mention 'zoning variance'."
2. Concept search: "Find documents about affordable housing policies" — even if the phrase "affordable housing" never appears.

We needed both. And we needed it in under 200ms for 90th percentile.

Phase 1: PostgreSQL Full-Text Search (FTS)

PostgreSQL's built-in tsvector and tsquery are remarkably capable. We created a GIN index on the body column:

ALTER TABLE documents ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

CREATE INDEX idx_search_vector ON documents USING GIN (search_vector);

Queries looked like:

SELECT id, title,
       ts_rank(search_vector, plainto_tsquery('english', 'zoning variance')) AS rank
FROM documents
WHERE search_vector @@ plainto_tsquery('english', 'zoning variance')
ORDER BY rank DESC
LIMIT 20;

What worked

• Exact matches: If a user typed "zoning variance", FTS found documents containing those exact words. Stemming handled plurals and verb forms.
• Speed: Queries in the 20-50ms range on our 16-core, 64GB RAM server. The GIN index is fast.
• No extra infrastructure: It's just PostgreSQL. Backup, replication, and point-in-time recovery all work out of the box.

What didn't work

• Synonym blindness: A search for "affordable housing" missed documents using "low-income housing" or "subsidized housing". We tried ts_thesaurus but maintaining synonym dictionaries for policy language was impractical.
• No semantic understanding: "Rent control" and "rent stabilization" are different concepts in policy, but FTS treats them as unrelated if the words don't overlap.
• Ranking limitations: ts_rank is TF-IDF based. It doesn't understand document importance or user intent beyond word frequency.

We could have built a synonym expansion layer, but that's a rabbit hole. We needed a different approach for concept search.

Phase 2: pgvector Embedding Search

We added pgvector (version 0.7.0) and an embeddings column:

CREATE EXTENSION vector;
ALTER TABLE documents ADD COLUMN embedding vector(768);
CREATE INDEX idx_embedding ON documents USING hnsw (embedding vector_cosine_ops);

We used BGE-M3 from BAAI (via the sentence-transformers library) to generate 768-dimensional embeddings. The model runs on a separate inference server with vLLM 0.6.0, but for batch processing we used a single A100 GPU.

Search query:

SELECT id, title,
       1 - (embedding <=> (SELECT embedding FROM documents WHERE id = ?)) AS similarity
FROM documents
ORDER BY embedding <=> (SELECT embedding FROM documents WHERE id = ?)
LIMIT 20;

For ad-hoc queries, we'd compute the embedding client-side and send the vector:

SELECT id, title, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 20;

What worked

• Semantic matching: Searching for "affordable housing" returned documents about Section 8 vouchers, inclusionary zoning, and housing trust funds. The model understood the concept.
• Multilingual: BGE-M3 handles multiple languages, which mattered for our Spanish-language documents.
• Single database: Still no extra infrastructure. pgvector integrates cleanly.

What didn't work

• Keyword precision: A search for "ordinance 2023-45" returned documents about "2023 budget" and "45-day notice" because the embedding space conflated numbers with nearby concepts. Exact identifiers were lost.
• Cold start: For new documents, we needed to run the embedding model. Latency for embedding generation was ~200ms per document, which we handled via background workers. But for real-time indexing, it's a bottleneck.
• HNSW index speed: With 2.3M vectors, HNSW index queries took 100-300ms. Acceptable but not as fast as FTS. And the index build took 45 minutes.

We also tried IVFFlat. It was faster to build but query accuracy dropped significantly for concept searches. HNSW was the clear winner.

Phase 3: Hybrid Search (The Right Answer)

Neither approach alone was sufficient. We needed to combine them. The hybrid search strategy:

1. Run FTS and vector search in parallel.
2. Normalize scores from both.
3. Merge and re-rank with a weighted sum.

We implemented this in a PL/pgSQL function:

CREATE OR REPLACE FUNCTION hybrid_search(
    query_text TEXT,
    query_embedding VECTOR(768),
    weight_fts FLOAT DEFAULT 0.3,
    weight_vec FLOAT DEFAULT 0.7,
    result_limit INT DEFAULT 20
) RETURNS TABLE(id BIGINT, title TEXT, score FLOAT) AS $$
DECLARE
    fts_results JSON;
    vec_results JSON;
BEGIN
    -- FTS results with normalized rank (0-1)
    CREATE TEMP TABLE fts_temp ON COMMIT DROP AS
    SELECT id, title,
           COALESCE(ts_rank(search_vector, plainto_tsquery('english', query_text)) / 
                    NULLIF(max(ts_rank(search_vector, plainto_tsquery('english', query_text))) OVER (), 0), 0) AS fts_score
    FROM documents
    WHERE search_vector @@ plainto_tsquery('english', query_text)
    ORDER BY fts_score DESC
    LIMIT 100;

    -- Vector results with normalized similarity
    CREATE TEMP TABLE vec_temp ON COMMIT DROP AS
    SELECT id, title,
           1 - (embedding <=> query_embedding) AS vec_score
    FROM documents
    ORDER BY embedding <=> query_embedding
    LIMIT 100;

    -- Merge and re-rank
    RETURN QUERY
    SELECT COALESCE(f.id, v.id) AS id,
           COALESCE(f.title, v.title) AS title,
           (COALESCE(f.fts_score, 0) * weight_fts + COALESCE(v.vec_score, 0) * weight_vec) AS score
    FROM fts_temp f
    FULL OUTER JOIN vec_temp v ON f.id = v.id
    ORDER BY score DESC
    LIMIT result_limit;
END;
$$ LANGUAGE plpgsql;

We call it from the application:

SELECT * FROM hybrid_search('zoning variance', '[0.1,...]'::vector, 0.4, 0.6, 20);

Weight Tuning

We ran A/B tests with 50 users over two weeks. The default weights (0.3 FTS, 0.7 vector) worked well for concept searches. For keyword-heavy queries (ordinance numbers, names), we let the client pass custom weights via a query parameter. Users quickly learned that adding ?mode=exact would bias toward FTS.

Performance

We optimized by:

• Parallel queries: The function runs FTS and vector search in separate transactions (via dblink or async Rust clients). In practice, we moved this logic to the application layer (Rust with sqlx) to avoid PostgreSQL's single-threaded function execution.
• Caching embeddings: The application caches query embeddings in Redis for 5 minutes. Common queries ("affordable housing", "zoning") hit cache 80% of the time.
• Materialized view for FTS: For very frequent searches, we pre-compute top-100 FTS results in a materialized view, refreshed every 5 minutes.

Final latency: 50-150ms for hybrid queries, well under our 200ms target.

Lessons Learned

1. Don't choose one or the other. FTS and vector search solve different problems. Hybrid is the production answer for search over diverse content.
2. pgvector is production-ready. With HNSW indexes and proper tuning, it handles millions of vectors. But watch your index build times and memory.
3. Weight tuning is user-dependent. Give users control. Our "exact mode" and "explore mode" toggles reduced support tickets.
4. Embedding model matters. BGE-M3 was a good fit for multilingual documents. We tried OpenAI's text-embedding-3-small but the 1536 dimensions doubled index size and query time for marginal accuracy gain.
5. Monitor index bloat. pgvector HNSW indexes can bloat if you update rows frequently. We switched to append-only document storage and rebuilt the index weekly.

What We Shipped

The civic intelligence app now serves 10,000 queries/day with hybrid search. Users report finding relevant documents 2x faster than the previous system (Elasticsearch-based, which we migrated away from). The entire stack runs on two servers: one for PostgreSQL (32 cores, 128GB RAM, NVMe) and one for the embedding inference (A10 GPU). No external search service, no vector database, no Elasticsearch cluster.

Total monthly cost: $1,200 for bare metal. The previous Elasticsearch setup cost $4,500/month on managed cloud.

When Hybrid Search Isn't the Answer

If your documents are all short and keyword-dense (e.g., product SKUs, error codes), pure FTS might suffice. If your queries are purely semantic (e.g., "find similar documents"), pure vector search can work. But for real-world applications with mixed content and user intents, hybrid is the sweet spot.

Our next step: adding reranking with a cross-encoder model (like BGE-reranker-v2) on the top-50 results. That's a story for another post.

All tools mentioned: PostgreSQL 16, pgvector 0.7.0, BGE-M3, vLLM 0.6.0, Rust with sqlx 0.8.