Every month, we do an external database import into our production PostgreSQL database. In a single transaction, we drop dozens of tables, create new ones with the same names, insert hundreds of thousands of rows, and recreate indexes, all in a single transaction. It works flawlessly.

wow! have you tested for network errors in any of these steps? Will it rollback automatically if there are any errors?

If it's in a transaction, it will do the same thing it would do for any failed transaction (roll it back). DDL isn't special.

I wouldn't use that particular thing against MySQL. DDL normally supposed to be always outside of a transaction, it's just PostgreSQL feature that you can use them inside and be able to rollback. BTW I'm convinced you also can drop table within a transaction in PostgreSQL.

No, MySQL stands out here. Postgres, SQL Server, DB2, and Firebird all give at least some way to do some major DDL transactionally. Usability varies (e.g. Oracle supports a very specific kind of change that is not its normal DDL statements), but it's at least possible.

https://wiki.postgresql.org/wiki/Transactional_DDL_in_Postgr...

That MySQL autocommits is also even worse than just "doesn't support it."

> DDL normally supposed to be always outside of a transaction

A basic element of the relational model is that metadata is stored as relational data and that the same guarantees that apply to manipulating main data in the database apply to manipulating the schema metadata.

It's true that many real relational databases compromise on this element in various ways at times, but it is absolutely not the case that DDL “is supposed to be” non-transactional.