10 ms·
It makes me wonder why I would use anything else?
by jimnotgym 4y ago
It makes me wonder why I would use anything else?
- stefanos82 4y agoExactly what I was thinking!
- metadat 4y agoPerhaps because as of today: > The prototype is still missing features and contains many bugs. But it works well enough to test.
- Aperocky 4y agoeven when this is not the case, sqlite works well enough to replace maybe 80% of heavier database setup out there. Scaling and ability to scale is very valuable, but I would guess massive premature scaling is much more common than scaling cliffs - especially as they don't get reported or have nearly as much visibility.
- fierro 4y agothe fact that SQLite is a library (embedded DB) means only one node can access the DB at a time. This would not be appropriate for many apps that require HA.
- zie 4y agoMay I introduce you to https://litestream.io https://litestream.io
- bsaul 4y agoI don’t see how sqlite replicated to other servers can be faster than any other relational dbs replicated on those same servers, at least without taking more chances to loose data. The limit is most likely network latency anyway, isn’t it ?
- tptacek 4y agoOther relational databases require network roundtrips to fetch information from the database once it's replicated. SQLite doesn't. It's a huge performance difference, to the point where it changes how you access your database; for instance, there's not much point in hunting down N+1 query patterns anymore.
- bsaul 4y agohm i see. so the cost is in the initial setup of the client instance, which needs to download the whole database to its local sqlite ?
- luckylion 4y agoFrom what I understand, that streams the file to a separate location, but it doesn't change the file-locking, does it?
- zie 4y agoRight, for a SQLite based app, you are mostly limited to 1 instance running at any given time(there are multi-node stuff shoved behind SQLite done by 3rd parties, but that's a whole diff. can of worms). The point being, you run 1 instance and you have litestream replicate to your backup node for HA purposes. Now you are going to think, what about scaling?!!? How many apps actually need to scale beyond 1 node? Very few. If you run into scaling problems, that is when you deal with solving your scaling problem. Because scaling is unique to each application. But before you remotely think about scaling past one node, you just build the node bigger. Nodes can get pretty massive these days.
- hamandcheese 4y agoLitestream didn’t exist until late 2020, meanwhile postgres has existed for decades. Moreover, SQLite requires the place you run your application to have durable storage, which is a huge departure from the status quo. It’s definitely neat, but the stack as a whole doesn’t strike me as mature enough to replace Postgres just yet.
- ngrilly 4y ago> SQLite requires the place you run your application to have durable storage, which is a huge departure from the status quo. Having durable storage used to be the status quo for decades. It changed only recently with cloud providers (or their customers) pushing for stateless workloads because they are much easier to manage in a distributed system than stateful workloads.
- zie 4y agoSQLite has been around for a very long time, it is easily the worlds most deployed SQL database. Litestream is just a way to do live sync/replication to make HA easier. There are certainly use cases where SQLite is not a good fit. There are likewise use-cases where PostgreSQL is not a good fit either. One is not better than the other, it just depends on your particular needs for that particular project. My point is, SQLite is a totally sane and reasonable storage/DB solution for many server side applications as well.
- tptacek 4y agoYou're writing this as if LiteFS and Postgres were basically the same thing, and the selection criteria just boils down to the Postgres pedigree and maybe all the Postgres-specific SQL features. But that's not the case at all. The difference between replicated SQLite and Postgres is that SQLite is in-core: it doesn't have to round-trip on the network to fetch data; it can burn through a large set of N+1 queries faster than Postgres can handle a single select. The difference is that SQLite is much faster. You sacrifice things to get that speed (Postgres features, set-and-forget write concurrency). Nobody is saying there's no reason to use Postgres, or maybe even that Postgres is the right call most of the time. But the idea that SQLite is rarely appropriate for concurrent serverside applications? It's received wisdom and it's wrong. Somebody across the thread actually suggested that WordPress was an example of the kind of application that SQLite wouldn't work for, that needed an n-tier database. (Leave aside the fact that WordPress doesn't support SQLite, has instead a longstanding MySQL dependency). WordPress! WordPress is a perfect example of a concurrent serverside application that probably should almost exclusively use SQLite. As I said in a different comment: the whole movement towards static site generators is, in large part, a reaction to how bad n-tier databases are for a very large, popular class of concurrent serverside applications.
- Aperocky 4y agoIndeed, SQLite should not be used anywhere that scaling or concurrency is a concern, or in general any external customer facing features. In a similar vein, an internal subsystem can probably do away with a dedicated database server. Latter is likely more common.
- MobiusHorizons 4y agoCare to justify that? The evidence seems to be against you for most read heavy workloads, especially if latency is any concern.
- tptacek 4y agoThat's silly. SQLite works fine for all sorts of customer features, and, deployed carefully, is fine with concurrency (writes are serialized, per database, but SQLite makes it easy to use multiple databases). SQLite has this weird reputation, I think, because frameworks like Rails used it as their "test" database, and the industry has broadly slept on how capable SQLite is in serverside applications.
- AlphaSite 4y agoI think if you use a lot of database features, sqlite isn’t really powerful enough for a lot of use cases IMO. Or atleast that’s the impression I have.
- tptacek 4y agoWhich features would those be?
- fierro 4y agowon't long running write transactions block eachother? Not all apps can avoid the need for these kinds of transactions.
- luckylion 4y agoConcurrent inserts. There are applications where you don't care about that, but that excludes a lot of web-apps, or things running with multiple processes. If that changes, I'd agree with your point, but currently that's a big constraint.
- tptacek 4y agoOnly one node can write to a given SQLite database at a time. SQLite handles concurrent readers swimmingly. The fact that SQLite is a library has not that much to do with its concurrency model?
- fierro 4y agoyes this is true, I was imprecise
- beoberha 4y agoWhat do you do if the node running the SQLite instance goes down?
- tptacek 4y agoThe same thing you do in a multi-reader, single-writer MySQL cluster, except with SQLite there's one less thing to go down, because the database is embedded in your process.
- beoberha 4y agoI’m not familiar with MySQL specifically, but people worried about HA have standby replicas. Enterprise DBs make this almost trivial to do and it’s very possible with PG extensions. I’m sure someone has built a system like that with SQLite, but it’s much less ideal than other database systems.
- snovv_crash 4y agoWhat is the point of having a HA database if your frontend app isn't running to be able to query it? SQLite gives the guarantee that if your frontend app is running, then the DB is available as well. Try doing that with a non-embedded DB.
- anonymousDan 4y agoThe point of disaggregating your replicated database is you can scale your app and db tiers independently. Typically you only want to have a handful of database replicas (e.g. to failover after your primary fails), especially if you want strongly consistent replication. But your stateless app tier may require many more servers.
- randomdata 4y agoThe only real difference between SQLite and something like Postgres or MySQL is that Postgres and MySQL bundle a networking layer on top of their embedded DB engines. If you are worried about high availability, chances are you too are building a networking layer on top of a database, so what do you need two networking layers for?
- pjmlp 4y agoAnd stored procedured, debugging, JIT compilation, extension in multiple languages, ....
- beoberha 4y agoDo people build their own networking layers above SQLite (besides for fun)? If you’re doing that, then you need to build some kind of replication story. At that point it makes sense to just use PG or MySQL
- randomdata 4y agoUnquestionably. Web apps and the like which are little more than networking frontends to a database are probably the most common type of software written these days.
- beoberha 4y agoAh, we’re talking about different things. I took “network layer” to mean something like the ability to connect directly to the database over the network, not through some shim application.
- randomdata 4y agoMeaning something like rqlite[1]? The age of fat desktop clients all connecting back to the central SQL server is long behind us, so yeah there is probably little reason beyond fun for something like that these days, but where there is fun! [1] https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- hamandcheese 4y agoNot only that, but typical devs write slow application code, which needs to be distributed horizontally across many workers.
- Cthulhu_ 4y agoThere's rqlite (https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite), which looks cool on the surface but... it's a layer on top of sqlite, at which point you should probably think long and hard whether it's still the right tool or you should switch to e.g. postgres.
- ngrilly 4y agoTo get HA, it is now possible to replicate the SQLite database using LiteFS, which is similar to how you would get HA with MySQL or PostgreSQL: https://fly.io/docs/litefs/ https://fly.io/docs/litefs/
- rcarmo 4y agoLack of JSON CONTAINS would be a good reason.
- blacklion 4y agoDo these developers, who require JSON CONTAINS, know about basic normal forms and RDBMS in general?! I understand, that sometimes for performance it is needed to de-normalize huge datalakes, but typical website controlled by CMS is not huge by any means.
- TkTech 4y agoHeh, this is the reason Takahe is currently broken on SQLite. Hashtags currently need a JSON contains.
- simonw 4y agoIt's possible to simulate JSON contains in SQLite using a devious subselect. Here's an example: https://datasette.io/content/plugin_repos?_facet_array=tags&tags__arraycontains=Data+Import https://datasette.io/content/plugin_repos?_facet_array=tags&... That page shows every row where the tags column (a SQLite JSON list of strings) contains the string "Data Import" Here's the underlying SQL: https://datasette.io/content?sql=select%0D%0A++rowid%2C%0D%0A++repo%2C%0D%0A++tags%0D%0Afrom%0D%0A++plugin_repos%0D%0Awhere%0D%0A++%3Atag+in+%28%0D%0A++++select%0D%0A++++++value%0D%0A++++from%0D%0A++++++json_each%28%5Bplugin_repos%5D.%5Btags%5D%29%0D%0A++%29%0D%0Aorder+by%0D%0A++rowid%0D%0Alimit%0D%0A++101&tag=Data+Import https://datasette.io/content?sql=select%0D%0A++rowid%2C%0D%0... select rowid, repo, tags from plugin_repos where :tag in ( select value from json_each(plugin_repos.tags) ) order by rowid limit 101 I haven't tried it yet, but I have a strong hunch that this could be dramatically accelerated using a join against a FTS table on that "tags" column in order to first filter the rows down to a likely subset.
- rcarmo 4y agoDjango would need to be patched to do that, since the default SQLite provider would make it hard to do that cleanly. Here’s the relevant Takahe issue: https://github.com/jointakahe/takahe/issues/325 https://github.com/jointakahe/takahe/issues/325
- deleted 4y ago[deleted]
- FpUser 4y ago>"It makes me wonder why I would use anything else?" this: "There is no support for recovering from a power failure or operating system crash. Such an event may corrupt the database."
- gnarbarian 4y agoYou don't always need this at the db level. depending on your use case backups or high availability in multiple datacenters can do fine.
- metadat 4y agoBut then what is the recovery scenario.. the story could get hairy and complex quickly.
- gnarbarian 4y agoyou either restore from your snapshot/backup of the database, or replicate from the HA node that didn't fail. it depends on the nature of your app as to whether either of those scenarios are acceptable. the former comes with downtime.
- hot_gril 4y agoPostgres focuses more on this use case. It's probably still going to do a better job with concurrency, or at least have more features. It'll also cost more to pay for as a service, so you decide if it's worth. Whereas HC-tree is an obvious win if you're already using SQLite. It's fine for two things to approach similar use cases from different sides. Personally I want the opposite direction, a sort of Postgres-lite that uses a single flat file and lives inside a lib, for things where the power of the full thing isn't needed yet but I want to design my backend code against a Postgres interface. I've seen Wasm Postgres examples, and maybe there are others, but nothing mainstream. And yes there was this SQL standard that everything was supposed to conform to, but that went out the window long ago.
- samuel 4y agoDuckdb should be compatible with postgres to a high degree. https://duckdb.org/why_duckdb.html https://duckdb.org/why_duckdb.html > SQL Parser: We use the PostgreSQL parser that was repackaged as a stand-alone library. The translation to our own parse tree is inspired by Peloton.
- pbowyer 4y agoWatch out with DuckDB that if you have an open reader you can't open a writer. A writer must be the only connection. https://duckdb.org/faq#how-does-duckdb-handle-concurrency https://duckdb.org/faq#how-does-duckdb-handle-concurrency
- nl 4y ago> direction, a sort of Postgres-lite that uses a single flat file and lives inside a lib, Can you expand on this? How did you package postgres as a lib?
- hot_gril 4y agoI haven't done this, but I'd want it. I'd like to write my code to use a Postgres database, except swap in something similar to a SQLite database instead when I'm either testing or not yet at scale. SQLite does everything in just a library linked with your code, unlike Postgres which is a separate running server. I can't simply use SQLite for this because its API is different from Postgres's.
- sargun 4y agoFor workloads that are on the same node -- Putting the database in the same memory space as my code as well as the same I/O and scheduling contexts isn't always what I want. I want to be able to prioritize database I/O far above app I/O. In addition, I want to make sure my database isn't prioritized "lower" than my application, as this can trigger priority inversion issues. Also, the scaling characteristics of database nodes vs service nodes are very different. I might not want to put reliable storage into every node I have, but I'm probably willing to shell out a couple bucks for my database server's storage. An example from my last project that used a lot of postgres. The database node was about ~20x the price as the workers. From an operational perspective, having the database be maintained separately from the app also has its benefits.
- api 4y agoOne scaling paradigm that's occurred to me for SQLite would be to run lots of small replicated instances in a system like Nomad or Kubernetes and even allow many running on the same physical node. So you're replicating multiple times on the same box which wastes some space but storage is cheap. Wouldn't be good for huge data sets but these days huge means like a terabyte or more. You don't really have to start thinking about sharding or smart fractional replication until you hit many terabytes. Even many successful SaaS companies don't have databases that big. Several hundred gigs these days is not too big for a brute force replicate everywhere approach and it comes with some benefits like less locking and stronger fault tolerances. If you have 50 replicas the odds of them all being screwed at once gets very low.
- treis 4y agoTo add to this, you want you application to be stateless and immutable. This makes your life so much easier in so many different ways: (1) You can know exactly what code was running when debugging past issues (2) Scaling can be done dynamically and relatively easy. Just turn on/off application nodes. (3) Rollbacks are (relatively) trivial. Just go back to the know good immutable version (4) Memory leaks can be "solved" by restarting nodes every so often (5) If, for whatever reason, your application node dies you shrug your shoulders and start a new one Once you add DBs with state to your application all of become much harder. You want that state in something that's rock solid and rarely changes to avoid all the above problems.
- ComodoHacker 4y agoBecause it won't have as many years of rigorous testing and polishing as current back-end or PostgreSQL or whatever.