Earlier quoted context omitted.
Looks like that branch is still under actively development - seven new commits this month: https://sqlite.org/hctree/timeline
[deleted]
[deleted]
Beyond the SQLite single-writer limitation with concurrent writes
71–75 of 75 posts
Re: Beyond the SQLite single-writer limitation with concurrent writes
#72The 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 lik…
> 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…
I would argue that BEGIN CONCURRENT doesn't solve the concurrency problems either, which is why SQLite also has an HCTree experimental branch :P
Re: Beyond the SQLite single-writer limitation with concurrent writes
#73Earlier quoted context omitted.
> SQLite is built around a file that stores data for a single application A single application can need multiple concurrent writes
So that's what asking. When? Why? I've simply never encountered that, and can't imagine what a use case would be. SQLite already supports lots of threads writing, they just all take turns. What application needs those writes to be concurrent?
I have such application. It scans hosts for gathering some information in several parallel threads. Then each thread stores part of this information in Sqlite database.
Re: Beyond the SQLite single-writer limitation with concurrent writes
#74Earlier quoted context omitted.
I 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. Wo…
Both 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.
>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.
Re: Beyond the SQLite single-writer limitation with concurrent writes
#75Kind 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…
SQLite 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/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 i…
ncruces helped me with some code I made for VFS. It uses [zstd seekable](https://github.com/jtarchie/sqlitezstd) for reading a file. I thought it would be really well-suited for S3.
- Support for HTTP range queries - "Fast" read times - No disk required
I was wrong.
It turns out that for specific SQL queries, it might be fine, but not fast. For queries that do aggregations, like `COUNT`, sqlite loads the whole database anyway.