Skip to content
RED SAGSkills
mediumDatabase Administrator interview question

What are the main operational differences between running MySQL (InnoDB) and PostgreSQL?

What the interviewer wants to hear

  • MVCC: PostgreSQL keeps old row versions in the table and needs vacuum; InnoDB uses undo logs and purge
  • InnoDB clusters tables by primary key; PostgreSQL uses heap tables with separate indexes
  • Replication: PostgreSQL streams WAL; MySQL uses binlog with GTIDs
  • Default isolation: PostgreSQL READ COMMITTED, InnoDB REPEATABLE READ
  • Different tools: pgBackRest and Patroni vs XtraBackup and Orchestrator or Group Replication

Model answer

Both are solid, but they behave differently day to day. PostgreSQL MVCC stores old row versions inside the table, so vacuum and autovacuum tuning, bloat and transaction ID wraparound are big topics. InnoDB keeps old versions in undo logs and cleans them with purge, so long transactions grow the undo history instead. InnoDB stores each table as a clustered index on the primary key, so key choice affects layout and secondary indexes include the primary key; PostgreSQL uses heap tables with separate indexes. PostgreSQL replicates by streaming WAL, while MySQL uses the binlog, usually with GTIDs and row format. Defaults differ too: PostgreSQL uses READ COMMITTED, InnoDB REPEATABLE READ. The tooling also differs: pgBackRest, Patroni and pgBouncer on one side; XtraBackup, Orchestrator or InnoDB Cluster and ProxySQL on the other.

Can you answer it under time pressure?

Take a timed medium round and get scored on every answer.

Start mock interview

More medium Database Administrator questions