5 ms·

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has

SQLite is so robust, that I bet most websites could use it without really needing to move onto a client/server RDBMS.[1] I use MySQL, and I know PostgreSQL has a large marketshare now, but I wonder how much of either is really necessary when you think about traffic usage alone. I know at least in my use cases, neither seem necessary. [1]: https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html

Thaxll 6y ago
SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html https://sqlite.org/lockingv3.html
- andrewmcwatters 6y ago
  I've used SQLite a bit, but not enough to say I know where you run into performance issues. I would anticipate though, if it's similar to fread/fwrite with some marginal overhead for dealing with SQL, after considering most queries aren't very complex, I think most people are going to have a hard time hitting those limits. And that's assuming you're making queries every time an event occurs versus persisting data at particular points in time.
- busterarm 6y ago
  Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)
  - Thaxll 6y ago
    That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.
    - busterarm 6y ago
      Yes. ;)
    - OldHand2018 6y ago
      Well, let me throw out a crazy idea: Nobody said you have to use a single database/file. Obviously, you are going to want to spend a couple minutes thinking about referential integrity. But how often do you delete records in your web app?
      - quesera 6y ago
        UPDATE is also a write, and references crossing files sounds like a nightmare. If your deployment environment has serious constraints, I'm sure you could make it work. The product you deliver would be SQLite + custom DBI layer to hide SQLite's limitations. It would be a lot more work, and not be as robust or scalable, compared to a more traditional selection. But I can imagine cases where it would be appropriate.
      - HelloNurse 6y ago
        A big database id not an issue. Contention requires a large number of writers, not a large amount of cold data.
      - kuschku 6y ago
        As an IRC client with an SQLite backend: several hundred times per second. Per user. While reads are more like once every day per user.
    - mywittyname 6y ago
      One could implement a write queue and caching for the front end. That might sound like a lot of work to go through to avoid moving to a proper multi-user database. But given that writes are probably done through a handful of API endpoints, it's probably not really that much work to implement.
  - quesera 6y ago
    Any site with real volume will have a connection pool. All of those connections might need to write, and this is where SQLite gets tricky to implement at scale. I love SQLite! It's perfect for many use cases, but not all. Fortunately, Postgres is also excellent.
  - trisiak 6y ago
    Can you provide some examples of frameworks with such pattern? I actually have never seen it in any of the Python or Go web applications that I had a chance to work with.
- gwd 6y ago
  The article addresses this. Basically, you can have any number of concurrent readers, but only a single writer. Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck? That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [EDIT the number maximum number of concurrent users] is actually "1". And for many, that will be in the thousands or hundreds of thousands. FWIW the webapp I use to help organize my community's conference has almost 0 cpu utilization with 50 users. Using sqlite rather than a separate database greatly simplifies administration and deployment.
  - eingaeKaiy8ujie 6y ago
    >That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where that number is actually "1". I can imagine a static website where the content is read-only for users and is only editable by admins/developers/content managers through some CMS.
    - duskwuff 6y ago
      That'd be a near-infinite ratio. The parent is discussing a site where the ratio is near 1 (i.e. roughly as many reads as writes).
      - gwd 6y ago
        It's not even the percentage of reads vs writes; it's about the total time spent writing. Suppose that while a single user was actively using the website, the time spent writing was 70%. That's a totally mad write load; but even then, if you had two concurrent users, things would probably still be quite useable -- you'd have to go up to 4 or 5 users before things really started to slow down noticeably. Suppose, on the other hand, that a single user generated around a 1% write utilization when they were actively using the website (which still seems pretty high to me). You could probably go up to 120 concurrent users quite easily. And given that not all of your users are going to be online at exactly the same time, you could probably handle 500 or 1000 total users.
