Building a Production Knowledge Base Search With pgvector
How much time does your team spend grep-ing through docs, old Slack threads, and closed tickets to answer a question a knowledge base should answer in one query? I'd guess more than anyone wants to admit.
The pipeline that fixes this isn't complicated: crawl the source content, chunk and embed it, store the vectors in Postgres with pgvector, then query it with a single SQL statement. That part takes an afternoon.
What most pgvector tutorials don't tell you is what happens after it works on your laptop with 50 rows. This post covers the same pipeline, but built and run for real against a real documentation site, plus what changes once it's a real knowledge base with thousands of pages that has to stay current: ingestion, index choice at scale, and keeping embeddings fresh.
I ran every step below against a local Postgres with pgvector in Docker. The commands, numbers, and query results in this post are copied straight from that run, not made up for the article.
The Pipeline End to End
The flow is: source pages -> crawl -> chunk -> embed -> store in Postgres (pgvector) -> query with semantic search.
Every tutorial glosses over step one. Ingestion is the part that actually breaks first once you're past a demo: pages rendered with JavaScript that a plain HTTP request never sees, pagination and crawl depth you have to bound, docs behind a login, PDFs mixed in with HTML, rate limits, and raw HTML full of nav bars and cookie banners that pollute your chunks and quietly wreck retrieval quality.
WebCrawlerAPI's job in this pipeline is turning arbitrary docs sites into clean markdown ready to chunk, so you're not hand-rolling a scraper (and a JS renderer, and a retry loop) per source.
Step 1: Crawl and Normalize the Source Content
A single requests.get() doesn't cut it for production ingestion. You need JS rendering for sites that build their content client-side, a depth/item limit so a crawl doesn't run away on you, and clean output (markdown, not raw HTML) so chunking isn't fighting nav links and footer boilerplate.
Here's the actual script I ran against WebCrawlerAPI's own docs, using the Python SDK:
"""
Step 1: crawl a docs site with WebCrawlerAPI and save clean markdown per page.
"""
import os
import re
import json
from pathlib import Path
from webcrawlerapi import WebCrawlerAPI
OUT_DIR = Path(__file__).parent / "crawled_docs"
SEED_URL = "https://webcrawlerapi.com/docs"
ITEMS_LIMIT = 8
def main():
api_key = os.environ["WEBCRAWLER_API_KEY"]
client = WebCrawlerAPI(api_key=api_key)
job = client.crawl(
url=SEED_URL,
output_formats=["markdown"],
items_limit=ITEMS_LIMIT,
main_content_only=True,
max_depth=2,
)
if job.status != "done":
raise SystemExit(f"Job did not complete successfully: {job.status}")
OUT_DIR.mkdir(exist_ok=True)
manifest = []
for item in job.job_items:
if not item.markdown_content_url:
continue
content = item.get_markdown()
fname = re.sub(r"[^a-zA-Z0-9]+", "-", re.sub(r"^https?://", "", item.original_url)).strip("-") + ".md"
(OUT_DIR / fname).write_text(content, encoding="utf-8")
manifest.append({"url": item.original_url, "file": fname, "chars": len(content)})
(OUT_DIR / "manifest.json").write_text(json.dumps(manifest, indent=2))
main_content_only=True is doing real work here: it's what strips the nav and footer noise before you ever see the markdown. Here's what it actually returned:
Starting crawl: https://webcrawlerapi.com/docs (items_limit=8) Job 7c64a16a-0ce1-4d0d-a092-f0b5cdc336fd finished with status: done saved webcrawlerapi-com-docs.md (4603 chars) <- https://webcrawlerapi.com/docs saved webcrawlerapi-com-docs-crawling-agent.md (9876 chars) <- https://webcrawlerapi.com/docs/crawling-agent saved webcrawlerapi-com.md (7641 chars) <- https://webcrawlerapi.com/ saved webcrawlerapi-com-docs-access-key.md (1981 chars) <- https://webcrawlerapi.com/docs/access-key saved webcrawlerapi-com-docs-job.md (5240 chars) <- https://webcrawlerapi.com/docs/job saved webcrawlerapi-com-docs-pricing.md (2582 chars) <- https://webcrawlerapi.com/docs/pricing saved webcrawlerapi-com-docs-api-crawl.md (3361 chars) <- https://webcrawlerapi.com/docs/api/crawl saved webcrawlerapi-com-docs-getting-started.md (4603 chars) <- https://webcrawlerapi.com/docs/getting-started Done. 8 pages saved to crawled_docs/
8 pages, clean markdown, one job. If you want the cleaned-content output without the surrounding chrome, that's the markdown vs. cleaned text option worth knowing about, since junk HTML is one of the fastest ways to wreck embedding quality later.
Step 2: Chunk and Embed
Chunk size is a tradeoff. Too big and irrelevant context bleeds into every match. Too small and you lose the sentence that made the chunk meaningful. A practical starting point is 500-800 tokens with a bit of overlap so you don't cut a sentence at a chunk boundary. My docs pages here are short, so I scaled that down to 220 words with 40 words of overlap; use bigger numbers on longer source pages.
On the embedding model: the obvious choice is a hosted API like OpenAI's text-embedding-3-small (1536 dimensions). I didn't have an API key handy for this run, so I used sentence-transformers with all-MiniLM-L6-v2 instead, an open-source model that runs locally and outputs 384-dimensional vectors. That's an honest tradeoff: smaller vectors mean less storage and faster index builds, at the cost of somewhat lower semantic precision than a bigger hosted model. For an internal tool or a first production pass, that trade is often worth it. Swap the model if your accuracy bar is higher.
"""
Step 2: chunk crawled markdown and embed each chunk with a local open embedding model.
No API key needed here - we use sentence-transformers (all-MiniLM-L6-v2, 384 dims)
as the "open alternative" to a hosted embedding API. Swap in OpenAI's
text-embedding-3-small (1536 dims) by changing embed_batch() if you want a hosted model.
"""
import json
from pathlib import Path
from sentence_transformers import SentenceTransformer
CRAWLED_DIR = Path(__file__).parent / "crawled_docs"
OUT_FILE = Path(__file__).parent / "chunks.jsonl"
CHUNK_WORDS = 220
OVERLAP_WORDS = 40
def chunk_text(text: str, chunk_words: int, overlap_words: int):
words = text.split()
chunks, start = [], 0
while start < len(words):
end = min(start + chunk_words, len(words))
chunks.append(" ".join(words[start:end]))
if end == len(words):
break
start = end - overlap_words
return chunks
def main():
manifest = json.loads((CRAWLED_DIR / "manifest.json").read_text())
model = SentenceTransformer("all-MiniLM-L6-v2")
rows = []
for entry in manifest:
text = (CRAWLED_DIR / entry["file"]).read_text(encoding="utf-8")
for i, piece in enumerate(chunk_text(text, CHUNK_WORDS, OVERLAP_WORDS)):
rows.append({"url": entry["url"], "chunk_index": i, "text": piece})
texts = [r["text"] for r in rows]
embeddings = model.encode(texts, show_progress_bar=False, normalize_embeddings=True)
for row, emb in zip(rows, embeddings):
row["embedding"] = emb.tolist()
with OUT_FILE.open("w") as f:
for row in rows:
f.write(json.dumps(row) + "\n")
The real run split those 8 pages into 30 chunks:
https://webcrawlerapi.com/docs: 554 words -> 3 chunks https://webcrawlerapi.com/docs/crawling-agent: 1152 words -> 7 chunks https://webcrawlerapi.com/: 955 words -> 6 chunks https://webcrawlerapi.com/docs/access-key: 291 words -> 2 chunks https://webcrawlerapi.com/docs/job: 662 words -> 4 chunks https://webcrawlerapi.com/docs/pricing: 359 words -> 2 chunks https://webcrawlerapi.com/docs/api/crawl: 437 words -> 3 chunks https://webcrawlerapi.com/docs/getting-started: 554 words -> 3 chunks Embedding 30 chunks... Wrote 30 embedded chunks to chunks.jsonl Embedding dimension: 384
Step 3: Store and Index in Postgres
Two tables: documents (one row per source page, so you can trace a chunk back to its URL and detect when the source changed) and chunks (one row per chunk, holding the actual vector column).
-- One row per source page, one row per chunk with its vector.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE IF NOT EXISTS documents (
id SERIAL PRIMARY KEY,
url TEXT UNIQUE NOT NULL,
content_hash TEXT NOT NULL,
crawled_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS chunks (
id SERIAL PRIMARY KEY,
document_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
chunk_index INTEGER NOT NULL,
content TEXT NOT NULL,
embedding vector(384) NOT NULL
);
vector(384) matches the MiniLM output dimension. If you switch embedding models later, that number has to change too, and you'll need to re-embed everything (vectors from different models aren't comparable).
Loading chunks.jsonl into Postgres is a plain insert loop, with a content_hash on each document that I use later for freshness checks:
"""Step 3 (cont): load chunks.jsonl into Postgres."""
import hashlib, json, os
from pathlib import Path
import psycopg2
CHUNKS_FILE = Path(__file__).parent / "chunks.jsonl"
def main():
conn = psycopg2.connect(os.environ["DATABASE_URL"])
conn.autocommit = True
cur = conn.cursor()
rows = [json.loads(line) for line in CHUNKS_FILE.read_text().splitlines()]
by_url = {}
for row in rows:
by_url.setdefault(row["url"], []).append(row)
for url, url_rows in by_url.items():
full_text = "\n".join(r["text"] for r in url_rows)
content_hash = hashlib.sha256(full_text.encode()).hexdigest()
cur.execute(
"""INSERT INTO documents (url, content_hash) VALUES (%s, %s)
ON CONFLICT (url) DO UPDATE SET content_hash = EXCLUDED.content_hash
RETURNING id""",
(url, content_hash),
)
doc_id = cur.fetchone()[0]
cur.execute("DELETE FROM chunks WHERE document_id = %s", (doc_id,))
for r in url_rows:
cur.execute(
"INSERT INTO chunks (document_id, chunk_index, content, embedding) VALUES (%s, %s, %s, %s)",
(doc_id, r["chunk_index"], r["text"], r["embedding"]),
)
Real output against the local Docker instance:
Applying schema... Inserted/updated 8 documents, 30 chunks. documents table now has 8 rows chunks table now has 30 rows
At this point the pipeline is set up end to end: crawl, chunk, embed, store. Here's what that whole sequence actually looks like in a terminal, back to back:
HNSW vs IVFFlat: Which Index for Production
This is the decision every "getting started with pgvector" post skips, and it's the one that actually matters once your table stops being tiny.
pgvector gives you two approximate-nearest-neighbor index types:
- IVFFlat: faster and cheaper to build, but it needs a lists parameter tuned to your row count (lists roughly sqrt(row_count) as a starting point), and its recall quietly degrades as the table grows past whatever size it was tuned for.
- HNSW: no tuning step, slower and more memory-hungry to build, but it holds up better as data grows and doesn't need retuning every time the table doubles.
My rule of thumb: start with HNSW unless build time or memory is genuinely the constraint. IVFFlat is the right call when you're doing a bulk one-time load of millions of rows on tight memory and can tolerate rebuilding the index as the table grows. For most teams building an internal or customer-facing knowledge base with anywhere from a few thousand to a few hundred thousand chunks, HNSW's steadier recall is worth the extra build cost.
Building both, for comparison, on the same 30-row table:
-- HNSW: no training step, higher recall at scale, more memory/time to build.
CREATE INDEX IF NOT EXISTS chunks_embedding_hnsw_idx
ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- IVFFlat: faster/cheaper to build, needs a `lists` tuning step, and its
-- recall degrades as the table grows past what `lists` was tuned for.
CREATE INDEX IF NOT EXISTS chunks_embedding_ivfflat_idx
ON chunks USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 10);
CREATE INDEX CREATE INDEX
Both built instantly here because 30 rows is nothing. That gap opens up once you're indexing hundreds of thousands of chunks, which is exactly the point of this section: pick your index based on where the table is going, not where it is today.
Querying: What "Instant" Actually Looks Like
The query itself is short. Embed the question with the same model you used for the chunks, then ask Postgres to order by distance:
"""Step 4: semantic search. Embed a question, ask Postgres for the closest chunks."""
import os, sys, time
import psycopg2
from sentence_transformers import SentenceTransformer
def main():
question = sys.argv[1] if len(sys.argv) > 1 else "how do I check the status of a crawl job"
model = SentenceTransformer("all-MiniLM-L6-v2")
t0 = time.time()
query_embedding = model.encode(question, normalize_embeddings=True).tolist()
embed_ms = (time.time() - t0) * 1000
conn = psycopg2.connect(os.environ["DATABASE_URL"])
cur = conn.cursor()
t0 = time.time()
cur.execute(
"""
SELECT d.url, c.chunk_index, c.content, 1 - (c.embedding <=> %s::vector) AS similarity
FROM chunks c
JOIN documents d ON d.id = c.document_id
ORDER BY c.embedding <=> %s::vector
LIMIT 3
""",
(query_embedding, query_embedding),
)
rows = cur.fetchall()
query_ms = (time.time() - t0) * 1000
print(f"(embed: {embed_ms:.1f}ms, pgvector query: {query_ms:.1f}ms)")
<=> is cosine distance, so 1 - distance gives you a similarity score you can actually reason about. Real output, asking "how do I check the status of a crawl job" against those 30 chunks:
Q: how do I check the status of a crawl job (embed: 875.1ms, pgvector query: 14.2ms) [0.635] https://webcrawlerapi.com/docs/job#chunk-0 # What is Crawling Job? Job - is a task that you can run on the Webcrawler API. It has an asynchronous nature. It means you will get a notification when it is done... [0.545] https://webcrawlerapi.com/docs#chunk-1 website. The `items_limit` parameter specifies how many items you want to extract. The `output_formats` parameter specifies that you want to see `markdown` formatted data... [0.545] https://webcrawlerapi.com/docs/getting-started#chunk-1 website. The `items_limit` parameter specifies how many items you want to extract...
Read those numbers honestly. The Postgres query itself took 14.2ms, basically instant at this table size, and that's the part people mean when they say pgvector is fast. Embedding the question took 875.1ms, almost all of which is loading the local model on a cold start, not the vector math. Keep the model warm in memory in production (or call a hosted embedding API) and that number drops a lot. The vector search step is almost never your bottleneck.
The top result correctly matched "check the status of a crawl job" against the /docs/job page at 0.635 similarity, ahead of two more general docs pages at 0.545. That's the point of semantic search: no keyword in the question ("status") needed to appear verbatim in the winning chunk, just related meaning.
Two things worth knowing before you ship this:
- Pair vector search with a normal WHERE filter. Pure vector search across your whole table is rarely what production needs. Scope by WHERE document_id = ANY(%s) or a product/source/date filter first, then order by distance within that scope. It's faster and it's usually what the user actually wants.
- The index gives you approximate nearest neighbors, not guaranteed exact top-k. HNSW and IVFFlat trade a small amount of recall for speed. For a knowledge base that's a fine trade. If you need exact top-k for some other reason, you can query without the index at a real cost to latency.
Here's that full query run in the terminal:
Keeping the Index Fresh at Scale
Every tutorial stops at "it works." Nobody covers what happens when the source docs change and your index doesn't. A stale embedding isn't a missing answer, it's a wrong answer delivered with total confidence, and that's worse.
You've got two options. Full re-crawl and re-embed everything on a schedule (simple, wasteful once you're past a small site), or change detection: hash each page's content, re-crawl on a schedule, and only re-chunk and re-embed the pages whose hash actually changed.
I simulated that here: re-crawl all 8 pages, compare hashes, and only touch what's different.
"""Step 5: freshness check. Only re-embed pages whose content actually changed."""
import hashlib, os
from pathlib import Path
import psycopg2
def content_hash(text: str) -> str:
return hashlib.sha256(text.encode()).hexdigest()
def main():
conn = psycopg2.connect(os.environ["DATABASE_URL"])
conn.autocommit = True
cur = conn.cursor()
cur.execute("SELECT id, url, content_hash FROM documents ORDER BY id")
docs = cur.fetchall()
to_reembed = []
for doc_id, url, old_hash in docs:
new_hash = content_hash(fetch_latest(url)) # re-crawl result
if new_hash != old_hash:
to_reembed.append((doc_id, url, new_hash))
print(f"{len(to_reembed)}/{len(docs)} pages changed. Only those get re-chunked "
f"and re-embedded - the rest skip straight through, no wasted embedding calls.")
Real result, with one page (the pricing page) simulated as changed:
URL STATUS ---------------------------------------------------------------------- https://webcrawlerapi.com/docs unchanged -> skip https://webcrawlerapi.com/docs/crawling-agent unchanged -> skip https://webcrawlerapi.com/ unchanged -> skip https://webcrawlerapi.com/docs/access-key unchanged -> skip https://webcrawlerapi.com/docs/job unchanged -> skip https://webcrawlerapi.com/docs/pricing CHANGED -> re-embed https://webcrawlerapi.com/docs/api/crawl unchanged -> skip https://webcrawlerapi.com/docs/getting-started unchanged -> skip 1/8 pages changed. Only those get re-chunked and re-embedded - the other 7 skip straight through, no wasted embedding calls.
1 out of 8 pages needed work. At this scale hashing saves you almost nothing. At 10,000 pages, where maybe 200 change on a given day, hashing is the difference between re-embedding 200 chunks and re-embedding your whole knowledge base for no reason.
For how often to actually run that re-crawl in the first place, I wrote a full cadence framework in how often should you re-crawl a website for an AI knowledge base: the short version is it depends entirely on how fast each source actually changes, not a fixed schedule for everything. WebCrawlerAPI's scheduled and recurring crawl jobs are the mechanism that feeds this pipeline without you hand-rolling a cron job and a hashing script yourself.
Cost and Ops Tradeoffs at Scale
pgvector is genuinely cheap to start with and it stops being free once you're at real scale. Worth knowing that going in.
Storage. A 384-dimension vector is roughly 1.5KB uncompressed (4 bytes per float x 384). At 1536 dimensions with OpenAI's larger model, that's about 6KB per chunk. A few hundred thousand chunks at 1536 dimensions is a couple gigabytes just for the vector column, before indexes. HNSW and IVFFlat indexes add their own overhead on top, often comparable in size to the raw vectors themselves. None of this is expensive in absolute dollar terms on modern disk, but it's not nothing either, and it catches people off guard when their chunks table quietly becomes the biggest table in the database.
Index build and rebuild time. This is the tax nobody budgets for. HNSW build time grows with row count and the m/ef_construction parameters. Rebuilding an HNSW index on a few million rows can take real minutes to hours depending on hardware, and it holds a lock or at minimum eats CPU and memory while it runs. Plan rebuilds like you'd plan a migration, not like a background job you forget about.
When to graduate off pgvector. For a lot of teams, pgvector never stops being the right answer. Postgres is probably already your database, and running vector search in the same place as your relational data means one less service to operate, one less data-sync problem, and transactional consistency between your app data and your embeddings for free. That's a real advantage over a dedicated vector database, and I wouldn't give it up lightly.
Where it starts to strain: very large scale (tens of millions of vectors and up), a need for multi-region replication of the vector index specifically, or query patterns that genuinely need sub-millisecond latency at high concurrency that a general-purpose Postgres instance struggles to hold. If you're there, a dedicated vector database earns its keep. Most teams asking "should I use pgvector or a dedicated vector DB" are nowhere close to that line yet, and reach for the extra infrastructure before they need it.
Wrapping Up
The shift this whole pipeline buys you is simple: manual doc-digging replaced by a query that returns an answer in milliseconds, backed by real numbers from a real run, not marketing copy.
The part that's actually hard in production isn't pgvector. The vector search itself, once you've got clean data and the right index, just works. The hard part is everything upstream and downstream of it: getting clean content in reliably (ingestion), and keeping it correct as sources change (freshness). That's exactly where WebCrawlerAPI fits, as the layer that keeps this pipeline fed without you hand-rolling scrapers and cron jobs.
If you want to try this yourself, the crawl API and getting started guide get you from an API key to clean markdown in a few minutes, ready to drop into Step 2 above. And if a local, single-machine version of this search problem is more your speed than running Postgres, I also wrote up how I made 1,000+ pages of Kubernetes docs searchable on my laptop using a different tool for the indexing side.
