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.

> Sqlite smokes postgres on the same machine even with domain sockets [1]

for inserts only into singe table with no indexes.

Also, I didn't get why sqlite was allowed to do batching and pgsql was not.

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?

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

FYI, the color gradient on your website is an easy tell that it was vibe coded: https://prg.sh/ramblings/Why-Your-AI-Keeps-Building-the-Same...

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