Supporting thousands to tens of thousands of SQLite DBs on one machine

[marcua]

Hello! Thank you for building a wonderful tool! I have three related questions to gauge the feasibility of using litestream in another open source project:

• Are there any known limitations to the number of SQLite databases per node that litestream can monitor/back up? For context, I'm looking at an application where a single machine would have thousands to tens of thousands of SQLite databases of variable size, most of which are tens of megabytes large and most of which sit idle.
• As new databases are created, is there a way to avoid having to explicitly add it to the dbs list in the configuration file and restart litestream? I didn't see a wildcard/glob configuration or something like that in the documentation, and apologize if I missed it.
• Is there anything like a liblitestream where I can programmatically call replicate/restore?

For context, a few of us are building ayb, an open source tool that makes it easy to create SQLite databases, share them with collaborators, and serve queries against them over HTTP. I'm trying to figure out whether we can utilize litestream to provide the backup/disaster recovery aspect of the project.

Thank you in advance for your help!

[hifi]

Hi!

We have an internal project that uses Litestream as a library to run a single service that replicates hundreds of databases per node. We have considered open sourcing it but haven't done that yet. It exposes a REST API to manage the databases it replicates and keeps its own SQLite database for state between restarts.

One thing to consider when Litestreaming a lot of databases is that there's some memory overhead per database and it isn't fully consistent. We've observed somewhere around 1 megabyte per database when used in our environment but if there's a problem with a replica and writes are happening the lz4 library can balloon its memory allocations permanently. That is fixable by changing the compression library and #554 is work towards the idea of having more than one compression method to be able to pick a library that sacrifices CPU cycles over RAM.

In the context of ayb it's either using Litestream as a native library over FFI and adding a little bit of cgo boilerplate to do that or to run a separate Go service next to it which can manage the Litestreaming side of it, I think.

[marcua]

Thank you so much for this context on both memory overhead and approach, @hifi! Is this to say that your internal project utilizes litestream's replicate/restore functionality programmatically and not by way of the command line / yml configuration? If I understood that correctly, do you know if there is documentation of API somewhere (apologies if I missed it)?

Assuming all of this is true, I'm guessing that you had considered using litestream's command line interface to manage the hundreds of databases, but didn't find it feasible and thus went down the litestream-as-library path. Hopefully I'm interpreting all of this correctly. I really appreciate your explanation.

Count me as +1 to being interested in the open sourced service! :)