Hacker News Reader

Comment by xmodem | Hacker News Reader

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

View on HN

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-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

> adding a column is instaneous on MySQL 8

Has been since MySQL 5.6 (released Feb `13).

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-in...

²=https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-op...

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.

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

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.

Reply on news.ycombinator.com