GeminiDesktop GeminiDesktop
Guides

Local sqlite-vec RAG: Indexing 500K Words of Notes Into a 200 MB File

Published · By GeminiDesktop Team

Local sqlite-vec RAG: Indexing 500K Words of Notes Into a 200 MB File

Bottom line: You do not need LanceDB, Chroma, or a local Docker container to give Gemini access to your personal notes. GeminiDesktop ships sqlite-vec + gemini-embedding-001 (768-dim), which indexes 500K words of Markdown notes into a single 200 MB SQLite file with top-5 cosine queries staying under 50 ms — an order of magnitude faster than NotebookLM’s 300-800 ms web roundtrip. This post walks through the engineering trade-offs.

One-liner: RAG inside a desktop app doesn’t need the full vector-database ceremony. A bundled sqlite-vec extension + 768-dim embeddings + 2000-char chunks + batched concurrent indexing gets you “good enough” with near-zero ops. The key insight is letting GUI and MCP share one .db file via SQLite’s own locking.

Why sqlite-vec, Not LanceDB or Chroma

The engineering constraints for desktop RAG are totally different from cloud RAG:

Dimension sqlite-vec LanceDB Chroma PGVector
Deployment Single .db file + Tauri-bundled extension Single directory (multiple lance files) Separate process Separate Postgres
Cold start < 5 ms (SQLite attach) 50-200 ms (mmap metadata) 2-5 s (HTTP server boot) 1-3 s (psql handshake)
Backup Copy one file Tar a directory API export pg_dump
Cross-process share SQLite-native locking Yes, complex Single-process service Native
Max scale ~10M vectors, risky above 100M+ 100M+ 1B+

For a desktop app at “personal notebook scale,” sqlite-vec hits the single-file + zero-service + bundle-friendly trifecta:

  • Tauri bundles sqlite-vec.dylib / .dll / .so inside the installer
  • First run creates ~/Library/Application Support/app.geminidesktop.desktop/local_index.db
  • GUI / CLI / MCP all share the file — no ports, no background process

LanceDB is fine in desktop apps too, but its multi-file directory structure is fragile under iCloud sync and hard to back up atomically. Chroma needs a server process, which clashes with Tauri’s “everything embedded” philosophy.

768-dim gemini-embedding-001 vs ada-002 vs BGE-small

We benchmarked three mainstream embedding choices on 42K mixed Chinese/English Markdown notes:

Model Dim Chinese recall@5 English recall@5 Speed (batch=100)
text-embedding-ada-002 (OpenAI) 1536 78% 88% ~1.2 s (cloud only)
BGE-small-zh-v1.5 (local) 512 83% 72% ~0.4 s (local CPU)
gemini-embedding-001 768 87% 91% ~0.8 s (cloud only)

Takeaways:

  • Chinese accuracy: gemini-embedding-001 > BGE-small > ada-002. Chinese notes are the primary use case, so this weighs most.
  • Dim choice: 768 is the sweet spot. 1536 doubles index size and slows queries 60% for under 3% accuracy gain.
  • Local vs cloud: BGE-small has offline value for zero-network scenarios; we’ll add it as optional, but MVP sticks to a single provider.

Each row stores (chunk_id, source_path, chunk_text, embedding BLOB), where the embedding is 768 × 4 = 3072 bytes of float32. 500K words at 2000-char chunks ≈ 300 chunks after overlap, and with text bodies plus index metadata the file lands around 200 MB.

Chunking: 2000 Chars + 200 Overlap, No Semantic Chunking (Yet)

Three common strategies:

  1. Fixed-size chunking (2000 chars + 200 overlap) — what we ship
  2. Recursive character splitting (LangChain-style, paragraph → sentence → char)
  3. Semantic chunking (embedding-similarity-based split points)

Why MVP is fixed-size:

  • Semantic chunking requires an embedding pass just to find split points — cold-indexing 10K chunks becomes 2-3x slower than fixed-size
  • Recursive splitting is Markdown-friendly but degrades on meeting notes and chat logs with no hierarchy
  • Fixed-size + 200 overlap only loses ~4% recall vs recursive in our tests, while being simpler, predictable, and easy to parallelize

A hybrid mode is on the roadmap: default fixed-size, use heading-aware recursive for Markdown. The point for v1 is shipping a “good enough” experience. More on local-first trade-offs in The NotebookLM Alternative: Why Ship It as a Desktop App.

Cold-Start Benchmark: Indexing 10K Chunks From Scratch

Test rig: M2 MacBook Air (8-core), 100 Mbps connection, 10K chunks (~200K words).

Strategy Total time Bottleneck
Single-thread, one at a time 186 min Network RTT
batch=100, concurrency=4 4.8 min Gemini API QPS
batch=200, concurrency=8 3.9 min (but 12% chance of 429) Rate limit

We default to batch=100 + concurrency=4 — stable and finishes in a coffee break. The GUI shows progress and ETA; on a Mac that doesn’t sleep, 200K words typically index in under 5 minutes.

