Hacker News Reader

Comment by xmodem | Hacker News Reader

At my current gig, we've been burned pretty badly by scalability problems with MySQL, and the prevailing wisdom is that new functionality and services that have to deal with data at scale should be built on Cassandra. My cries of "we wouldn't have this problem with Postgres" have mostly fallen on death ears.

View on HN

I don't see where PG would scale better than MySQL since InnoDB has historically been more performant than PG.

Historically, MySQL has been better at single concurrency setups, like your average Wordpress site whose main reader is the person posting to it. PostgreSQL is usually much better in high volume scenarios.

For instance, suppose you have a query that requires a full table scan. In PostgreSQL, that query comes in and the DB starts the scan. Now, a second such query comes in. PostgreSQL notes “I’m on row 10,000” and continues running one single scan, sending results to both queries. When it reaches the end of the table, it marks the first query as complete, then goes back to the beginning of the table and starts scanning again, sending results to the second query up until it reaches row 10,000. Now the second query has seen every row in the table and it’s finished.

Now imagine 100 such queries arrive. Rather than doing 100 full table scans, PostgreSQL satisfies all of them concurrently, taking at most 2 full scans (assuming the 100th queries joins in on the very last row of the first scan).

PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed. You can vertically scale a single-instance PostgreSQL server a lot higher than many people would believe.

> PostgreSQL has a million such optimizations under its hood that make it happily chug away even when it’s getting slammed

So does MySQL. Facebook, Google, and Percona have contributed many upstream patches to harden and improve MySQL over the years. MySQL also has much better observability to find issues causing problems, from index_statistics to hunting down individual queries causing locks.

I agree that Postgres is by far the better engineered product, but it's not necessarily the better RDBMS product because of that. MySQL is battle tested in a way that Postgres isn't (yet).

In my cases I found that mysql was faster for “select * from <table> where <unique key>=<blah>” — but for anything more complicated than that, postgres won (admittedly I haven’t checked recently - it was around 10 years ago that my 500-concurrent-user site was melting mysql, so I switched to postgres, which is now happy with 15,000 concurrent users - I’m mostly just responding to “historically more performant”)

MyISAM was historically notably faster for simple SELECTs on the primary key and SELECT COUNT(*).

InnoDB had ranged from slightly faster to much slower than Postgres depending on exactly what you’re doing. Once you’re doing more than trivial queries the MySQL optimizer tends to fall apart, often in weird ways with arbitrary thresholds where performance goes from decent to terrible after a table grows by 5%.

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.

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

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