5 ms·

SQLite in its current form is not going to "take over". But it's the right tool for so many instances where Postgres/MySQL/... were a massive overkill but were

by deskr 2y ago

SQLite in its current form is not going to "take over". But it's the right tool for so many instances where Postgres/MySQL/... were a massive overkill but were the only thing available in common use.

kvgr 2y ago
Exactly, i have website that is visited by couple people a day and I just want to store some data on every action in one table + some settings in DB. Perfect for SQLite and anything else if overkill. Also if I ever want to rewrite it in the future its not a big thing, its just a database.
- creshal 2y ago
  I have a website that's visited by a couple thousand people a day, but since it's not a bloated mess that writes to the DB for every page hit, it's still faster than any competition site in that particular niche, and those deploy to expensive DB clusters to handle their spurious writes. :)
  - da02 2y ago
    Are you able to disclose the URL of the website? How often are you writing to the database? Every few seconds or minutes?
    - jddj 2y ago
      Not OP but just checked the logs for one that I'm running. As a first approximation I grepped for PATCH in the web server logs and it seems to service about 30 of those per second on a $40/month vps. I haven't load tested it so not sure how high it could go. That's not including any POSTs etc which may also generate DB writes. Bear in mind this isn't particularly performance tuned either as the webserver uses go (slow in general) and the portable go sqlite3 driver (also slow)
      - sofixa 2y ago
        > webserver uses go (slow in general) Go isn't slow in general, it's pretty damn fast without getting into the crazy handcraft artisanal stuff with e.g. C/C++. It's, IMO, the perfect balance between speed, features, language complexity, standard library richness. For reference, a random benchmark that shows that even the optimised to hell Twister Python web server is slower than the out of the box, in the standard library, `net/http` in Go.
        creshal 2y ago
        I just run django on nginx-passenger in debug mode, but I don't vomit dozens of megabytes of Javascript into the output so I still hit sub-second render targets. I think there's maybe two dozen lines of JS for some interactive calculator widget.
    - creshal 2y ago
      It's a fan website for an obscure mobile game (that I don't really want to associate with my professional profiles, it is really just a hobby); the database sees a bunch of write transactions per week, whenever an editor writes new content. If it wasn't for non-technical editors requiring an interactive WYSIWYG backend, it could've been made with a static site generator, but as it is, Django needs a couple dozen megabytes of RAM at worst for logged in users.
datadeft 2y ago
It has taken over already. Sqlite is the most popular database for some time already.
- doctor_eval 2y ago
  Isn’t that a bit like saying that the iPhone is the most popular computer? To stretch the analogy: if you need a database server, neither SQLite nor the iPhone are a good fit.
  - datadeft 2y ago
    I think Sqlite is an option in many cases when people want to use Postgres. I believe there is more overlap between Sqlite and Postgres than iPhone and a general computer.
    - doctor_eval 2y ago
      SQLite is pretty great but I’ve spent my career building multi-user apps, and this is a category for which SQLite is simply a bad fit - as they point out themselves at https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html While there is significant overlap in the basic DML operations due to them both being SQL based, getting SQLite working for my use cases would be like using an iPhone to serve my app over the internet: a whole lot of work, and many compromises, for no good reason.
baq 2y ago
SQLite is not competing with RDMBSes. SQLite is competing with fopen(). There are of course solutions which wrap this fopen() replacement in a network/cluster-aware tools, e.g. https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite - these are competing with postgres.
- eatonphil 2y ago
  rqlite isn't competing with Postgres since postgres does not provide any mechanism for highly available + serializable consistency and that's what rqlite gives you. Postgres does have replication out of the box but it has a bunch of limitations meaning you have to manage it yourself (ddl isn't replicated I think). And the replication will not get you strict serializability.
  - baq 2y ago
    It's absolutely competing in that it doesn't fall apart by design when there are many concurrent writers.

by deskr 2y ago

SQLite in its current form is not going to "take over". But it's the right tool for so many instances where Postgres/MySQL/... were a massive overkill but were the only thing available in common use.

kvgr 2y ago

Exactly, i have website that is visited by couple people a day and I just want to store some data on every action in one table + some settings in DB. Perfect for SQLite and anything else if overkill. Also if I ever want to rewrite it in the future its not a big thing, its just a database.

creshal 2y ago
I have a website that's visited by a couple thousand people a day, but since it's not a bloated mess that writes to the DB for every page hit, it's still faster than any competition site in that particular niche, and those deploy to expensive DB clusters to handle their spurious writes. :)
- da02 2y ago
  Are you able to disclose the URL of the website? How often are you writing to the database? Every few seconds or minutes?
  - jddj 2y ago
    Not OP but just checked the logs for one that I'm running. As a first approximation I grepped for PATCH in the web server logs and it seems to service about 30 of those per second on a $40/month vps. I haven't load tested it so not sure how high it could go. That's not including any POSTs etc which may also generate DB writes. Bear in mind this isn't particularly performance tuned either as the webserver uses go (slow in general) and the portable go sqlite3 driver (also slow)
    - sofixa 2y ago
      > webserver uses go (slow in general) Go isn't slow in general, it's pretty damn fast without getting into the crazy handcraft artisanal stuff with e.g. C/C++. It's, IMO, the perfect balance between speed, features, language complexity, standard library richness. For reference, a random benchmark that shows that even the optimised to hell Twister Python web server is slower than the out of the box, in the standard library, `net/http` in Go.
      - creshal 2y ago
        I just run django on nginx-passenger in debug mode, but I don't vomit dozens of megabytes of Javascript into the output so I still hit sub-second render targets. I think there's maybe two dozen lines of JS for some interactive calculator widget.
  - creshal 2y ago
    It's a fan website for an obscure mobile game (that I don't really want to associate with my professional profiles, it is really just a hobby); the database sees a bunch of write transactions per week, whenever an editor writes new content. If it wasn't for non-technical editors requiring an interactive WYSIWYG backend, it could've been made with a static site generator, but as it is, Django needs a couple dozen megabytes of RAM at worst for logged in users.

datadeft 2y ago

It has taken over already. Sqlite is the most popular database for some time already.

doctor_eval 2y ago
Isn’t that a bit like saying that the iPhone is the most popular computer? To stretch the analogy: if you need a database server, neither SQLite nor the iPhone are a good fit.
- datadeft 2y ago
  I think Sqlite is an option in many cases when people want to use Postgres. I believe there is more overlap between Sqlite and Postgres than iPhone and a general computer.
  - doctor_eval 2y ago
    SQLite is pretty great but I’ve spent my career building multi-user apps, and this is a category for which SQLite is simply a bad fit - as they point out themselves at https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html While there is significant overlap in the basic DML operations due to them both being SQL based, getting SQLite working for my use cases would be like using an iPhone to serve my app over the internet: a whole lot of work, and many compromises, for no good reason.

baq 2y ago

SQLite is not competing with RDMBSes. SQLite is competing with fopen(). There are of course solutions which wrap this fopen() replacement in a network/cluster-aware tools, e.g. https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite - these are competing with postgres.

eatonphil 2y ago
rqlite isn't competing with Postgres since postgres does not provide any mechanism for highly available + serializable consistency and that's what rqlite gives you. Postgres does have replication out of the box but it has a bunch of limitations meaning you have to manage it yourself (ddl isn't replicated I think). And the replication will not get you strict serializability.
- baq 2y ago
  It's absolutely competing in that it doesn't fall apart by design when there are many concurrent writers.