Hacker News Reader

Hacker News

Comment by alberth | Hacker News Reader

Full thread

alberth

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%.

View on HN

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.

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.

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.

You'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.)

Mentioning RDS is an apples to oranges comparison. RDS is a fully managed service by a 3rd party.

Nevertheless, checkout 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-q...

EDIT: I don’t understand, why the downvote?

I 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.

"[Litestream] runs as a separate background process and continuously copies write-ahead log pages from disk to one or more replicas." - https://litestream.io/how-it-works

Instead of just continuously firing off questions when you acknowledge you’re not even reading the articles linked too … nonetheless, bedrockdb has replication and failover.

https://bedrockdb.com/

Can you really still call what Expensify uses SQLite? It is very heavily based on SQLite, but they say that they've "wrapped it in a custom distributed transaction layer named Bedrock". Feels like they've gotten much closer to a traditional DB architecture, with the related management overhead that people try to avoid by using SQLite.

http://www.bedrockdb.com/

I’d wager that many of the people pushing SQLite for 95% of use cases have not been involved in running a database for a large always-on business. Just because your SQLite database can handle the traffic doesn’t mean you’ve solved robust database infrastructure.

Now if they said postgres on RDS can be used for 95% of real world use cases, that I could get behind.

> is your business cool with a business disruption

Every few years in Seattle, there's a big snowstorm, and everything just shuts down. We live.

If a service goes down once in a blue moon, I could catch up my email, spend some time morale building with my team, or, god forbid, just go home because work isn't that important.

I mean … perhaps. But it's not my call, and of the places I've worked … that's not been the business decision that gets made.

> is your business cool with a business disruption, dataloss, or both when that VM goes down

Most businesses seem to handle downtimes okay; some money[1] is lost, maybe.

[1] And if the business feels that the expected (negative) value of the downtime is less than the cost of mitigating it, they simply (correctly) ignore it. For example, most businesses don't build a separate highway to their building in the event that a traffic jam causes fewer customers to show up. The expense is not worth the savings.

The customers we have today can tolerate some degree of outage. VM snapshots are usually the path.

Our roadmap has replication being done at the application level (i.e. business logic is aware of other nodes and policy around replication). Those specific entities which absolutely must survive would be synchronously replicated to additional witnesses on the network.

Note that this is about non-repudiation, not operational uptime. If we cared about always on operations, we would take a radically different path.

Abstractly, I think the role of ensuring business data survives some incident is not something to be pushed down to the database layer. When you pull this concern into business logic, you can produce far more robust solutions.

Buy something like nimble and with the correct config your system is down less than 30 minutes if something really bad happened. And no, that is usually not a severe problem for most businesses.

There is good middleware like activeMQ that can manage transactions in the meantime if your DB is on holidays.

A but of planning makes you very resilient against threats.

> 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/src/doc/begin-concurrent/doc/begin_co...

The 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.

> We way over complicate things.

...because complexity pays people's salaries...

Reply on news.ycombinator.com