7 ms·

I love that sqlite article. It seems like "everyone" is certain that sqlite can only be used for up to a single query per second, anything more and you need to

I love that sqlite article. It seems like "everyone" is certain that sqlite can only be used for up to a single query per second, anything more and you need to spin up a triple sharded postgres or Hadoop cluster because it 'needs to scale'. I love being able to show that study, if you properly architect your sqlite system and am willing to purchase hardware, you can go a long long way, much further than almost all companies go, with your data access code needing nothing more than the equivalent of System.Data.Sqlite

tutfbhuf 6y ago
Why choose sqlite over MySQL on a single server (e.g. small vm instance)?
- doublerabbit 6y ago
  Overhead. SQLite resources are a lot lower due to the database being a flatfile on the OS. It's only main resource is storage. MySQL is a application that not only requires configuration, tweaking, turning and tender-loving-care but consumes constant resources utilising the processor, memory and storage.
- acdha 6y ago
  More standard without all of the correctness foot-guns, less to configure and operate, and it’s usually faster. MySQL has to do a lot of work running as a separate process, handling connections, etc. whereas SQLite is just doing file I/O in your current process.
- JAlexoid 6y ago
  Low maintenance overhead - SQLite, Firebird, Sybase are all like that.
threeseed 6y ago
My favourite part is how they: (a) built their own transaction/caching/replication layer using Blockchain no less. (b) paid SQLite team to add a number of custom modifications. (c) used expensive, custom, non-ephemeral hardware. Now you could do all of this or just use an off the shelf database that you aren't having to write custom code to use and if you choose a distributed one e.g. Cassandra will be able to run on cheap, ephemeral hardware.
- Spivak 6y ago
  This really isn't a fair take on the situation. (a) They implemented a very boring transaction/caching/replication layer that is like any other DB except they borrowed the idea that "longest chain" should be used for conflict resolution. (b) They worked with upstream to get a few patches that were unique to their use-case. Once you're in deep with any DB this really isn't that uncommon. (c) They used a dedicated (lol non-ephemeral) white-box server that has a lower amortized cost than EC2. (d) Bedrock isn't bound to the hardware. You could run it on EC2 and reap the benefits just the same except you'd pay more.
  - JAlexoid 6y ago
    I don't know where they have it, but cohosting isn't exactly free. 1U of cohost with 100mbps in a cheap Eastern European DC will cost a few hundred euro per month... and my info is a few years old. It's more expensive now. It is very specific case, that we should not extrapolate to mean it's general use case. AWS/GCP/Azure are still a better places to start for most people.
    - xearl 6y ago
      14U colocation in Germany with 1 Gbps is EUR 100/m + power: https://www.hetzner.com/colocation https://www.hetzner.com/colocation
      - JAlexoid 6y ago
        That power bit is the important bit.
  - dahauns 6y ago
    >They implemented a very boring transaction/caching/replication layer that is like any other DB except they borrowed the idea that "longest chain" should be used for conflict resolution. Handwaving this layer away as "very boring" isn't exactly fair, either. What does boring even mean here? I mean, this layer solves problems that are both essential to performance scaling of RDBMS and have been proven time and again to be hard to reliably solve in a general case. And it has furthermore been built from the ground up tailored towards the specific needs/use cases of the company. By the aforementioned handwaving the presented successes are implicitly attributed to SQLite to a degree that isn't justified IMO.
quickthrower2 6y ago
I don’t use SQLite on a server for the simple reason I’m to lazy to look after it. There are cloud managed sql server or psql databases. AWS backs me up by default. Why mess around with SQLite. Not knocking SQLite - great for desktop apps or maybe local dev environments.
- WrtCdEvrydy 6y ago
  It depends on whether your data is confidential as well... I have been known to send a full SQLite dump to a small app that needed a lot of local data. You can store it in localStorage and read it making your reload be a lot smaller. If it's not in localStorage, you can just request a fresh copy of SQLite from us.
