Hacker News Reader

Comment by jes5199 | Hacker News Reader

Some engineers I worked with went and interviewed people at various San Francisco startups about their experiences with their databases.

The MySQL startups tended to say "We love MySQL. We've gotten in the habit of taking an hour or two of downtime in the middle of the night every week to run all of our schema migrations, and we've had to build our process around that, but one we had it in place, everything's been fine."

The PostgreSQL startups said "We love PostgreSQL. We run schema migrations in real-time during the middle of our workday, and we don't have any problems."

View on HN

pt-online-schema-change from Percona addresses this particular point with MySQL nicely for us, incidentally. We use their Percona XtraDB Cluster fork and are quite happy.

But that's not included in mainline MySQL, correct?

The Percona Toolkit works with mainline MySQL and is trivial to install.

I guess that means "No, it's an unofficial, third party toolkit".

So's jQuery, but that hardly stops me from using it. Unsure as to the point you're trying to make.

There is no "point". I asked a question and you didn't answer it directly.

For starters, the requirement of a third-party library introduces at least two problems:

1. An additional potential point of failure 2. The core software (a DB, in this case) can (and probably will) evolve independently of the third-party tool--thus introducing an additional layer of maintenance problems.

I'd argue further--and this is of course just an opinion--that such a basic feature as this ought to be supported out-of-the-box by anything that claims to call itself a "database" in the sense that MySQL does.

Percona and pt-online-schema-change is not just "some third-party library".

It's an industry-standard tool and one of the most respected forks of MySQL. It's not a layer of maintenance problems and they sell commercial support.

Their customers include the BBC, Yelp, and Cisco: http://www.percona.com/about-us/customers

Also, for the record, Oracle added online schema changes in 5.6.

So you actually have to use a fork of MySQL to get online schema changes? I was thinking this was just a drop-in of some sort.

I guess we are talking about RDS options though :)

Entirely possible to use pt-online-schema-change on an RDS instance. It's just running a bunch of SQL commands and is very happy to do them against a remote database like an RDS instance.

Very important to note that MySQL 5.6 has ONLINE ALTER.

Is it transactional?

https://blogs.oracle.com/mysqlinnodb/entry/online_alter_tabl...

Looking through the documentation, though, it's even worse than just saying every alter is an offline alter. There are a few, but significant, schema changes that one could want to make -- like when dealing with a table that has full text search -- that need to be done offline; you have to tread real real lightly in dealing with online alters. I'd personally argue that online alter is not ready for prime time with 5.6; there are too many other things they need to do with the database engines to make it really useful.

Which tools enable real-time schema migrations in Pg?

CREATE INDEX CONCURRENTLY, ALTER, and others. Batteries included.

This has saved our bacon a number of times. The only kind of DB-related downtime we have is when we're doing a Postgres upgrade.

Reply on news.ycombinator.com