- tyingq 6y ago
  A nice fix for this might be to extract just the wire protocol from dqlite to make a network connected, but not Raft replicated, sqlite. https://github.com/canonical/dqlite https://github.com/canonical/dqlite
  - therealdrag0 6y ago
    There's also Expensify's wrapper of sqlite, bedrockdb.com. But at that point why not just use traditional RDMS?
    - tyingq 6y ago
      An embedded DB with an optional network protocol, but without raft, replication, etc, seems "normal" to me. Raima used to be popular in the 90's and was pretty much that. It's still less to manage than MySql or Postgres.
- dwohnitmok 6y ago
  As far as I'm aware that threading information is on a per-connection basis. SQLite is perfectly capable of supporting multiple, parallel reads. SQLite must serialize writes, which makes a highly parallel write-heavy workload not good for it. However, with WAL enabled writes do not block reads. Basically highly-parallel read loads with low write counts (low-enough that serializing them doesn't lead to unacceptable slow down of writes) or with loads where latency is acceptable in writes (but not reads) is a perfect use case for SQLite. And it turns out that a lot of web services are heavily asymmetrically biased towards reads.
- hsnewman 6y ago
  I disagree. I wrote a multiplayer web based game using sqlite in go: http://decwars.com/ http://decwars.com/
- bob1029 6y ago
  With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives. Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to <10k inserts per second even on the fastest hardware. The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you. The whole point of an embedded database is that the application should have exclusive control over it, so you don't have to worry about the kinds of things that SQL Server needs to worry about. SQLite is not a direct replacement for SQL Server, but with enough effort it can theoretically handle even more traffic in your traditional one-database-per-app setup, because it's not worrying about multiple users, replication, et. al.
  - Thaxll 6y ago
    "The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you." It's not the right approach because it's hard to get right, you want to offload that to the DB.
    - bob1029 6y ago
      It's actually not that hard to get right. You can put a simple lock statement around a SQLiteConnection instance to achieve the same effect with 100% reliability, but with dramatically lower latency than what hosted SQL offerings can provide. Also, the only reason we ever want to lock a SQLiteConnection is to obtain a consistent LastInsertRowId. With the latest changes to SQLite, we don't even have to do this anymore as we can return the value as part of a single invocation.
      - warmwaffles 6y ago
        You won't have to worry about the last insert row id anymore. `RETURNING` was added recently. Enjoy the speeeed.
petercooper 6y ago
I ran a niche community social bookmarking site (around 100-200k pageviews per month) on SQLite for several years and it was no problem at all. If a write was occurring, having a simultaneous request wait 100 milliseconds was no big deal. It only became a problem when I got tired of ops and wanted to put it on Heroku at which time I had to migrate to Postgres. I've always been surprised WordPress didn't go with SQLite though - it'd have made deployment so much easier for 99% of users running a small, simple blog.
- unnouinceput 6y ago
  At the time when WordPress rolled out SQLite was still in its infancy while MySQL was already a mature DB, hence the choice. These days however they could, at least, do a nice wizard to ask "do you want to run a blog only?" and deploy SQLite instead.
  - fpoling 6y ago
    I looked at using SQLite with Wordpress for sites I maintain. In theory Wordpress core is quite DB-agnostic and according to random blogs MySQL could work. But plugins use so much MySQL-specific features that it was hopeless.
  - jacurtis 6y ago
    I don't have any data on this, but I do know that a significant (if not majority) of Wordpress sites now aren't even blogs. They are small websites for things like restaurants, hair salons, construction companies, photographers, and personal portfolios. Especially since most of these sites are also running on $3/mo shared hosts, switching to SQLite would be a substantial improvement. Most these sites are running a homepage, an about page, a contact page, and maybe one or two misc pages. They might have a blog that has two blog posts on it from nine years ago. But that is really it. MySQL is really overkill considering the scenario. SQLite's big "limitation" is non-concurrent writes. But this is rarely a problem with most Wordpress sites because they are single-author and they aren't updated very often. SQLite can handle plenty of reads to support even heavily trafficked websites. Not to mention, SQLite's greatest advantage is portability. A single file contains your entire database. A non-technical user could transfer hosts or backup their data by copying their database file like it was a photo or an excel document. That's pretty incredible when you think about it.
