How to Build a Million-Entry Knowledge Base for Your Local AI (SQLite + Python)

I spent weeks figuring out how to build a local knowledge base that can hold over 1 million entries and still return results in under 50ms. Here's what I learned.

The Challenge

Most tutorials show you how to build a knowledge base with 100-1000 entries. But real-world AI needs millions. And naive approaches fail:

fetchall() everything in memory → crashes on Android (limited RAM)
INSERT OR IGNORE with primary keys → silently skips duplicates, you lose 99% of your data
Wrong tokenization for Chinese text → \\w+ matches Chinese characters too, splitting them incorrectly

The Solution

1. Schema Design — No Primary Key for Bulk Insert

CREATE TABLE knowledge_base (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    content TEXT,
    category TEXT,
    keywords TEXT
);
CREATE INDEX idx_kb_content ON knowledge_base(content);

2. Batch Insert (Not INSERT OR IGNORE!)

# WRONG - will skip duplicates silently
cursor.execute("INSERT OR IGNORE INTO kb VALUES (?,?,?)", data)

# RIGHT - allow autoincrement, deduplicate in Python
cursor.executemany("INSERT INTO kb (title, content, category) VALUES (?,?,?)", batch)

3. Chinese-Aware Tokenization

# WRONG - \\w matches Chinese characters
tokens = re.findall(r'[\\w]+|[\\u4e00-\\u9fff]+', text)

# RIGHT - separate English words and Chinese characters
import jieba
tokens = re.findall(r'[a-zA-Z0-9_]+', text) + list(jieba.cut(text))

4. SQL-Level Search (Not In-Memory)

def search(query, limit=10):
    cursor.execute('''
        SELECT * FROM knowledge_base 
        WHERE content LIKE ? OR title LIKE ?
        LIMIT ?
    ''', (f'%{{query}}%', f'%{{query}}%', limit))
    return cursor.fetchall()