Hacker News Reader

Comment by crazygringo | Hacker News Reader

While I understand that you could do this, I genuinely don't understand why.

SQLite and Postgres/MySQL/etc. occupy different niches. If you need massive concurrent writes, surely that's what Postgres/MySQL/etc. is for? Their engines are built around that from the ground up.

SQLite is built around a file that stores data for a single application, as opposed to being client-server with many clients. I've used it a ton for that, but the idea that your application would have so many threads needing to write concurrently that it would be slowed down by a file lock... just doesn't make sense to me.

What applications need this, and why wouldn't they use Postgres/MySQL/etc. if they need such a high level of concurrent performance? This feels like trying to adapt a small sports car to tow a semi-trailer. It doesn't seem like it's what it's meant for.

View on HN

sqlite is embedded. I understand that there might be scenarios in which multi-threaded sqlite is beneficial when an application has many concurrent writers. But taking a look at the company's website makes me wonder what the project's motivation is. The company offers a database service, which is a completely different scenario from embedded dbs. If the intention is to offer a cloud service, evolving from "sqlite" seems odd. The only benefit I can think of is that the new db service helps existing sqlite users migrate. My issue is that if I choose sqlite to store data locally in my browser or my cell phone, why do I suddenly want to store it in the cloud?

> My issue is that if I choose sqlite to store data locally in my browser or my cell phone, why do I suddenly want to store it in the cloud?

I don't think those solutions are necessarily that bad. There was one the other week that offered a means of guaranteed sync to a sqlite file in the cloud. Its nice to have the infra to allow users to hop around devices and have backups. What's weird to me, is trying to magic it into a performant multi-client db which the underlying technology was never designed to be.

I'll give you an example of what I'm doing with SQLite.

I have an app that organizes my movie collection. I use IMDB public datasets as data source. Because versioning is hard, data changes over time (remember who directed Matrix?) it's just easier to rebuild the database every time.

A naive import of datasets is ~131M inserts. It takes 10 minutes (of which 100M takes exactly 6 minutes). Another 10 minutes to further clean up the data, set up indexes, optimize and vacuum.

It's fine for the use case, but still it would be nicer to be able to achieve that in half the time.

The app has no dependencies, database is a single file I can store on S3 and with a little bit of magic use it as a real database - meaning I can run a complex queries over http on that whole database and exchange 50kb.

The client is a standalone HTML file with some JS. No need of server for client, no need for hosting for server except of that S3 bucket.

Sure, it's definitely a niche application, but, at least for me, it makes perfect sense. Start your app with zero deps and add only what you really need. There's too much projects that start with google-esque architecture, with plenty of microservices, only to reach MVP with technical debt the size of US treasury.

I don't understand what you are trying to say here, this doesn't seem to have anything to do with single writer limitations.

That's not concurrent writes. You would see zero speedup.

Your use case makes perfect sense for SQLite. It's a great example of what it's for. It won't benefit. That's my point.

Why not take something that’s great and make it support more use cases? People are doing it the other way around too: https://pglite.dev/

IMO this project kneecapped itself by being web-focused.

Perhaps they will support native builds some day?

Agreed, Sqlite docs specifically state that its not designed for this, or rather that the solution isn't appropriate, that database as a service is appropriate when you want multiple clients writing.

The entire "problem" is a side-effect of using the wrong tool for the job.

Linux was designed to run in home PCs, and we keep running it in supercomputers. It works just fine. Tools evolve.

Tools don't always need to evolve though. I don't want my hammer to evolve into a screwdriver too, or vice-versa.

Having separate tools for separate things makes sense when the things are different enough.

Yeah but this tool is evolving. You don't get a say on that, this is open source and devs do whatever they want with their time

But I do get to ask why. And that's what I'm asking.

"Tools evolve" isn't a use case. So what's the use case where you need concurrent writes for a database that isn't client-server?

its a file on disk, it relies on the file handling of the OS and an OS file system doesn't give you the same guarantees as a fully fledged db. Even if you try to fix this problem you're just going to end up with trade offs which to properly mitigate you'll end up approaching running some sort of service anyway. At some point you have to ask yourself if jumping through all these hoops has saved you more effort than just running a proper service.

You can justify it as a migration strategy between a file and a service but you're just hammering in screws if you try to force Sqlite to be a multi-client database.

> If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite[1].

[1] https://www.sqlite.org/whentouse.html

> SQLite is built around a file that stores data for a single application

A single application can need multiple concurrent writes

So that's what asking. When? Why? I've simply never encountered that, and can't imagine what a use case would be.

SQLite already supports lots of threads writing, they just all take turns. What application needs those writes to be concurrent?

I have such application. It scans hosts for gathering some information in several parallel threads. Then each thread stores part of this information in Sqlite database.

Reply on news.ycombinator.com