5 ms·

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

by dhd415 9y ago

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 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.

tiffanyh 9y ago
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 https://www.fossil-scm.org
- bensummers 9y ago
  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.
- mritun 9y ago
  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.
- SQLite 9y ago
  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.
  - tiffanyh 9y ago
    @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.
asdfjklasdfjkl 9y ago
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.
- eropple 9y ago
  If you're in-process, though, you can just lock when you need exclusive access to the datastore.
  - asdfjklasdfjkl 9y ago
    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.)
mwotton 9y ago
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.)

by dhd415 9y ago

tiffanyh 9y ago

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 https://www.fossil-scm.org

bensummers 9y ago
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.
mritun 9y ago
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.
SQLite 9y ago
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.
- tiffanyh 9y ago
  @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.

asdfjklasdfjkl 9y ago

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.

eropple 9y ago
If you're in-process, though, you can just lock when you need exclusive access to the datastore.
- asdfjklasdfjkl 9y ago
  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.)

mwotton 9y ago

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.)