3 ms·
Running SQLite in production for our mid-six figure SaaS for a year now. We use it with Go and run it in WAL mode that allows for concurrent reads and serialize
by tazu 3y ago
Running SQLite in production for our mid-six figure SaaS for a year now. We use it with Go and run it in WAL mode that allows for concurrent reads and serialized writes. Litestream for backups. No issues, and extremely good performance.
Our app is read-heavy so it works great with WAL mode only allowing one writer at a time. If you have a write-heavy workload, I would go with Postgres.
We use Go's mattn/sqlite3 library and open a connection pool for read-only queries, and a single connection for write queries. The read-only connection pool uses DEFERRED transactions, and the write-only connection uses IMMEDIATE transactions.
All tables are STRICT which helps some with type safety, and foreign key checks are always turned on.
- knur 3y agoHow do deal with horizontal scaling or failovers in general? These are two of the reasons I am still unconvinced about using SQLite in production: - if only one process can write to the db, how do you architect your system so that writes are performed by just one of the instances (the writer) - how do you gracefully upgrade your app without any downtime?
- tazu 2y ago> How do deal with horizontal scaling or failovers in general? We prefer vertical scaling over horizontal. The entire company is one server, which works for us since most of our customers are close to it. In the future if we needed geographically distributed read replicas and failover protection I would probably go with LiteFS [1]. > if only one process can write to the db, how do you architect your system so that writes are performed by just one of the instances (the writer) For multiple machines, LiteFS has ways of dealing with this. But the general idea applies to our system as well. All of our SQL is in either a read.sql or write.sql file. On startup, we open a connection pool for all the read queries and open a single connection for all the write queries. This keeps things clean and separated. > how do you gracefully upgrade your app without any downtime? We use a systemd socket and service. When the service is restarted with a new binary, systemd delays request processing until it's back online. It's really simple (thanks to our monolith setup) and entails zero downtime except a 1-2 second delay on unlucky requests. But as far as I can tell, no requests are lost. [1]: https://fly.io/docs/litefs/ https://fly.io/docs/litefs/
- martinbaun 3y agoHey Tazu, great breakdown. How much is "write-heavy" in this context? And do it depend also more on the hardware?
- tazu 3y agoBy "read-heavy" I mean 95% reads and 5% writes (GET vs POST/PUT). So I'd classify anything more than that as "write-heavy". I'd say hardware doesn't matter that much, because SQLite writes are always serialized.