Postgres + pgvector: Building Vector Search for Documentation

You already have a vector database. It's called Postgres.

If you're running a documentation site, knowledge base, or any product that needs "search by meaning," the instinct in 2024 was to reach for Pinecone, Weaviate, or Qdrant. The instinct in 2026 should be to check what's already running in your stack. For most engineering teams, that's Postgres—and pgvector makes it genuinely viable as a vector store.

This isn't a "we replaced Pinecone with pgvector" hype post. pgvector has real trade-offs: index build times, recall ceilings, and operational quirks you need to understand. But for documentation search specifically—where you're indexing a few thousand to a few hundred thousand chunks, not a billion images—pgvector hits a sweet spot. You get semantic recall without a new infrastructure layer, a second backup story, a second auth model, or a second monitoring dashboard.

Let's walk through how to actually build this.

Why pgvector works for documentation

Documentation has structural properties that make it unusually well-suited to vector search:

• Stable corpus size. Unlike a product catalog or user-generated content, docs grow incrementally. You're not dealing with millions of inserts per day.
• High semantic density per chunk. A paragraph about "connection pooling in Node.js" is informationally dense. Embeddings capture this well.
• Hybrid query patterns work cleanly. Users often want "docs that mention pgvector AND are about indexing." You can combine BM25 or trigram search with vector similarity in a single SQL query.

The result: you can ship semantic search with a schema migration and one new Postgres extension.

Setting up pgvector

On any modern Postgres (14+), installation is a single command:

CREATE EXTENSION IF NOT EXISTS vector;

That's it. No separate service, no separate connection pool. The vectors live in your existing tables.

Schema design for doc chunks

A practical schema for documentation chunks:

CREATE TABLE doc_chunks (
  id           bigserial PRIMARY KEY,
  doc_id       text NOT NULL,           -- e.g. "auth/oauth.md"
  section      text,                    -- "Token refresh"
  content      text NOT NULL,
  token_count  int NOT NULL,
  embedding    vector(1536),            -- OpenAI text-embedding-3-small
  source_url   text,
  updated_at   timestamptz DEFAULT now()
);

CREATE INDEX doc_chunks_embedding_idx
  ON doc_chunks USING hnsw (embedding vector_cosine_ops);

Two decisions matter:

• Dimension size. 1536 for OpenAI's small model, 768 for many open-source models, 1024 or 3072 depending on provider. Don't hardcode this; parameterize it in migrations.
• HNSW vs IVFFlat. Use HNSW for almost everything. It has better recall, faster queries, and supports incremental inserts without rebuilds. IVFFlat only wins if you have a static corpus and want slightly faster index builds on millions of rows. For docs, HNSW is the default choice.

Chunking strategy

This is where most teams under-invest. The embedding quality is bounded by chunk quality. A few rules that consistently work:

• Target 200–500 tokens per chunk. Smaller chunks give more precise retrieval; larger chunks preserve context. Below ~150 tokens you start losing semantic signal.
• Chunk on structural boundaries. Split on H2/H3 headings, then subdivide if a section exceeds your token limit. Never split mid-sentence.
• Include the heading in the chunk. When you embed "## Token refresh\n\nAccess tokens expire after 3600 seconds...", the heading travels with the chunk. This materially improves retrieval for queries like "how long do tokens last."
• Store metadata you'll filter on. doc_id, section, source_url—these become your WHERE clauses for scoped searches.

A minimal chunker in Python:

def chunk_doc(markdown_text, doc_id, max_tokens=400):
    chunks = []
    current_section = ""
    buffer = []
    buffer_tokens = 0

    for line in markdown_text.split("\n"):
        if line.startswith("## "):
            if buffer:
                chunks.append(make_chunk(buffer, current_section, doc_id))
            current_section = line.strip("# ")
            buffer, buffer_tokens = [], 0
        buffer.append(line)
        buffer_tokens += estimate_tokens(line)
        if buffer_tokens >= max_tokens:
            chunks.append(make_chunk(buffer, current_section, doc_id))
            buffer, buffer_tokens = [], 0
    if buffer:
        chunks.append(make_chunk(buffer, current_section, doc_id))
    return chunks

Don't over-engineer this. The LangChain and LlamaIndex splitters exist, but for docs you usually want something that respects markdown structure.

Embedding and indexing pipeline

Once chunks exist, the pipeline is straightforward:

1. Generate embeddings in batch (most providers batch 100–500 inputs well).
2. Upsert into Postgres with ON CONFLICT on a stable chunk hash if you're handling updates.
3. Reindex on content change via webhook from your Git repo. This is exactly the integration DocPilot provides out of the box for documentation repos—every push triggers re-embedding of changed files only.

A batch upsert:

INSERT INTO doc_chunks (doc_id, section, content, token_count, embedding, source_url)
VALUES
  ($1, $2, $3, $4, $5::vector, $6),
  ($7, $8, $9, $10, $11::vector, $12),
  ...
