4 ms·
We way over complicate things. SQLite can be used for 95% of real world use cases. And whenever WAL2 + BEGIN CONCURRENT get merged into main, it will be able
by alberth 5y ago
We way over complicate things.
SQLite can be used for 95% of real world use cases.
And whenever WAL2 + BEGIN CONCURRENT get merged into main, it will be able to handle 99% use cases.
And don’t fool yourself into believing you’re not in that 1%.
- bob1029 5y agoWe'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.
- 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.
- brandmeyer 5y ago> WAL2 + BEGIN CONCURRENT Woah. There are some gotchas. It still won't support as much concurrency as row-level MVCC. But it'll still be a big improvement. https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...
- snovv_crash 5y agoThe amount of IO we gained in the switch from spinning rust to NVME SSDs means that a SQLite scales serialised 'concurrency' much further than eg. Postgres ever could before.
- deathanatos 5y agoYou're not the only commenter saying this, so I gotta ask: whatever VM that SQLite DB is on, is your business cool with a business disruption, dataloss, or both when that VM goes down, when that AZ goes down, or that disk fails? Or am I missing something? If you have some sort of fail-over-to-last-backup plan, is that not just a distributed database with more steps, and why not something like RDS, or CockroachDB? (From mine, it is an explicit requirement that we weather such events. I keep trying to keep global outages from our platforms off the requirements list…; so, IDK, perhaps your requirements allow different a different approach.)
- alberth 5y agoMentioning RDS is an apples to oranges comparison. RDS is a fully managed service by a 3rd party. Nevertheless, checkout https://litestream.io https://litestream.io - given that SQLite is just a file, it’s incredibly easy to replicate. Failover is coming in the next release, which is already beta. Also checkout this Expensify blog post on how they use SQLite. It’s scales amazingly well. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... EDIT: I don’t understand, why the downvote?
- deathanatos 5y agoI mention RDS because it mostly solves the failover/continuity problem. While failovers on RDS are typically outages, they're also typically quite short lived, no more than a few minutes. That can work for many people/business's requirements. That it is managed means you (in theory) can rely on AWS to manage the underlying infrastructure. You just worry about the SQL. It is a "boring technology" choice, as the article that HN likes to post every now and then says. Litestream: What are the durability guarantees? (Are there any?) what would make me pick that over RDS or Cockroach? "given that SQLite is just a file, it’s incredibly easy to replicate" — sorry, that just doesn't click with me. What about a file is "inherently easy to replicate"? I would not know how to implement something that wouldn't be O(n) with the filesize. Ideally you'd just sync up changes, but how to get those? Normal DBs with streaming WALs (RDS/PG/MySQL) or Raft-based replication (Cockroach) have good answers there. Expensify: I skimmed the article, but AFAICT, the article talks about scaling QPS to a single SQLite file, but nothing about replication/durability.
- goodpoint 5y ago> We way over complicate things. ...because complexity pays people's salaries...