published: true
description: A Principal Engineer's breakdown of ACID guarantees, InnoDB vs Heap storage, replication lag nightmares, and eliminating database single points of failure.
tags: systemdesign, architecture, postgres, mysql
canonical_url: https://medium.com/@talitadsouza/postgresql-vs-mysql-in-2026-why-most-teams-pick-the-wrong-database-249611b7805e
Now that we have covered entry points—DNS, reverse proxies, load balancers, CDNs, and in-memory caches—we are officially stepping into Phase 2: The Storage & Data Tier.
Stateless routing is relatively straightforward. Stateful storage is where a single architectural mistake causes data loss, cascading write locks, or financial discrepancies.
In senior system design interviews and production systems, drawing a generic "Database" box is not enough. You must justify why you picked a specific engine and how it behaves under failure.
1. The Core Architecture: PostgreSQL vs MySQL (InnoDB)
[ Client / Connection Pool ]
│
┌─────────────┴─────────────┐
▼ ▼
[ MySQL (InnoDB) ] [ PostgreSQL ]
• Multi-threaded model • Process-per-connection model
• Clustered B+Tree index • Heap-based storage + Secondary indexes
• In-place updates + Undo • Append-only tuples (MVCC vacuuming)
• Optimized for pure OLTP • Advanced types (JSONB, PostGIS, Vectors)
| Feature | MySQL (InnoDB) | PostgreSQL |
|---|---|---|
| Process Model | Multi-threaded (~256KB per connection) | Process-per-connection (Needs PgBouncer) |
| Primary Index | Clustered B+Tree (Data in index pages) | Heap storage (Indexes reference Tuple IDs) |
| MVCC Updates | In-place update + Undo Logs | Append-only tuple creation (Requires Autovacuum) |
| Best Used For | High-throughput web applications | Complex relations, JSONB, Geospatial, Vectors |
2. The ACID Principles: The ATM Analogy
Relational databases prioritize Immediate (Strong) Consistency over availability during network partitions (CAP theorem).
- Atomicity: You withdraw $100. The cash drops AND your ledger updates. If the dispenser jams, everything rolls back. All-or-nothing.
- Consistency: Account balances never violate domain rules (e.g., negative balances without overdraft protection).
- Isolation: Concurrent balance updates process sequentially without race conditions.
- Durability: Once committed, records are permanently flushed to the Write-Ahead Log (WAL) on disk.
3. The Asynchronous Replication Catch-22
To scale read throughput, standard practice is adding Read Replicas. However, asynchronous replication creates a tricky edge case:
- A user updates their profile picture (
Write -> Primary DB). - The Primary returns
200 OKimmediately to keep latency low. - The Primary streams the WAL log over the network to the Read Replicas.
- The user refreshes their feed immediately (
Read -> Read Replica). - The Bug: Because of replication lag (network delay), the replica serves the old profile picture.
Client ──(1. Write Profile)──► [ Primary DB ] ──(3. Async WAL Lag)──► [ Read Replica ]
▲ │
└────────(4. Immediate Read returns STALE Profile!)─────────────────────────┘
The Fix: Read-Your-Own-Writes Routing
Solve replication lag at the application routing layer:
- Set a short-lived flag/cookie on write operations (
read_primary = truefor 5 seconds). - Route that specific user's read requests to the Primary Database for 5 seconds.
- Route all global static reads from other users to Read Replicas.
4. Eliminating Single Points of Failure (SPOF)
[ Client Application ]
│
▼
[ PgBouncer Pooler ]
│
┌───────────────┴───────────────┐
▼ ▼
[ Primary Instance ] ◄──(Sync Phys)──► Standby (Multi-AZ) (Auto-Promoted by Patroni)
- Active-Passive Multi-AZ Failover: Maintain a synchronous standby instance in an isolated Availability Zone. If the active primary fails, an orchestrator (such as Patroni) promotes the standby in seconds.
- Connection Pooling: Put PgBouncer in front of PostgreSQL to prevent connection spikes and thread exhaustion from bringing down the cluster.
Originally published as part of my complete System Design Masterclass on Medium.
Top comments (0)