Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Beyond the SQLite single-writer limitation with concurrent writes

turso.tech

Post reply on HN
Print view View original

51–60 of 75 posts

Re: Beyond the SQLite single-writer limitation with concurrent writes

#51

Post by grandfugue »

crazygringo wrote: post #22

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…

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?

Re: Beyond the SQLite single-writer limitation with concurrent writes

#52

Post by Quarrelsome »

glommer wrote: post #39
Quarrelsome wrote: post #36

Earlier quoted context omitted.

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.

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

Re: Beyond the SQLite single-writer limitation with concurrent writes

#53

Post by Quarrelsome »

grandfugue wrote: post #51
crazygringo wrote: post #22

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…

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" se…

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

Re: Beyond the SQLite single-writer limitation with concurrent writes

#54

Post by mchristen »

conradev wrote: post #26
kgwxd wrote: post #20

Earlier quoted context omitted.

Joins and Transactions are a pretty big part of SQL. I'm no expert, but if my quick search results are right, both are lost in the separate file per table scenario.

They work just fine! Joins: https://simonwillison.net/2021/Feb/21/cross-database-queries... Transactions: https://www.sqlite.org/atomiccommit.html#_multi_file_commit

Unfortunately if you use WAL mode the transactions are only isolated per database file.

https://www.sqlite.org/lang_attach.html

Re: Beyond the SQLite single-writer limitation with concurrent writes

#55

Post by solarkraft »

crazygringo wrote: post #22

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…

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/

Re: Beyond the SQLite single-writer limitation with concurrent writes

#56

Post by thayne »

conradev wrote: post #26
kgwxd wrote: post #20

Earlier quoted context omitted.

Joins and Transactions are a pretty big part of SQL. I'm no expert, but if my quick search results are right, both are lost in the separate file per table scenario.

They work just fine! Joins: https://simonwillison.net/2021/Feb/21/cross-database-queries... Transactions: https://www.sqlite.org/atomiccommit.html#_multi_file_commit

But then you need the write lock for all the databases (assuming your transaction involves writes).

Re: Beyond the SQLite single-writer limitation with concurrent writes

#57

Post by nick_ »

solarkraft wrote: post #55
crazygringo wrote: post #22

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…

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.

Re: Beyond the SQLite single-writer limitation with concurrent writes

#58

Post by avinassh »

conradev wrote: post #9

The single-writer limitation in SQLite is per-database, not per-connection. You can shard your SQLite tables into multiple database files and query across all of them from a single connection. I agree that "the single-writer limitation isn't just a theoretical concern", but it's also solvable without forking SQLite. ulimit's the limit! If your goal is resource maximization of a given computer, though, Postgres is lik…

> You can shard your SQLite tables into multiple database files and query across all of them from a single connection.

You mean using ATTACH statement, right? If you use WAL mode, then you cannot get transaction safety / ACID with ATTACH [0]

> If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.

Moreover, ATTACH do not support more than 125 databases, so that limits the shards to 125. [1]

ATTACH does not solve the concurrency problems. That's why SQLite also has a BEGIN CONCURRENT experimental branch

[0] - https://www.sqlite.org/lang_attach.html

[1] - https://www.sqlite.org/limits.html

Re: Beyond the SQLite single-writer limitation with concurrent writes

#59

Post by nextaccountic »

crazygringo wrote: post #22

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…

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

A single application can need multiple concurrent writes

Re: Beyond the SQLite single-writer limitation with concurrent writes

#60

Post by nextaccountic »

crazygringo wrote: post #50
glommer wrote: post #39

Earlier quoted context omitted.

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
Post reply on HN