We skip the bullship text here and go in medias res. Due to problems RDBMS need locks. Here a first direct look into them comparing MySQL and PostgreSQL.
Comparison of blocking and non-blocking operations
Operation
MySQL
PostgreSQL
alter table … add column if not exists …
non-blocking
non-blocking
alter table … drop column if exists …
not supported
almost instant since 11
create index if not exists …
non-blocking
non-blocking (when used with CONCURRENTLY)
drop index if exists …
non-blocking
blocking if index is used in query otherwise not.
create index references
blocking
blocking (per documentation way less time consuming than mysql)
alter table … add constraint …
blocking
blocking but supported to minimize it via CONCURRENTLY
alter table … drop constraint …
blocking
yes (short-lived)
When writing DB migrations it is important to know the implications of these migrations during running. Yes, are going to change the database because thats why they are here. But moreover they also have to get executed. And especially when they are executed they can have massive impact. They can lock tables and hence the whole production environment is maybe down. In the above table there are listed a lot of operations (not all, sure but the important ones I excessively use).
The MySQL column lists the MySQL 8 operations. With MySQL 5 it is every worse and there you have to come up with other even custom solutions to do changes.