Good news! Postgresql scales really well, and will always be open source. Plus, it has more features and great compliance with the SQL standard.
Most of them aren't DBA's, and they don't need to be for the purpose for which they use the DB. For them, MySQL is a tool that just works, without issues. And they certainly don't have any need to deal with the less than helpful Postgres community, touting "advantages" that are completely irrelevant to them. Hell, most MySQL users only use a fraction of the features in MySQL.
If they ever migrate to a different DB, it's more likely to be a MySQL-fork. Although Postgres could very well replace MySQL, the Postgres community chooses not to address that audience. Which is fine, but than please stop pointlessly pissing on MySQL at every available opportunity...
I've had to professionally administer both mysql and postgresql installations. Mysql is an awful piece of software. Every second spent using it is painful.
You say 'it just works', and in that case it really doesn't matter what you use. If you haven't run into bugs/quirks/problems with whatever datastore you use, whether sql, or nosql, whether open or closed source, you haven't pushed it very hard. All software has rough edges and bugs.
I think you will have a very hard time finding people who have used both mysql and some other database, whether sql-server, postgresql, or oracle (or even firebird) that would have a very high opinion of it.
Now, what are the 'actual needs' of the vast majority of MySQL users that Postgresql doesn't satisfy? I hope that requirement isn't 'open source' :)
The person to whom you replied did not say that. I read it more like "Oracle can mess up MySQL if they want, because we still have this great alternative called 'postgres'".
"without ever addressing the actual needs of the vast majority of MySQL-users."
Your comment struck me because I still have a piece of paper next to me with a list of all of the complaints and feature requests I saw in various discussions around the time of the postgresql 9.1 release. That doesn't mean that every single issue you have is addressed yesterday, but a lot of development is happening in response to the needs of the MySQL community.
The most obvious example is replication (9.0, but improved in 9.1), but there's also per-column collation. And you might also consider unlogged tables to be targeted at typical MySQL use-cases. And 9.2 is likely to have index-only scans (covering indexes), for which a patch has already been posted by Robert Haas -- I happen to know mysql users who have been waiting for that feature alone, but it took some groundwork development starting in 9.0.
Can you please cite something specific that you feel is not met or being addressed, so that I can add that to my list, too?
"touting 'advantages' that are completely irrelevant to them"
Well, they might be advantages to somebody -- they were developed for a reason. Postgres folks who have done something interesting like synchronous replication or K-nearest-neighbor indexing aren't going to whisper quietly about them.
"If they ever migrate to a different DB, it's more likely to be a MySQL-fork"
It's hard to get exact numbers, but a common theme at postgresql user group meetings is people migrating from mysql to postgres or mysql people starting their next project in postgresql. There was a noticeable uptick after 9.0 was released.
"than please stop pointlessly pissing on MySQL at every available opportunity"
People come here to learn new things and see (and take part in) progress. Please stop declaring the discussion over ("...we already know...nobody is debating that...") and products like MySQL good enough ("...it just works...").
That said, is there something that would be lost if everyone just switched from MySQL to PostgreSQL tomorrow? What benefits does MySQL have over PostgreSQL these days?
I run a big website and would love to switch; the only thing stopping me is that I'd have to manually fix hundreds of carefully hand-crafted queries to support pg syntax.
If someone wrote a "MySQL emulator" layer for postgres, I'd switch tomorrow. (And I'd work quickly to progressively replace emulated calls with real pg SQL -- it's a lot easier to justify the effort after you make the switch than before!)
Where you are going to experience the most pain is that MySQL lets you write idiotic queries like this:
SELECT id, last_name FROM some_table GROUP BY last_name;
That is not valid SQL, for good reason.
Other things that will bite you: http://andreas.scherbaum.la/blog/archives/657-PostgreSQL-9.0...
God, looking at that link makes me so sorry for anyone using MySQL. Life is too short for that.
I'm actually curious: what id would a MySQL user expect to be displayed for this query?
MySQL discourages using this feature if columns not included in GROUP BY are not constant in the group:
> Do not use this feature if the columns you omit from the GROUP BY part
> are not constant in the group. The server is free to return any value
> from the group, so the results are indeterminate unless all values are
> the same.
SELECT order.custid, customer.name, MAX(payments)
FROM order,customer
WHERE order.custid = customer.custid
GROUP BY order.custid;That's what errors are for, not documentation. It is pretty easy to forget something in the group by, and documentation won't help with that.
The dangerous thing is that the result returned from such a nonsense query looks valid in many cases, while being wrong in subtle ways.
PostgreSQL detects when the query is valid, and executes it if so. So, if you do a GROUP BY customer_id (a key column), you can also see customer_name without adding it to the GROUP BY list. But if you group by customer_zipcode (not a key), and try to select the customer_name, it will throw an error.
id|score|last_name 1|11|smith 2|22|jones 3|33|smith 4|44|jones
Query we are pretending is valid is: SELECT id, last_name FROM table GROUP BY last_name;
What rows are returned? I expect to see something like:
?|?|smith ?|?|jones
However, what ? is isn't clear. Could we get a row like 1|33|smith ?
In SQL, all selected columns must be part of the group by or inside an aggregate.
One issue is with non-standard MySQL functions (such as inet_aton/inet_ntoa) and aggregate functions (such as group_concat). While most have great and superior pg implementations, it's a lot of work to cross-reference each one, grep the source code, and hope the pg implementation is sufficiently identical.
Another issue is that MySQL considers the 'as' keyword to be optional. I'm sure pg has good reason for requiring it, but there's probably about a thousand 'as'es to be added.
And that's about where I gave up last time, if I recall.
1. InnoDB has certain optimizations that PG lacks which can make a big performance difference at the high end: index-only queries, insert buffer (or change buffer in MySQL 5.5+), clustered index
2. Lightweight connection creation: MySQL can handle many more concurrent connections and also can create new connections much faster due to threading vs. process model
3. More flexible replication: PG is catching up, but MySQL still has the edge here imo.
As for lightweight connections, I see this as completely moot. While you might make tens of thousands of cheap connections to a mysql server, postgresql is much better at executing concurrent queries. Connection poolers like pgbouncer let you make as many cheap connections as you want if most of them are going to be idle anyway.
2. use a connection pooler (e.g. pgbouncer), problem solved, good practice anyway, even with mysql
3. what (practical) edge do you see given the replication features in 9.1?
b) Non-transactional tables
Both allow you optimize the memory usage of some particularly problematic cases, specifically very large, simple tables. Postgres cannot return data directly from indexes (covering indexes). It always has to go back to the table itself to fetch the actual data. If the table is large, that can be inefficient for some types of queries.
Non-transactional tables use a lot less memory as well. For instance, if you have a large table that represents a N:M relationship (id1 int, id2 int), the two ints use 8 bytes of memory. A postgres table adds about 24 bytes per record, three times the actual data, plus some overhead per page.
Don't take this to mean that MySQL is faster than Postgres. That's not generally the case. The Postgres query optimizer is vastly better than MySQL's. So for complex queries and data models, Postgres is way superior. The big differences are always related to very specific data model and query combinations, so general benchmarks are utterly useless.
9.1 added unlogged tables. They are completely unsafe, and quite fast.
http://rhaas.blogspot.com/2011/08/index-only-scans-now-there...
http://archives.postgresql.org/pgsql-hackers/2011-08/msg0073...
PHP blog generation software, or some lower level, more general purpose tool???
But on that note PgAdmin3 is pretty crap. At least on my mac.
1) taps ( done by some people from Heroku ) : https://github.com/ricardochimal/taps and http://adam.heroku.com/past/2009/2/11/taps_for_easy_database...
2) mysql2psql : https://github.com/maxlapshin/mysql2postgres
3) Just slurping in the data via the FDW feature for 9.1 , http://www.pgxn.org/dist/mysql_fdw/1.0.0/
Seriously, even overpriced, self-aggrandizing Oracle DBAs are less insufferable than you guys.
That is a reference to waiting for queries to finish.
Mysql is slow.
>Haters gonna Hate
back to the rubbish bin you found in and don't come back.
Mindless, off-topic propaganda isn't welcome here.
I, for one, would like to see more migration away from MySQL, but that doesn't mean you get to act like a total nob.
Cue PG telling me to shut up again while I'm taking out the trash.