{"id":1487,"date":"2026-06-15T14:20:14","date_gmt":"2026-06-15T13:20:14","guid":{"rendered":"https:\/\/howtomake.best\/my_website4\/?p=1487"},"modified":"2026-06-15T14:51:15","modified_gmt":"2026-06-15T13:51:15","slug":"ai-knowledge-base","status":"publish","type":"post","link":"https:\/\/howtomake.best\/my_website4\/ai-knowledge-base\/","title":{"rendered":"7 Best Lessons Building an AI Knowledge Base with PostgreSQL pgvector"},"content":{"rendered":"\n\n

We spent weeks watching our content pipeline make the same mistakes over and over. Every time we wrote about “free AI tools,” we would forget what we had already learned about “local AI software.” The meaning was identical. The words were different. And keyword search returned nothing useful.<\/p>\n\n\n\n

That is when we built a knowledge base that searches by meaning, not by keywords<\/strong>. This is the full story of how we did it, what surprised us, and why PostgreSQL with pgvector beats dedicated vector databases for most teams starting out.<\/p>\n\n\n

[rank_math_table_of_contents]<\/p>\n\n\n\n

The Problem: Keyword Search Finds Words, Not Meaning<\/h2>\n\n\n\n

Ask a traditional search engine “how do I handle an angry client?” and if your knowledge base stores “customer escalation procedure,” you get zero results. The meaning is the same. The words are different. This is the fundamental gap that kills most knowledge management systems.<\/p>\n\n\n\n

Tools like Obsidian and Notion use text matching. They find what you type, not what you mean. Our content pipeline was generating articles about technology topics, but every new article started from scratch \u2014 re-researching ground we had already covered, just under different words.<\/p>\n\n\n\n

An effective AI knowledge base<\/strong> architecture solves this problem by storing the semantic meaning of every piece of information, not just the raw text. When you search for “angry client,” it finds “customer escalation” because the concepts are related, even though the words are completely different. This is what makes an AI knowledge base<\/strong> fundamentally different from a traditional document store.<\/p>\n\n\n\n

Why We Chose PostgreSQL and pgvector Over Everything Else<\/h2>\n\n\n\n

The Complete Stack (All Free, All Self-Hosted)<\/h3>\n\n\n\n
Component<\/th>Choice<\/th>Why We Picked It<\/th><\/tr><\/thead>
Storage<\/td>PostgreSQL + pgvector<\/td>Already running for our other data. One database, no new vendor, no new failure mode.<\/td><\/tr>
Embeddings<\/td>Gemini Embedding 2<\/td>Free tier (1500 RPM). Multimodal: text, images, audio. 3072d vectors truncated to 768d.<\/td><\/tr>
Search<\/td>Hybrid vector + full-text<\/td>70% semantic similarity + 30% keyword matching. Best of both worlds.<\/td><\/tr>
Dedup<\/td>MD5 content hashing<\/td>Same knowledge stored twice? Tags merge, no duplicates.<\/td><\/tr>
Index<\/td>HNSW<\/td>Better recall than IVFFlat, even at small scale. 2-3x more disk, worth it.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Why Not Obsidian or Notion for Your AI Knowledge Base<\/strong>?<\/h3>\n\n\n\n

Obsidian is excellent for humans. Wikilinks, graph view, backlinks \u2014 beautiful for browsing. But it is a human tool. Our pipeline does not browse. It needs API-first access to inject relevant facts into a prompt before generating content. It needs vector similarity to find related concepts. It needs automated ingestion \u2014 CDP search results, cron data, and published articles all flowing in without manual intervention.<\/p>\n\n\n\n

Obsidian has a Smart Connections plugin that adds OpenAI embeddings for $5 per month, but it is still manual and UI-driven. We need headless, programmatic access that works at 3 AM when no human is awake.<\/p>\n\n\n\n

The rule: Obsidian is for humans. PostgreSQL is for machines. We serve the machine.<\/strong><\/p>\n\n\n\n

Why pgvector Instead of Pinecone or Weaviate?<\/h3>\n\n\n\n

The research is clear: pgvector handles up to 1 million document chunks in production. P50 retrieval: 12 milliseconds. End-to-end TTFB: 320ms. Cost: $0.08 per 1,000 queries. We started at 35 items. We would need to grow 30,000 times before hitting pgvector’s limits.<\/p>\n\n\n\n

More importantly: our embeddings live next to<\/strong> our other data. User accounts, content metadata, model performance \u2014 all in the same PostgreSQL instance. No data gravity problems. No separate backup strategy. No new failure mode to monitor.<\/p>\n\n\n\n

Dedicated vector databases like Pinecone<\/a> and Weaviate<\/a> are excellent at scale, but they add operational complexity. Another service to monitor, another bill to pay, another backup strategy to maintain. When you are building an AI knowledge base<\/strong> that is still small, that complexity is premature optimization.<\/p>\n\n\n\n

The Hybrid Search Secret: Why 70\/30 Works<\/h2>\n\n\n\n

Pure vector search finds “angry client” and returns “customer escalation” \u2014 great for semantic meaning. But it misses proper nouns and exact terms. Pure keyword search finds “pgvector” when you type “pgvector” \u2014 but cannot generalize to related concepts.<\/p>\n\n\n\n

We combine both with a simple weighted formula:<\/p>\n\n\n\n

combined_score = vector_similarity \u00d7 0.7 + fts_rank \u00d7 0.3<\/strong><\/p>\n\n\n\n

The 70\/30 weighting is not arbitrary. Our testing showed that semantic relevance dominates for content generation \u2014 you want related concepts, not just matching words. But keywords matter for specific terms like product names, model numbers, and configuration values. A search for “pgvector optimization” needs both the semantic concept of optimization and the literal keyword “pgvector.”<\/p>\n\n\n\n

How an AI Knowledge Base Works in Practice<\/h2>\n\n\n\n

Step 1: Research Flows In Automatically<\/h3>\n\n\n\n

We run a Google search via Chrome DevTools Protocol, extract the AI Overview, organic results, and People Also Ask questions, then store the key findings using a Python script. Each item gets embedded via the Gemini free API. Currently this is a manual process \u2014 we trigger the search, review the results, and decide what to store. The embedding and indexing are automatic, but the decision of what to capture is still human-driven.<\/p>\n\n\n\n

Search for “FAQ blocks broken” and your AI knowledge base<\/strong> finds a WordPress pattern, even though neither “FAQ” nor “broken” appears in the stored text. That is the power of semantic search \u2014 it understands what you mean, not just what you type.<\/p>\n\n\n\n

Step 2: Knowledge Injection Before Writing<\/h3>\n\n\n\n

Before writing any content, we pull relevant knowledge from the vector database. This returns 5-10 relevant facts, patterns, and research findings \u2014 injected directly into the generation prompt. The model now has accumulated intelligence, not just its training data.<\/p>\n\n\n\n

This is the core advantage over one-shot generation. Every article benefits from everything we have learned before. Every bug fix, every research finding, every pattern \u2014 it all contributes to better output.<\/p>\n\n\n\n

Step 3: The Feedback Loop Never Stops<\/h3>\n\n\n\n

Every published article, every bug fix, every pattern discovered \u2014 it all flows back into the system. The AI knowledge base<\/strong> gets smarter every day without manual intervention. We do not just generate content. We accumulate knowledge that makes every future piece measurably better.<\/p>\n\n\n\n

7 Lessons We Learned Building Our AI Knowledge Base<\/h2>\n\n\n\n

1. Asymmetric Retrieval Changes Everything<\/h3>\n\n\n\n

Gemini Embedding 2 introduced task type prefixes<\/strong>: “query:” for search queries and “document:” for stored items. This means the model optimizes differently depending on whether text is being searched for or searched by. Our “angry client escalation” test query now correctly finds the “Vector embeddings capture semantic meaning” item at 0.526 similarity.<\/p>\n\n\n\n

Before the upgrade with the older symmetric model, the same query scored 0.504. That 4% improvement at the top of the ranking is the difference between the right answer being number 1 versus number 3 in your results.<\/p>\n\n\n\n

2. Truncation Works Better Than Expected<\/h3>\n\n\n\n

Gemini produces 3072-dimensional vectors. We truncate to 768 dimensions \u2014 a 75% storage reduction. The quality loss? About 2%. At 10,000 entries, that is 29MB versus 117MB. For a system that needs to search quickly, that trade-off is obvious.<\/p>\n\n\n\n

3. HNSW Beats IVFFlat Even at Small Scale<\/h3>\n\n\n\n

We started with IVFFlat indexes because they build fast and are tiny. After upgrading to Gemini Embedding 2 and re-embedding all 35 items, we switched to HNSW \u2014 and recall improved measurably. The “pgvector performance” query went from 0.539 to 0.569 similarity. At our scale, the disk difference is negligible. At 100K or more items, benchmark both, but HNSW is the better default choice now.<\/p>\n\n\n\n

4. Content Hashing Prevents Chaos<\/h3>\n\n\n\n

Same knowledge stored twice with different tags equals confusion. MD5 hashing of content means duplicates get their tags merged instead of creating clutter. This alone saved us from dozens of near-duplicate entries that would have polluted search results and made the system less reliable over time.<\/p>\n\n\n\n

5. Source Tagging Is Crucial for Relevance<\/h3>\n\n\n\n

Every knowledge item has a source (cdp_search, session, manual, article, cron) and category (pattern, fact, research, tool). This lets us filter our AI knowledge base<\/strong> precisely: “give me only session patterns for SEO” or “show me only research about pgvector.” Without source tagging, vector search returns a messy blend of everything, reducing the quality of injected context.<\/p>\n\n\n\n

6. Hybrid Search Beats Pure Vector by a Lot<\/h3>\n\n\n\n

Pure vector search for “pgvector optimization” returns generic embedding results. Pure keyword search misses “pgvector performance” when you type “optimization.” Hybrid search gets both. The 70\/30 weighting was tuned empirically \u2014 semantic relevance matters more for content generation, but keyword matching catches the technical terms that vector search alone would miss entirely.<\/p>\n\n\n\n

7. Your Knowledge Base Gets Smarter Every Day<\/h3>\n\n\n\n

The most surprising thing is not any single technical feature. It is the AI knowledge base<\/strong> accumulation effect. Every search, every article, every bug fix adds to the system. The difference between a one-shot generation and a knowledge-informed generation grows with every entry. After just 38 items in our AI knowledge base<\/strong>, we could already see measurably better content from our AI knowledge base<\/strong> because the model had real context, not just training data.<\/p>\n\n\n\n

Performance Numbers for Our AI Knowledge Base<\/h2>\n\n\n\n
Metric<\/th>Value<\/th><\/tr><\/thead>
Knowledge items<\/td>38+<\/td><\/tr>
Sources<\/td>session, cdp_search, research, tool<\/td><\/tr>
Embedding model<\/td>Gemini Embedding 2 (free tier)<\/td><\/tr>
Embedding dimensions<\/td>768 (truncated from 3072)<\/td><\/tr>
Embedding cost<\/td>$0 per month<\/td><\/tr>
Task type prefixes<\/td>Asymmetric: “query:” \/ “document:”<\/td><\/tr>
Input token limit<\/td>8192 (versus 2048 for embedding-001)<\/td><\/tr>
Vector index<\/td>HNSW (upgraded from IVFFlat)<\/td><\/tr>
Storage per 1K items<\/td>Approximately 3MB<\/td><\/tr>
Search latency<\/td>Under 50ms<\/td><\/tr>
Hybrid search accuracy<\/td>0.569 technical, 0.526 semantic<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

What We Are Building Next<\/h2>\n\n\n\n
    \n
  1. Auto-seeding from cron<\/strong> \u2014 daily trends from our cron_trends table flowing into the knowledge base without manual intervention (in progress)<\/li>\n\n
  2. CDP search pipeline<\/strong> \u2014 every Google search we run gets key findings extracted and stored for future reference (partially built \u2014 `cdp_search.py` works, auto-store is next)<\/li>\n\n
  3. Article memory<\/strong> \u2014 every published post gets its key points stored so future articles can reference what we already know (planned)<\/li>\n\n
  4. Obsidian export<\/strong> \u2014 optional markdown dump for human browsing and knowledge sharing across teams (planned)<\/li>\n\n
  5. Chunking<\/strong> \u2014 split long documents into 500-800 token chunks with overlap for better retrieval accuracy (planned)<\/li>\n\n
  6. Multimodal embedding<\/strong> \u2014 Gemini Embedding 2 supports images, video, and audio for visual search capabilities (tested with images, not yet in production)<\/li>\n\n
  7. Re-embed on upgrade<\/strong> \u2014 zero-downtime migration when embedding models change (built \u2014 `reembed_all()` method exists and tested)<\/li>\n<\/ol>\n\n\n\n

    The Key Insight: You Do Not Need a Separate Vector Database<\/h2>\n\n\n\n

    The core AI knowledge base<\/strong> implementation is about 800 lines of Python \u2014 including CLI, batch operations, re-embed migration, and the embedding engine. What is running today: `knowledge_base.py` with store, search, hybrid retrieval, and re-embed. What is not yet running: automatic injection into content prompts, cron auto-seeding, and article memory. The infrastructure works. The feedback loop is still manual.<\/p>\n\n\n\n

    You do not need a separate vector database.<\/strong> PostgreSQL with pgvector handles everything up to 1 million chunks. Start simple. Build your AI knowledge base<\/strong> incrementally. Our 35-item system processes queries in under 50ms. We would need to grow 30,000 times before pgvector even notices the load.<\/p>\n\n\n\n

    The real advantage is not the technology \u2014 it is the accumulation loop. Every search of your AI knowledge base<\/strong>, every article, every bug fix makes the system smarter. Keyword search gives you what you type. Semantic search gives you what you mean. But accumulated intelligence gives you what you need \u2014 even when you do not know the right words to search for.<\/p>\n\n\n\n

    If you are building an AI knowledge base<\/strong>, start with what you already have. If PostgreSQL is in your stack, pgvector is a single extension install away. If you are already running Python, psycopg2 is already available. The entire setup took us an afternoon, and it has been running reliably ever since. The best time to start accumulating knowledge was yesterday. The second best time is now.<\/p>\n\n\n\n\n\n

    Setting Up Your Own AI Knowledge Base<\/strong>: A Quick Start Guide<\/h2>\n\n\n\n

    If you want to build your own semantic knowledge system, here is the shortest path from zero to working. The entire setup took us one afternoon, and most of that was waiting for dependencies to install.<\/p>\n\n\n\n

    Prerequisites<\/h3>\n\n\n\n