SQLite in Production: Why the "Small" Database Is Our Secret Weapon
When we designed RAG Enterprise PRO for on-premise installations, we needed a database that required no DBA, had no external dependencies, and worked on any machine. SQLite turned out to be the perfect choice for configurations, sessions, and local metadata — and the numbers prove us right.
Zero Configuration, Zero Problems: SQLite in the On-Premise World
Our RAG Enterprise PRO product is installed directly on client servers — law firms, healthcare organizations, public institutions. Every environment is different: some have a minimal Linux server without even Docker, some run Windows Server 2016 with strict IT policies, some have air-gapped machines with no Internet access. In this context, installing PostgreSQL or MySQL would mean adding an enormous layer of complexity: ports to open, users to create, backups to configure, security updates to manage. SQLite eliminates all of this. It's a single .db file living in the application directory. There's no separate process to start, no credentials to manage, nothing that can "go down" independently of the application. For RAG Enterprise PRO, we use SQLite for three main purposes: user configuration storage (selected LLM models, chunking parameters, similarity thresholds), chat session management (conversation history, accumulated context), and indexed document metadata (file name, upload date, processing status). None of these use cases require heavy concurrent access or distributed transactions — they're all sequential read/write patterns where SQLite excels.
SQLite vs PostgreSQL: When to Choose What
We're not dogmatic: in our stack we use both SQLite and PostgreSQL, each where it makes sense. The rule we follow internally is simple. SQLite for: local data of a single application instance, configurations, local cache, rapid prototyping, and any scenario where simple deployment is the priority. PostgreSQL for: data shared across multiple services, CRM81 (our cloud-based CRM where multiple users access concurrently), analytics with complex queries, and anything requiring replication or high availability. A concrete example: CRM81 uses PostgreSQL because it handles hundreds of concurrent users reading and writing to the same contacts, deals, and activities tables. If we used SQLite, WAL mode would give us a single writer at a time — unacceptable for a multi-user CRM. RAG Enterprise PRO, on the other hand, is a single-tenant application where each installation serves one organization at a time. Here SQLite isn't just sufficient, it's superior: 30-second deployment, backup with a simple file copy, zero maintenance. One thing that surprised us: in our internal benchmarks, SQLite in WAL mode is faster than PostgreSQL for simple read queries (primary key lookups) — roughly 3x faster, because there's no network overhead or client-server protocol. For complex analytical queries with JOINs on millions of rows, PostgreSQL wins obviously.
Lessons Learned: SQLite in Production Without Regrets
After over a year of SQLite in production across dozens of RAG Enterprise PRO installations, here are the lessons we've learned. First: always enable WAL mode. Without WAL, a write blocks all reads. With WAL, reads are never blocked. One line of code (PRAGMA journal_mode=WAL) that changes everything. Second lesson: handle migrations carefully. Without a built-in migration tool like Alembic for PostgreSQL, we wrote a simple schema versioning system. Every RAG Enterprise PRO update checks the schema version in the database and applies necessary migrations in order. It works perfectly and is less than 100 lines of Python. Third: backups are trivial but don't forget them. Our installer configures a cron job that copies the .db file every 6 hours. That's it. No pg_dump, no complex configurations. If something goes wrong, you restore by copying a file. Fourth: don't underestimate the limits. SQLite doesn't support ALTER TABLE DROP COLUMN in older versions, has no native BOOLEAN type (uses 0/1), and dates are strings. These are manageable limitations, but you need to know them. In our codebase we have a thin abstraction layer that normalizes these behaviors.
Related Services
See how we apply these technologies in our enterprise projects.
Interested?
Contact us to receive a personalized quote.
Securvita S.r.l. — i3k.eu