PostgreSQL vs MySQL: The Storage Decision That Can Make or Break Your Architecture

M Dsouza — Sat, 15 Aug 2026 13:54:29 +0000

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 OK immediately 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 = true for 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.

https://medium.com/@talitadsouza