deleted 6y ago
[deleted]
smoe 6y ago
I'm sure most website could traffic wise. But for me it is not a question whether I could, but if I should. If my goal is building a website, I don't necessarily want to experiment with different technologies if I already know Postgres will work perfectly fine without adding much operational overhead and covering use cases I don't have yet, vs the unknown unknowns of using SQLite and maintaining it over time. Again, this is not about some problem with SQLite, but just me not having experience using it this way. Same reason why I wouldn't just add any database system I haven't used before, even if on paper it would be the "better tool for the job" for a particular use case, and sure would be an interesting learning experience. In my opinion practicality and prior experience often beats what is strictly necessary or "best".
- pdimitar 6y ago
  Completely fair. It's just that some of us got obsessed with minimalism lately. With the mind-boggling power of the computers today it pays off to be able to make stuff small and still very functional. But not wanting to get out of your comfort zone is a completely valid stance to take. We do this for money after all.
  - smoe 6y ago
    For me it is more about not leaving all the comfort zones all at once. Say if I start doing a project with 5 different technologies in the stack, I pick at least 4 that I’m solid with and maybe 1 wildcard. E.g. I got interested SvelteJS exactly because I got jaded of how ridiculously complex many front end applications have gotten, albeit they are doing just barely more than fetching a JSON from a server and turning it into html. Then I got a really good use case for using it in production because the low end mobile phones and bad internet connections of our users were struggling with the very heavy SPA the company started off with. For another project in the future, it might well be SQLite that is the more experimental part, but in the end it comes down to managing risks and benefits. While one part of me would love to experiment with everything all the time, the other part likes to finish the work day on time to be able to have plenty time dedicated to non tech related things and that sleeps well at night being fairly sure that stuff is running smoothly

Thaxll 6y ago

SQLite is very limited because of its threading model, imo it's not usable outside of the single app model where you have a single user. https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html https://sqlite.org/lockingv3.html https://sqlite.org/lockingv3.html

andrewmcwatters 6y ago
I've used SQLite a bit, but not enough to say I know where you run into performance issues. I would anticipate though, if it's similar to fread/fwrite with some marginal overhead for dealing with SQL, after considering most queries aren't very complex, I think most people are going to have a hard time hitting those limits. And that's assuming you're making queries every time an event occurs versus persisting data at particular points in time.
busterarm 6y ago
Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)
- Thaxll 6y ago
  That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.
  - busterarm 6y ago
    Yes. ;)
  - OldHand2018 6y ago
    Well, let me throw out a crazy idea: Nobody said you have to use a single database/file. Obviously, you are going to want to spend a couple minutes thinking about referential integrity. But how often do you delete records in your web app?
    - quesera 6y ago
      UPDATE is also a write, and references crossing files sounds like a nightmare. If your deployment environment has serious constraints, I'm sure you could make it work. The product you deliver would be SQLite + custom DBI layer to hide SQLite's limitations. It would be a lot more work, and not be as robust or scalable, compared to a more traditional selection. But I can imagine cases where it would be appropriate.
    - HelloNurse 6y ago
      A big database id not an issue. Contention requires a large number of writers, not a large amount of cold data.
    - kuschku 6y ago
      As an IRC client with an SQLite backend: several hundred times per second. Per user. While reads are more like once every day per user.
  - mywittyname 6y ago
    One could implement a write queue and caching for the front end. That might sound like a lot of work to go through to avoid moving to a proper multi-user database. But given that writes are probably done through a handful of API endpoints, it's probably not really that much work to implement.
- quesera 6y ago
  Any site with real volume will have a connection pool. All of those connections might need to write, and this is where SQLite gets tricky to implement at scale. I love SQLite! It's perfect for many use cases, but not all. Fortunately, Postgres is also excellent.
