Hacker News Reader

(HINT: it's not just hype, for many people, MySQL was the product they wanted)

Just curious, could you explain what it is that people want from MySQL that they don't get from Postgres?

I (and another team member) recently moved a big project from MySQL to Postgres due to deadlocks, failure to respect constraints and similar issues, but I'm curious what I might be giving up.

So far the main thing I miss is is "UPDATE foo INNER JOIN bar ON foo.id=bar.foo_id SET foo.x={x}, bar.y={y}...".

I really have a hard time taking him seriously. As someone who's been a heavy user of both databases in the past, IMO the only thing MySQL has done better than PostgreSQL is in the marketing department. PostgreSQL has no concerted marketing effort. PostgreSQL is not a company with a marketing budget...it's a community of peers and db geeks getting things done.

I think MySQL's edge is just that is was initially perceived to be simpler to set up. Perhaps that is true or not, but the perception was created. The result was a massive head start. You now have a huge pool of users, support and knowledge, which makes it a safe choice. (notice I say safe, not necessarily the best)

Outside of the startup community developers don't have such a great appetite for risk, especially with their data.

I'm one of those people - I just know My SQL better. It has nothing to do with ease of installation or building queries. It has to do with troubleshooting. I know how to tune MySQL and troubleshoot. I know how to install and use other systems but it introduces risk for me to put them into production without a very compelling reason.

I would happily use another system if there were a db admin to take responsibility for running the server. In fact my favorite db is Oracle, but I have no experience as DB admin on that platform.

I can say that postgres made overblown claims for their products in the late 90's. They'd use FUD to scare people away from MySQL, but two times I had a "Dude Where's my Data" experience with PostgreSQL which sent me back to MySQL.

I was involved with one project with PostgresSQL that was successful around 2005. I did 5 "shootouts" of DBMS alternatives that considered PostgresSQL and other products for various projects and despite having some attractive features, PostgresSQL always lost out. For instance, in mysql I was able to create indexes that could handle Freebase text fields without any "impeadance mismatch and it wasn't clear how to do it with pgsql.

MySQL is easier to install, much simpler to use, easier to manage and most importantly every little issue has been solved online many, many times over. It's a known quantity.

Fact is that simplicity is more important than features for most people.

Installation? All modern Linuxes have package managers that make installation a headache of the past.

Configuration is a different story. When you are done with a Postgres config you have a more secure setup. They don't allow you to shoot yourself in the foot by default.

Honestly, installation as a differentiator between databases is a seriously weak argument. If you're going to troll pick something a little more technical like acid compliance or something else that nobody seems to care about.

You don't like my argument so I am trolling ? Nice.

And you ignored Windows, Mac as well as the huge number of LAMP based installers. Being able to quickly get the entire stack up and running very much helped the popularity of MySQL.

You keep saying this, but in my recent experience, postgres had been fantastically easy to get up and running on both Windows and Linux (debian and ubuntu to be precise. don't know about Mac). So this argument only applies possibly to its distant past. It's simply not relevant now. It's worth keeping an open mind.

I agree with you on the development setup. Production setup is about the same.

Meant no offense. Seemed trollish but when you factor in development setup then yes MySQL is slightly easier. I think heroku is doing a good job making Postgres a snap.

Regarding installation and ease of use, can you be more specific? I see this complaint frequently but, as a postgresql user, it's hard for me to see where the difficulty for new users is and what should be done about it.

Installation of PostgreSQL are in my experience no harder than for MySQL. As for management and usage I would say PostgreSQL is simpler because it has a much better command line with excellent error messages and good tab completion. The backslash commands of psql are a bit confusing at first, but so are MySQL's SHOW commands and once you get used to the backslash commands they are very powerful.

> Just curious, could you explain what it is that people want from MySQL that they don't get from Postgres?

Nothing, but I'd really love the migration story to be much nicer. The only thing stopping me is a few hundred queries that will need to be rewritten to conform to the Postgres way. There's a few things that Postgres could offer to make the transition a bit easier:

- Improve mysqlcompat so that it's a really first class implementation

- Offer a configuration package that helps with a lot of annoying differences like booleans values not printing as 0/1

- Offer some MySQL style syntax sugar, like ON DUPLICATE KEY UPDATE, backticks for field names

- Offer a case insensitive collation for text fields or build something like citext into the core

- Supporting TINYINT and UNSIGNED numeric types would be useful

- I like the mysql date handling syntax...

I realise that many of these things have perfectly good workarounds available, but it would be much nicer if I didn't have to test as much stuff to confirm it still works.

I really miss "REPLACE INTO". Upserts are a complete mess on postgres. It requires 3-5 queries to get the same behavior.

Maybe I'm missing something, but doesn't it do the same as `IF EXISTS(SELECT ...) THEN UPDATE ... ELSE INSERT ... END`, so 2 queries actually executed?

What cases does it not cover?

This is a procedural function, which is a pain in the butt, and slow.

MERGE support has been a want-to-have for a long time. Unfortunately Postgres is stuck between the complex but useful behavior of implementing MERGE (without destroying concurrency) and the simpler and still-useful MySQL extension "OR REPLACE".

Although it seems like some people have floated the idea of going with the simpler non-standard option without being shot down, it seems like the people interested in doing the work are most interested in implementing the complex and more featureful standardized option, and it's rather difficult, so there is no MERGE yet.

It's not really a functionality issue. MySQL just works.

The PHP API is incredibly direct and there's little development overhead.

Postgres on the other hand always felt kludgy for small projects. Just getting it up and running and creating databases took longer than with mysql. And phpMyAdmin is a tool most people are familiar with, for which I can't even think of the postgres analogue.

Pardon my butchering of the phrase but postgres is a hammer and mysql is a scalpel, and most projects call for a scalpel.

Compare:

    $ sudo aptitude install mysql-server
    $ echo "CREATE DATABASE dbname;" | mysql

    $ sudo aptitude install postgresql-9.1
    $ createdb -U postgres dbname

    mysql_query("SELECT * FROM foo...");

    pg_query($db_handle, "SELECT * FROM foo...");

http://www.freewebmasterhelp.com/tutorials/phpmysql/4

http://www.techrepublic.com/blog/howdoi/how-do-i-use-php-wit...

I haven't used phpMyAdmin, so I won't comment on that.

Right. The same scalpel that when told to not insert a null value will make a decision to insert an empty string.

MySQL works but it was always a hack. PostgreSQL is superior and has been a superior solution for quite some time.

I'm sad to see MySQL go, but I find that Postgres works just as well.

You forgot to mention, that MySQL has no transactions support.

Seriously, before claiming your beloved choice as "superior solution" would you mind as much as to learn something about competition you dismiss as inferior?

For one I cannot wait PG go become popular—maybe when more people start to use it and start to shoot themselves in the feet will they shut-up about superiority.

Mysql has had quite solid transaction support for many, many years. It hasn't been the play database you think it is for a very long time. This seems like an important topic to you so you should seriously consider looking in to it.

He was being sarcastic. It's the kind of thing people keep saying even though it hasn't really been true for a while.

MySQL was great, we all loved it. It allowed many real businesses to make real money without the overhead. Then the oracle thing happened. In the end they are all just tools. Its not really interesting to have a religious debate over tools.

No transactions support?

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transaction...

Try phpPgAdmin as an analogue to phpMyAdmin. Note: I've only used it for small projects.

> team Postgresql has never seriously asked the question of why MySQL has had more market traction than Postgresql.

And how could you possibly be so sure about this?

because they never admit that they're doing anything wrong

A lot of features have been driven by users saying that postgres needs to solve something. Replication in core is the most obvious case. Index-only scans are another example. If you want a zillion more, read old tech discussion about postgres and see how many of those issues have been fixed.

I'll tell you two things that MySQL does right that postgresql doesn't: checksums and ON DUPLICATE KEY UPDATE.

MongoDB works best when it is thought of as a JSON document store.

And when you do and have lots of embedded entities it becomes very fast and with its fluid schema a lot easier to manage and maintain. For my use case I do a single query to fetch my self contained User object which would need 30+ queries if I was using a relational database. It's an order of magnitude faster.

So really NoSQL databases aren't a replacement for relational databases just another alternative.

>For my use case I do a single query to fetch my self contained User object which would need 30+ queries if I was using a relational database.

This can't possibly be right. The 30+ SQL queries would have provided additional information that your 1 NoSQL query doesn't.

THe only disadvantadge is that the secondary information you're storing in User - the one that would be stored in other tables - is now very hard to query. Am I right?

No it is exactly the same query as if it were a separate table/collection. You can just say fetch from User.Profile instead of Profile.

If you need to fetch across users then you will need an index in order to have decent performance. But the point is that in MY use case this never, ever happens so I made that tradeoff. Much faster "fetch everything about this user by id" in exchange for much slower "fetch me all profiles for all users".

Yes, I was trying to understand if, for your use case, storing JSON properties for each user in a SQL text column would give the same result.

or this case could have the best of both worlds and use an HSTORE column - which can be queried by field - in PostgreSQL.

You could but then you're really trying to jam a JSON style modal into a relational database. You CAN do it. But the tools and query language will make it much harder to work with. Not to mention lack of indexes on individual fields.

People avoid introducing new data stores because it adds complexity and it's harder to do queries across data sets. Jamming different data models together in one system is a very viable strategy.

Postgres supports functional indexes, so indexing individual fields within a JSON document is already possible. If it's not easy now it will be easy soon.

Remember XML databases? They became extremely marginalized when relational systems started adding basic support.