Will anyone please explain the tactical reasons why PostgreSQL won? It's pretty obvious it has. I've basically ignored the database wars for a few years, so it's kind of interesting to see that everyone's using PostgreSQL now.
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."
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.
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.
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.
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.
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.
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.
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.
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.
Which is why it is being replicated, like I said.
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.
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 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.
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.
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.
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.
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/
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.
And even though I love PostgreSQL (and was working with all major databases), I still think that the real winner is sqlite :)
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.
5.6 had some pretty critical improvements, especially to the query planner.
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…)
Plus Wordpress has a hard requirement for MySQL, and like it or not a huge number of projects still use it as a framework.