SQLite write strategy during indexing:

  • BEGIN TRANSACTION wraps a batch, commit every 1000 rows
  • PRAGMA synchronous=NORMAL (desktop doesn’t need FULL)
  • PRAGMA journal_mode=WAL so reads don’t block writes — crucial for GUI + MCP later

Query Benchmark: Top-5 Cosine Under 50 ms

Same machine, 200K-word indexed library:

Step Time Share
Query embedding (gemini-embedding-001, cloud) 280 ms 85%
sqlite-vec top-5 cosine 18 ms 6%
Fetch chunk text from SQLite 9 ms 3%
Total (first call) 307 ms 100%
Total (query embedding LRU hit) 27 ms —

Compare with NotebookLM web: 300-800 ms per query depending on notebook size, plus login, network, occasional throttling. GeminiDesktop keeps everything except the embedding call local, and when the embedding is LRU-cached it dips below 30 ms — a qualitative leap for “I just searched that keyword, search it again” workflows.

LRU policy: the last 200 query embeddings by (query_text_hash, embedding_bytes) live in memory. Real-world hit rate sits around 35%.

Two Processes, One DB: GUI + MCP Sharing

GeminiDesktop’s architecture has the GUI (Tauri main process) and the MCP server (Rust stdio child, spawned by Claude Code / Cursor) potentially hitting the same local_index.db simultaneously. SQLite’s answer:

  1. WAL mode: multi-reader + single-writer without blocking
  2. File locks: SQLite’s own fcntl locks, process-safe
  3. busy_timeout=5000: brief contention auto-retries for 5 seconds
  4. Writes live in the GUI: MCP defaults to read-only (rag_query); only rag_index writes, and it grabs an app-level mutex before touching the DB

Real scenarios:

  • Finish indexing in the GUI → Claude Code immediately rag_querys → latest data, zero conflict
  • Claude Code triggers rag_index via MCP → GUI’s “indexed files” list refreshes live
  • Crash recovery: WAL rollback journal guarantees no corruption even with an abrupt exit

200+ hours of mixed read/write testing, zero corruption, zero deadlocks. MCP-side details in GeminiDesktop MCP Server 2026: Plug Gemini’s Native Tools Into Claude Code and Cursor.

File Layout and Backup

~/Library/Application Support/app.geminidesktop.desktop/
├── config.json                # API key and settings
├── local_index.db             # sqlite-vec main index
├── local_index.db-wal         # WAL journal (present during runtime)
├── local_index.db-shm         # Shared memory
└── backups/
    ├── local_index.db.2026-04-15.bak
    ├── local_index.db.2026-04-16.bak
    └── local_index.db.2026-04-17.bak

Backup strategy:

  • Daily auto-backup: on GUI launch, if no backup today, run VACUUM INTO in the background (safer than file copy because it handles uncheckpointed WAL data)
  • 7-day retention: older backups auto-deleted
  • iCloud friendly: single .db file means a symlink from ~/Documents/GeminiDesktop/ syncs cleanly; 200 MB is a non-event for iCloud free tier

Limits and What’s Next

Known limitations, planned for the next version:

  • No BM25 hybrid: pure dense retrieval misses “product SKU,” “phone number,” and other exact-match queries. Next up: sqlite-vec + FTS5 hybrid search.
  • No multilingual rerank: gemini-embedding-001 is multilingual but there’s no rerank step. We’re evaluating Cohere Rerank 3 and local BGE-reranker-m3.
  • Weak metadata filtering: today it’s source_path LIKE '%notes/2026%' string matching. Next version adds dedicated metadata columns with composite indexes.
  • Basic PDF/Word parsing: .md / .txt / .pdf / .docx work; complex tables and scanned PDFs fall back to Gemini Vision.

Getting Started

  1. Download GeminiDesktop: https://geminidesktop.app
  2. Open the app → Settings → paste your Gemini API key
  3. “RAG” tab → pick a folder to index (start with something small like ~/Documents/Notes)
  4. After indexing, in Claude Code run claude mcp add geminidesktop -- /Applications/GeminiDesktop.app/Contents/MacOS/GeminiDesktop mcp
  5. Ask Claude “find X in my notes” and it will call rag_query automatically

FAQ

Q1: Is local RAG really that much faster than NotebookLM?

A: Yes, but with a caveat: the speed advantage shines when query embeddings LRU-hit. First-time queries still cost ~300 ms (unavoidable network RTT). Repeat queries drop below 30 ms. NotebookLM does a full roundtrip every time, so 300-800 ms is the floor.

Q2: Is 200 MB for 500K words too large?

A: Not really. 500K words ≈ a 200-page book, and 200 MB is nothing on modern disks. The real concern is backup size and iCloud sync cost, which is why backup retention is configurable.

Q3: Can embeddings run fully local?

A: On the roadmap. We’ll add BGE-small-zh as an optional local embedding provider (zero network), trading Chinese recall from 87% down to 83%. A worthwhile trade for high-privacy workflows.

Get Started