Sqlite backend status

[casey]

First off, I can't start off this issue without a huge thank you for this awesome crate.

I'm evaluating sqlx for a project. My plan is to use sqlx with sqlite until I hit scaling issues. (Which might be never, who knows, sqlite is plenty fast.) Then, I plan to switch to sqlite for development and postgresql for production.

I'd like to avoid using postgres from the get-go, so I can always develop without a postgres database running locally, and since I'm likely to start out at small scale it seems simple enough to use sqlite in prod at first too.

I read in this post that the postgres backend was the most mature and tested, whereas the other backends weren't as robust. (Or maybe they are as robust now, but might not receive as much maintenance and testing in the future.)

Do you think the using sqlx with the sqlite backend is a reasonable choice for a production application, not considering the merits of sqlite itself, but only considering the maturity of the sqlx sqlite bindings?

[abonander]

The SQLite backend has been improved a bit since this issue was opened. I don't think it's a terrible idea to use, we are actively maintaining it and fixing bugs as they come up. It's inherently kind of a bad pairing with async, though, and its performance suffers because of that (#1481). It's up to you whether that's a dealbreaker or not. There might be some improvements we can make there but I'm not sure how much better we can get.

I would personally choose Postgres even for a small scale app because I have a lot of experience with it and it has a large featureset, and it's easier to scale if/when an app does need to scale. It's also really easy to spin up Postgres in Docker now (I don't even bother with a native install anymore, that's still quite the nightmare so I understand where you're coming from).

[casey]

Thank you for the thoughtful response!

Just FYI, and for anyone who reads this: We've been using sqlx with SQLite for a while now, and performance hasn't been great. We're using it on an anemic $5 Linode instance, so that certainly contributes to the bad performance, but I suspect it's worse than it would be if we were using SQLite directly. We'll see if bumping the instance size helps, but otherwise will look at switching to Postgres.

[abonander]

It's likely the high overhead involved in communicating with the backend worker thread. I imagine one significant issue is that the thread is probably constantly context-switching as it's woken by messages coming in, handles them, and then goes back to sleep when the channel is empty.

We probably want to modify it to spin a little bit before going to sleep, although it looks like crossbeam-channel already does this internally. It's hard for me to be sure because the code for channels is kind of scarily complex.

In the future we're going to have support for switching SQLx to a synchronous API with blocking calls, and at that point it'll probably be more performant to just run the whole bit of the code that touches the database directly in a tokio::task::spawn_blocking() call.

[casey]

Just an update: We switched from SQLite to PostgreSQL, and it solved all of our performance issues. We're still on the same anemic $5 Linode instance, but it seems to be able to handle orders of magnitude more requests per second.

[abonander]

I think I'm gonna work next week on rearchitecting the SQLite driver to fix the performance issues as well as some segfaults people are seeing. Glad you saw an improvement with Postgres though, that's definitely our most solid driver. We have it deployed in several Launchbadge projects.