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

SQLite is all you need for durable workflows

obeli.sk

Post reply on HN
Print view View original

51–60 of 413 posts

Re: SQLite is all you need for durable workflows

#51

Post by fragmede »

refulgentis wrote: post #33
fragmede wrote: post #31

Earlier quoted context omitted.

So teach them. If you want to bring up computer science fundamentals, the question is where does SQLite sit with regards to the CAP theorem. Consistency, Availability, and Partition tolerance. SQLite isn't a distributed system, so there are no partitions to tolerate, so it's a CA system. Other databases make different tradeoffs. For systems that don't need concurrent writes , SQLite is pretty great! There are no user…

Strawman, no? "run an Obelisk server with a SQLite database", now we're distributed. SQLite is a nice local store. It's this server stuff that I don’t grok, well, yet. :)

What changed is SSDs. SSDs means that local access is faster than hitting the network. An expensive SAN stopped making sense because of this in specific cases. So for read heavy, or even read only database loads, you copy the SQLite file to the node that's processing the file, and just update that file whenever the data does get changed.

Re: SQLite is all you need for durable workflows

#52

Post by fragmede »

doctorpangloss wrote: post #42
levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

sqlite is more like a file format than a database. it competes with .xlsx. > "SQLite for everything" crowd is a little bit inexperienced. every time i see it in a real application, it becomes a huge focus of issues (for example: jellyfin, hermes, openwebui, comfyui)

What kind of issues commonly arise?

Re: SQLite is all you need for durable workflows

#53

Post by lanstin »

levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

I had very good results giving 1 SQL DB per go routine, so the accesses were serialized up front, on a very high volume (130K requests/second) service. Exact transactionality was not a product goal, and the SQLite was just to backup the in memory state. If we lost a little due to abend or something, that was ok (although for normal maintenance it caught SIGTERM and stopped the listen and then waited for in flight calls and then flushed the remaining changes to SQLite; then on startup it would read the SQLite into memory to populate before taking the listen; persistent storage across container runs, and never both reads and writes to the same file at the same time. (It also just closed the DB and opened a new one when it hit some limit of rows, so as not to fill the disk; the max size of the SQLite corresponded to the max size of the LRU map being served from in memory; then it just flipped A / B between "a full memory worth of data stored" and "the currently updating state." A lot easier than having to write out proto bufs to disk or whatever I would have done for transient (during restarts/maintenance) persistence.

Re: SQLite is all you need for durable workflows

#55

Post by malisper »

sevenzero wrote: post #32
levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

Isn't concurrency also limited by your machines disk speed for writes, what difference does it make if you write sequentially vs concurrently? Why does concurrency even matter for databases?

> Isn't concurrency also limited by your machines disk speed for writes, what difference does it make if you write sequentially vs concurrently? Why does concurrency even matter for databases?

For a simplified example, having three processes reading blocks X, Y, Z in parallel is much faster than having a single process read block X, wait for the read to finish, read block Y, wait for the read to finish, read block Z and wait for the read to finish.

Re: SQLite is all you need for durable workflows

#56

Post by peterspath »

levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

That’s why there are billions of SQLite databases right?

SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. SQLite is found in:

Every Android device Every iPhone and iOS device Every Mac Every Windows 10/11 installation Every Firefox, Chrome, and Safari web browser Every instance of Skype Every instance of iTunes Every Dropbox client Every TurboTax and QuickBooks PHP and Python Most television sets and set-top cable boxes Most automotive multimedia systems Countless millions of other applications

https://sqlite.org/mostdeployed.html

Re: SQLite is all you need for durable workflows

#58

Post by abtinf »

levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

There are many cases where SQLite + concurrent front end (like a go net/http server) can handle all the load that a service might ever conceivably have to handle, especially if allowed to scale up hardware over time. You can trivially scale up SQLite to, what, hundreds of thousands of tps?

The only thing you really give up is HA/failover and DR. But there are solutions to deal with those. And single-server systems are generally surprisingly robust (since, in the absence of very complex control planes, uptime goes down with more systems).

Re: SQLite is all you need for durable workflows

#60

Post by ai_fry_ur_brain »

teaearlgraycold wrote: post #23
levkk wrote: post #22

I don't understand this obsession with SQLite for real, production apps. SQLite is an embedded database, completely unsuitable for managing concurrency. This is what database _servers_ are for, e.g., Postgres, MySQL, etc. Their entire job is to allow you to modify data from multiple processes, on different machines, at the same time. This is a foundational principle of computer science. It seems to me that the "SQLit…

Well if you run a tiny single-threaded app then SQLite is a nice simplification over spinning up a separate machine for Postgres.

I use postgres for very simple apps. I have a Dockerfile I use in my boilerplate repo. It takes a single make cmd for me to build, start and run migrations. Its as simple as using sqlite.
Post reply on HN