- trisiak 6y ago
  Can you provide some examples of frameworks with such pattern? I actually have never seen it in any of the Python or Go web applications that I had a chance to work with.
gwd 6y ago
The article addresses this. Basically, you can have any number of concurrent readers, but only a single writer. Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck? That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [EDIT the number maximum number of concurrent users] is actually "1". And for many, that will be in the thousands or hundreds of thousands. FWIW the webapp I use to help organize my community's conference has almost 0 cpu utilization with 50 users. Using sqlite rather than a separate database greatly simplifies administration and deployment.
- eingaeKaiy8ujie 6y ago
  >That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where that number is actually "1". I can imagine a static website where the content is read-only for users and is only editable by admins/developers/content managers through some CMS.
  - duskwuff 6y ago
    That'd be a near-infinite ratio. The parent is discussing a site where the ratio is near 1 (i.e. roughly as many reads as writes).
    - gwd 6y ago
      It's not even the percentage of reads vs writes; it's about the total time spent writing. Suppose that while a single user was actively using the website, the time spent writing was 70%. That's a totally mad write load; but even then, if you had two concurrent users, things would probably still be quite useable -- you'd have to go up to 4 or 5 users before things really started to slow down noticeably. Suppose, on the other hand, that a single user generated around a 1% write utilization when they were actively using the website (which still seems pretty high to me). You could probably go up to 120 concurrent users quite easily. And given that not all of your users are going to be online at exactly the same time, you could probably handle 500 or 1000 total users.
tyingq 6y ago
A nice fix for this might be to extract just the wire protocol from dqlite to make a network connected, but not Raft replicated, sqlite. https://github.com/canonical/dqlite https://github.com/canonical/dqlite
- therealdrag0 6y ago
  There's also Expensify's wrapper of sqlite, bedrockdb.com. But at that point why not just use traditional RDMS?
  - tyingq 6y ago
    An embedded DB with an optional network protocol, but without raft, replication, etc, seems "normal" to me. Raima used to be popular in the 90's and was pretty much that. It's still less to manage than MySql or Postgres.
dwohnitmok 6y ago
As far as I'm aware that threading information is on a per-connection basis. SQLite is perfectly capable of supporting multiple, parallel reads. SQLite must serialize writes, which makes a highly parallel write-heavy workload not good for it. However, with WAL enabled writes do not block reads. Basically highly-parallel read loads with low write counts (low-enough that serializing them doesn't lead to unacceptable slow down of writes) or with loads where latency is acceptable in writes (but not reads) is a perfect use case for SQLite. And it turns out that a lot of web services are heavily asymmetrically biased towards reads.
hsnewman 6y ago
I disagree. I wrote a multiplayer web based game using sqlite in go: http://decwars.com/ http://decwars.com/
bob1029 6y ago
With a single thread & SQLite connection instance, I have been able to insert hundreds of thousands of rows per second when using NVMe drives. Note that WAL and synchronous flags must be set appropriately. Out of the box and using the standard "one connection per query" meme will handicap you to <10k inserts per second even on the fastest hardware. The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you. The whole point of an embedded database is that the application should have exclusive control over it, so you don't have to worry about the kinds of things that SQL Server needs to worry about. SQLite is not a direct replacement for SQL Server, but with enough effort it can theoretically handle even more traffic in your traditional one-database-per-app setup, because it's not worrying about multiple users, replication, et. al.
- Thaxll 6y ago
  "The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you." It's not the right approach because it's hard to get right, you want to offload that to the DB.
  - bob1029 6y ago
    It's actually not that hard to get right. You can put a simple lock statement around a SQLiteConnection instance to achieve the same effect with 100% reliability, but with dramatically lower latency than what hosted SQL offerings can provide. Also, the only reason we ever want to lock a SQLiteConnection is to obtain a consistent LastInsertRowId. With the latest changes to SQLite, we don't even have to do this anymore as we can return the value as part of a single invocation.
    - warmwaffles 6y ago
      You won't have to worry about the last insert row id anymore. `RETURNING` was added recently. Enjoy the speeeed.

