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

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.

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.

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.

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.

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

Very important to note that MySQL 5.6 has ONLINE ALTER.

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.

Is it transactional?

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.

I don't know if I would really say Postgres won. Among people who care about databases, Postgres has always been the more popular of the big two free databases. Postgres was actually dedicated to being a good relational database, whereas MySQL in its earlier days was willing to cut corners to the point where it wasn't even ACID in most common configurations. But MySQL was faster (because, again, cutting corners) and its tooling was a little more friendly (especially for the PHP crowd).

Over the years, Postgres has made up those shortcomings, so it retains its respect among database wonks and now other people can easily use it too, so you see a lot of people adopting it over the past few years.

But in overall usage numbers, I would be surprised if it were anywhere near MySQL. It takes a long time to overcome that kind of inertia.

don't forget postgres didn't officially support replication until late 2010, which was a total non-starter for many/most production uses

It is a bit surprising how long it took for that to land. On the plus side, replication is very easy and low latency with 9.3.

There's still a bit to do on the auto-failover front, but that may end up being more of a third party undertaking. Postgres has failover facilities included, they just have to be driven by something external right now.

We ran PostgreSQL in production before 2010, and there were several solutions back then for replication but all of them had their own set of problems. We went with shipping the write-ahead log with rsync to the standby server, but this was trickier to set up and had much worse latency than the current built-in solution (which is basically streaming the write-ahead log over a tcp connection).

Yeah, I think that was the big hurdle that finally made Postgres acceptable to people and started the wave of switchers.

That is absolutely ridiculous. Less than 1% of 1% of mysql users are using replication. Suggesting it is a "total non-starter" for most production uses is crazy.

> Less than 1% of 1% of mysql users are using replication.

If you count all the crappy shared hosts, XAMPP local installs, and hobbyists setting up their own little VPSes, perhaps.

Using a single, unreplicated database instance in production for anything serious is bizarre. Failed hardware is hardly unheard of.

>Using a single, unreplicated database instance in production for anything serious is bizarre

It is incredibly common. Go check out a thousand businesses running mysql, you'll be able to count the ones using replication on your fingers.

>Failed hardware is hardly unheard of.

You don't need to use mysql replication to deal with that. Even the crappiest low end SAN storage devices do it vastly better than mysql does, without any of the bugs and problems mysql replication has.

I have heard stories of entire SANs failing so I would not advice trusting your SAN too much. For example there was a huge outage at a Swedish host caused by a failed SAN, all data was lost so people had to use daily backups to restore.

Replication can be done off-site so you at most lose a couple of seconds worth of data. I do not know anything about MySQL's replciation but my trust in PostgreSQL's is very high.

>I have heard stories of entire SANs failing

Which is why it is being replicated, like I said.

You're kidding right?

I'd wager that a huge number of MySQL users are using replication. Running a single database in a production environment is totally unacceptable and a major business continuity problem.

How many wordpress installs use replication?

>Running a single database in a production environment is totally unacceptable and a major business continuity problem.

You don't need to use mysql's broken replication to get HA. Hell, I've seen more people (wisely) using DRBD for that than using mysql's replication. But even entry level storage devices do replication.

I've written a couple of blog posts about what makes Postgres a great database, many of these reasons are why its now preferred by so many devs - http://www.craigkerstiens.com/2012/04/30/why-postgres/

I wouldn't say PostgresSQL has won. MySQL is still very relevant.

I would say that if you are just starting out and you already know MySQL and you are proving out your MVP, MySQL is still relevant.

Once you have a stable product and you need a better database, PostgresSQL is a good move up.

Given the number of companies who'll provide you with a fully managed Postgres installation there's no reason not start out on it for the MVP - as a user its no harder than MySQL. Even as a sysadmin its not significantly harder, just different to work with to MySQL.

Tactically, because PostgreSQL predictably releases robust software every year with major improvements and innovations.

Strategically, because PostgreSQL is not just trying to be a free database checking off features. It's trying to be something better -- lots of innovation that is having a bigger impact on what developers and DBAs can do.

One of many reasons is MySql being bought by Oracle and name change/lost of community backing etc...along with some missing advanced technical features....

I still use MySQL because trying out PostgreSQL recently scared me away. Maybe I was trying the wrong thing but I couldn't get pgAdmin to display my tables. I think I sank most of my afternoon trying unsuccessfully to do something MySQL can do in 5 minutes.

I recognize I'm used to MySQL but I was under the impression that because PostgreSQL has a similar syntax (SQL) it wouldn't take too long to pickup.

Unfortunately things that take 5 minutes in a complex application typically required an order of magnitude larger investment up front in manual reading and experience.

I deal with Postgres a good bit, but will be the first to tell you that pgAdmin has got some issues. It's a very clunky, buggy, moody piece of software.