ON CONFLICT (doc_id, section, content_hash) DO UPDATE
  SET embedding = EXCLUDED.embedding,
      updated_at = now();

Add a uniqueness constraint on (doc_id, section, content_hash) and you have idempotent indexing. Re-running the pipeline on unchanged content becomes a no-op.

Querying: pure vector and hybrid

The basic similarity query:

SELECT doc_id, section, content,
       1 - (embedding <=> $1) AS similarity
FROM doc_chunks
ORDER BY embedding <=> $1
LIMIT 10;

<=> is the cosine distance operator. Lower is closer; 1 - distance gives you similarity in [0, 1].

Hybrid search: where pgvector actually wins

Pure semantic search is great for "what is this concept" queries and terrible for "find the doc that mentions pgvector_index_scan." Hybrid search fixes this. Combine vector similarity with full-text or trigram search:

WITH semantic AS (
  SELECT id, content, 1 - (embedding <=> $1) AS sem_score
  FROM doc_chunks
  ORDER BY embedding <=> $1
  LIMIT 50
),
keyword AS (
  SELECT id, content,
         ts_rank_cd(to_tsvector('english', content), plainto_tsquery('english', $2)) AS kw_score
  FROM doc_chunks
  WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
  LIMIT 50
)
SELECT
  COALESCE(s.id, k.id) AS id,
  COALESCE(s.content, k.content) AS content,
  (COALESCE(s.sem_score, 0) * 0.7 + COALESCE(k.kw_score, 0) * 0.3) AS combined
FROM semantic s
FULL OUTER JOIN keyword k USING (id)
ORDER BY combined DESC
LIMIT 10;

The 0.7/0.3 weighting is a starting point. Tune it against a held-out set of queries. Reciprocal Rank Fusion (RRF) is a more principled combiner if you want to get fancy.

Pre-filtering with metadata

Because vectors are just a column, standard SQL filtering works:

SELECT ...
FROM doc_chunks
WHERE doc_id LIKE 'api/%'
ORDER BY embedding <=> $1
LIMIT 10;

This is harder to express in dedicated vector databases without duplicating data. In Postgres it's free.

Performance characteristics you should know

pgvector is fast enough for documentation workloads, but "fast enough" has specific bounds:

• HNSW build time scales with row count and ef_construction. For 100k 1536-dim vectors, expect several minutes on a modest Postgres instance. Run index builds with CREATE INDEX CONCURRENTLY or use pg_prewarm after.
• Query latency for k=10 with HNSW is typically 5–30ms for tens of thousands of rows, 20–100ms for hundreds of thousands. Adequate for documentation search.
• Memory. HNSW indexes live in RAM. A 100k-vector index at 1536 dims is roughly 200–400MB. If you're running on a 1GB Postgres instance, this matters. Plan memory accordingly.
• Recall. With default ef_search=40, you'll see ~95–99% recall on standard benchmarks. If recall matters more than latency, raise ef_search.

Quantization (added in pgvector 0.7.0) helps with memory:

CREATE INDEX doc_chunks_embedding_idx
  ON doc_chunks USING hnsw (embedding vector_cosine_ops)
  WITH (quantization = {compress, 32});

Half-precision storage roughly halves memory at a small recall cost. Worth it once your index exceeds ~500MB.

Operational concerns

A few patterns we've seen work:

• Separate the embedding column from the content column physically if your docs are large. Hot data (content, metadata) and cold data (embeddings) benefit from different storage.
• Watch for index bloat. HNSW doesn't vacuum cleanly. Periodic REINDEX is occasionally necessary.
• Monitor slow queries. pg_stat_statements surfaces vector queries. Add an alert when p95 query time crosses your threshold.
• Version pin. pgvector moves fast. Pin to a known version and test upgrades; the operator set has evolved across releases.

When pgvector is not the right answer

Be honest about the limits. If you need:

• Multi-billion-vector scale → dedicated vector infra wins
• Sub-millisecond latency at high QPS → in-memory vector engines
• Built-in replication and tiered storage tuned for vectors → managed services

For documentation, knowledge bases, internal tools, and most "search by meaning" features on top of existing Postgres data—pgvector is the right default. The operational simplicity is worth more than the last 10% of recall.

Shipping it

The shortest path to production:

1. Add vector extension to your database.
2. Build a chunker that respects markdown structure.
3. Wire up a webhook from your Git repo to regenerate embeddings on changes.
4. Implement hybrid search with a fallback to pure full-text when embeddings are unavailable.
5. Add a small eval set—20–50 queries with known-good results—and track recall weekly.

If you're building documentation tooling specifically, this is exactly the problem DocPilot was designed to solve. DocPilot connects to your GitHub repo, parses your markdown structure into sensible chunks, generates embeddings, and keeps everything in sync with webhooks on every push. You get semantic search over your docs without writing or maintaining the pipeline—and without paying for a separate vector database when the one you already have is good enough.

The best infrastructure is the infrastructure you don't have to think about. For documentation search at realistic scale, that's Postgres with pgvector.