> 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
> I've been working on something similar... although with slightly larger scope (intended to be used within containers/sandboxes) https://github.com/andrewbaxter/passworth
> stored in encrypted sqlite3
you had me at encrypted sqlite3. it would be great if you mention in readme that it uses SQLCipher
Is it possible to create a filter that can work over a complex join operation?
That's what IVM systems like Noria can do. With application + cache, the application stores the final result in the cache. So, with these new IVM systems, you get that precomputed data directly from the database.
Views in Postgres are not materialized right? so every small delta would require refresh of entire view.
> I don't think WAL can ever really have a durable transaction; it is essentially based on leaving a transaction open until it gets "check-pointed" or actually committed to the database file
why not? if you use synchronous=FULL, then WAL does provide durable transactions, no?
I wrote the first article, and I thought documentation is clear, but then I saw comment by Hipp which got confused me:
> If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored.
I don’t want to use JavaScript /TS but I want to make games for the web. Are there any nice framework which lets me write in Rust, it compiles to WASM for web?
> If a system has distributed-consensus mechanisms, many different forms of event-driven communication, CQRS, and other clever tricks, I wonder if there’s some fundamental bad decision that’s being compensated for (or if the system is just straightforwardly over-designed).
then later down in the article:
> Send as many read queries as you can to database replicas. A typical database setup will have one write node and a bunch of read-replicas. The more you can avoid reading from the write node, the better - that write node is already busy enough doing all the writes.