petercooper 6y ago

I ran a niche community social bookmarking site (around 100-200k pageviews per month) on SQLite for several years and it was no problem at all. If a write was occurring, having a simultaneous request wait 100 milliseconds was no big deal. It only became a problem when I got tired of ops and wanted to put it on Heroku at which time I had to migrate to Postgres. I've always been surprised WordPress didn't go with SQLite though - it'd have made deployment so much easier for 99% of users running a small, simple blog.

unnouinceput 6y ago
At the time when WordPress rolled out SQLite was still in its infancy while MySQL was already a mature DB, hence the choice. These days however they could, at least, do a nice wizard to ask "do you want to run a blog only?" and deploy SQLite instead.
- fpoling 6y ago
  I looked at using SQLite with Wordpress for sites I maintain. In theory Wordpress core is quite DB-agnostic and according to random blogs MySQL could work. But plugins use so much MySQL-specific features that it was hopeless.
- jacurtis 6y ago
  I don't have any data on this, but I do know that a significant (if not majority) of Wordpress sites now aren't even blogs. They are small websites for things like restaurants, hair salons, construction companies, photographers, and personal portfolios. Especially since most of these sites are also running on $3/mo shared hosts, switching to SQLite would be a substantial improvement. Most these sites are running a homepage, an about page, a contact page, and maybe one or two misc pages. They might have a blog that has two blog posts on it from nine years ago. But that is really it. MySQL is really overkill considering the scenario. SQLite's big "limitation" is non-concurrent writes. But this is rarely a problem with most Wordpress sites because they are single-author and they aren't updated very often. SQLite can handle plenty of reads to support even heavily trafficked websites. Not to mention, SQLite's greatest advantage is portability. A single file contains your entire database. A non-technical user could transfer hosts or backup their data by copying their database file like it was a photo or an excel document. That's pretty incredible when you think about it.

deleted 6y ago

[deleted]

smoe 6y ago

I'm sure most website could traffic wise. But for me it is not a question whether I could, but if I should. If my goal is building a website, I don't necessarily want to experiment with different technologies if I already know Postgres will work perfectly fine without adding much operational overhead and covering use cases I don't have yet, vs the unknown unknowns of using SQLite and maintaining it over time. Again, this is not about some problem with SQLite, but just me not having experience using it this way. Same reason why I wouldn't just add any database system I haven't used before, even if on paper it would be the "better tool for the job" for a particular use case, and sure would be an interesting learning experience. In my opinion practicality and prior experience often beats what is strictly necessary or "best".

pdimitar 6y ago
Completely fair. It's just that some of us got obsessed with minimalism lately. With the mind-boggling power of the computers today it pays off to be able to make stuff small and still very functional. But not wanting to get out of your comfort zone is a completely valid stance to take. We do this for money after all.
- smoe 6y ago
  For me it is more about not leaving all the comfort zones all at once. Say if I start doing a project with 5 different technologies in the stack, I pick at least 4 that I’m solid with and maybe 1 wildcard. E.g. I got interested SvelteJS exactly because I got jaded of how ridiculously complex many front end applications have gotten, albeit they are doing just barely more than fetching a JSON from a server and turning it into html. Then I got a really good use case for using it in production because the low end mobile phones and bad internet connections of our users were struggling with the very heavy SPA the company started off with. For another project in the future, it might well be SQLite that is the more experimental part, but in the end it comes down to managing risks and benefits. While one part of me would love to experiment with everything all the time, the other part likes to finish the work day on time to be able to have plenty time dedicated to non tech related things and that sleeps well at night being fairly sure that stuff is running smoothly