What are the downsides of "server-side SQLite" with Litestream?

[callum]

Just been reading this great blogpost again:

I'm All-In on Server-Side SQLite

From my patchy understanding, it seems like there are a lot of upsides and no real downsides to this approach - having SQLite built into each app instance and sync’d with Litestream - compared to eg. the standard Fly.io approach of postgresql servers in a leader-replica arrangement. If your database is part of your app process, you don’t have to worry about database servers. And it makes read queries so fast you don’t need to worry about batching them up into as few transactions as possible, leading to more maintainable code. It sounds amazing. I want to try it.

But there must be some downsides? Why isn’t everyone doing it? Are there hidden complexities with maintaining it? Is it incompatible with other things like ORM systems?

[tj1]

I’ve been using Postgresql for mutable data and SQLite for fixed data in my application with a view to switch everything to SQLite in the future.

Off the top of my head:

1. Having separate database pools for read vs write queries is pretty standard and will work fine on a single node. The issue for me is if I move to multiple nodes with SQLite, I will also have to write code to handle queries over the network. This may not be the case for other applications, but as I’m using Phoenix Liveview the way that Fly potentially re-submits queries(fly-replay) to the write-only postgresql node won’t work the same way as the initial connection was over a websocket.

2. GUI admin. If you like having a GUI admin which connects to a remote server, well, again, there’s no network connection.

3. For read-only replicas, backups, etc. the replication lag and monitoring will be very new. Not sure how complex to maintain compared to MySQL for instance.

[tj1]

Regarding 1 - it looks like this repo could be repurposed for sqlite and using RPC.

• GitHub - superfly/fly_postgres_elixir: Library for working with local read-replica postgres databases and performing writes through RPC calls to other nodes in the primary Fly.io region.
• GitHub - superfly/fly_rpc_elixir: Elixir libraries that makes it easier to build distributed Elixir applications on the Fly.io platform. They help with geographically distributed applications talking to primary/replica postgres databases in multiple regions.

@Mark - are there any plans for a separate fly_sqlite_elixir library?

[tj1]

So the new hotness is LiteFS, which will support read-replicas and LiteStream looks like it will be only for disaster recovery.

I’ve made an experimental library elixir_litefs that appears to mostly work with Liveview. I was testing today and had some issues with litefs not failing over as expected which will hopefully stabilize soon.

[benbjohnson]

@tj1 Thanks for testing out LiteFS and for making elixir_litefs. The tool is definitely still in alpha. I’ve been working out bugs mostly at the FUSE and local storage level so far but I have a long-running test harness set up now so I’ll be doing more hardening of the cluster code next.

[tj1]

No, thank you.

Litefs fills a sweet spot that has been surprisingly untouched over the past decade (or two). I’m really looking forward to swapping over.