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, summaries, tags, and ingestion state.
I chose SQLite.
Not because Postgres is bad. Postgres is great. I have actually been spending time building out a PostgreSQL backend course for myself because I want to get much better at using it. It has better tooling for concurrent users, a real server process, pgvector, mature migrations, replication options, and basically every feature I could want if this became a normal cloud application.
But Librarian is not a normal cloud application.
What Librarian actually is
The important detail is that Librarian is designed around one person’s local book library.
The EPUBs live in a folder on my computer. The app parses them locally, keeps the source text and metadata locally, and runs embeddings through a local model. The resulting database is mostly there to make my own collection searchable.
There is no multi-user app. There are no user accounts. There is no fleet of web servers racing to update the same rows. Ingestion is pretty bursty: I add a book, the program does a bunch of work, then I mostly read and search the results. That is a very different workload from, say, an API that has a bunch of people constantly writing to it.
SQLite is a pretty natural fit for that. It is a single local file, it is boring, and it has basically no operational ceremony. I do not need to run another database container, keep credentials around, think about a network connection, or wonder why my personal book search tool cannot start because a separate service decided to have a bad day.
It is also fast. Like, surprisingly fast. For ordinary relational queries over one person’s local library, SQLite is not some sad little temporary database that needs to be replaced the second a project becomes real. It is embedded in the application, avoids a network round trip, and handles this kind of read-heavy local workload with very little drama. The slow part I have hit is vector search across enough embeddings, not SQLite doing normal database things. I have a hunch that bottleneck was actually due to computing cosine similarity across the embeddings (using Numpy), not the database reads.
I like Postgres, but using Postgres for this situation is like using a bazooka to hunt squirrels. It’s just overkill.
Starting simple was the actual requirement
The first version of Librarian needed to prove that the whole retrieval loop was useful:
- Can I ingest messy EPUB files reliably?
- Can I preserve enough chapter and source information for citations?
- Are local embeddings good enough to find useful passages?
- Does asking questions across my own books actually feel helpful?
None of those questions get meaningfully easier because I started with a production-grade database server.
SQLite gave me a place to store the book records, raw text, chunks, vectors, summaries, and job state while I figured out the more interesting problems. Once the basic loop worked, I could see the actual bottleneck: scanning vectors in SQLite gets slow enough to be annoying. That is a retrieval-index problem, not a sign that every bit of data needs to graduate to Postgres. That is why I am adding OpenSearch as a local hybrid-search index for vector, keyword, and filtered retrieval.
That is also the part I like about the choice. SQLite is still the source of truth. I can improve the part that needs improvement without prematurely turning the entire project into a small distributed system that I now have to babysit.
If this were a cloud product, I would choose Postgres
If Librarian needed to put the book data in the cloud, I would probably choose Postgres. That is a hypothetical, though, not a roadmap item.
That would be a totally reasonable product decision. A hosted service that lets people use their library from multiple devices, sync their data, have accounts, share collections, or access it from a web app has very different needs. At that point, a managed Postgres database would give me durable remote storage, concurrent access, backups, migrations, observability, and a much more normal path to operating the thing.
SQLite would start getting awkward because its biggest strength here is that the data and the application are sitting together on one person’s machine.
But I don’t want to build that. I made Librarian to learn how RAG actually works: ingestion, chunking, embeddings, retrieval, citations, and the rest of the weird little pipeline. It also does the useful thing I wanted it to do, which is let me search and ask questions about my own book collection. That fulfills the need.
I am not interested in turning this particular project into a hosted backend with accounts and cloud sync.
Putting the actual book data in the cloud would also be a terrible idea from a copyright-risk perspective.
SQLite is not a permanent moral commitment
I think people sometimes talk about database choices as if they are picking a sports team. I am not on Team SQLite (I do like it though). I am on Team “use the thing that matches the project you are actually building.”
For a local-first, single-user tool that is still proving its core loop, SQLite is the right amount of database. It keeps the project easy to start, easy to inspect, and easy to move around. If the requirements change, I can change the storage architecture too.
If I ever build a different product that genuinely needs to host data in the cloud, Postgres will be waiting for me. Librarian does not need to become that product.