4 ms·

I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time.

I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time. Isn’t that right?

teirce 4y ago
I think this is kind of their point. If you're starting out, you may not need to meet the same considerations as a "large multi user website". So you don't get stuck trying to do that - you start with what you need (appropriately) and adjust down the line. It is hard to do right.
- Gigachad 4y ago
  The problem is you have SQLite which works some of the time and Postgres which works all of the time. Postgres is a great option for websites of all sizes. There is vanishingly few reasons to ever pick SQLite for a website. When the only real advantage is not having to run a second process which only really matters for local apps.
themerone 4y ago
SQLlite can do handle concurrent reads, writes are a problem. A site like this which is mostly a read-only archive is a perfect use case for SQLite.
colechristensen 4y ago
Sure, but you can have raised millions with large numbers of customers before this is an issue. Having multiple customers using your platform at the same time to the extent that their transactions actually end up having to wait a meaningful amount of time requires a lot of usage that you often don’t reach for a long long time.
- derefr 4y ago
  Or just having any long OLAP queries mixed into your OLTP workload. A readers-writer lock can often end up with tons of writers waiting on one last long read to drain.
  - manigandham 4y ago
    SQLite WAL mode means readers and writer don't block each other: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html Since it's SQLite, it's also trivial to just copy the database file as a snapshot for separate OLAP queries if necessary.
    - derefr 4y ago
      Not all OLAP queries are for reporting. Sometimes they drive OLTP processes — really complex, separate-statements but same-transaction equivalents to "INSERT/UPDATE/DELETE ... WHERE" queries. Of course, you can still drive those using a separate snapshot for the read side, to generate an ID set for a follow-up write on the business layer. But now you're doing CQRS — less trivial. You're not getting any of the ACID benefits of doing the OLAP and OLTP queries together in a single transaction; so your data model has to be designed for that (by e.g. having all tables be temporal tables.)
- xhrpost 4y ago
  This is sounds pretty great. Is there though that much of a dev ramp up increase from using SQLite over MySQL/PGSQL though?
chasil 4y ago
SQLite only allows one writer at a time. Attempts at multiple DML logins will not block until successful, but simply fail. The allowed writer can also impact readers, depending on the WAL mode setting. For DSS uses, where a data store is published once a day, SQLite is wonderful. For OLTP, look elsewhere.
- sgbeal 4y ago
  > Attempts at multiple DML logins will not block until successful, but simply fail. sqlite has a "busy handler" mechanism which tells it how to respond to blocks and most apps use the builtin one with which they can tell it "wait for X milliseconds before failing on a lock." The Fossil SCM (https://fossil-scm.org https://fossil-scm.org), the SCM in which sqlite itself is hosted, is an sqlite application which serves thousands of users per day, many of them in write transactions. Its forum is a separate sqlite3 instance. sqlite3's own forum is another... all of those serve many users, many of whom are writing, and in 14 years of using/contributing to fossil-scm.org, almost on a daily basis, i've hit exactly two locking errors.
dundarious 4y ago
The Single Writer Principle is probably the most important principle for concurrent systems design. Often it’s best to design wholly around it. In this case, I doubt there are any writers at all.
- hinkley 4y ago
  I haven't gotten much past the planning phase but I'm thinking it would be a pretty good fit to use Erlang (Elixir in my case) and sqlite together, which should make SWP pretty straightforward, which in addition to offloading some of the concurrency work off of sqlite, also should make it fairly straightforward to swap out for postgres. If in fact I ever hit that point.
  - dugmartin 4y ago
    Phoenix now has sqlite3 as an option when running phx.new. I just played with it yesterday to also checkout litespeed syncing the database to backblaze. It was very easy to setup and test by running phx.gen.auth and monitoring the data being synced to b2. There is a blog post about running SQLite in dev and Postgres in production using Phoenix, much like rails does. It’s worth spending a few minutes checking it all out. I have a project in mind where I think it would be a good fit.
hedora 4y ago
Define large. I'd guess sqlite can do 10-100K TPS, no problem. You'll want a read cache to avoid repeatedly rendering the same HTML, etc in the frontend, where most CPU is spent. That'll accidentally reduce database load. Assuming 10% writes (which is really, really write heavy), that'll get you to 1M page views per second. After that, you'll need to rearchitect. (MySQL and Postgres are probably better choices, but I'd evaluate all three if I was setting this up for real.)
- Closi 4y ago
  Just because you can use something doesn’t mean you should use it. If you have 10k writes a second and 90k reads a second then SQLite is an awful choice.
  - kasey_junk 4y ago
    Have you benchmarked it? Given modern commodity servers and no network overhead I’d buy it not being a problem. My intuition is you are right but given the operational simplicity improvements it would be worth exploring.
    - hedora 4y ago
      This. Like I said, postgres or mysql are probably better choices. However, modern hardware (especially small sync disk writes) is really fast,and sqlite has its own strong points.