bob1029 6y ago
SQLite is incredible. If you are struggling to beat the "one query per second" meme, try the following 2 things: 1. Only use a single connection for all access. Open the database one time at startup. SQLite operates in serialized mode by default, so the only time you need to lock is when you are trying to obtain the LastInsertRowId or perform explicit transactions across multiple rows. Trying to use the one connection per query approach with SQLite is going to end very badly. 2. Execute one time against a fresh DB: PRAGMA journal_mode=WAL If at this point you are still finding that SQLite is not as fast or faster than SQL Server, MySQL, et.al., then I would be very surprised. I do not think you can persist a row to disk faster with any other traditional SQL technology. SQLite has the lowest access latency that I am aware of. Something about it living in the same process as your business application seems to help a lot. We support hundreds of simultaneous users in production with 1-10 megs of business state tracked per user in a single SQLite database. It runs fantastically.
- hans_castorp 6y ago
  > If at this point you are still finding that SQLite is not as fast or faster than SQL Server, MySQL, et.al., then I would be very surprised. What about large aggregation queries, that are parallelized by modern DBMS? Does it still scale that well if you have many concurrent read and write transactions (e.g. on the same table)?
  - bob1029 6y ago
    We aren't running any reports on our databases like this. I would argue it is a bad practice in general to mix OLTP and OLAP workloads on a single database instance, regardless of the specific technology involved. If we wanted to run an aggregate that could potentially impact live transactions, we would just copy the SQLite db to another server and perform the analysis there. We have some telemetry services which operate in this fashion. They go out to all of the SQLite databases, make a copy and then run analysis in another process (or on another machine). I am not aware of any hosted SQL technology which is capable of magically interleaving large aggregate queries with live transactions and not having one or both impacted in some way. At the end of the day, you still have to go to disk on writes, and this must be serialized against reads for basic consistency reasons. After a certain point, this is kinda like trying to beat basic information theory with ever-more-complex compression schemes. I'd rather just accept the fundamental truth of the hardware/OS and have the least amount of overhead possible when engaging with it.
thomascgalvin 6y ago
To me, this is the most important line in the article: > SQLite scales almost perfectly for parallel read performance (with a little work) They aren't using stock SQLite, they're using SQLite wrapped in Bedrock[1], and their use case is primarily read-only. SQLite is fantastic at read-only, or read-mostly, use cases. You start to run into trouble when you want to do concurrent writes, however. I tried to use SQLite as the backend of a service a couple of years ago, and it locked up at somewhere around tens of writes per second. [1]: www.bedrockdb.com
Spooky23 6y ago
This is true of anything SQL. I've been so many solutions that would be easily and reliably implemented on a single or small SQL database cluster of various types that turn into these complex systems to avoid the costs of scaling up the RDBMS.
smoe 6y ago
I'd certainly believe sqlite can be taken very far. Never done it so far. But what does "properly architect your sqlite system" mean and how does this compare to just spinning up a postgres service (nothing sharded or fancy otherwise)?
- therealdrag0 6y ago
  In this case it seems to mean building an extra server layer on-top of it. - https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... - https://bedrockdb.com/ https://bedrockdb.com/
efreak 6y ago
As a casual user of random software that I test and then immediately forget: I really wish more applications supported sqlite databases. I've set up everything from IRC bots to server animation software to log analysis to forums and WordPress, to test it out, and the first thing that makes me drop something is a dependency on MySQL, pgsql, etc. If your software can work out of a single directory otherwise but can't work with a local database in the same directory, then I'm not going to use it.

tutfbhuf 6y ago

Why choose sqlite over MySQL on a single server (e.g. small vm instance)?

doublerabbit 6y ago
Overhead. SQLite resources are a lot lower due to the database being a flatfile on the OS. It's only main resource is storage. MySQL is a application that not only requires configuration, tweaking, turning and tender-loving-care but consumes constant resources utilising the processor, memory and storage.
acdha 6y ago
More standard without all of the correctness foot-guns, less to configure and operate, and it’s usually faster. MySQL has to do a lot of work running as a separate process, handling connections, etc. whereas SQLite is just doing file I/O in your current process.
JAlexoid 6y ago
Low maintenance overhead - SQLite, Firebird, Sybase are all like that.

threeseed 6y ago

My favourite part is how they: (a) built their own transaction/caching/replication layer using Blockchain no less. (b) paid SQLite team to add a number of custom modifications. (c) used expensive, custom, non-ephemeral hardware. Now you could do all of this or just use an off the shelf database that you aren't having to write custom code to use and if you choose a distributed one e.g. Cassandra will be able to run on cheap, ephemeral hardware.

Spivak 6y ago
This really isn't a fair take on the situation. (a) They implemented a very boring transaction/caching/replication layer that is like any other DB except they borrowed the idea that "longest chain" should be used for conflict resolution. (b) They worked with upstream to get a few patches that were unique to their use-case. Once you're in deep with any DB this really isn't that uncommon. (c) They used a dedicated (lol non-ephemeral) white-box server that has a lower amortized cost than EC2. (d) Bedrock isn't bound to the hardware. You could run it on EC2 and reap the benefits just the same except you'd pay more.
- JAlexoid 6y ago
  I don't know where they have it, but cohosting isn't exactly free. 1U of cohost with 100mbps in a cheap Eastern European DC will cost a few hundred euro per month... and my info is a few years old. It's more expensive now. It is very specific case, that we should not extrapolate to mean it's general use case. AWS/GCP/Azure are still a better places to start for most people.
  - xearl 6y ago
    14U colocation in Germany with 1 Gbps is EUR 100/m + power: https://www.hetzner.com/colocation https://www.hetzner.com/colocation
    - JAlexoid 6y ago
      That power bit is the important bit.
