4 ms·
I regularly use SQLite as the database for production apps. User registrations, content posts, in-app purchase receipts etc. Up to hundreds of thousands of con
by isodev 6y ago
I regularly use SQLite as the database for production apps.
User registrations, content posts, in-app purchase receipts etc. Up to hundreds of thousands of concurrent users it works just fine.
It saves me a tone of time and complexity from setup I don’t have to do for a dedicated database engine.
There is a good overview with general use cases here: https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
- mastre_ 6y agoI have one of the simplest toy services you can envision and am already running into a problem. Basically, I'm logging a temperature from a sensor to a SQLite db every minute or so (whenever it sends the reading). Sometimes I run a query manually thru the SQLite CLI to look into the data -- the query tabulates about 1.5 years' worth of data, by month and with averages, and can take about 20-30s as it's all running on an RPi3. As far as I can tell SQLite locks the entire table instead of just the records involved in the read, if one of my readings, new data to be inserted, happens to come in during me running the query I get "database locked" and the write fails. Yes this is a heavy query that would not make it to a production system, still I am surprised that load placed on a sqlite db by "hundreds of thousands of concurrent users" would not surface problems due to this simple detail.
- jasonjayr 6y agoHave you tried journal_mode=wal? https://sqlite.org/pragma.html#pragma_journal_mode https://sqlite.org/pragma.html#pragma_journal_mode This lets sqlite read w/o a write lock (among other things) Since it stores the log to an adjacent file you have to make sure the process can write to the whole directory containing the db.
- mastre_ 6y agoWill give that a shot, thanks!
- philipyoungg 6y agoPlease update us about this!
- Thaxll 6y ago100k CCU on sqlite I just don't believe it.
- hu3 6y agoWould you share details about the architecture of such systems? Specially on how to deal with concurrent writes on SQLite.
- isodev 6y agoI avoid concurrent writes as much as possible. With this approach, services are usually separated (e.g. microservices or a monolith with several db clients) and use several sqlite db files. For example, users.db, receipts.db, posts.db etc. For data which is frequently accessed, it really helps to consider caching (e.g. HTTP). I realise of course this won't scale infinitely so I usually make use of ORMs (e.g. GORM for Go/Gin or Diesel for Rust/Actix) just in case the SQL engine would need to change (so I don't have to rewrite queries etc). I haven't had the need to do so yet.