Interview › Databases & Caching
What is the difference between PostgreSQL and MySQL from an operator's view?
Databases & Caching · Basic level
Answer
PostgreSQL and MySQL differ operationally in storage behavior, MVCC implementation, replication, indexing, planner behavior, extensions, and maintenance. PostgreSQL MVCC keeps row versions for concurrent snapshots, and VACUUM/autovacuum is essential to clean dead tuples and prevent wraparound.
Technical explanation
PostgreSQL readers do not usually block writers, but old row versions create dead tuples and bloat if vacuum falls behind.
Long-running transactions can prevent cleanup and increase wraparound risk.
Operational checks should include dead tuples, transaction age, autovacuum timing, slow queries, and extension compatibility.
Hands-on example
PostgreSQL health checks:
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;
If values are high, investigate long transactions, tune autovacuum per hot table, and schedule safe VACUUM work.
Check how well your resume matches the role with our free resume checker— match score, ATS check, and the skills you're missing.
More Databases & Caching interview questions
- What is Amazon RDS, and what does it manage for you versus self-managed databases?
- What database engines does RDS support?
- What is the difference between RDS and Aurora?
- What is Multi-AZ in RDS, and how does automatic failover work?
- How long does an RDS Multi-AZ failover typically take, and what triggers it?
- What is the difference between Multi-AZ and a read replica?
- When would you use a read replica, and can it become a standalone database?
- Can a read replica be in a different region, and why would you do that?