4 ms·

SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if the

SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.

tptacek 3y ago
SQLite works just fine for website backends. Writes are serialized, first from replicas to the central write leader (like in Postgres), and then with the WAL and transaction isolation.
- evantbyrne 3y ago
  The SQLite documentation itself mentions write concurrency as a limitation. > Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however. It goes on to say that other databases provide more concurrency. > However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine. https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5
  - tptacek 3y ago
    Yes: sqlite3 only allows a single open write transaction per database file. Whether this matters or not depends on your application. For a large class of applications, this isn't a real issue at all. By way of example: until a few months ago, we were concurrently merging state updates from tens of thousands of VMs across our global worker fleet into a single SQLite database. As has been said across this thread, SQLite works well as a backend for read-heavy applications. A great many CRUD apps are read-heavy! If you want more concurrency, you can break your schema up into multiple .db files. This sounds onerous but SQLite makes it very easy to do.
    - evantbyrne 3y ago
      That's great it worked for you, but that's not a website backend. It would be foolish to run HN on SQLite for instance. I would go as far as to call it a vulnerability in such context, due to how easily it could be DoS'd.
      - tptacek 3y ago
        HN famously ran (still runs?) on a series of flat files. You found the actual worst possible rebuttal.
        evantbyrne 3y ago
        Maybe you don't understand SQLite's locking issues? Having a bunch of different files that can be written to concurrently sounds like a better solution than SQLite for a social network backend.
        tptacek 3y ago
        Yes, maybe that's it.

tptacek 3y ago

SQLite works just fine for website backends. Writes are serialized, first from replicas to the central write leader (like in Postgres), and then with the WAL and transaction isolation.

evantbyrne 3y ago
The SQLite documentation itself mentions write concurrency as a limitation. > Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however. It goes on to say that other databases provide more concurrency. > However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine. https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5
- tptacek 3y ago
  Yes: sqlite3 only allows a single open write transaction per database file. Whether this matters or not depends on your application. For a large class of applications, this isn't a real issue at all. By way of example: until a few months ago, we were concurrently merging state updates from tens of thousands of VMs across our global worker fleet into a single SQLite database. As has been said across this thread, SQLite works well as a backend for read-heavy applications. A great many CRUD apps are read-heavy! If you want more concurrency, you can break your schema up into multiple .db files. This sounds onerous but SQLite makes it very easy to do.
  - evantbyrne 3y ago
    That's great it worked for you, but that's not a website backend. It would be foolish to run HN on SQLite for instance. I would go as far as to call it a vulnerability in such context, due to how easily it could be DoS'd.
    - tptacek 3y ago
      HN famously ran (still runs?) on a series of flat files. You found the actual worst possible rebuttal.
      - evantbyrne 3y ago
        Maybe you don't understand SQLite's locking issues? Having a bunch of different files that can be written to concurrently sounds like a better solution than SQLite for a social network backend.
        tptacek 3y ago
        Yes, maybe that's it.