[kimmel]

I recently moved an application off mysql to postgresql after hitting this error. After over a decade of mysql doing things their way and it always being a pain in the ass for everyone else when is enough, enough?

I no longer develop software to run on mysql because I like my sanity.

[willvarfar]

Well, funnily enough MySQL are now owned by Oracle and I've wasted a couple of days trying to get some sane semblance of `LIMIT` working for me on Oracle... so don't hold your breath, Oracle have an even longer history of damning their customers...

I've been keeping a list of MySQL gotchas, but steering clear of the MySQL take on the SQL standard itself: http://williamedwardscoder.tumblr.com/post/25080396258/oh-my...

I still contemplate MySQL for new projects, because better the enemy you know and all that... OMG, I'm mad! Postgres rah rah rah!

[fendale]

Oracle does seems to make limit queries much harder than they need to be. I wrote an article about limit and paging queries in oracle ages ago that may interest you, but you probably have it all figured out now.

http://betteratoracle.com/posts/18-limiting-query-results-to...

[sendob]

This actually made me think of: Oracle's "UTF8" vs "AL32UTF8" which I consider to be a gotcha, even though it is reasonable in terms of the evolution of the standard, a modern land mine perhaps.

The lack of limit sucks, but the thing that annoys me most regularly is that Oracle refuses to make the 0 length string / null behavior configurable to allow for ANSI compliance ( MySQL null handling is worse IMO, makes me insane.... )

[chris_wot]

Oracle historically has poor support. Here's an old example with an ADO bug, they never fixed it even though the support ticket was open for almost 2 years!

http://randomtechnicalstuff.blogspot.com.au/2009/11/nasty-or...

[sendob]

and unfortunately, they are the only ones who can offer support or implement a patch for their software!

With postgres and mysql you can find many companies competing to offer great service, and who can contribute their fixes and improvements directly should they so choose. The stronger the output of these communities and the organizations that support them, the more pressure for even the companies like Oracle to improve ( or perish :))

[AlisdairO]

You should be able to do LIMIT in Oracle using http://www.oracle.com/technetwork/issue-archive/2006/06-sep/... . If you want a standards-compliant way to do it, you can use the ROW_NUMBER() window function. This might not be as fast, but will work on virtually every DB except MySQL...

[willvarfar]

Yes I'm deep beyond correctness and now want to make it fast...

Oracle is like COBOL; the only techies who like it are those whose job security depends upon it ;)

[grey-area]

Yep, I switched to Postgresql after seeing a client lose data to corrupted databases - they had rebooted their server, and mysql refused to restart as a result and had to be manually recovered. Every time I hear another mysql horror story like this (truncation without warning was another favourite), it makes me thankful I no longer use it.

[sendob]

if it matters, must have backups! ( +replication +geographical distribution etc as needs must )

Even a perfect database, should such a thing ever exist, is not sufficient to protect us from a determined disaster

[grey-area]

Sure, I came in after their disaster to clean up, and their issue was their backups weren't up to date and they had no replication etc (it was a small site, no sysadmin, they rebooted their server during a busy time and boom! All the other services came up ok, but not mysql). The fact that Mysql failed and ended up with corrupted dbs on a simple reboot (not power failure) doesn't fill me with confidence - that really shouldn't happen. That's just an anecdote of course, but I think I prefer working with psql anyway.

[rimantas]

Interesting, pretty much every system has insane things in it. Is postgresql an exception?

[sitharus]

Certainly not! However, its WTFs per minute count is far lower than MySQL.

I started off my career as a PHP/MySQL developer. PostgreSQL is definitely years ahead of MySQL. It used to be that MySQL had far superior read performance for simple queries, but that hasn't been true since around 2006. Also Postgres' SQL optimiser is really good.

[rimantas]

  > PostgreSQL is definitely years ahead of MySQL

I guess this does not apply to replication, upserts, etc. Well, I think I will just grab some popcorn and wait. It will be fun to watch how those who never bothered to learn MySQL (and by this I don't only mean its peculiarities) will get bitten for not bothering to learn PostgreSQL.

[gbog]

Why? Because you think everything is equal, everything has its own gotchas and not one is better than the other?

That is against all history, not only in software. Postgres is obvectively better (safer, better engineered, easier to use, etc.) than MySql, just as Python is objectively better than PHP, and an AK47 was better than most other assault rifles of its time.

[the_mitsuhiko]

The lack of upserts is a problem in postgres, as much as some people say they can get away without them. There are objective concurrency issues with the lack of it, that have been acknowledged by postgres developers when they started work on upserts.

[einhverfr]

> There are objective concurrency issues with the lack of it, that have been acknowledged by postgres developers when they started work on upserts.

These concurrency issues don't fully go away by adding the feature, which is why it is taking time to work out the desired behavior there.

[sendob]

may be a problem, there are workarounds, and workloads where it is not required, but I agree, merge/upsert is a much anticipated enhancement.

[sendob]

I find the official documentation to be superior for postgres (YMMV), but I think there are certainly more examples / blogs / tutorials for MySQL.

I prefer to work with Postgres when I can, but I also have found instances where MySQL better fit my needs, a heap table, even with more recently added index only scans ( and even reordering the table with cluster may not be desirable) you may want an index organized table (oracle term ) (mysql would say clustered index )

I am curious why even in the context of freely available engines we focus on competition, which I think can lead to great things, but I don't understand the animosity the communities feel towards one another, isn't it just a matter of horses for courses? Are we really interested in competitive kills or improving both the tools we use and our understanding thereof ?

I don't mean to single your comment out gbog, and I know this is certainly not exclusive to databases either ( witness almost any discussion of various programming languages on HN )

[fendale]

The MySQL clustered index (and Oracle IOT) are, in my opinion, often over looked powerful features. To pick an example I recently encountered ... I have an Rails app to implement a RSS reader. Each user has many feeds that get added over time. If you cluster the feeds on user_id then all the feeds for a given user will be in a single database block or two, so reading them out is super efficient. If they are in a standard heap table, (or the way Rails creates the table by default, clustered on ID), then reading the list of N feeds could require N database block reads in the extreme case. Same goes for all articles on a given feed - cluster the related articles together and its very efficient to find the most recent articles for a feed.

[mgkimsal]

in at least some cases, animosity comes from people who've have to clean up someone else's exploded mess in the tech of their targeted derision.

I do think many (most?) people are interested in seeing all tools get 'better' over time, but what needs to be improved, or how it should be improved, often don't get agreed on.

[stephenr]

i don't think you know what objectively means.

[sgt]

In my experience, it has fewer "insane things in it" than MySQL.

[einhverfr]

PostgreSQL is no exception, but hey I do insane things with the db so I suppose it is fitting that I occasionally (but rarely) run into insane aspects of the system.

The difference is, you don't have to do crazy things to run into truly insane behavior on MySQL ;-)

[s_kilk]

Postgres may not be perfect, but it's certainly not as consistently dopey as MySQL.

[codeka]

I basically chose MySQL because of familiarity for the most part, but I think I'll definitely consider an alternative for my next project...

[kalleboo]

For me it was because AWS RDS didn't support Postgres until recently, although that has changed now.