- dahauns 6y ago
  >They implemented a very boring transaction/caching/replication layer that is like any other DB except they borrowed the idea that "longest chain" should be used for conflict resolution. Handwaving this layer away as "very boring" isn't exactly fair, either. What does boring even mean here? I mean, this layer solves problems that are both essential to performance scaling of RDBMS and have been proven time and again to be hard to reliably solve in a general case. And it has furthermore been built from the ground up tailored towards the specific needs/use cases of the company. By the aforementioned handwaving the presented successes are implicitly attributed to SQLite to a degree that isn't justified IMO.

quickthrower2 6y ago

I don’t use SQLite on a server for the simple reason I’m to lazy to look after it. There are cloud managed sql server or psql databases. AWS backs me up by default. Why mess around with SQLite. Not knocking SQLite - great for desktop apps or maybe local dev environments.

WrtCdEvrydy 6y ago
It depends on whether your data is confidential as well... I have been known to send a full SQLite dump to a small app that needed a lot of local data. You can store it in localStorage and read it making your reload be a lot smaller. If it's not in localStorage, you can just request a fresh copy of SQLite from us.

bob1029 6y ago

SQLite is incredible. If you are struggling to beat the "one query per second" meme, try the following 2 things: 1. Only use a single connection for all access. Open the database one time at startup. SQLite operates in serialized mode by default, so the only time you need to lock is when you are trying to obtain the LastInsertRowId or perform explicit transactions across multiple rows. Trying to use the one connection per query approach with SQLite is going to end very badly. 2. Execute one time against a fresh DB: PRAGMA journal_mode=WAL If at this point you are still finding that SQLite is not as fast or faster than SQL Server, MySQL, et.al., then I would be very surprised. I do not think you can persist a row to disk faster with any other traditional SQL technology. SQLite has the lowest access latency that I am aware of. Something about it living in the same process as your business application seems to help a lot. We support hundreds of simultaneous users in production with 1-10 megs of business state tracked per user in a single SQLite database. It runs fantastically.

hans_castorp 6y ago
> If at this point you are still finding that SQLite is not as fast or faster than SQL Server, MySQL, et.al., then I would be very surprised. What about large aggregation queries, that are parallelized by modern DBMS? Does it still scale that well if you have many concurrent read and write transactions (e.g. on the same table)?
- bob1029 6y ago
  We aren't running any reports on our databases like this. I would argue it is a bad practice in general to mix OLTP and OLAP workloads on a single database instance, regardless of the specific technology involved. If we wanted to run an aggregate that could potentially impact live transactions, we would just copy the SQLite db to another server and perform the analysis there. We have some telemetry services which operate in this fashion. They go out to all of the SQLite databases, make a copy and then run analysis in another process (or on another machine). I am not aware of any hosted SQL technology which is capable of magically interleaving large aggregate queries with live transactions and not having one or both impacted in some way. At the end of the day, you still have to go to disk on writes, and this must be serialized against reads for basic consistency reasons. After a certain point, this is kinda like trying to beat basic information theory with ever-more-complex compression schemes. I'd rather just accept the fundamental truth of the hardware/OS and have the least amount of overhead possible when engaging with it.

thomascgalvin 6y ago

To me, this is the most important line in the article: > SQLite scales almost perfectly for parallel read performance (with a little work) They aren't using stock SQLite, they're using SQLite wrapped in Bedrock[1], and their use case is primarily read-only. SQLite is fantastic at read-only, or read-mostly, use cases. You start to run into trouble when you want to do concurrent writes, however. I tried to use SQLite as the backend of a service a couple of years ago, and it locked up at somewhere around tens of writes per second. [1]: www.bedrockdb.com

Spooky23 6y ago

This is true of anything SQL. I've been so many solutions that would be easily and reliably implemented on a single or small SQL database cluster of various types that turn into these complex systems to avoid the costs of scaling up the RDBMS.

smoe 6y ago

I'd certainly believe sqlite can be taken very far. Never done it so far. But what does "properly architect your sqlite system" mean and how does this compare to just spinning up a postgres service (nothing sharded or fancy otherwise)?

therealdrag0 6y ago
In this case it seems to mean building an extra server layer on-top of it. - https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... - https://bedrockdb.com/ https://bedrockdb.com/

efreak 6y ago

As a casual user of random software that I test and then immediately forget: I really wish more applications supported sqlite databases. I've set up everything from IRC bots to server animation software to log analysis to forums and WordPress, to test it out, and the first thing that makes me drop something is a dependency on MySQL, pgsql, etc. If your software can work out of a single directory otherwise but can't work with a local database in the same directory, then I'm not going to use it.