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

PostgreSQL is more popular than MySQL

databasefriends.co

Post reply on HN
Print view View original

41–50 of 51 posts

Re: PostgreSQL is more popular than MySQL

#41

Post by xtrumanx »

cwyers wrote: post #37
Amezarak wrote: post #36

Earlier quoted context omitted.

It makes some sense to me. I always find it pretty painful writing anything but the most simple queries in MySQL - for starters, there's no CTEs, no windowing functions, and no table-valued functions, all of which I find invaluable - and the query optimizer (and performance in general) seems... not so hot. As far as I can tell, the main reasons people use MySQL are 1) it's well-known, well-documented, free, and widel…

I used MySQL a lot at my last job (MS SQL Server now, which has its own ups and downs) and I examined using Postgres for some personal projects, and there's a few features that I used rather heavily in MySQL (partitioned tables and upsert are the really big ones) that Postgres doesn't have an out-of-the-box equivalent to. There's a lot of tradeoffs between the two, I think it's wrong to say one is better than the oth…

Here's a great article[0] regarding different ways to accomplish to upsert in Postgres. I bring it up because the author adds his thought on why Postgres hasn't implemented it unlike the other open source RDBMS:

> As a final word – yeah, but why PostgreSQL doesn't handle it internally, as the other database?

> Answer lies somewhere between "developers have other things to do first" and "it's complicated, because there are many ways to do it, and the most correct one is slow – so there is a trade off decision to be made".

I'm not sure how upsert is implemented in MySql but given its reputation and the fact that it is open source; I would imagine the Postgres team has considered their solution and have decided it wasn't good enough for their standards.

Postgres may not have an out-of-the-box solution but there are solutions out there which are mostly pretty trivial functions. Although they may add a bit of bloat since it seems you'd need a function for each table you'd want to use upsert on.

[0] http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...

Re: PostgreSQL is more popular than MySQL

#42

Post by davvolun »

thehal84 wrote: post #19

I'll stick with MySQL, the features and stability just keep getting better. Replication, Memcache Plugin (with amazing write and read performance that supports replication), InnoDB Barracuda compression, JSON datatypes (in 5.7), it has scaled amazing well for us at Enginuity Search Engine. I should do a write up on our company blog sometime about all this.

It's not a deal-breaker per se, but I need someone to explain to me why, at this point in history, you need to make a choice, at some level, between relational integrity and full text indexing (InnoDB vs MyISAM)?

Re: PostgreSQL is more popular than MySQL

#43

Post by parasubvert »

Someone1234 wrote: post #22
jasondc wrote: post #12

This survey (which seems more thorough) has MySQL ahead of Postgres: http://db-engines.com/en/ranking Ranking methodology: http://db-engines.com/en/ranking_definition Google trends also shows MySQL above Postgres in popularity: http://www.google.com/trends/explore#q=%2Fm%2F05ynw%2C%20mys...

I legitimately don't understand how Oracle remains so popular. I mean, I use it at work, but that is because this project dates back to the late 1980s(!) and decisions were made 20+ years ago. It doesn't even support a Boolean type(!), costs a ton (over 2x MS SQL, 4x Postgres), passwords are limited to 30 characters, up until 2007 passwords were case insensitive, error feedback is "basic," and the syntax is horrific.…

Oracle remains popular because people are buying it for very different reasons than you bring up. Most of those matters (data types, really?) are trivial compared to things like its approach to clustering, partitioning, indexing, replication, OLAP queries, approach to locking, query optimization, and recovery. Having DBA'd and developed against Sybase, Oracle, and SQL server, Oracle remains both magnificent and horrific in its comprehensiveness.

That said, SQL server is quite competitive. I still prefer Oracle to the others, I presume it's just a personal preference.

Postgres or MySQL are fine alternatives if you really only need a small subset of the features. Trouble is, most enterprises like to throw on the pork with most projects.

Re: PostgreSQL is more popular than MySQL

#44

Post by bradleyfalzon »

davvolun wrote: post #42
thehal84 wrote: post #19

I'll stick with MySQL, the features and stability just keep getting better. Replication, Memcache Plugin (with amazing write and read performance that supports replication), InnoDB Barracuda compression, JSON datatypes (in 5.7), it has scaled amazing well for us at Enginuity Search Engine. I should do a write up on our company blog sometime about all this.

It's not a deal-breaker per se, but I need someone to explain to me why, at this point in history, you need to make a choice, at some level, between relational integrity and full text indexing (InnoDB vs MyISAM)?

I believe InnoDB does have this now in MySQL 5.6: http://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index...

Re: PostgreSQL is more popular than MySQL

#45

Post by feld »

bradleyfalzon wrote: post #44
davvolun wrote: post #42

Earlier quoted context omitted.

It's not a deal-breaker per se, but I need someone to explain to me why, at this point in history, you need to make a choice, at some level, between relational integrity and full text indexing (InnoDB vs MyISAM)?

I believe InnoDB does have this now in MySQL 5.6: http://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index...

But it's not yet equivalent to Postgres' fulltext

Re: PostgreSQL is more popular than MySQL

#46

Post by nieve »

