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

231–240 of 413 posts

Re: SQLite is all you need for durable workflows

#231

Post by zaptheimpaler »

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…

Personally I like Postgres for this reason too. Its extremely easy to run with Docker, I can dump data from all kinds of apps in there and I know it's not going to take any rearchitecting as soon as I need multiple concurrent writers.

I think docker is still super underappreciated so setting up any kind of server is seen as a chore. In my eyes it makes running tons of services like this very easy, so ill take the extra functionality, extensibility etc of postgres.

Re: SQLite is all you need for durable workflows

#232

Post by zaptheimpaler »

rsalus wrote: post #196
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 is a difference between concurrency in a distributed environment and concurrency on a single machine across processes. SQLite is incredibly useful for the latter. you seem like the inexperienced one to me..

SQLite does not support concurrent writes at all (on a single machine), a single writer process locks the entire database.

Re: SQLite is all you need for durable workflows

#233

Post by petre »

MattJ100 wrote: post #143
tasuki wrote: post #107

Earlier quoted context omitted.

> SQLite can work well for the parts of your system where there is naturally strong partitioning. Or the parts of your system that don't have big data and no need for massively concurrent writes. And that's the vast majority of systems!

You can do big data in SQLite. Concurrent writes, sure, I'd recommend something else. If you think the majority of systems require massively concurrent writes, I think you need to look a bit harder. SQLite is, after all, the most widely deployed database system, ever.

We recently just partitioned the data into many SQLite databases and got away with it. It's telemetry data from IoT devices: one device, one database. Backups are an easy rsync job now instead of streaming a multi gigabyte database with compression that take hours. Reporting will just open each database and aggregate multi device data into another database (Duckdb, SQLite or something else, we'll see). Duckdb is not readable when locked so it's probably also going to be SQLite. Even it it's going to spit out JSON it will go into SQLite rows instead of many files.

Re: SQLite is all you need for durable workflows

#234

Post by ummonk »

abtinf wrote: post #58
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 ar…

Why go through the trouble of shoehorning SQLite into a cloud database by getting solutions for HA/failover and DR, when you can just use Postgres off the shelf?

Re: SQLite is all you need for durable workflows

#235

Post by chrismorgan »

zimmi wrote: post #128
Thaxll wrote: post #118

I started using SQLite for a home project after years of reading about it, I was shocked at the poor type system coming from Postgres. It is really inferior, not sure why it gets so much praise. https://sqlite.org/datatype3.html https://www.postgresql.org/docs/current/datatype.html Working with date/time feels like using a 30years old database, nothing is enforced at insert. Really someone needs to explain why so man…

You can use strict tables: https://sqlite.org/stricttables.html

I don’t like strict tables, because it conflates two concerns, with one somewhat good and one distinctly bad effect (in my assessments).

The somewhat good: it gets rid of most of the weak typing. It still coerces, in line with other SQL databases, but at least a column will only store values of one type. Personally I’d prefer to opt out of the coercion. And I don’t think most ways of writing SQL (in applications especially, but also manually) will ever actually trigger the strict differences. So it doesn’t feel like it’s actually particularly useful.

The distinctly bad: you’re limited to six datatype names. You may well now want external documentation or load-bearing comments in your schema, and your application code may be hobbled, if it liked to infer types based on the datatype name. For example, in sqlx, SQLite datatype BOOLEAN can automatically map to Rust type bool https://github.com/transact-rs/sqlx/blob/75bc0487eb661da811b...>. Without that, you have to resort to a variety of less-pleasant techniques, such as selecting `done as "done: bool"` or overriding things in sqlx.toml.

I really, really wish they’d implement some form of CREATE TYPE and let that work with strict tables. If I could `CREATE TYPE BOOLEAN FROM INTEGER` and such, I’d be all in on strict tables.

Re: SQLite is all you need for durable workflows

#236

Post by ummonk »

wat10000 wrote: post #153
eterm wrote: post #85

Earlier quoted context omitted.

Or you can run postgres on the same machine as the application, which lets you much more easily migrate if the time comes when you need to scale to multiple application servers. There's a world between "local file" and "network DB server", running a DB server locally has lots of benefits from being able to easily query from outside if needed to forcing you to consider concurrency without the latency overhead of a net…

Now you've added a substantial dependency, and annoying setup requirements. Good luck doing this for a native app on mobile or desktop.

If someone is talking about "spinning up a separate machine" for Postgres, they're not talking about a desktop or mobile app...

Re: SQLite is all you need for durable workflows

#237

Post by bitexploder »

fcarraldo wrote: post #207
bitexploder wrote: post #7

I started setting up my workflows using Temporal. It deploys as relatively light weight local app. For an isolated local installation it uses SQLite. It makes the process of dealing with API retries and organizing workflows and tasks really simple. I recommend giving it a try. It is, philosophically, exactly what this article is suggesting, but it adds an incredibly rich and flexible interface for agents to work with…

It sounds like you’re running this mostly on a single machine? Temporal gets much more complex with scale. Cassandra isn’t fun to manage. Ringpop and TChannel are hard to debug when things go wrong. The SQL backend support doesn’t support horizontally scaled replicas (just single instance) due to consistency requirements. Depending on how your code is written, modifying code baked into workflows becomes complex, as a…

Yep. Individual systems with yolo agents doing stuff in isolation. I could see how it can get complex. Most distributed systems are. No free lunches I guess. I am not sure what the alternatives are at scale.

Re: SQLite is all you need for durable workflows

#238

Post by bitexploder »

platz wrote: post #209
bitexploder wrote: post #164

Earlier quoted context omitted.

I told an agent to set it up for me for some local stuff. It is written in Go. It has a painless path to run on a local SQLite DB. My agents use it to organize and coordinate workflows. It handles retries and long horizon tasks fine. As far as I can tell for the core workflows and tasks pieces it’s great. MIT license. Like anything it isn’t free to manage but it offers a lot in return. High reliability systems are ha…

whats an example of things that you have your agents do that use workflows and sqlite db

Autonomous C to Rust. Automated penetration testing and vuln validation.

Re: SQLite is all you need for durable workflows

#239

Post by franga2000 »

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…

And I don't understand the obsession with server-based databases for single apps. Especially in containerised setups, every "app" gets its own database anyways, and if the app is further broken down into services, they usually communicate between each other and not with a shared database. So in those cases, what do you gain by pulling the database out of the "process" and onto the other end of a socket? In most cases, absolutely nothing. So why bother?

Don't get me wrong, I've worked with plenty of server-based databases, including proper dedicated database servers. It's great tech and often the best tool for the job. But not always and I'd argue not in the majority of uses.

Re: SQLite is all you need for durable workflows

#240

Post by datadrivenangel »

petre wrote: post #233
MattJ100 wrote: post #143

Earlier quoted context omitted.

You can do big data in SQLite. Concurrent writes, sure, I'd recommend something else. If you think the majority of systems require massively concurrent writes, I think you need to look a bit harder. SQLite is, after all, the most widely deployed database system, ever.

We recently just partitioned the data into many SQLite databases and got away with it. It's telemetry data from IoT devices: one device, one database. Backups are an easy rsync job now instead of streaming a multi gigabyte database with compression that take hours. Reporting will just open each database and aggregate multi device data into another database (Duckdb, SQLite or something else, we'll see). Duckdb is not…

Check out Quack for DuckDB.
Post reply on HN