Which also conveniently makes you spend more money on tokens.
With agile, at least no one was charging you for it. Like sure, there’s a cost to the process. But there wasn’t direct agile.com profiting from you.
Meanwhile agentic workflows every solution to the problem is giving more money to the ai companies.
Model is bad? Made more expensive model. Still bad? Here’s an infrastructure that reads huge text files again and again making you consume tokens. Still bad? Here’s a way to easily spin up multiple agents at once so you can delegate work. Still bad? Here’s a new service that will automatically review code. Still bad? Maybe a biggger more expensive model will help.
>> With agile, at least no one was charging you for it.
Depends. There are companies [1] making loads of money out of it. Charging for certification and imposing the idea that either you are certified, or you are going to fail. They are even eating the lunch of PMI, as PMI (PMBoK) is turning into an Agile manual.
Where I work is being expended literally millions per year in Agile.
> With agile, at least no one was charging you for it.
Charging people for Agile via his company ThoughtWorks (which sold for 785M) is how Neville Roy Singham made the money to fund far left groups in the US from his base in China.
If it's an internet-required smarthammer without a handle that instead hits out on voice prompt, sometimes without enough or with too much force, sometimes knocks the nail out of the way and punches a hole, and sometimes hits you in the face, then yeah
> If it's an internet-required smarthammer without a handle that (...)
A suitable comparison would be to be faced with a nailgun and proceeding to criticize it on the grounds it doesn't have a handle, it doesn't pull nails, and it requires electricity to run.
While you complain about those detailed those using nailguns are an order of magnitude more productive at the same task, and can still carry a hammer in their toolbelt.
Well, no, the examples are basically the same. A wifi nailgun that sometimes shoots a nail straight through the board, doesn't shoot one at all, shoots it randomly to the side, shoots you with it, etc.
You can use brick as hammer. Doesn't mean brick makes a good hammer or that person who tells that brick is a bad hammer and doesn't work from them is bad workman.
Sqlite smokes postgres on the same machine even with domain sockets [1]. This is before you get into using multiple sqlite database.
What features postgres offers over sqlite in the context of running on a single machine with a monolithic app? Application functions [2] means you can extend it however you need with the same language you use to build your application. It also has a much better backup and replication story thanks to litestream [3].
The main problem with sqlite is the defaults are not great and you should really use it with separate read and write connections where the application manages the write queue rather than letting sqlite handle it.
Thing is though - either of those options is still multiple orders of magnitude faster than running on a remote host. Either will work, either will scale way farther than you reasonably expect it to.
I've slowly evolved from just writing to and looking up json files to using SQLite, since I had to do a bit more advanced querying. I'm glad I did. But the defaults did surprise me! I'm using it with php, and I noticed some inserts were failing. Turns out there's no tolerance for concurrent writes, and there's no global config that can be changed. Rertry/timeout has to be configured per connection.
I'm still not sure if I'm missing something, since this felt like a really nasty surprise, since it's basically unusable by default! Or is this php's PDO's fault?
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...
Actually, there are no inserts in this example each transaction in 2 updates with a logical transaction that can be rolled back (savepoint). So in raw terms you are talking 200k updates per second and 600k reads per second (as there's a 75%/25% read/write mix in that example). Also worth keeping in mind updates are slower than inserts.
> no indexes.
The tables have an index on the primary key with a billion rows. More indexes would add write amplification which would affect both databases negatively (likely PG more).
> Also, I didn't get why sqlite was allowed to do batching and pgsql was not.
Interactive transactions [1] are very hard to batch over a network. To get the same effect you'd have to limit PG to a single connection (deafeating the point of MVCC).
- [1] An interactive transaction is a transaction where you intermingle database queries and application logic (running on the application).
Not the person you are responding to, but sqlite is single threaded (even in multi process, you get one write transaction at a time).
So, if you have a network server that does BEGIN TRANSACTION (process 1000 requests) COMMIT (send 1000 acks to clients), with sqlite, your rollback rate from conflicts will be zero.
For PG with multiple clients, it’ll tend to 100% rollbacks if the transactions can conflict at all.
You could configure PG to only allow one network connection at a time, and get a similar effect, but then you’re paying for MVCC, and a bunch of other stuff that you don’t need.
Sqlite supports nested transactions with SAVEPOINT so each client can have their own logical transaction that can be rolled back. The outer transaction just batches the fsync effectively. So an individual client failing a transaction doesn't cause the batch to fail. But, a crash would cause the batch to fail. Because, it's a single writer, there's no rollback/retries from contention/MVCC.
You could try to imitate this in postgresql but the problem is the outer transaction does not eliminate the network hops for each inner/client transaction so you don't gain anything doing it and you still have the contention problem which will cause rollbacks/retries. You could reduce your number of connections to one to eliminate contention. But, then you are just playing sqlite's game.
An interactive transaction works like this in pseudo code.
beginTx
// query to get some data (network hop)
result = exec(query1)
// application code that needs to run in the application
safeResult = transformAndValidate(result)
// query to write the data (network hop)
exec(query2, safeResult)
endTx
How would you batch this in postgres and get any value? You can nest them all in a single transaction. But, because they are interactive transactions that doesn't reduce your number of network hops.
The only thing you can batch in postgres to avoid network hops is bulk inserts/updates.
But, the minute you have interactive transactions you cannot batch and gain anything when there is a network.
Your best bet is to not have an interactive transaction and port all of that application code to a stored procedure.
> How would you batch this in postgres and get any value? You can nest them all in a single transaction. But, because they are interactive transactions that doesn't reduce your number of network hops.
you can write it as stored procedure in your favorite language, or use domain socket where communication happens using through shared memory buffs without network involved.
In your post, I think big performance hit for postgres potentially comes from focus on update only statement, in SQlite updates likely happen in place, while postgress creates separate record on disk for each updated record, or maybe some other internal stuff going on.
Your benchmark is very simplistic, it is hard to tell what would be behavior of SQlite if you switch to inserts for example, or many writers which compete for the same record, or transaction would be longer. Industry built various benchmarks for this, tpc for example.
Also, if you want readers understand your posts better, you can consider using less exotic language in the future. Its hard to read what is and how is batched there.
> What features postgres offers over sqlite in the context of running on a single machine with a monolithic app
The same thing SQL itself buys you: flexibility for unforeseen use cases and growth.
Your SQLite benchmark is based in having just one write connection for SQLite but all eight writable connections for Postgres. Even in the context of a single app, not everyone wants to be tied down that way, particularly when thinking how it might evolve.
If we know our app would not need to evolve we could really maximize performance and use a bespoke database instead of an rdbms.
It seems a little aggressive for you to jump on a comment about how it’s reasonable to run Postgres sometimes with “SQLite smokes it in performance.” That’s true, when you can accept its serious constraints.
As a wise man once said, “Postgres is great and there's nothing wrong with using it!”
Interesting comparison. Have you done one for sqlite vs H2? Since you're using clojure, it seems a natural fit. According to what I've read, H2 is faster than sqlite, but it would be interesting to see some up to date numbers on it.
That's a good point. I'll give that a go and do a write up at some point.
The choice to use SQLite for me isn't actually about speed (LMDB is way faster).
I just get tired of people saying switch to Postgres for speed/scale. Postgres is great for many things but speed/scale is not its strength.
The main reason I use SQLite is the affordances and conveniences of an embedded database. Being able to easily have many databases. When databases are "cheap" it opens up loads of options. Of the embedded/file OLTP databases SQLite also has the largest toolbox: litestream, R*Tree indexes, JSONB, FTS, etc.
I haven't tried sqlite because the lack of data types is off putting to me. I want to like derby because they have gone to the trouble of making their database JPMS modular. But derby doesn't have UUID types and I want UUID types. Especially with Java 26 adding UUIDv7. I end up on H2 as a result.
It's funny, we're now trained to see these things where they can't possibly ever have been (like in this case with the 11 year old blog). It's as if we all collectively forgot that whatever the LLMs are doing comes from somewhere, so it's obviously going to be found out in the wild.
> Sqlite smokes postgres on the same machine even with domain sockets [1].
SQLite on the same machine is akin to calling fwrite. That's fine. This is also a system constraint as it forces a one-database-per-instance design, with no data shared across nodes. This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS and you need to serve traffic beyond your local region then you have no alternative other than to have more than one instance of your service running in parallel. You can continue to shoehorn your one-database-per-service pattern onto the design, but you're now compelled to find "clever" strategies to sync state across nodes.
Those who know better to not do "clever" simply slap a Postgres node and call it a day.
> SQLite on the same machine is akin to calling fwrite.
Actually 35% faster than fwrite [1].
> This is also a system constraint as it forces a one-database-per-instance design
You can scale incredibly far on a single node and have much better up time than github or anthropic. At this rate maybe even AWS/cloudflare.
> you need to serve traffic beyond your local region
Postgres still has a single node that can write. So most of the time you end up region sharding anyway. Sharding SQLite is straight forward.
> This is fine if you're putting together a site for your neighborhood's mom and pop shop, but once you need to handle a request baseline beyond a few hundreds TPS
It's actually pretty good for running a real time multiplayer app with a billion datapoints on a 5$ VPS [2]. There's nothing clever going on here, all the state is on the server and the backend is fast.
> but you're now compelled to find "clever" strategies to sync state across nodes.
That's the neat part you don't. Because, for most things that are not uplink limited (being a CDN, Netflix, Dropbox) a single node is all you need.
May be an "out" there question, but any tech book suggestions you'd recommend that can teach an average dev on how to build highly performant software with minimal systems?
I feel like the advice from people with your experience is worth way way way way more than what you'd hear from big tech. Like what you said yourself, big tech tends to recommend extremely complicated systems that only seem worth maintaining if you have a trillion dollar monopoly behind it.
Not specific books per say. Though I'd advise starting with some constraints. As that really helps you focus.
Your reading/learning material can spin out of those constraints.
So for me my recent constraints were:
1. Multiplayer/collaborative web apps built by small teams.
2. Single box.
3. I like writing lisp.
So single box pushes me towards a faster language, and something that's easy to deploy. Go would be the natural choice here, but I want a lisp so Clojure is probably the best option here (helps that I already know it). JVM is fast enough and has a pretty good deployment story. Multiplayer web apps, pushed me to explore distributed state vs streaming with centralised state. This became a whole journey which ended with Datastar [1]. Thing is immediate mode streaming HTML needs your database queries to be fast and that's how I ended up on SQLite (I was already a fan, and had used it in production before), but the constraints of streaming HTML forced me to revisit it in anger.
Your constraints could be completely different. They could be:
1. Fast to market.
2. Minimise risk.
3. Mobile + Web
4. Try something new.
Fast to market might mean you go with something like Rails/Django. Minimise risk might mean you go with Rails because you have a load of experience with it. Mobile + web means you read up on Hotwire. Try something new might mean you push more logic into stored procedures and SQL queries so you can get the most out of Postgres and make your Rails app faster. So you read The Art of Postgresql [2] (great book). Or maybe you try hosting rails on a VPS and set up/manage your own postgres instance.
A few companies back mine were:
1. JVM but with a more ruby/rails like development experience.
2. Mobile but not separate iOS/Android projects.
3. Avoid the pain of app store releases.
4. You can't innovate everywhere.
That meant Clojure. React native. Minimal clients with as much driven from the backend as possible. Sticking to postgres and Heroku because it's what we knew and worked well enough.
Thanks for the thoughtful reply. I'll have to look at data star again, I understand HTMX fits in a similar role (not reactive tho) but at the moment I've just been doing basic go templates for my CRUD like stuff.
Do appreciate the comments about forcing constraints, we see how constraints can help in other creative endeavors makes sense it would also apply to programming too.
Backups, litestream gives you streaming replication to the second.
Deployment, caddy holds open incoming connections whilst your app drains the current request queue and restarts. This is all sub second and imperceptible. You can do fancier things than this with two version of the app running on the same box if that's your thing. In my case I can also hot patch the running app as it's the JVM.
Server hard drive failing etc you have a few options:
1. Spin up a new server/VPS and litestream the backup (the application automatically does this on start).
2. If your data is truly colossal have a warm backup VPS with a snapshot of the data so litestream has to stream less data.
Pretty easy to have 3 to 4 9s of availability this way (which is more than github, anthropic etc).
My understanding is litestream can lose data if a crash occurs before the backup replication to object storage. This makes it an unfair comparison to a Postgres in RDS for example?
Last I checked RDS uploads transaction logs for DB instances to Amazon S3 every five minutes. Litestream by default does it every second (you can go sub second with litestream if you want).
Interesting - I had not looked deep into this before.
Is suppose the difference is RDS has high 9s, whereas in the Litestream case the frequency of crashes is tied to your application code and deployment process. In practice this will take more work to reach the same uptime?
> Backups, litestream gives you streaming replication to the second.
You seem terribly confused. Backups don't buy you high availability. At best, they buy you disaster recovery. If your node goes down in flames, your users don't continue to get service because you have an external HD with last week's db snapshots.
If anything backups are the key to high availability.
Streaming replication lets you spin up new nodes quickly with sub second dataloss in the event of anything happening to your server. It makes having a warm standby/failover trivial (if your dataset is large enough to warrant it).
If your backups are a week old snapshots, you have bigger problems to worry about than HA.
> If anything backups are the key to high availability.
Not really. Backups are complementary in disaster recovery. They play no role in high availability. Putting your data in cold storage plays no role in keeping your system up and handling traffic.
> Streaming replication lets you spin up new nodes (...)
You seem to be confused. Replication and backups are two entirely separate things. Replication is used to preserve consistency across a distributed system and improve fault tolerance, whereas backups just means you are able to recover the state of your system at each checkpoint. Either you're using a word while giving it a new personal meaning, or you're confusing concepts.
Depends how you do your backups. If you do them by replicating. They are both. See litestream [1].
With SQLite this is even more obvious as a database is just a file (or three in the case of WAL). Which means you can replicate to not just another machine (or any file system) but much more resilient object storage like S3 (most cloud provider offer S3 compatible object storage).
This is a disingenuous scenario. SQLite doesn't buy you uptime if you deploy your app to AWS/GCP, and you can just as easily deploy a proper RDBMS such as postgres to a small provider/self-host.
Do you actually have any concrete scenario that supports your belief?
> SQLite doesn't buy you uptime if you deploy your app to AWS/GCP
This is...not true of many hyperscaler outages? Frequently, outages will leave individual VMs running but affect only higher-order services typically used in more complex architectures. Folks running an SQLite on a EC2 often will not be affected.
And obviously, don't use us-east-1. This One Simple Trick can improve your HA story.
> This is...not true of many hyperscaler outages? Frequently, outages will leave individual VMs running but affect only higher-order services typically used in more complex architectures. Folks running an SQLite on a EC2 often will not be affected.
You're trying too hard to move goalposts. Look at your comment: you're trying to argue that SQLite is immune to outages in AWS even when AWS is out, and your whole logic lies in asserting the hypothetical outage will be surgically designed to somehow not affect your deployment because it may or may not consume a service that was affected.
In the meantime, the last major AWS outage was Iran blowing up a datacenter. They should have just used SQLite to avoid that, is it?
All I'm saying is that people mention HA, when there isn't a need for it or when most people are fine with some downtime.
For example,
> When AWS/GCP goes down, how do most handle HA?
When they go down, what do most do? Honestly, people still go about their day and are okay. Look how many systems do go down. What ends up happening? An article goes out that X cloud took out large parts of the internet.. and that's it.
Even when there's ways of doing it, they just go down and we accept it. I never said this doesn't go down or can't go down, it's just that it's okay and totally fine if it does.
> All I'm saying is that people mention HA, when there isn't a need for it or when most people are fine with some downtime.
I don't think it's smart to just cherry pick the design constraints you feel don't apply to you, and proceed to argue others should also ignore them.
Just because you are ok to let your pet project crash and be out for long periods of time, why do you assume it's ok for everyone to do the same?
Think about it for a second: what would be the impact of a storefront to crash during a black Friday type event? Do you think people don't get fired for dropping the ball in these circumstances? Heck, you have papers that document how a few extra milliseconds of latency in a store page is correlated to measurable drops in revenue, and here you are claiming that having businesses crash is no biggie.
Nonsense. You can't outrun physics. The latency across the Atlantic is already ~100ms, and from the US to Asia Pacific can be ~300ms. If you are interested in performance and you need to shave off ~200ms in latency, you deploy an instance closer to your users. It makes absolutely no sense to frame the rationale around performance if your systems architecture imposes a massive performance penalty in networking just to shave a couple of ms in roundtrips to a data store. Absurd.
https://antonz.org/sqlite-is-not-a-toy-database/ — 240K inserts per second on a single machine in 2021. The problem you describe is real, but the TPS ceiling is wrong by three orders of magnitude on modern hardware.
Do you know why it is a toy? Because in a real prod environment after inserting 240k rows per second for a while you have to deal with the fact that schema evolution is required. Good luck migrating those huge tables with Sqlite ALTER table implementation
Try doing that on a “real” DB with hundreds of millions of rows too. Anything more than adding a column is a massive risk, especially once you’ve started sharding.
Yes it might be risky. But most schema evolution changes can be done with no or minimal downtime even if you have to do then in multiple steps. When is a simple ALTER going to be totally unacetable if youare using Sqlite?
This doesn't seem like a toy but you know... realizing different systems will have different constraints.
Not everyone needs monopolistic tech to do their work. There's probably less than 10,000 companies on earth that truly need to write 240k rows/second. For everyone else, we can focus on better things.
I’ve seen multimillion dollar “enterprise” projects get no where close to that. Of course, they all run on scalable, cloud native infrastructure costing at least a few grand a month.
I mean you can easily do 100K TPS on a M1 with sqlite and a dynamic language. With sub 100ms latency.
People don't do it because it's not fashionable (the cool kids are all on AWS with hundreds of containers, hosting thousands micro services, because that's web scale).
Well, transactions in this context are business transactions, which may involve 1 or N remote calls. Imagine checks against no fly lists, fraud detection, flight delay and so on. Speed of light is also another concern. So it’s not as simple as doing 35k TPS on a local SQL database.
True, but a lot of those checks will be against a local snapshots of the data.
> But yes, you don’t always need cool technologies.
That's kinda the irony mainframes are incredibly cool piece's of tech, just not fashionable. They have insane consistency guarantee at the instruction level. Hot swapping features etc. Features you'd struggle to replicate with the dumpster fire that is modern microservice based cloud computing.
So the bigger models hallucinate better causally hitting more real problems?
reply