> 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
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
[1] - https://www.sqlite.org/limits.html