Well, I'm sure on a site about RDBMSes, you're gonna get some skewed results. In the wild, MySQL sees higher use than PostgreSQL. That said, as an old coworker would say, MySQL is the PHP of RDBMSes. Ubiquitous, gets the job done, and works well mostly. Of course it does horrendous comparisons, conversions, and mutilations of data. Exactly the thing a database should not do.
When did I conflate the two? For that matter, how do you measure "popularity" of a piece of software? You could just as well say a piece of software is popular because everyone knows and loves it or because everyone uses it and hates it. Webster gives you "liked or enjoyed by many people" and "accepted, followed, used, or done by many people".
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 think users of postgresql are usually more experienced, care about features and complete polls than users of mysql who probably don't user foreign-key constraints.
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 widely available; shared hosting, for example, is almost always MySQL and 2) replication. I often wonder if the inadequacies of MySQL are part of the reason for the success of NoSQL databases and general un-hipness of relational databases.
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 other without context.
Postgres does have partitioned tables, although the implementation is different from MySQL's. Upsert stands, though.
I don't disagree there's tradeoffs - I mentioned replication, for example, but it seems to me that MySQL is lacking a lot of features that other DBMS's provide and except for a few use cases, the fewer features unique (or better implemented) in MySQL doesn't make up for it.
Honestly I prefer SQL Server by leaps-and-bounds over the four I've used enough to be very familiar with - MySQL, MS SQL, Oracle, and Postgres - but I think Postgres has much more to offer to most users for most use cases than MySQL.
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.
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."
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.
I'm talking solely based on my experience, which has been much harsher than I wrote in my comment. I know in-person only 1 guy that likes postgresql and he had good reasons for it and experience with many rdbms. While everyone else is like: lol php+mysql for life (talking only people that I know in-person).
Nearly the same experience, but much better is online in my opinion.
Exercise: Go to reddit.com/r/postgresql and reddit.com/r/mysql and see if there is any difference in topics.
The question is what is your favorite. I use MySQL and Postgress. I use MySQL more, but I would say I prefer Postgress for features,so depends what definition of popular you are using (by usage, or by users personal favorite).
At work we are moving from Postgresql to MySQL, simply because for our current needs both will do just fine, and as of today, thanks to galera cluster, a high availability setup in MySQL is much easier to operate.
They've both continued to improve, a lot, but whenever I'm forced to use Mysql, there always seem to be lots of little annoying things that I run into that make me grumpy.
In any event, I'm positive Mysql is actually more widely used. But Fiats are more widely used than Ferraris, too.
Yes, I know your feeling. But sometimes you need to be pragmatic, specially when resources are limited, and in many use cases you can stick to the standard ORM functionality and forget about what's underneath.
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.
Oh and don't get me started on how poorly Oracle maps to programming languages. number(9,0) and number(18,0) is not an intuitive way to map into int32 and int64!
Microsoft's bigint, int, smallint for 64, 32, 16 respectively is far more intuitive.
Mm, my understanding is that it's for a few reasons:
- Oracle have good name recognition (no one ever fired for using Oracle), presumably an army of support people, well trained consultants, etc.
- Postgres traditionally has not been as perforrmant, which makes it less useful for very large datasets.
- Furthermore Oracle perf can usually be improved further by hinting properly, which is not possible in Postgres. Certainly moving like-for-like onto postgres simply led to perf regressions.
I'm not an expert, but those were my main takeaways from talking to people who are.
Once you're married to an RDBMS, it's difficult to move off. And Oracle was first to the market.
If you're a F500 with your general ledger in Oracle, it's just easier to pay a few million dollars for a known evil than to spend who knows how much to replace it with a raft of potential difficulties during a migration project.
After working with it for a few years I hate Oracle as much as the next developer, but most of the time business decisions are made for business reasons, not technical reasons.
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.
In this survey I learned that dBase/Clipper, as well as Access, are relational databases.
I actually liked working with XBase back in the 80s, but I don't miss not having foreign key constraints enforced, not having transactions, having long data values truncated, not being able to take a backup while running.
Early MySQL was too much like XBase for my taste, whereas PostgreSQL felt like a "real" database. I realize the InnoDB backend makes data integrity a bit more of a priority, but MySQL always felt like "worse is better", even if a bunch of "will code HTML for food" 90s internet crack heads helped make it ubiquitous. Windows is ubiquitous, and that doesn't mean much to me.
It might be the db that more people like, but MySQL is more popular by a wide margin. If only because it runs WordPress which runs like 20% of the internet.
Others have replied with basically the same thing but replication has been in MySQL for a long time. Just last night I set up replication for a single MySQL server to a new 3-node Galera cluster then switched over the Galera cluster as the primary with only a few seconds of downtime. The downtime could have been entirely eliminated if the standalone MySQL server had binary logging enabled already.
Galera cluster is an awesome multi-master solution and I've had nothing but excellent experience with it in production environments. We currently typically direct writes at a single node and spread reads in round-robin to all three nodes using an HAProxy front-end to the cluster, but there is nothing preventing us from spreading writes across the cluster if it was needed. If the current write node goes down there is only a couple seconds of interruption as a new write node is elected and the application reconnects. Furthermore, if a node goes down rejoining is completely automatic once it starts back up with no slowdown in reads and writes. I don't have near as much PostgreSQL experience but replication and failover have been serious pain points from an operations stand-point when I have had to use it. I appreciate the end-user features of PostgreSQL, but operationally MySQL/MariaDB (plus Galera cluster) are miles ahead of anything I've seen for PostgreSQL.
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.
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.
So far as liking one versus the other; they are the same. Nobody is going to say "I like MySQL over MariaDB because I have to change MarioDB options to make it work the same as MySQL".
