One more reason to choose Postgres over MySQL

Almost always, I'd choose Postgres over any database for a new project because it's flexible and support multi-variances of scenarios that may require different performance characteristics.

However, I don't generally feel strongly about MySQL vs. Postgres. Either seems fine.... until I've learned MySQL doesn't support Transactional DDL. Transactional DDL means you can make multiple database schema changes in a single transaction. If one of the changes fails, the whole thing will be rolled back.

Transactional DDL is much better in the context of a database migration framework.

I use PlayFramework with Evolutions where I can write a migration that contains an up script and a down script.

The issue arises because the down scripts are almost always never tested upfront. When I rewrite the Evolution script, the framework would automatically apply the down script and the new up script. This is where things can go wrong. The previous down script might not truly reverse the previous up script in a compatible way. For example, an up script might rename a column from name to first_name, but the corresponding down script doesn't rename it back.

When an incompatible down script is applied, it would get the database schema in a weird state where you would have to untangle it manually before you can apply the new up script.

So, I have this idea where, in a local dev, we would apply the up script, the down script, and the up script again. This would immediately test that the up and down script reverse with each other completely before our code gets anywhere. Since Postgres supports Transactional DDL, if the mentioned process fails, it would not impact the current database schema at all. It would be pretty slick.

I had this discussion with the PlayFramework maintainer here: https://github.com/orgs/playframework/discussions/13296

And that is where I've learned Postgres is probably one of the rare databases that supports Transactional DDL. MySQL, Maria, and Oracle don't.

One more reason to use Postgres, I suppose.