3 ms·

This can happen even with something as robust as postgres. If you have a huge table undergoing lots of read/write activity, the locking needed to modify the tab

by timr 11y ago

This can happen even with something as robust as postgres. If you have a huge table undergoing lots of read/write activity, the locking needed to modify the table schema can take (essentially) infinite time, and/or slow your writes to a crawl and/or spike memory usage on your DB such that you no longer have faith in process completion. Example from my past: setting a default column value on a huge table undergoing hundreds of writes per second. Oops.

manigandham 11y ago
This doesnt really affect any advanced database that has online alters and workload management. It would just take longer but wouldn't affect any normal transactions. It's a solved problem unless the database just doesn't support it.
- timr 11y ago
  AFAIK, all of the common open-source databases have this problem. Postgres is better than mysql, but it still needs to do extensive locking when you alter a column. Under heavy read/write loads and/or with large tables, this can blow up in your face.
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 ago
  I 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 ago
  The 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.

by timr 11y ago

manigandham 11y ago

This doesnt really affect any advanced database that has online alters and workload management. It would just take longer but wouldn't affect any normal transactions. It's a solved problem unless the database just doesn't support it.

timr 11y ago
AFAIK, all of the common open-source databases have this problem. Postgres is better than mysql, but it still needs to do extensive locking when you alter a column. Under heavy read/write loads and/or with large tables, this can blow up in your face.

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 ago
I 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 ago
The 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.