Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why PostgreSQL is better than MySQL

blog.2ndquadrant.com

Post reply on HN
Print view View original

51–60 of 100 posts

Re: Why PostgreSQL is better than MySQL

#51

Post by astrodust »

pgaddict wrote: post #12
geofft wrote: post #6

Earlier quoted context omitted.

Wow, this surprises me a lot, especially combined with the consensus position that Postgres is good, robust software. What is Postgres doing right and how can other projects follow their lead?

On a more serious note, what makes Postgres so reliable is a number of things. For example: * careful patch review process - Sometimes it's a bit grueling, and it takes time to get stuff in, but it's extremely valuable. Not only for finding bugs but alternative approaches to implementing the feature. * careful testing - It's a natural part of the review process (Does the patch have tests? Can I come up with something…

* Not being owned by Oracle, a corporation that slowly sucks the life out of everything it touches.

Re: Why PostgreSQL is better than MySQL

#52

Post by wvenable »

true_religion wrote: post #43
icebraining wrote: post #40

Earlier quoted context omitted.

Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years. Coincidentally, using those two features together on MySQL can have fun results: "Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE sta…

> An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. Insanity. Also pretty much the reason I stopped using MySQL. I am not a DBA. A very small part of my job is actually interacting with the database, and I have to trust that statement execution will be consistent in all cases. That's something PostgresQL has been able to give for the p…

> and I have to trust that statement execution will be consistent in all cases.

I'm pretty sure no database engine guarantees the ordering of un-ordered SELECT statements.

Re: Why PostgreSQL is better than MySQL

#53

Post by pgaddict »

true_religion wrote: post #43
icebraining wrote: post #40

Earlier quoted context omitted.

Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years. Coincidentally, using those two features together on MySQL can have fun results: "Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE sta…

> An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. Insanity. Also pretty much the reason I stopped using MySQL. I am not a DBA. A very small part of my job is actually interacting with the database, and I have to trust that statement execution will be consistent in all cases. That's something PostgresQL has been able to give for the p…

I don't want to be bashing MySQL, but this nicely illustrates the project's approach to engineering - if there's an issue, put it into the docs as a known limitation.

Furthermore, MySQL manual actually has "Restrictions and Limitations" (which is an interesting read too), yet this "unsafe" nature of "INSERT ... ON DUPLICATE KEY UPDATE" is not mentioned there.

I'd say that if PostgreSQL learned something from MySQL, it's "Never accept feature that is incompatible with existing features, even if it's promised to be fixed soon."

Re: Why PostgreSQL is better than MySQL

#54

Post by arekkas »

The reality, for once, met my expectations in this article. Making imperative assumptions and backing them up with a poorly written 450 word count article that some angry (literally: "OMG sorry LMAO there is a 14,5 year old bug that was solved just now so the product must definitely be worse than the other one") developer wrote down in two minutes. That's what the title implied instantaneously.

Sometimes I don't understand the HN community. There is so much good stuff on this page, but at other times things that certainly don't deserve any attention make it to front page.

Re: Why PostgreSQL is better than MySQL

#55

Post by pizza234 »

icebraining wrote: post #40
craigkerstiens wrote: post #9

I'm a huge Postgres fan and yes I'm probably biased towards it. I do think it's probably the best open source database out there, but I also don't love these types of posts. What makes something better than another things is a whole host of things, not just highlighting one really bad case from the other side. I'm sure if the MySQL world wanted they could point out ways that MySQL is so much better than Postgres. Jus…

Just look back at how long it too for us to get upsert, we're finally getting a better replication story in recent years. Coincidentally, using those two features together on MySQL can have fun results: "Because the results of INSERT ... SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT ... SELECT ON DUPLICATE KEY UPDATE sta…

You're wrong, and interestingly, you're omitting the key part:

> Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode

The problem you're writing about is not related to MySQL itself, it's a problem implicit in statement-based replication. Row/mixed mode replication has been available in MySQL since 5.1 (almost 10 years ago).

DBAs must know how replication works, as RDBMSs are not toys. It's naive to think that replication in other RDBMs itjustworks! - see, for example, the replication bug that caused slave corruption to the famous transportation company.

The choice to raise a warning rather than stopping replication is arguable, but it's far from being "insanity".

Re: Why PostgreSQL is better than MySQL

#56

Post by pgaddict »

danudey wrote: post #38
craigkerstiens wrote: post #9

I'm a huge Postgres fan and yes I'm probably biased towards it. I do think it's probably the best open source database out there, but I also don't love these types of posts. What makes something better than another things is a whole host of things, not just highlighting one really bad case from the other side. I'm sure if the MySQL world wanted they could point out ways that MySQL is so much better than Postgres. Jus…

