MySQL vs PostgreSQL - Who Wins? | Systems Design Interview 0 to 1 with Ex-Google SWE - 8 mins
https://www.youtube.com/watch?v=n2Fluyr3lbc&ab_channel=Fireship
https://www.hellointerview.com/learn/system-design/deep-dives/postgres
NotebookLM Podcast on PostgreSQL Basics
NotebookLM Podcast on Intermediate PostgreSQL
→ Video I made on the topic:
https://youtu.be/oNCEXY83yuQ?si=xEI1ck3VKEbyEXyF
| Computational Intensity | Problem(s) | What you’re solving | Isolation Level | Why it works | Tradeoffs |
|---|---|---|---|---|---|
| Highest | Banking Transfers | ||||
| Inventory Reservation | Your business would be adversely affected by this transaction if it goes wrong | Serializable | Uses Serializable Snapshot Isolation to detect possible anomalies | The application needs to handle serialization failures (the error SSI throws) | |
| Mid | Financial calculations, order processing | The task you are trying to accomplish is necessary enough to protect from most concurrency issues, but not so crucial that you can’t tolerate occasional errors. | Repeatable Read | Uses a single snapshot for the entire transaction | Protect from some (but not all) phantom reads. |
| Best protection from concurrency issues given the overhead | |||||
| Lowest | Solves most problems, particularly UI / read-heavy problems. |
This is the default isolation level in Postgres. | Best tradeoff in terms of balancing performance and concurrency | Read *Committed (also Read Uncommitted in Postgres)
**This is the default setting | It uses a new snapshot for each statement, so transactions can only see committed changes. | Risks Phantom Reads and non-repeatable reads (might get different data from different snapshots). Protects from Dirty Reads. |