6 ms·

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/thr

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