Comment by dhd415 | Hacker News Reader

Comment by dhd415 | Hacker News Reader

Parent Full thread

I have but I'd say that SQLite is not intended for use in that scenario. It's an in-process library for persisting data to disk in a generally relational format with a SQL interface. It starts to degrade under highly concurrent read and write workloads that you can experience in client-server applications. At that point, a typical RDBMS with more robust concurrency support starts to be a better choice. I experienced that when using SQLite and we eventually moved the application to a full RDBMS which was more complex but also performed and scaled much better.

Note that this is very much in line with their recommendations from the docs (http://sqlite.org/whentouse.html):

>>SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases. SQLite competes with fopen().<<

It's worth noting that when considered as competition to fopen(), SQLite is quite good.

I used sqlite as a backend for scraping 160 million websites a day. It works extraordinarily well if you hold it right - in my case, that meant devoting a database file to each thread. (Arguably in this context, it's competing with flat files, but it does a really good job there.)

Fossil-SCM.

I've always wondered why the creator of SQLite (who's also the creator of Fossil SCM) will state that SQLite is not meant for client/server scenarios ... yet his Fossil SCM (which is client/server) uses SQLite.

https://www.fossil-scm.org

fossil on the server gets low numbers of writes, and not many reads. And being a distributed system, most of the SQLite action is on the client.

SQLite does not have a client-server interface. That’s ok because the creator says it is supposed to be an embedded library that gives a better fopen interface. That is, you don’t need to invent file formats (think JSON, YAML, etc). SQLite is not just a database, it’s a persistence layer that happens to have SQL interface.

It places no restrictions whatsoever on the applications that uses it. That will be pointless. Fossil-SCM uses SQLite and is itself client server.

Fossil is the server in this case, not SQLite.

It is fine to use SQLite as the local storage in the implementation of some custom server like Fossil, as there is a separate server (Fossil) that sits in between the client and the data. In fact, SQLite excels at this and usually works better than traditional client/server databases. The scenario you want to avoid is where the client attempts to access SQLite data directly across the network, with no intermediary server. The proverb is: "Always send your query to the data, not the data to the query."

It will work to access an SQLite database across a network filesystem. Just remember that the amount of information that flows between the SQL engine and the storage medium is much greater than the amount of information that flows between the SQL engine and the application. So if the storage is separated by from the application by a relatively slow network, you want to position that network on the low-bandwidth link to obtain the best performance. That means that the SQL engine needs to be on the same side of the network as the storage - hence a client/server database. If you use SQLite to access a network file, the SQL engine will be on the application side of the network and a lot more content will need to traverse the relatively slow network link.

And so, while SQLite will work in a client/server situation, you might be disappointed by the resulting network load and/or performance of the system.

In the case of Fossil, Fossil itself is the "client" for SQLite and it is on the same machine as the data, which is exactly what you want with SQLite. The client of the Fossil server might well be across a network, but that does not matter to SQLite.

@SQLite

So how is what you described above architecturally different than a web application architecture?

Which is a use case I thought you did not recommend.

I'm running into a weird case where even within an application, concurrency has become relevant because of parallel processing that the application wants to do over the data stored in the database.

But, our desire for both simplicity and obscenely low latency means that SQLite is still the better choice for us at the moment. In the future, though, I can see robust concurrency support being important even in single application use-cases.

If you're in-process, though, you can just lock when you need exclusive access to the datastore.

Ideally, I'd have several separate threads each with fine grained (table-level, maybe even row-level with deferred index updating) control on reading and writing. I admit that I haven't gone sufficiently down this path to know exactly what I'd want though, as it isn't the top priority right now. (We are "fast enough" without concurrency, ... for now.)

Reply on news.ycombinator.com