MySQL
MySQL is the worlds most popular open-source relational database, powering much of the web (Facebook, Uber, Shopify). The InnoDB storage engine (default since MySQL 5.5) provides ACID transactions, row-level locking, and MVCC. MySQL is simpler to operate than PostgreSQL but has fewer advanced features. It excels at read-heavy workloads with straightforward schemas.
MySQL's clustered index is like a physical encyclopedia: the primary key IS the page order. Random UUIDs as PKs are like shuffling encyclopedia pages randomly — everything breaks. Auto-increment keeps the pages in order, just like volume numbers.
MySQL InnoDB uses a clustered index — the primary key IS the table storage order. This means primary key lookups are extremely fast (no separate heap fetch), but choosing a poor primary key (like a UUID) causes random I/O and page splits. Use auto-increment integers or ULIDs for primary keys. Secondary indexes store the primary key value (not a row pointer), so wide primary keys bloat every secondary index. Replication: MySQL supports async replication (default), semi-sync (waits for at least one replica to acknowledge WAL receipt), and Group Replication (Paxos-based, similar to a mini-Raft cluster). Replication lag is a common production issue — reads from replicas may return stale data. The query cache was removed in MySQL 8.0 because it caused more contention than benefit.
InnoDB internals: the buffer pool (innodb_buffer_pool_size, set to 70-80% of RAM) is the most important tuning parameter. The redo log (innodb_redo_log_capacity in MySQL 8.0.30+) determines crash recovery time and write throughput — larger redo logs mean fewer checkpoints but longer recovery. The undo log stores old row versions for MVCC and can cause tablespace bloat under long-running transactions. MySQL REPEATABLE READ uses next-key locking to prevent phantom reads in most cases, but this also means more lock contention compared to PostgreSQL SSI. The InnoDB change buffer batches secondary index updates for non-unique indexes, improving write performance. MySQL limitations vs PostgreSQL: no partial indexes, no expression indexes (until 8.0 functional indexes), limited JSONB query capabilities, no GiST/GIN/BRIN indexes, no table inheritance, weaker CTE support historically.
MySQL with InnoDB is a solid OLTP database that excels at read-heavy workloads with straightforward schemas. Its key architectural feature is the clustered index — the primary key determines physical row order, making PK lookups extremely fast. This means primary key choice is critical: always use auto-increment integers or ULIDs, never random UUIDs which cause page splits. For replication, I prefer semi-sync replication for data safety with Group Replication for automatic failover. The main watch-out is replication lag — I handle this by routing writes and their subsequent reads to the primary, or using semi-sync to ensure at least one replica is up to date. Compared to PostgreSQL, MySQL is simpler to operate but has fewer advanced features — no partial indexes, limited JSONB support, and weaker analytics capabilities.
Using random UUIDs as the primary key in MySQL InnoDB. Because InnoDB uses a clustered index, random UUIDs cause constant page splits and random I/O. Use auto-increment, UUIDv7 (time-sortable), or ULID instead.