ww520 4y ago
With the amount of memory these days, the whole SQLite db would be cached in memory. Read heavy application is not a problem at all.
- qbasic_forever 4y ago
  Yeah at that point you're basically as fast as redis or any other good binary tree datastructure store.
hinkley 4y ago
Somewhere along the way, we seem to have forgotten that we have processors that can do 3 billion mathematical operations per second. I'm currently trying to shame some people at work into acting on the fact that they wrote some code that should take about 1µs per call that is instead taking over 100. If you're stupid with cycles then you get to be stupid with cores too, and then you get to be stupid with locking semantics.
- leksak 4y ago
  Unless you are working in aerospace, military, or similar shaming people seems uncouth and perhaps not the best way to inspire meaningful change?
  - hinkley 4y ago
    Shame is somewhere around #5 on a list, and never for subordinates. Also I’ve worked on commercial aviation software, and maybe three of us even knew the meaning of the word, so I’m curious what domain you saw it in.
easytiger 4y ago
If its mostly read then it's fine

I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time. Isn’t that right?

teirce 4y ago

I think this is kind of their point. If you're starting out, you may not need to meet the same considerations as a "large multi user website". So you don't get stuck trying to do that - you start with what you need (appropriately) and adjust down the line. It is hard to do right.

Gigachad 4y ago
The problem is you have SQLite which works some of the time and Postgres which works all of the time. Postgres is a great option for websites of all sizes. There is vanishingly few reasons to ever pick SQLite for a website. When the only real advantage is not having to run a second process which only really matters for local apps.

themerone 4y ago

SQLlite can do handle concurrent reads, writes are a problem. A site like this which is mostly a read-only archive is a perfect use case for SQLite.

colechristensen 4y ago

Sure, but you can have raised millions with large numbers of customers before this is an issue. Having multiple customers using your platform at the same time to the extent that their transactions actually end up having to wait a meaningful amount of time requires a lot of usage that you often don’t reach for a long long time.

derefr 4y ago
Or just having any long OLAP queries mixed into your OLTP workload. A readers-writer lock can often end up with tons of writers waiting on one last long read to drain.
- manigandham 4y ago
  SQLite WAL mode means readers and writer don't block each other: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html Since it's SQLite, it's also trivial to just copy the database file as a snapshot for separate OLAP queries if necessary.
  - derefr 4y ago
    Not all OLAP queries are for reporting. Sometimes they drive OLTP processes — really complex, separate-statements but same-transaction equivalents to "INSERT/UPDATE/DELETE ... WHERE" queries. Of course, you can still drive those using a separate snapshot for the read side, to generate an ID set for a follow-up write on the business layer. But now you're doing CQRS — less trivial. You're not getting any of the ACID benefits of doing the OLAP and OLTP queries together in a single transaction; so your data model has to be designed for that (by e.g. having all tables be temporal tables.)
xhrpost 4y ago
This is sounds pretty great. Is there though that much of a dev ramp up increase from using SQLite over MySQL/PGSQL though?

