2 ms·
IIRC, this isn't true for Postgres. Many common schema alterations in Posgres are lock-free (non-blocking). In MySQL, these alterations used to be locking (ie
by beambot 11y ago
IIRC, this isn't true for Postgres. Many common schema alterations in Posgres are lock-free (non-blocking). In MySQL, these alterations used to be locking (ie. O(n) in number rows)... but perhaps it has gotten better? (Wouldn't know; don't use MySQL.) I did a quick cursory google search to find some substantiation:
- http://www.estelnetcomputing.com/index.php?/archives/12-Locking-when-altering-a-table-in-postgres-vs.-mysql.html http://www.estelnetcomputing.com/index.php?/archives/12-Lock...
- http://stackoverflow.com/a/6542757 http://stackoverflow.com/a/6542757
- SapphireSun 11y agoI ran into this problem with MySQL. We have a table of user submissions that is millions of rows but the amount of time it was taking to migrate the test server was unacceptable downtime in production. We ended up using https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/2.1/pt-online-sc... to do an online update.
- timr 11y agoThe relevant bit from your first link: "postgres will update the table structure and there is no locking unless you do something that needs to alter existing rows." So, altering a column (e.g. adding a default) will trigger the problem. Adding a new column or table will not.