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

141–150 of 413 posts

Re: SQLite is all you need for durable workflows

#141

Post by andersmurphy »

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…

Thing is SQLite scales better than both those network databases [1] if you're prepared to stick with one big machine (+ a standby).

This is even more obvious when you start doing transactions processing an row locks across the network limit you to 1-3k TPS that you cannot scale out of (Pareto distribution is merciless).

[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...

Re: SQLite is all you need for durable workflows

#142

Post by themafia »

bob1029 wrote: post #48
shukantpal wrote: post #19

SQLite is surprisingly performant for single node applications even when comparing to Postgres. Postgres consumes a lot more memory and requires IO to hop through IPC whereas you can keep everything in process in SQLite with a shared connection pool. I've been testing different storage engines for my agent harness and I can get up to 7.5k concurrent sessions on a single vCPU with SQLite whereas Postgres crashes or ru…

When used properly, SQLite is effectively an in-process method invoke. If the only remaining things in the way are your runtime, kernel, file system and a local NVMe storage device, you may find it massively outperforms hosted alternatives. Leaving the current thread is where you lose the game in terms of latency. SQLite can work on timescales measured in microseconds if you don't force interthread communication.

> an in-process method invoke.

Pedantically it's an in process virtual machine for operating on structured data. Which is precisely where it shows it's weakness, in my experience, when you end up with complicated table structures and complex join mechanics you then need to start thinking ahead of the query planner and VM code a bit in order to maintain reasonable performance.

There are more than a few unusual things worth knowing:

https://sqlite.org/optoverview.html

Re: SQLite is all you need for durable workflows

#143

Post by MattJ100 »

tasuki wrote: post #107
jph00 wrote: post #49

Earlier quoted context omitted.

You seem to have a rather limited understanding of what kinds of concurrency exist and how those needs are best met. Whether something is a server or not is not very relevant to this discussion. SQLite is an excellent production db for many real world workloads, as has been widely documented. It is very different to Postgres, so requires learning a whole new skill set. One way to think about it is that SQLite can wor…

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

Re: SQLite is all you need for durable workflows

#144

Post by formerly_proven »

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…

This is the fault/price of backwards compatibility. Most users of SQLite should just fire off a few pragmas on each connection:

    PRAGMA journal_mode = WAL
    PRAGMA foreign_keys = ON
    # Something non-null
    PRAGMA busy_timeout = 1000
    # This is fine for most applications, but see the manual
    PRAGMA synchronous = NORMAL
    # If you use it as a file format
    PRAGMA trusted_schema = OFF
You might need additional options, depending on the binding. E.g. Python applications should not use the defaults of the sqlite3 module, which are simply wrong (with no alternative except out-of-stdlib bindings pre-3.12): https://docs.python.org/3/library/sqlite3.html#transaction-c...

Also use strict tables. https://www.sqlite.org/stricttables.html

While it has bad ergonomics, you can also use CHECK constraints. For example, using sqlite's built in date support, it's possible but awkward:

    CHECK (
      date(my_date_col) IS NOT NULL
      AND my_date_col = date(my_date_col)
    )
The IS NOT NULL is needed because date returns NULL for invalid dates; the other check because it also accepts Julian days (date('2026') is sometime during year 4707 BC).

Re: SQLite is all you need for durable workflows

#145

Post by mollerhoj »

zimmi wrote: post #140
pseudalopex wrote: post #133

Earlier quoted context omitted.

This could enforce dates are strings. They wanted to enforce dates are dates I thought.

Storing dates as INTEGER (year * 10000 + month * 100 + day, e.g. 20260530) is not so bad. Proper date / timestamp types would be great though.

"feels like using a 30years old database"

Re: SQLite is all you need for durable workflows

#146

Post by skybrian »

chrsstrm wrote: post #101
skybrian wrote: post #95

Instead of "just use Litestream," I'd like to see a review of different object stores one could use and which ones work well with Litestream. Is there a nice object store I could run in another Linux VM? As a hobbyist, which services providing an S3-like API make the most sense?

Litestream is just the replication layer, it works with any S3 compatible storage, and all of these in their guide as well https://litestream.io/guides/#replica-guides

No, I know that already. That's not a recommendation. Some storage layers have to be better than others on price, reliability, and so on?

Think Wirecutter, not install guide.

Re: SQLite is all you need for durable workflows

#147

Post by wat10000 »

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…

Someone with experience would know that concurrency isn't a universal requirement.

Re: SQLite is all you need for durable workflows

#148

Post by Scaevolus »

strbean wrote: post #74
sevenzero wrote: post #44

Earlier quoted context omitted.

I guess I need to dive deeper into this as I do not understand the implications you gave me, but I appreciate the attempt. Generally I understand why concurrency is good in many cases, I just dont get why its important for database stuff too. Edit: thanks for clarifying in the edit, makes a lot more sense.

Imagine if every tweet had to go through a one-at-a-time queue before being persisted. There's about 6000 tweets per second, so you would have to be able to save them at <0.17ms per tweet or else you would become backlogged. If you are getting backlogged, you have to buffer those incoming tweets somewhere until they can be writted, and eventually that buffer gets full and you start losing tweets.

Sqlite can quite easily do 5000+ insert+commits per second on typical NVMe drives.

Speed is rarely the constraint that makes it unsuitable for an application.

Re: SQLite is all you need for durable workflows

#149

Post by OutOfHere »

pibaker wrote: post #67
peterspath wrote: post #56

Earlier quoted context omitted.

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…

GP calls out concurrency as a weakness of SQLite. Most of the examples here don't experience the same load even a moderately sized web service experience day to day. And no, being a part of the python standard library doesn't means it is being used by the average python user. These days I'd say at least half of them are just there for machine learning.

SQLite is good for read-concurrency, not great for write-concurrency.
Post reply on HN