Remote In-Memory DB?

[wobbet]

In reference to my previous discussion about replicating to multiple targets where I have

• One database that has all writes
• Many databases that are only read from

Is it possible for Litestream to replicate from the write instance to an in-memory instance on the readers?

If it isn't possible with the current design then...

• Is there a recommendation on the best way to periodically refresh an in-memory instance from an on-disk instance?
• Is it possible (future maybe) to run Litestream as a library instead of a service and I have my app ask it to pull from the read instance?
• Is it possible for Litestream to have an API that I can connect an I/O stream to instead of on disk? That doesn't seem like a clearly phrased question though...

Thanks!

rjsjr

[benbjohnson]

Is it possible for Litestream to replicate from the write instance to an in-memory instance on the readers?

Since Litestream runs as a separate process, it can't access the memory inside another process. If it were used as a library, I'm not sure if there is access to the in-memory implementation where WAL pages could be applied. Maybe there's a low-level API that could inject into the in-memory virtual file system (VFS).

Is there a recommendation on the best way to periodically refresh an in-memory instance from an on-disk instance?

You may be able to use the SQLite backup API. What's the benefit of it though? It seems like you'd have pages in the on-disk instance in the OS page cache so I would think it'd be pretty fast.

Is it possible (future maybe) to run Litestream as a library instead of a service and I have my app ask it to pull from the read instance?

There is a library example but the API is in flux so it's not recommended. Also, it doesn't work well on macOS because of how BSD does per-process locks.

Is it possible for Litestream to have an API that I can connect an I/O stream to instead of on disk? That doesn't seem like a clearly phrased question though...

The read replication implementation will have an API over HTTP that streams out the snapshot plus WAL changes. You could read off of that depending on what you need to do.

After I get the read replication working, my plan is to work on a serverless implementation of SQLite that works via a VFS. That might fit more inline with what you're trying to do. At that point, it'd have more control to keep the read replicas purely in-memory.

[ddevienne]

I don't think you can use WAL for an in-memory DB anyway, FWIW.

[benbjohnson]

I think the WAL still exists but it's just in-memory [1]. Otherwise I don't think you'd have MVCC.

[ddevienne]

Not sure what you mean Ben. You link to the thread I replied to, where Keith said it wasn't possible, and I also said it wasn't.
Indeed you cannot have MVCC for in-memory DB. Which is typically fine, as there's a single connection anyway.
Except in that special shared-cache mode, but still no MVCC, just read parallelism. I'm no Richard or Dan of course...

[benbjohnson]

Ah, sorry, I misread it and thought Keith was saying in-memory uses write ahead. My mistake.