9 ms·
Concurrent writes and replication! See also this thread on the SQLite forum: https://sqlite.org/forum/forumpost/d9b3605d7ff40cf4 https://sqlite.org/forum/forum
by simonw 4y ago
Concurrent writes and replication!
See also this thread on the SQLite forum: https://sqlite.org/forum/forumpost/d9b3605d7ff40cf4 https://sqlite.org/forum/forumpost/d9b3605d7ff40cf4
- metadat 4y agoThe linked performance graphs look remarkably promising, indicating hctree with 25% smaller pages scales to 1 million transactions per second: https://sqlite.org/hctree/doc/hctree/doc/hctree/threadtest.wiki https://sqlite.org/hctree/doc/hctree/doc/hctree/threadtest.w...
- freeqaz 4y agoNit: 1kb pages vs 4kb pages, so 75% smaller (25% of the size).
- michaelcampbell 4y agoThank you. One of my (granted, OCD driven) peeves is the recent ubiquity of framing that sort of comparison as "3 times smaller!". Ohmygod, no.
- metadat 4y agoOops, yes thank you for the correction!
- jimnotgym 4y agoIt 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 ?
- 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.
- sedatk 4y agoHugged to death currently: The server load is currently too high. Please try again later. Current load average: 5.190000. Load average limit: 4.000000
- SQLite 4y agoThe server is on Linode. Looking at the stats, they appeared to have had an outage of some kind last night at about the time you got this message. Everything seems to be running fine now. Please try again. https://sqlite.org/tmp/cpu-20230119.jpg https://sqlite.org/tmp/cpu-20230119.jpg
- bufferoverflow 4y agoShould've used SQLite on the backend.