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.

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)
  

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.

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.

- [1] https://data-star.dev

- [2] https://theartofpostgresql.com

There's no right answer. Hope that's helpful.

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

No offense, you wait. Like everyone's been doing for years in the internet and still do

- When AWS/GCP goes down, how do most handle HA?

- When a database server goes down, how do most handle HA?

- When Cloudflare goes down, how do most handle HA?

The down time here is the server crashed, routing failed or some other issue with the host. You wait.

One may run pingdom or something to alert you.

You need regional state, or you're still back hauling to the db with all the lag.

That only solves read latency not write latency. Unless you don't care about consistency.

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.

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.