Incidentally, I was not aware of issues around default settings with MySQL when I switched our infrastructure to Postgres - the primary reason we switched was transactional and much more efficient DDL changes. I was at a company previously that I felt was seriously hampered from innovating due to strong resistance against schema changes by the DBAs and want to do my best to prevent a similar culture where I work.
Before I decided to switch I benchmarked schema changes between Postgres and MySQL and found Postgres to be quite impressive, see the gist of results here: https://gist.github.com/1620133. If you (or anyone else) can point out why this benchmark is false or misleading, I'm all ears.
Another interesting part about your benchmarks are that they throw serious doubts on the popular myth that count(*) is slow in PostgreSQL. And your benchmarks were made before PostgreSQL 9.2 which will add index-only scans.
Whew! Glad that was only a myth! I'll remind myself of that whenever my code takes > 10 seconds to return the count() from a table. "Just a myth - this isn't really happening". I'll try repeating that to myself while waiting for the results - probably should only take 10-15 repeats of the phrase before the row returns, right?
Whoah - just did a check on a real table with a whopping 2 million records - postgresql 9 just blazed through that in < 7 seconds to give me
select count(*) from student; count --------- 2032609 (1 row)
w00t! 7 seconds! I'm not even sure how that myth got started, let alone why people still believe that hogwash.
Are there DBMSs where this doesn't happen?
I just did a SELECT COUNT(*) on a table here in our QA environment; 20.5 seconds to count 42385875 records from one table and 68.8 seconds to count 191906711 records from another table (Oracle 10g 64-bit).
When operating on a table, my understanding is that pg will select a version and operate on that version. If other versions are being worked on in transactions - that's a different story. Why can't metadata about the number of rows be assigned with the table version data, so after every operation, you'd know what the number of rows was at at that moment in time?
In PostgreSQL every row has two numbers. The transaction ID it was insert in and the transaction ID it was deleted in. An update is an insert plus a delete.[2] When running a select in PostgreSQL you just traverse the table and for each row check these two numbers to know if you are allowed to see the row.
The details above are PostgreSQL specific but most other databases have the same problem with there being no way to know the exact count without actually counting the rows.
Footnotes:
1. There is contention currently in PostgreSQL when writing the database journal (used for crash recovery and replication).
2. There are some optimization which are done here. For example HOT to avoid index updates.
If you had read his benchmark you would have seen COUNT( ) was slow in both databases (three times as slow in MySQL).
select n_live_tup
from pg_stat_user_tables
where relname = 'mytable'You might want to check out the "Online DDL for InnoDB Tables" section of the manual, it should help with understanding the results. http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.htm...
for me it would be missing common table expressions, weak implementation of functions and SPs (recursion) and horrible .NET connector (connections dying, int can get returned as byte[]). And I'm not even mentioning the gazillions of advanced features that pgsql (and Oracle or SQL Server) has that mysql doesn't.
MySQL has a low barrier to entry with some reasonable tools that make it easy to get up and running with a minimum of fuss. For a large swathe of applications it does the job just fine. There's little point in trying to pretend otherwise. Where PostgeSQL really fits in is when the next step is needed, either because the app has outgrown what MySQL can offer or there are some use (edge) cases where PostgreSQL does a better job. Address these areas competently and the world will beat a path to your door.
Exactly. I mean seriously what do Facebook, Twitter, Yelp, LinkedIn, Flickr etc know about storing lots of data.
It seems like Oracle is more concerned about trying to keep mysql less well suited for major deployments, so it can upsell its flagship product. Or else their development process simply doesn't properly accommodate community assistance. Either way, as long as their resistance to community-aided development continues, mysql and oracle/mysql disparagement will continue, not because people hate Oracle, but because Oracle's management of mysql makes it less than ideal for those who simply want a good open source database.
Which is the point of the OP. Anyone who is open to looking at options and who wants a good open source database should look at PostgreSQL.
I doubt any of those example companies you listed even use stock Percona. Facebook for sure has extensive modifications beyond even the publicly available distributions like Percona and MariaDB.
Source: http://tinyurl.com/7adp6fl (PDF)
http://zurlocker.typepad.com/theopenforce/2005/12/googles_us... http://research.google.com/pubs/pub38125.html
You think they are equally going to be okay with losing data ? Or do you want to try again.
Let's add some more companies: 37Signals, DHL, Dropbox, Evernote, UPS, Kayak, LastMinute, Orbitz, Continental, Mint, Quora, Tumblr, Techcrunch, Slashdot, NYT, NBC, Reuters, Wotif, Zappos, Wikipedia, Youtube.
You still think any of these companies would tolerate loss of ANY data for ANY reason ?
For example, taking the first company on your list that actually deals with real stuff in the real world rather than just running a website, DHL, here I see http://www.oracle.com/us/corporate/press/037809 - they're an Oracle shop. I could continue but I think I've proved my point.
So before you get all starry-eyed about these amazing companies and their amazing MySQL installations, understand that you are starting from a false premise. Oracle - for a cost - provides some powerful capabilities (e.g. stretch-RAC[1]). Is it worth the money? Well, it comes back to what your data is worth. This incidentally is why some organizations cling to paper forms - it's cheaper to re-key the data than build out the infrastructure to do it all online...
[1] Tho' if you think you need this, what you probably really need is DB/2 and Sysplex
I wonder why this bug is still open today after 5 years.
Oracle on the other hand has a setting for everything. A good Oracle DBA can spend all his time fiddling with Oracle settings to get the best possible performance. On the other hand, a good Oracle DBA can spend all his time fiddling with settings to get that little bit of extra performance gain. PostgreSQL is simpler.
PostgreSQL on the other hand lets you write your stored procedures in any language. On the other hand, you can spend all your time writing and debugging stored procedures written in brainfuck.....( and in case you want to do that, download the handler at https://github.com/mikejs/pl-bf and go get started!)
There are a few other differences as well. In general, I think Oracle is better for some things, PostgreSQL for others, but they have more overlap than either does with MySQL.
1. It was the only game in town when the application was designed.
2. It was head-and-shoulders above everyone else. More, better, faster. Most people think you just throw SQL-99 at it, but then you're wasting your money. Look at their analytical languages, for example.
So why do Postgres advocates insist on dissing MySQL with false and misleading arguments? The usual target is some default settings, when obviously there are three kinds of MySQL users: the ones that actually have a reason to want less strictness, the ones that know how and when to change the settings, and the ones that just don't give a fuck.
You're not going to convince the latter until you are #1, and you're insulting the intelligence of the first two, whilst also making them question whether the arguments in favor of Postgres are actually true, since you're not being particularly honest in your criticism of MySQL.
And you're wondering why Postgres doesn't get any more love?
Most MySQL users are well aware that Postgres is technically a better DB. They mostly (right or wrong) feel MySQL serves their needs sufficiently, and are not particularly attracted to an open source community with such a douchy attitude.
If even Oracle doesn't scare people of sufficiently to jump ship and switch to a better, free and open database, you've got a serious image problem. Stop blaming it on other people's "stupidity".