I've had better luck learning how to use the psql command/shell than mess with pgAdmin, at least in certain cases. To take your example of displaying tables, psql in and type: \dt

The rest is just a search away.

I have idle daydreams where Sequel Pro is finally ported to Postgres. pgAdmin is horrendous.

That would be amazing. Sequel Pro makes MySQL management so easy. Have you emailed the author asking for that feature?

Edit: It seems they are working on it but I i'm not sure when we can expect a release: http://stuconnolly.com/blog/sequel-pro-postgresql-support/

I've used Sequel Pro and I don't understand why others love it; it's as clunky as anything I've ever used. My preference is for SQLyog - and my dream is that to be ported to OSX...

That article is from September of 2012.

I have always stated postgres would become much more widely adopted if it was released on RDS and.... they finish the sequel pro support. Half way there ;)

Can you elaborate further?

Coming from a MySQL background, I sort-of assumed PostgreSQL worked the same way. I remember after setting up the server, pgAdmin was very difficult to navigate. I managed to get a database and table made and got some data in (using SQL) but I couldn't view any of the data. Compared to MySQL + Sequel Pro it was very difficult to use for a beginner.

Postgres is very powerful and can do a lot. I think I just need a good tutorial and some time to get used to it.

None of that has anything to do with postgresql. Pgadmin is crappy, don't use it. Use one of the dozens of database independent admin tools, or just stick with the best tool: psql.

You must be joking right? Either that or spending too much time on HN. PostgreSQL has won in the same sense that Golang or Clojure have won: in frequency of frontpage HN articles.

I think it all depends how you define winning here. Based on popularity alone - yeah, MySQL is still the king. Based on capabilities - I think it's fair to say that pgSQL is more capable and it's just that not everybody needs that one.

And even though I love PostgreSQL (and was working with all major databases), I still think that the real winner is sqlite :)

I'd venture a guess, and say that Postgresql didn't win, sqlite and mongodb won. I think a lot of the reason behind MySQLs initial success was blogs and various php-apps that weren't architected properly (from a db perspective). Some of these didn't need a ("proper") db, and most of them probably did, but "worked well enough" without one.

I never really understood why you'd want to use a separate dbms if it couldn't do proper constraints, triggers, transactions and materialized views for you -- then it starts to feel like a lot of wasted effort.

So for many of the use-cases where MySQL might have been appropriate, we now have sqlite, mongodb, memcache/redis and a few others.

Personally I don't really see any reasonable use-cases for mongodb, just as I didn't see many reasonable use-cases for mysql -- not that you couldn't build stuff on top of it, just that it wasn't a very good idea.

IMO it was when Oracle bought MySQL and effectively killed development. MySQL still lives on with MariaDB I suppose, but in the years since Postgres has leapfrogged it in terms of functionality.

I'm a PostgreSQL fan, but Oracle are investing in MySQL development, but their flavour of open source compliance is tarballing.

5.6 had some pretty critical improvements, especially to the query planner.

I would like to say it is because MySQl has been sold to a company with no reason to keep it well. That's the one of biggest reason to make existing MySQL users to move on.

Hmmm...I was wondering why MySQL is still much more widely used.

It was used, so still just there. But not anymore if they finally learned why safety in design philosophy is important by paying data corruption or service break. (anyway some people never learn…)

Safety is annoying. Safer system usually generate more errors to prevent accidental mistakes. Extremely safe system prohibits even booting up the product if it's not been properly configured. It simply has much more annoying safety net. If you care the safety, this kind of annoying errors are good sign to you. But if you're newbie, this is just a big obstacle, which makes stiff learning cube.

Really. We still see many people who hate to use safety belt. Because it's annoying. And everybody did before benefits of safety belt were widely known and accepted.

Also, number of users says nothing about how well the product works. Usually, cheapest product takes biggest user-base. MySQL is cheapest to start due to lack of safety. Search this thread for the name "natural219". And see why he chose MySQL over PostgreSQL. I believe that's why most people started MySQL at first.

And surprisingly, there're so many people really don't care data safety. (maybe not that much surprise. we always see those people in TV…)

The analogy I use: PostgreSQL makes you eat your vegetables.

Inertia, largely. Postgres is more scalable and has quite a few nonstandard additions that are insanely useful from time to time, but it's not always a drop-in MySQL replacement. If you're just writing something simple like a blog or CMS then you don't need the extra functionality.

Plus Wordpress has a hard requirement for MySQL, and like it or not a huge number of projects still use it as a framework.

but if its just a blog use mongo not mysql. non relational is a benefit and you have backups... postgres for real stuff.

What has it won?

mindshare

I wish I lived in your world. Where I am, 90%+ of web developers believe there are two options for making web sites: "LAMP" and .net/sql server. I am constantly dealing with web developers who genuinely don't even know postgresql exists.