chasil 4y ago

SQLite only allows one writer at a time. Attempts at multiple DML logins will not block until successful, but simply fail. The allowed writer can also impact readers, depending on the WAL mode setting. For DSS uses, where a data store is published once a day, SQLite is wonderful. For OLTP, look elsewhere.

sgbeal 4y ago
> Attempts at multiple DML logins will not block until successful, but simply fail. sqlite has a "busy handler" mechanism which tells it how to respond to blocks and most apps use the builtin one with which they can tell it "wait for X milliseconds before failing on a lock." The Fossil SCM (https://fossil-scm.org https://fossil-scm.org), the SCM in which sqlite itself is hosted, is an sqlite application which serves thousands of users per day, many of them in write transactions. Its forum is a separate sqlite3 instance. sqlite3's own forum is another... all of those serve many users, many of whom are writing, and in 14 years of using/contributing to fossil-scm.org, almost on a daily basis, i've hit exactly two locking errors.

dundarious 4y ago

The Single Writer Principle is probably the most important principle for concurrent systems design. Often it’s best to design wholly around it. In this case, I doubt there are any writers at all.

hinkley 4y ago
I haven't gotten much past the planning phase but I'm thinking it would be a pretty good fit to use Erlang (Elixir in my case) and sqlite together, which should make SWP pretty straightforward, which in addition to offloading some of the concurrency work off of sqlite, also should make it fairly straightforward to swap out for postgres. If in fact I ever hit that point.
- dugmartin 4y ago
  Phoenix now has sqlite3 as an option when running phx.new. I just played with it yesterday to also checkout litespeed syncing the database to backblaze. It was very easy to setup and test by running phx.gen.auth and monitoring the data being synced to b2. There is a blog post about running SQLite in dev and Postgres in production using Phoenix, much like rails does. It’s worth spending a few minutes checking it all out. I have a project in mind where I think it would be a good fit.

hedora 4y ago

Define large. I'd guess sqlite can do 10-100K TPS, no problem. You'll want a read cache to avoid repeatedly rendering the same HTML, etc in the frontend, where most CPU is spent. That'll accidentally reduce database load. Assuming 10% writes (which is really, really write heavy), that'll get you to 1M page views per second. After that, you'll need to rearchitect. (MySQL and Postgres are probably better choices, but I'd evaluate all three if I was setting this up for real.)

Closi 4y ago
Just because you can use something doesn’t mean you should use it. If you have 10k writes a second and 90k reads a second then SQLite is an awful choice.
- kasey_junk 4y ago
  Have you benchmarked it? Given modern commodity servers and no network overhead I’d buy it not being a problem. My intuition is you are right but given the operational simplicity improvements it would be worth exploring.
  - hedora 4y ago
    This. Like I said, postgres or mysql are probably better choices. However, modern hardware (especially small sync disk writes) is really fast,and sqlite has its own strong points.

ww520 4y ago

With the amount of memory these days, the whole SQLite db would be cached in memory. Read heavy application is not a problem at all.

qbasic_forever 4y ago
Yeah at that point you're basically as fast as redis or any other good binary tree datastructure store.

hinkley 4y ago

Somewhere along the way, we seem to have forgotten that we have processors that can do 3 billion mathematical operations per second. I'm currently trying to shame some people at work into acting on the fact that they wrote some code that should take about 1µs per call that is instead taking over 100. If you're stupid with cycles then you get to be stupid with cores too, and then you get to be stupid with locking semantics.

leksak 4y ago
Unless you are working in aerospace, military, or similar shaming people seems uncouth and perhaps not the best way to inspire meaningful change?
- hinkley 4y ago
  Shame is somewhere around #5 on a list, and never for subordinates. Also I’ve worked on commercial aviation software, and maybe three of us even knew the meaning of the word, so I’m curious what domain you saw it in.

easytiger 4y ago

If its mostly read then it's fine