10 ms·
Beyond the SQLite single-writer limitation with concurrent writes
- fragmede 1y agoCan't use it locally (yet?) but it's definitely an interesting move in the space. For my personal projects lately I've been defaulting to sqlite in dev, and having a database wrapper layer to use something else in prod.
- stronglikedan 1y ago> Can't use it locally I'm imagining some insane replication behind the scenes, where every write is happening concurrently on a different SQLite DB, and then merged together sequentially into some master DB.
- glommer 1y agowhy not? Turso is a fully local database, you can just download the shell and use it as you would use sqlite.
- fragmede 1y agoMore specifically, I didn't see any commits about multiple simultaneous writers having landed in main and it's not document as a feature in the release notes https://github.com/tursodatabase/turso/commits https://github.com/tursodatabase/turso/commits neither the prerelease https://github.com/tursodatabase/turso/releases/tag/v0.3.0-pre.1 https://github.com/tursodatabase/turso/releases/tag/v0.3.0-p... nor their latest release. https://github.com/tursodatabase/turso/releases/tag/v0.2.2 https://github.com/tursodatabase/turso/releases/tag/v0.2.2 The core/mvcc directory does have commits though, so maybe you can. https://github.com/tursodatabase/turso/commits/main/core/mvcc https://github.com/tursodatabase/turso/commits/main/core/mvc...
- glommer 1y agoYou just have to pass the experimental flag. I thought this was explained in the blog ?
- fragmede 1y agoI searched https://turso.tech/blog/beyond-the-single-writer-limitation-with-tursos-concurrent-writes https://turso.tech/blog/beyond-the-single-writer-limitation-... with ctrl-f for "download" and "--exp" and got no results for either. There was a link to a live demo webpage at the very end. Am I looking at the right blog? That's the one linked at the top of the page.
- glommer 1y agoThe flag is --experimental-mvcc, it is in the help for the shell. You are right that the blog failed to mention. I will get this fixed soon!! Thanks for noticing it.
- fragmede 1y agoAh there it is! Thanks, I'll play around with it tonight.
- glommer 1y agoThere is a lot of work that still needs to be done to make this production-ready, both from a performance and reliability point of view, as we did our best to convey on the blog. We truly appreciate you trying it out! Report any issues, please.
- frumplestlatz 1y agoI’m not in a rush to use a reimplementation of SQLite — particularly from startup bros that had a very public, one-sided, and unpleasant fight with SQLite over their contribution model. D. Richard Hipp is a genuinely fantastic human being, and SQLite is a project developed at literally the level of planetary infrastructure given how broadly and everywhere it appears. Forking his project while using the name to garner recognition and attention is poor form, and it is difficult to have faith in the results.
- bitpush 1y agoI get where you're coming from, but isnt the whole idea of open source "if you dont like the approach, you're free to fork the code and do it the way you think is right?" As long as the fork doesnt violate trademark (turso vs sqlite) it is working-as-intended? I, for one, encourage this kind of behavior. We should have more forks. More forks = more competition = better results for everyone. --- To make an analogy. Would you say the same thing if this were a for-profit company? "I cant believe someone else is competing the same space as $x. $x is hugely successful, and so many people use it. I dont know why there's an alternative"
- immibis 1y agoBoth forking sqlite, and disliking the forking of sqlite, are allowed. Plus, it's just technically bad. Most cases where you'd want to scale up sqlite are better served by a client/server database.
- pod_krad 1y ago>Most cases But not all of them, right? I don't need scaling across different hosts, but I need scaling between different threads on the same host. And I don't want for that PostgreSQL, MySQL, Oracle an other stuff like that.
- stronglikedan 1y ago> Forking his project while using the name The don't use the name. They use Turso. Even the HN title is wrong - the article title doesn't mention SQLite. They refer to SQLite, but how could you not if that's what you forked from, and that's what has the functionality you're changing. That would be a very weird article if we didn't have that context.
- tracker1 1y agoKind of cool to see work on this. I do hope that the final db file result is still binary compatible with SQLite 3 in whatever direction Turso moves towards though... Rust or not. I've been advocating with several projects over recent years to get SQLite3 as an archive/export/interchange format for data. Need to archive 2019 data from the database, dump it into a SQLite db with roughly the same schema... Need to pass multiple CSVs worth of data dumps, use a SQLite file instead. As a secondary, I wonder if it's possible to actively use a SQLite interface against a database file on S3, assuming a single server/instance is the actual active connection.
- simonw 1y agoSQLite against S3 can work with some clever tricks. The neatest version of that I've seen is still this WebAssembly one: https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database... I also got sqlite-s3vfs working from Python a few months ago: https://simonwillison.net/2025/Feb/7/sqlite-s3vfs/ https://simonwillison.net/2025/Feb/7/sqlite-s3vfs/ Both of these are very much read-only mechanisms though.
- glommer 1y agosimon, you will be pleased to know that the python package for turso is in good shape! (and if you do find any issues please scream at us)
- huntaub 1y ago> As a secondary, I wonder if it's possible to actively use a SQLite interface against a database file on S3, assuming a single server/instance is the actual active connection. You could achieve this today using one of the many adapters that turn S3 into a file system, without needing to wait for any SQLite buy in.
- ncruces 1y agoSQLite directly against S3 is workable if you mean querying a read-only database. For example, from Go, you could use my driver, and point it to a database file stored in S3 using this: https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readervfs#example-package-Http https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readerv... For read-write it's a terrible idea. Object storage assumes objects are immutable. There may be some support for appends, but modifying the middle of an object in place involves copying the entire thing. What is on the verge of becoming viable is to use Litestream to do asynchronous replication to S3, and have read replicas that stream the data directly from S3. But what's stored in S3 isn't a database file, but a format created for the purpose called LTX.
- conradev 1y agoThe single-writer limitation in SQLite is per-database, not per-connection. You can shard your SQLite tables into multiple database files and query across all of them from a single connection. I agree that "the single-writer limitation isn't just a theoretical concern", but it's also solvable without forking SQLite. ulimit's the limit! If your goal is resource maximization of a given computer, though, Postgres is likely a better fit.
- kgwxd 1y agoJoins and Transactions are a pretty big part of SQL. I'm no expert, but if my quick search results are right, both are lost in the separate file per table scenario.
- conradev 1y agoThey work just fine! Joins: https://simonwillison.net/2021/Feb/21/cross-database-queries/ https://simonwillison.net/2021/Feb/21/cross-database-queries... Transactions: https://www.sqlite.org/atomiccommit.html#_multi_file_commit https://www.sqlite.org/atomiccommit.html#_multi_file_commit
- avinassh 1y ago> You can shard your SQLite tables into multiple database files and query across all of them from a single connection. You mean using ATTACH statement, right? If you use WAL mode, then you cannot get transaction safety / ACID with ATTACH [0] > If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not. Moreover, ATTACH do not support more than 125 databases, so that limits the shards to 125. [1] ATTACH does not solve the concurrency problems. That's why SQLite also has a BEGIN CONCURRENT experimental branch [0] - https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html [1] - https://www.sqlite.org/limits.html https://www.sqlite.org/limits.html
- asfs4fsdfadf 1y agoCan someone explain what "ecological niche" this new Turso DB occupies in between SQLite and Postgres?
- SchwKatze 1y agoTursoDB aims to be fully compatible with sqlite, so files you create with tursodb can be read by sqlite and vice-versa. Like sqlite, turso is in-process, so it runs alongside your application, which is quite different from postgres' client-server architecture. The only think turso has in common with postgres is mvcc, which is a rather standard concurrency control model across modern databases. Idk if I answered your question :)
- spiffytech 1y agoDon't forget that the SQLite team is working on their own multi-writer mode that blows BEGIN CONCURRENT' out of the water: https://news.ycombinator.com/item?id=34434025 https://news.ycombinator.com/item?id=34434025 Though this stuff moves slowly (that announcement was almost 3 years ago!), so I'm glad to see Turso giving us options today.
- simonw 1y agoLooks like that branch is still under actively development - seven new commits this month: https://sqlite.org/hctree/timeline https://sqlite.org/hctree/timeline
- deleted 1y ago[deleted]
- deleted 1y ago[deleted]
- rvitorper 1y agoEven in the “low-performing” zone of the single threaded SQLite and Turso, we are still talking about 50k rows per second and 1000 microseconds, aka 1 ms. It is insanely fast. 1ms is just the round-trip for my Postgres on RDS. It is amazing that SQLite is so awesome. I understand it is not for every use-case, but still awesome
- crazygringo 1y agoWhile I understand that you could do this, I genuinely don't understand why. SQLite and Postgres/MySQL/etc. occupy different niches. If you need massive concurrent writes, surely that's what Postgres/MySQL/etc. is for? Their engines are built around that from the ground up. SQLite is built around a file that stores data for a single application, as opposed to being client-server with many clients. I've used it a ton for that, but the idea that your application would have so many threads needing to write concurrently that it would be slowed down by a file lock... just doesn't make sense to me. What applications need this, and why wouldn't they use Postgres/MySQL/etc. if they need such a high level of concurrent performance? This feels like trying to adapt a small sports car to tow a semi-trailer. It doesn't seem like it's what it's meant for.
- Quarrelsome 1y agoAgreed, Sqlite docs specifically state that its not designed for this, or rather that the solution isn't appropriate, that database as a service is appropriate when you want multiple clients writing. The entire "problem" is a side-effect of using the wrong tool for the job.
- glommer 1y agoLinux was designed to run in home PCs, and we keep running it in supercomputers. It works just fine. Tools evolve.
- crazygringo 1y agoTools don't always need to evolve though. I don't want my hammer to evolve into a screwdriver too, or vice-versa. Having separate tools for separate things makes sense when the things are different enough.
- nextaccountic 1y agoYeah but this tool is evolving. You don't get a say on that, this is open source and devs do whatever they want with their time
- jauntywundrkind 1y agoVery excellent to see the embedded DB in the browser link at the bottom. https://shell.turso.tech/ https://shell.turso.tech/ I've been thinking Turso in the browser would be some eventual addition, is something the Rust rewrite would eventually unlock. And that would require considerable extra backing to make go. But here it is now!
- glommer 1y agoIt's very easy to do given the async model. It is one of the new features that is working the best so far.
- adzm 1y agoHow does the mvcc / row versioning changes that accumulate get shared among multiple processes? Is it another file like the WAL file? Or is it just local memory as part of the client? I guess with everything having to commit or entirely roll back at the end, it doesn't need to be shared. How does this compare with hctree then? It sounds similar, both using an MVCC model. Really cool though, love the technical details and would be interested to see further. I feel like there is certainly a space for sqlite+fancier features. Though it would break backwards compatibility, I've always wondered what kind of performance gains could be had by using a different varint implementation. And enforcing or always using "strict" for smarter column layouts. Also would be really cool to have indexed views. And columnstore seems like a logical direction as well.
- silvaadrian 1y ago[dead]