News for hackers. Stuff that matters.
Re:Cross-Database Queries in SQLite by isodev on Sunday February 21, 2021 @07:54PM (#26219178)
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

Re:Cross-Database Queries in SQLite by mastre_ on Sunday February 21, 2021 @09:40PM (#26220165)
I 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.

Re:Cross-Database Queries in SQLite by jasonjayr on Sunday February 21, 2021 @10:19PM (#26220478)
Have you tried journal_mode=wal?

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.

Re:Cross-Database Queries in SQLite by mastre_ on Monday February 22, 2021 @01:31AM (#26221486)
Will give that a shot, thanks!
Re:Cross-Database Queries in SQLite by philipyoungg on Monday February 22, 2021 @07:15AM (#26223621)
Please update us about this!
Re:Cross-Database Queries in SQLite by hu3 on Sunday February 21, 2021 @10:12PM (#26220423)
Would you share details about the architecture of such systems?

Specially on how to deal with concurrent writes on SQLite.

Re:Cross-Database Queries in SQLite by isodev on Monday February 22, 2021 @02:23AM (#26221746)
I 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.

Re:Cross-Database Queries in SQLite by Thaxll on Sunday February 21, 2021 @10:02PM (#26220362)
100k CCU on sqlite I just don't believe it.