SQL Databases

PostgreSQL and MySQL/InnoDB are the dominant open-source RDBMS engines, both offering full ACID compliance via MVCC (Multi-Version Concurrency Control), which lets readers and writers proceed concurrently without row-level read locks. PostgreSQL supports advanced types (JSONB, arrays, ranges, full-text), partial indexes, and table inheritance; MySQL InnoDB uses clustered primary-key indexes and achieves MVCC via undo log segments. Transaction isolation levels determine which concurrency anomalies are permitted, trading correctness for throughput.

Key Points

• MVCC creates a snapshot per transaction; readers see the database state as of their snapshot start, never blocking writers (PostgreSQL uses tuple visibility flags; MySQL uses undo logs).
• PostgreSQL WAL (Write-Ahead Log) guarantees durability; a commit is safe once the WAL record is flushed to disk — fsync=on is critical in production.
• InnoDB gap locks prevent phantom reads at REPEATABLE READ by locking the gap between index records, not just existing rows.
• Two-Phase Locking (2PL) is the classical isolation mechanism; MVCC replaces read locks but still uses write locks to prevent dirty writes.
• VACUUM in PostgreSQL reclaims dead tuple space and updates visibility maps; autovacuum must be tuned (vacuum_cost_delay, scale_factor) for high-churn tables.
• Statement-level vs row-level triggers affect isolation: a deferred constraint check runs at COMMIT time, not statement time.
• Connection overhead in PostgreSQL is ~5 MB per connection; pgBouncer (transaction-mode pooling) is essential beyond 200–300 active connections.
• MySQL binlog (ROW format) and PostgreSQL logical replication slots enable CDC (Change Data Capture) for downstream consumers.