I remember ages ago (a decade? or so?) I started working at a company using Postgres for a project, and one of the things my boss asked me to do was to set up replication for the database. We had it on all of our MySQL databases, and everyone knew (myself included) that Postgres was a more serious, enterprise-y, "real" database. Well, I googled around and couldn't find anything conclusive, so I hopped into the / chan…

Well, yeah.

Sorry to hear that your interactions with the community was not great. My experience is quite different - perhaps I was lucky, or maybe it's because I started contributing to the project, not sure. But I stuck with PostgreSQL for exactly the same reason why you left it - the community.

I'm sure there are still people who respond in the "RTFM" or "Try rebooting it" style, but I doubt we're alone in that. One thing that is still generally disliked on the mailing lists are people treating it as unpaid production support ("Hey, I've started using your open source database, and not it failed and the production DB is down. Help me! Pronto!"). And I kinda share that opinion.

Perhaps it's worth giving the community another try? There's loads of new people contributing to it, so hopefully a more diverse audience.

Regarding the replication, before the built-in replication was added to PostgreSQL 9.0, the expectation was that replication will be implemented as a plugin, using some sort of internal API. That's kinda how PostgreSQL does stuff - it's extremely extensible (functions, data types, operators, indexes, ...) so the development community expected that to be the case for replication too.

But the API never materialized, because no one knew how it should look, because no one attempted to implement a plugin using a non-existent API. Chicken-egg problem. That changed though, and nowadays the built-in replication is considered a great feature.

Interestingly enough, the logical replication added gradually over the several recent releases (and particularly in PostgreSQL 10) is implemented using the API approach, and it worked nicely in this case.

Of course, this does not make your past experience any better, but hopefully it illustrates that the development community learned from that.

Re: Why PostgreSQL is better than MySQL

#57

Post by zeroxfe »

danudey wrote: post #38
craigkerstiens wrote: post #9

I'm a huge Postgres fan and yes I'm probably biased towards it. I do think it's probably the best open source database out there, but I also don't love these types of posts. What makes something better than another things is a whole host of things, not just highlighting one really bad case from the other side. I'm sure if the MySQL world wanted they could point out ways that MySQL is so much better than Postgres. Jus…

I remember ages ago (a decade? or so?) I started working at a company using Postgres for a project, and one of the things my boss asked me to do was to set up replication for the database. We had it on all of our MySQL databases, and everyone knew (myself included) that Postgres was a more serious, enterprise-y, "real" database. Well, I googled around and couldn't find anything conclusive, so I hopped into the / chan…

I'm a Postgres fan too, and I remember this -- it really sucked. I loved Postgres, but setting up replication with it was basically duct-tape and string.

Not to derail the thread too much, but this is exactly what's going on with Go and generics right now (and I'm a Go fan too.) "Why do you need generics?" "Generics don't solve all your problems" "Generics don't work well" "No language does generics perfectly".

Re: Why PostgreSQL is better than MySQL

#58

Post by tbrock »

zeroxfe wrote: post #57
danudey wrote: post #38

Earlier quoted context omitted.

I remember ages ago (a decade? or so?) I started working at a company using Postgres for a project, and one of the things my boss asked me to do was to set up replication for the database. We had it on all of our MySQL databases, and everyone knew (myself included) that Postgres was a more serious, enterprise-y, "real" database. Well, I googled around and couldn't find anything conclusive, so I hopped into the / chan…

I'm a Postgres fan too, and I remember this -- it really sucked. I loved Postgres, but setting up replication with it was basically duct-tape and string. Not to derail the thread too much, but this is exactly what's going on with Go and generics right now (and I'm a Go fan too.) "Why do you need generics?" "Generics don't solve all your problems" "Generics don't work well" "No language does generics perfectly".

This only changed very recently. The replication story for postgres has been a joke for over a decade and most people won’t run 9 or 10 for a while.

Re: Why PostgreSQL is better than MySQL

#59

Post by pmarreck »

craigkerstiens wrote: post #9

I'm a huge Postgres fan and yes I'm probably biased towards it. I do think it's probably the best open source database out there, but I also don't love these types of posts. What makes something better than another things is a whole host of things, not just highlighting one really bad case from the other side. I'm sure if the MySQL world wanted they could point out ways that MySQL is so much better than Postgres. Jus…

Uber had a pretty good argument not long ago on going from Postgres back to MySQL:

https://eng.uber.com/mysql-migration/

Re: Why PostgreSQL is better than MySQL

#60

Post by pmarreck »

astrodust wrote: post #50
leetrout wrote: post #47

Earlier quoted context omitted.

And I still don't have my DDL transactions... :) ;)

This is a pretty huge deal in some uses cases. It makes undoing a bad migration trivial.

Manually cleaning up a bad migration fucking sucks. The best you can do is make migrations idempotent in both directions.
Post reply on HN