PostgreSQL Versus MySQL - Isn't a Database Just a Database

## Overview

This talk challenges the notion that databases are interchangeable, using an analogy of comparing a Ferrari sports car to a John Deere tractor—both have horsepower but serve fundamentally different purposes. The speaker, with over 30 years of database experience based in Barcelona, emphasizes that meaningful database comparison requires understanding architectural differences rather than relying on bias or anecdotal evidence from years past.

## Key Argument on Comparison

The speaker addresses the "Law of the Instrument"—the tendency to assume your familiar tool is superior. Common online comparisons are flawed because few people deeply understand both PostgreSQL and MySQL internals. Rather than declaring one universally better, the talk stresses that requirements and access patterns should drive selection. Both databases are relational, open-source, decades-old, production-stable, and growing in enterprise relevance, with PostgreSQL showing particularly rapid adoption in recent years.

## MySQL Architecture (InnoDB)

MySQL features a distinctive two-layer architecture: a SQL processing layer above an engine layer (InnoDB is the default). This enables flexibility but means 99.99% of users don't leverage alternative engines. InnoDB treats all tables as indexes—the primary key is the table itself. Rows are found by primary key and data is sorted by it, enabling efficient range scans. Sequential inserts perform exceptionally well because InnoDB stores insertion direction and packs pages nearly full before opening new ones. Storage uses rollback segments to store old versions of rows separately from the main table. Secondary indexes contain the primary key value, which increases index size if the primary key is large. An empty page disappears from indexes; the engine can merge adjacent pages to recover space, theoretically yielding no fragmentation in worst cases.

## PostgreSQL Architecture (Heap Model)

PostgreSQL stores tuples in heaps, identified by ctid (page number + offset within page). Data is stored in insertion order, so random inserts create fragmentation. Crucially, old row versions remain in the heap alongside new versions. Transaction metadata (xmin, xmax, ctid) tags each row. This design makes inserts very fast but creates storage overhead. Indexes reference ctid, which remains smaller than full primary keys, keeping secondary indexes leaner than MySQL's.

## Multiversion Concurrency Control (MVCC) Differences

Both use MVCC but implement it differently. PostgreSQL marks old versions as deleted and creates new ctids in the heap, requiring periodic index updates and table reorganization. MySQL assumes commit will succeed, modifying rows in-place and storing old versions in rollback segments. PostgreSQL's Heap-Only Tuple (HOT) updates can avoid secondary index changes under certain conditions.

PostgreSQL visibility checks require consulting transaction IDs, commit logs, and past transaction state. MySQL's approach is simpler because changes are assumed to commit. Long-running transactions in PostgreSQL can cause problems: old versions accumulate in the heap, consuming space and requiring many rollback segment accesses in MySQL.

## Vacuum and Transaction ID Management

PostgreSQL's background vacuum process removes old versions and freezes rows using a special transaction ID to prevent transaction ID wrap-around—a critical operation that cannot be disabled. MySQL has no equivalent requirement; rollback segments handle versioning transparently.

## Notable Differences

PostgreSQL offers materialized views, more sophisticated roles implementation, and filters—features absent or poorly implemented in MySQL. MySQL excels at high availability through group replication (similar to Galera clustering). Threading models differ: MySQL uses threads while PostgreSQL uses processes. Memory management differs philosophically—MySQL actively manages memory while PostgreSQL relies on the operating system, trading safety for flexibility on multi-application servers.

## Key Takeaway

Database selection should not default to the familiar tool. Understanding architectural differences—heap vs. B-tree storage, MVCC implementation, indexing strategies—enables informed decisions based on actual workload requirements rather than bias or outdated comparisons.