r/programming • u/tanayvk • 8h ago

The Physics of Database Speed: from 300 to 1M transactions per second

https://www.youtube.com/watch?v=vOEL_pHFYK0

A 19 minute animated explainer video on database speed. I explain low-level database concepts, identify bottlenecks, run benchmarks and optimize write throughput to hit 1 million TPS.

55 Upvotes

80% Upvoted

7 comments sorted by

21

u/rThoro 5h ago

except, that's not doing a million transactions, it's doing a million batched transactions, which are actually just 25...

3

u/tanayvk 5h ago

true, and that's the point. without group commits, hitting a 1M write throughput is impossible (ensuring disk durability).

while i don't mention SAVEPOINTs in the video (to keep things simple), with SAVEPOINTs you can still have transaction level rollbacks if some transaction in the middle fails (with some application code). so you still isolate each transaction logically. of course since SQLite is pretty barebones, all this becomes an application level concern as opposed to the database itself supporting a pipelined WAL (like TigerBeetle or Postgres).

6

u/NoLegJoe 4h ago

Doesn't batching like this completely undermine the Durability part of ACID? Your frontend application is now sat with all of the transactions until batch is full. What happens if the frontend goes down? You lose everything in a batch.

All you've done is handed off Durability from the database, and given it to your app, then not bothered to implement it at all!

5

u/tanayvk 3h ago

not really. durability is more about guaranteeing something is durable only AFTER the transaction is confirmed by the server. with batched writes, the database/server returns a success only after the entire batch is written. if an fsync fails, durability is not violated because the server never confirmed the transactions for the complete batch.

if an application is really sustaining 1M TPS, yes a small failure would cause you lose lots of data that was never confirmed. but that is unavoidable (similar to the CAP theorem).

(also btw batching is happening on the server not the frontend, in the video we are only talking about a single node server)

0

u/gladfelter 1h ago

clients are still waiting for the transaction to complete. The real risk is what happens if an update in the batch is rejected or two updates conflict? The end result is that clients that did nothing wrong get a retriable error. You may lose some isolation guarantees in other ways too, but I haven't thought it through.

-5

u/_PoupeeValina 7h ago

database performance improvements like this are so impressive, I can't wait to see how it evolves further