Hacker News Reader

Comment by bob1029 | Hacker News Reader

We've been using SQLite for 100% of our data persistence needs for the last ~5-6 years now. Our largest single environment is probably getting close to 500gb total size. Hundreds of concurrent users are no problem for us, even without these enhancements (we use WAL currently).

The biggest single trick I learned was to use 1 sqlite connection instance for the entire lifetime of the application. You can add orders of magnitude more throughput with this path. SQLite serializes writers by default so dont duplicate the effort. You can even use the RETURNING keyword to grab insert keys without needing to lock over LastInsertRowId.

We also are close to 100% of business logic being executed via SQL queries as well.

View on HN

Nice. That's basically my ideal work environment; if you happen to have an opening for a .NET developer who loves SQLite and databases in general, my contact info is in my profile.

That’s great to hear.

Would you mind sharing how you implement/use 1 connection?

I would just think of it as 1 global static SQLiteConnection instance. There's really not a whole lot to it. Like 4 lines in a class file to set this up.

How do you implement this using a stateless stack (eg PHP)?

I’d build a (stateful) shim micro service that the stateless code interacts with, no idea if that is the “most correctest” solution though

I think what bob1029 meant was just that opening multiple connections doesn't increase concurrency so don't do it unnecessarily (by using a connection pooling library for example). With PHP you have one "process" per request, so you'd just need to make sure you only open a single SQLite connection per request (using the singleton pattern for example).

PS: If this is incorrect, I'd love to know why instead of getting downvoted :)

The traditional pattern of one connection per transaction is actually a strong anti-pattern when working with SQLite in particular.

This is because SQLite is just a file, not a network connection or named pipe. Obtaining and releasing file handles is a non-trivial task that takes significantly longer than reusing the same one.

That's what I meant with "opening multiple connections doesn't increase concurrency so don't do it unnecessarily (by using a connection pooling library for example)". I was suggesting to only use a single connection for the entire request and NOT to use a connection pooling library.

Now, PHP doesn't share memory between requests so there's no way to re-use the same connection across requests. That's just a sad fact about PHP and I don't think there's a reasonable way around it other than not using PHP.

PS: I haven't used PHP in over a decade so my knowledge might be outdated.

> I don't think there's a reasonable way around it other than not using PHP.

Or not using SQLite for this task?

> Now, PHP doesn't share memory between requests so there's no way to re-use the same connection across requests.

That depends on the execution model/PHP SAPI. mod_php and php-fpm use long-lived PHP processes and so can support persistent database connections depending on the configuration.

FYI - PHP doesn't have native connection pooling (due to the execution model). And Persistent Connections is much different than a connection pool.

https://stackoverflow.com/questions/39753/connection-pooling...

How do you backup a 500 GiB sqlite database ?

Well, it's ultimately files on a disk, as well as queryable data. So probalby pretty easily.

Logical database backups (I'd point to mysqldump/mydumper & myloader if we were talking about mysql), combined with physical snapshots & copies of the binary files.

500 GiB is a lot, but not too out there these days.

It's typically being backed-up by way of whatever hypervisor running snapshots of the VM.

Longer term, we are looking at synchronous log replication so that no business data would ever be lost on a primary outage. Having an availability problem is acceptable as long as we can bring everything back up in a reasonable amount of time.

Reply on news.ycombinator.com