cwyers wrote: post #37
Amezarak wrote: post #36

Earlier quoted context omitted.

It makes some sense to me. I always find it pretty painful writing anything but the most simple queries in MySQL - for starters, there's no CTEs, no windowing functions, and no table-valued functions, all of which I find invaluable - and the query optimizer (and performance in general) seems... not so hot. As far as I can tell, the main reasons people use MySQL are 1) it's well-known, well-documented, free, and widel…

I used MySQL a lot at my last job (MS SQL Server now, which has its own ups and downs) and I examined using Postgres for some personal projects, and there's a few features that I used rather heavily in MySQL (partitioned tables and upsert are the really big ones) that Postgres doesn't have an out-of-the-box equivalent to. There's a lot of tradeoffs between the two, I think it's wrong to say one is better than the oth…

I've used PostgreSQL for 15 years and missed upsert/MERGE for all of them, but my understanding from previous research is that MySQL's is actually unreliable in the sense that it can violate ACID in some circumstances. This is believable to me, MySQL's performance lead on whole table count(*) is because it doesn't actually count the currently visible rows for large tables. Complete violation of sql's count semantics, but most people don't know or care.

I'm digging up some references now.

Re: PostgreSQL is more popular than MySQL

#47

Post by nieve »

nieve wrote: post #46
cwyers wrote: post #37

Earlier quoted context omitted.

I used MySQL a lot at my last job (MS SQL Server now, which has its own ups and downs) and I examined using Postgres for some personal projects, and there's a few features that I used rather heavily in MySQL (partitioned tables and upsert are the really big ones) that Postgres doesn't have an out-of-the-box equivalent to. There's a lot of tradeoffs between the two, I think it's wrong to say one is better than the oth…

I've used PostgreSQL for 15 years and missed upsert/MERGE for all of them, but my understanding from previous research is that MySQL's is actually unreliable in the sense that it can violate ACID in some circumstances. This is believable to me, MySQL's performance lead on whole table count(*) is because it doesn't actually count the currently visible rows for large tables. Complete violation of sql's count semantics,…

http://lucumr.pocoo.org/2014/2/16/a-case-for-upserts/ "There is an obvious question what happens if there are two unique constraints on a table, and the answer is quite simply that it breaks down in that case. If there are two uniques MySQL will not give an error but just execute the operation as if it was happening on the first row that matches. Essentially the where is extended to be be an or for any of the unique constraints compared to the values from the insert statement. This is obviously dangerous and definitely not optional."

Re: PostgreSQL is more popular than MySQL

#48

Post by jeltz »

vidarh wrote: post #14
carsongross wrote: post #6

The ability to spin up and sync a follower db, for eventual cut over... man I'd kill for that in MySQL.

Replication is an area where MySQL is still far ahead of Postgres. And I say that as someone who has almost exclusively used Postgres for the last 6 years or so. Postgres is finally starting to catch up, but it's taken forever.

Not really. PostgreSQL has excellent synchronous replication which can be controlled per transaction. This is missing in MySQL. What PostgreSQL is missing is logical replication (both master-slave and master-master. So depending on your use case either could be ahead of the other.

I am almost 100% certain PostgreSQL will have have stable master-master replication within 1.5 years. The team working on it has come really far along.

Re: PostgreSQL is more popular than MySQL

#49

Post by vidarh »

jeltz wrote: post #48
vidarh wrote: post #14

Earlier quoted context omitted.

Replication is an area where MySQL is still far ahead of Postgres. And I say that as someone who has almost exclusively used Postgres for the last 6 years or so. Postgres is finally starting to catch up, but it's taken forever.

Not really. PostgreSQL has excellent synchronous replication which can be controlled per transaction. This is missing in MySQL. What PostgreSQL is missing is logical replication (both master-slave and master-master. So depending on your use case either could be ahead of the other. I am almost 100% certain PostgreSQL will have have stable master-master replication within 1.5 years. The team working on it has come real…

Lacking master-master is a huge thing. And I still curse at Postgres whenever I set up replication. There's no good reason why I should have to worry about wal-archiving, for example.

I'll admit I've never, ever wanted synchronous replication, so that's not a feature I've cared about.

I agree it will probably get there. But from my point of view it is still substantially lagging.

Re: PostgreSQL is more popular than MySQL

#50

Post by jeltz »

vidarh wrote: post #49
jeltz wrote: post #48

Earlier quoted context omitted.

Not really. PostgreSQL has excellent synchronous replication which can be controlled per transaction. This is missing in MySQL. What PostgreSQL is missing is logical replication (both master-slave and master-master. So depending on your use case either could be ahead of the other. I am almost 100% certain PostgreSQL will have have stable master-master replication within 1.5 years. The team working on it has come real…

Lacking master-master is a huge thing. And I still curse at Postgres whenever I set up replication. There's no good reason why I should have to worry about wal-archiving, for example. I'll admit I've never, ever wanted synchronous replication, so that's not a feature I've cared about. I agree it will probably get there. But from my point of view it is still substantially lagging.

You will not have to worry about WAL archiving anymore in 9.4 with the addition of replication slots.
Post reply on HN