DDL ends a transaction in MySQL. In my opinion, this is a showstopper and deal breaker. Good luck failing a migration in production.
- MySQL from about 1999-2007
- Postgres from 2006-present
- Oracle at a job 4 years ago
One of the things that absolutely made my brain explode is that Oracle, too, does not support DDL transactions. I discovered this during a pretty funny code review.
“Why are you wrapping this DDL in a transaction? That’s pointless.”
“Wait… Oracle doesn’t support DDL transactions?”
“What do you mean ‘DDL transactions’? That’s… should you be working on DB code at all?”
“Wait… you mean if a migration in Oracle fails half way through it just… leaves things half broken? Let me show you what this would look like in Postgres”
“WHAT THATS AMAZING!!!”
It just happened to be a great opportunity for both of us to expand our mental model of how different systems work. If you imagine someone whose mental model is "DML commands can be transactional, DDL modifies the database structure directly" then it's pretty reasonable for them to go "Uh... this guy doesn't seem to know the difference between modifying data and modifying tables..."
Look ma, no DBA required.