Hacker News Reader

Comment by pizza234 | Hacker News Reader

Can you elaborate on the specifics of the scalability problems, and how the different databases do differently on them? It's an interesting subject.

We're having scalability problems with our RDBMS, but it's something RDBMSs in general don't solve.

View on HN

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

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

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

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.

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

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