Why I Chose SQLite Over Postgres
I have been building a little project called Librarian: a local-first assistant for my EPUB collection. The basic idea is that I can point it at a folder of books, have it extract and chunk the text, create embeddings locally, and then ask questions with citations back to the source. At some point I had to choose where all of that stuff would live: book metadata, chapters, raw chunks, embeddings,…
The author built a local-first assistant called Librarian for their EPUB collection. Librarian extracts, chunks, and creates embeddings from the books, allowing users to ask questions with citations back to the source. The choice of database for Librarian was SQLite, not Postgres, for several reasons. First, Librarian is designed for a single user's local library, so SQLite's simplicity and no operational ceremony make it a perfect fit.
Secondly, SQLite's fast performance for ordinary relational queries over one person's local library is ideal for the read-heavy workload of Librarian. Lastly, SQLite is embedded in the application, avoiding a network round trip and handling local workloads efficiently. The slow part was vector search across enough embeddings, not SQLite doing normal database things.
While Postgres has better tooling for concurrent users, real server processes, pgvector, mature migrations, replication options, and other features, it is overkill for Librarian's needs. Starting simple with SQLite allowed the author to focus on more interesting problems like ingestion, chunking, embeddings, retrieval, and citations.
Once the basic loop worked, the bottleneck was identified, and a local hybrid-search index using OpenSearch was added. SQLite still remains the source of truth, with OpenSearch enhancing specific retrieval needs. If Librarian were a cloud product with multiple users, accounts, and shared collections, Postgres would be a logical choice.
However, Librarian's goal is to learn RAG (retrieval-augmented generation) and provide a useful tool for the author's own book collection. Thus, SQLite is the appropriate database for this project, keeping it easy to start, inspect, and move around. If requirements change, the storage architecture can be adjusted, and Postgres will be ready if Librarian were to become a hosted backend with accounts and cloud sync.
SQLite is not a permanent commitment, but it is the right choice for this local-first, single-user tool.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
