4 ms·

We have a few large (not large in the scheme of things, but large for MySQL) tables that we can't add columns to without locking the table for an extended perio

by xmodem 6y ago

We have a few large (not large in the scheme of things, but large for MySQL) tables that we can't add columns to without locking the table for an extended period. In Postgres this operation is instant no matter the size of the table. This not the only problem, but it is the most pernicious

Thaxll 6y ago
MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...
pizza234 6y ago
I confirm the sibling post that adding a column is instaneous on MySQL 8 (although there are limitation), and point to the reference document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op.... Outside the context of db comparisons, and in relation to the specific case, if you don't have triggers/foreign keys on a given MySQL table, Gh-ost¹ solves the DDL locking issues. ¹=https://github.com/github/gh-ost https://github.com/github/gh-ost
- tomnipotent 6y ago
  > adding a column is instaneous on MySQL 8 Has been since MySQL 5.6 (released Feb `13).
  - zten 6y ago
    In the case that you're using MySQL 5.6-compatible Aurora RDS, you need to enable a special flag to get lock-free nullable column additions.
  - pizza234 6y ago
    Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data. ¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in... ²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...
spacemanmatt 6y ago
Adding a null column on PostgreSQL is instant. Import your new column data then apply defaults and constraints. This is how people have avoided length locking periods on production databases for years.

by xmodem 6y ago

Thaxll 6y ago

MySQL 8 aleviate that problem, although it's not perfect it's better than before: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in...

pizza234 6y ago

I confirm the sibling post that adding a column is instaneous on MySQL 8 (although there are limitation), and point to the reference document: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op.... Outside the context of db comparisons, and in relation to the specific case, if you don't have triggers/foreign keys on a given MySQL table, Gh-ost¹ solves the DDL locking issues. ¹=https://github.com/github/gh-ost https://github.com/github/gh-ost

tomnipotent 6y ago
> adding a column is instaneous on MySQL 8 Has been since MySQL 5.6 (released Feb `13).
- zten 6y ago
  In the case that you're using MySQL 5.6-compatible Aurora RDS, you need to enable a special flag to get lock-free nullable column additions.
- pizza234 6y ago
  Are you sure? Based on the official blog post¹, the INSTANT algorithm is new to MySQL 8.0. The manual of MySQL 5.6 indeed doesn't show any "Instant" option in the list of column operations². AFAIK, previous to MySQL 5.6, the fastest algorithm was INPLACE, which copies the data. ¹=https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-in... ²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...

spacemanmatt 6y ago

Adding a null column on PostgreSQL is instant. Import your new column data then apply defaults and constraints. This is how people have avoided length locking periods on production databases for years.