3 ms·
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 to
by bob1029 5y ago
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.
- alberth 5y agoThat’s great to hear. Would you mind sharing how you implement/use 1 connection?
- bob1029 5y agoI 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.
- alberth 5y agoHow do you implement this using a stateless stack (eg PHP)?
- dgfitz 5y agoI’d build a (stateful) shim micro service that the stateless code interacts with, no idea if that is the “most correctest” solution though
- olalonde 5y agoI 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).
- olalonde 5y agoPS: If this is incorrect, I'd love to know why instead of getting downvoted :)
- bob1029 5y agoThe 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.
- olalonde 5y agoThat'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.
- rad_gruchalski 5y ago> I don't think there's a reasonable way around it other than not using PHP. Or not using SQLite for this task?
- mschuster91 5y ago> 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.
- 5y ago
- slooonz 5y agoHow do you backup a 500 GiB sqlite database ?
- falcolas 5y agoWell, 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.
- bob1029 5y agoIt'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.
- ripley12 5y agoNice. 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.