3 ms·
Postgres supports transactional DDL statements natively, and many alter table statements don't end up locking the table nearly as severely as some MySQL version
by josegonzalez 8y ago
Postgres supports transactional DDL statements natively, and many alter table statements don't end up locking the table nearly as severely as some MySQL versions do.
- michaeldejong 8y agoActually both lock for many (crucial) schema operators, and often severely enough to block your application from reading from the table(s) under change. I've been researching this stuff for a while. Check out http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-ddl-statements-mysqls-return/ http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-... . It's slightly outdated, but still holds.
- fipar 8y agoThat is true, but I wanted to share another angle that may or may not affect PostgreSQL while it continues to affect MySQL even as it has crash-safe (though not transactional) DDL now: these schema changes are online for the master, but are not replication-aware and can have impact in replication delay on servers down the hierarchy. For this reason alone I think we'll continue to use schema-change tools on MySQL even if the server itself becomes better at those. In the specific case of gh-ost, another good point is that migrations can be completely paused, which in MySQL is not true of online DDL.