On the one hand, Postgres kicks MySQL’s butt in terms of SQL support, query optimizer, and all manner of query-related goodness.

For many database applications - particularly ones with lots of ad-hoc querying - this is enough to “seal the deal” in favor of Postgres.

The one huge advantage of MySQL over Postgres is in the InnoDB (and XtraDB) storage engine, which is extremely good for 24/7 OLTP environments of the sort you’d have in a large production website or customer-facing application. Postgres’ need for a storage garbage collector (VACUUM) basically kills it for these types of applications, which is the biggest reason why Uber switched from Postgres to MySQL.

In these situations, you can work past bad issues in querying for application-generated queries by carefully reviewing and tuning application query forms, but you can’t get past storage engine issues at the application level. This is unfortunate, as I really want to be a huge Postgres fanboy (after all, I’m an original Post-hacker), but I can’t in good conscience advocate it for these particular applications.

But if you have a less active environment or you’re doing hardcore data-mining or data-warehouse stuff? Postgres is awesome for these worlds.

As for “why people like it”, a nontechnical reason is Postgres - unlike MySQL - isn’t part of Oracle’s domain and is an “independent” open-source project. Postgres has a few commercial contributors like EnterpriseDB, but they aren’t the “owners” of the “main fork”, unlike Oracle is with MySQL.

I can answer this question as I have worked extensively on POSTGRESQL, after oracle. Main reason behind people like POSTGRESQL more than MySql is that,it supports Oracle almost. Most of the syntax of Oracle is supported here but not in case of MySql.

Both MySql and Postgres are open source RDBMSs. Both focuses on different areas.
PostgreSQL has traditionally focused on reliability, data-integrity and integrated developer-focused features. It has an extremely sophisticated query planner, which is capable of joining relatively large numbers of tables efficiently.

MySQL, on the other hand, has traditionally focused on read-mostly web apps, usually written in PHP, where the principal concern is with optimising simple queries.

We (our company) have decided to choose postgres over MySql because we have migrated our code from Oracle ;and 90 % syntax and features of oracle does match with PostgreSql and this is the main reason for it’s popularity over MySql.

Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007 Please visit wiki page of postgresql.
Moreover PostgreSql having better documentation than MySql on internet.

please visit this . Why PostgreSql click this link.
Thanks.

In UI world there’s a thing called principle of least surprise / principle of least astonishment. Basically, things reasonably expected to work should work in reasonably expected way and avoid astonishing user.

SQL can also be considered a UI of a peculiar sort.

When I was shortly exposed to MySQL, I had to work with all sorts of legacy or otherwise surprising gotchas, quirks and limitations that simply should have been eliminated from MySQL, but weren’t.

That was annoying. At some point I decided “**** that noise” and switched to Postgres whenever I could.

Few surprises, gotchas, weird behaviours, limitations. Things generally work like you’d expect them to work. Nirvana:

The test of the machine is the satisfaction it gives you. There isn't any other test. If the machine produces tranquility it's right. If it disturbs you it's wrong until either the machine or your mind is changed.

Using MySQL is irritating to me, but somehow I suspect not just to me. Using Postgres for the most part is tranquility. Mind you, not everywhere: e.g. pg_hba.conf is annoying. But the difference in user experience between Postgres and MySQL is vast.

People don’t like PostgreSQL more than MySQL. They love it more than MySQL. This is apparent in the 2020 Stackoverflow Developer Survey. It is 2nd to Redis in the most loved databases. MySQL is in 10th place. And 2nd to MongoDB for the most wanted by developers to learn. MySQL is at 6th place. It is also the 2020 DBMS of the Year according to DB-Engines. PostgreSQL got that title 3 times (2020, 2017, and 2018).

Why?

• For its stability and feature set.
• Supports a very large database size at high speeds (source)
• Supports more advanced data types (source) than MySQL.
• PostgreSQL is not just an RDBMS but an ORDBMS and supports NoSQL features (source). It even rivals MongoDB on NoSQL performance (source).
• License is free to use, modify, distribute (PostgreSQL license). Meanwhile, if you distribute MySQL with a commercially licensed software and do not wish to distribute the source code, you must enter a commercial license with Oracle (MySQL license).

These reasons alone can attract a developer who is looking for additional database skills.

There's an extensive albeit outdated comparison over at the PostgreSQL Wiki: http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009

If you compare MySQL and PostgreSQL I would only consider looking at InnoDB since MyISAM/MariaDB is a completely different beast.

Performance-wise PostgreSQL took the lead a few years ago IIRC since InnoDB and MySQL had quite a few lock contention problems back then when increasing the amount of parallel working backends. These were worked on extensively by Percona as well as MySQL AB and Oracle as of lately and I guess today you can benchmark both databases either way, depending on how you tune your workloads.

From the features point of view the things that speak for MySQL are covering indexes (more efficient lookups when querying data only from indexed columns) as well as the more flexible and less resilient replication mechanisms offered by MySQL. Covering indexes will ship with PostgreSQL 9.2 so this is one less thing that MySQL offers an advantage on.

In the end it depends on the level of familiarity of your developers and operations people, if you've got a team with a very strong MySQL background and the ops people are familiar with Maatkit/Percona Toolkit it probably doesn't hurt to stick with MySQL. If you're only somewhat familiar with MySQL or don't have any strong preference just use PostgreSQL for new projects.

The fewer WTFs, cleaner codebase and active community make more than up for the learning period and I haven't heard of anybody who's looked back to MySQL after switching to Postgres.

To answer why MySQL seems to be still relevant these days: Old habits die hard. Oh, and they offer support contracts from their parent company ;).

I wonder if the notion that MySQL has better performance over Postgres is a legacy claim and really refers to the MyISAM storage engine, which is optimized for speed, at the cost of some crucial features. Transactions, specifically.

Any sane person these days is likely using InnoDB, which is pretty neck and neck with Postgres. From my view the one feature that Postgres lacks that InnoDB has is index-only scans, but fortunately that will be coming in Postgres 9.2.

That being said, Postgres replication was not native until 9.0 and required choosing amongst a handful of community extensions, each with their own pros and cons. So I could see that being a sticking point.

For us, the prime reason for using MySQL over Postgres is the index-organized property of InnoDB, which effectively turns table storage into name-value-pair storage with the primary key of the table being the "name". We take advantage of this to make search tables that allow us to search a large dataset (>2B rows per table in many tables) in interactive time without needing to punt to NoSQL.

Also, we use GROUP_CONCAT extensively to "flatten" search results.

Other than that, I'd prefer Postgres (after all, I helped write it :) ) MySQL's top level is pretty primitive compared to Postgres, and it has only the most simple join methods.

I've even toyed with the thought of porting the InnoDB storage engine to Postgres. Modern versions of InnoDB are quite fast, especially if you're careful with your choice of primary key.

First off, I believe that whatever you're doing, you'll probably be fine using either.

That said, having used both, I'm generally more frustrated when using MySQL than when using PG. Here's a sample of the problems I've encountered with MySQL. I haven't updated my list in a while now - please feel free to correct me on things - but hopefully it's a little more illustrative than the Wikipedia feature matrix, and a little more specific to MySQL vs. PG. (This list is a cleaned-up selection from my notes wiki at http://yz.mit.edu/notes/Hackery)

• No referential integrity.
• No constraints (CHECK).
• No sort merge join, let alone hash-join. http://www.dbms2.com/2008/07/10/how-is-mysqls-join-performance-these-days/, http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/
• Generally poor at analytical workloads, since it's designed for transactional workloads.
• No group commit (!) https://www.facebook.com/note.php?note_id=465781235932
• Can't specify TEMP table more than once in a query - WTF? (Still not fixed!) http://bugs.mysql.com/bug.php?id=10327
• Multiple storage engines has always restricted progress: http://www.mysqlperformanceblog.com/2010/05/08/the-doom-of-multiple-storage-engines/ (PG also supported multiple storage engines in 80s, then concentrated on one)
• No WITH clause: http://stackoverflow.com/questions/324935/mysql-with-clause
• Crappy errors: “Incorrect key file for table ‘stock’; try to repair it” on alter table stock add constraint pk_stock primary key (s_w_id, s_i_id); where stock is in InnoDB (which has no “repair table”) means I have no /tmp space (and Googling for this turned up nothing)
• Optimizer only recently started working properly with certain subqueries
• Crappy EXPLAIN output - somewhat better when using the visual-explain tool from Percona.
• InnoDB auto-extends ibdata1 file; only way to trim (garbage collect) is dumping and loading.
• Scoping is broken: create table t(a int, b int) gives "Query OK, 0 rows affected (3.30 sec)" while select a, (select count(*) from (select b from t where a = u.a group by b) v) from t u gives "ERROR 1054 (42S22): Unknown column ‘u.a’ in ‘where clause'"
• MySQL only recently got such things as per-statement triggers and procedural language support.
• “InnoDB is still broken…Just last week we had to drop/re-create an InnoDB-table in one project because it would not allow to add an index anymore, no matter what we tried…Mysql::Error: Incorrect key file for table 'foo'; try to repair it: CREATE INDEX [...]” http://news.ycombinator.com/item?id=2176062
• MySQL has only its own internal auth system, whereas PG supports a wide array of auth providers.
• PG has more supple ALTER TABLE implementation.
• MySQL doesn’t support ASC/DESC clauses for indexes http://explainextended.com/2010/11/02/mixed-ascdesc-sorting-in-mysql/
• Optimizer leaves plenty to be desired, e.g. not pruning unnecessary joins.
• OK documentation, but still considerably unpolished compared to PG's. Random omission: auto_increment jumps up to next power of 2 but inconsistently across versions (platforms?).
• (Older issue, not sure if it's still relevant) Crappy concurrency, >3 cores sucks vs PG: http://spyced.blogspot.com/2006/12/benchmark-postgresql-beats-stuffing.html
• This is all just comparing the core standard SQL systems, and leaving out things like PostGIS and extra bells-and-whistles like anonymous code blocks (which are useful!).

As for the flip side:

• The biggest disadvantage of PG for me had been the lack of replication, but it's recently gotten both asynchronous and synchronous replication.
• Another recent addition that MySQL had earlier is true serializable transaction isolation.
• There's various other things, e.g., Robert Haas is working on the index-only scans performance optimization, which MySQL has and PG doesn't. (Update: index-only scans were added in Postgres 9.2.)
• Although community and support is large for both, MySQL's is larger.

At BuildFax, we chose MySQL over PostgreSQL--and we use MyISAM for our write-weekly-read-constantly databases. Our main reasons for MySQL over PostgreSQL:

1. When we built our initial cloud architecture in 2009, stable PostgreSQL did not have as good replication or a GROUP_CONCAT equivalent.

2. MyISAM is faster for data that functions as "read only" (or very close to read only)

3. Existing libraries and support for MySQL are better. For example, we use RightScale, which has a fabulous Database Manager server template (handling replication, backups, restores, failovers, etc)... for MySQL.

At the end of the day, it's always been easier for us to use MySQL, and our tests with PostgreSQL showed that switching wouldn't be worthwhile. If we had to do it again, and RightScale gets their PostgreSQL database manager to the same level as the MySQL one, it would come down to figuring out whether the transactional performance advantage of PostgreSQL was more important to us than MyISAM's speed for our read-only data.

This really depends on your application. As a MySQL DBA, after having attended the local PG Day in Austin I walked away with even more respect for PG.

So here are is my (likely MySQL slanted) answer:

Favor Postgres if:
You plan to scale your database vertically (that is through more processors, hardware, etc at it after you have it tuned to maximize what you have).
You plan to work on mission critical stuff. For instance if you are writing a banking back end where data integrity is a key concern, PG's stored procedures and triggers are much more mature.

Favor MySQL if:
You value simplicity.
Your application would scale better horizontally (add more machines rather than grow the machine size)
You don't have access to a DBA for things like backup/recovery, tuning, monitoring, etc.

Finally, PG Day was the first time I felt welcome as a new person in the PG community. It was apparent that the elitism was still there, but that they were making an effort to be more inclusive. MySQL is a come one, come all sort of community where you can often get an expert to answer a question on a forum easily.

It would depend on the application work load. Most applications would be better served by PostgreSQL, never having the types of problems that MySQL has been adapted to solve by companies like Google and Facebook.

MySQL is a simpler design, and less robust. When talking about MySQL, we’re really talking about InnoDB, which is the storage engine MySQL uses. Generally speaking, it is better than PostgreSQL if you are performing primarily key lookups (e.g. “WHERE id = “), especially if you are mutating rows frequently. Uber infamously complained about PostgreSQL when they embarked upon writing a K/V object store on top of PostgreSQL. It was an unfair critique, like complaining that a screwdriver is bad at hammering nails. The reason to prefer MySQL for this workload is because PostgreSQL uses MVCC. It stores each row as a collection of immutable “tuple” datatypes, one for each version of the row. This means that a typical index or table scan needs to skip over all the “tombstoned” tuples representing past versions of the rows. PostgreSQL has a separate process running that occasionally performs a VACUUM operations and cleans up these tombstones, restoring performance.

The MVCC design used by PostgreSQL is AMAZING for performing transaction isolation, so I would strongly prefer PostgreSQL for any workload that requires transactions and strong concurrency guarantees. MySQL can do transactions, but I have run into many strange performance issues and dead-lock scenarios using it even for short lived transactions (>10 ms). Alternatively, I have had PostgreSQL transactions open for up to an hour+ without issue, and this is awesome for fixing data issues during runtime in an online production setting.

MySQL is also preferable if you need strong high-availability guarantees, as there are a ton of tools to choose from to automate its operation. PostgreSQL is catching up though, and can also be operated with HA. PostgreSQL has a lot of nice, automatic features like being able to add data columns and indexes concurrently without locking the DB.

PostgreSQL has a ton of modules and is preferable if you handle GiS data (i.e. map coordinates) or want to have any security features like hashing or cryptography directly in the DB. PostgreSQL also has much more compliant and portable SQL. You can also write procedures in a number of languages, and pretty much turn it into an application server or whatever you want. PostgreSQL also has a much stronger user ACLs system for connections, but this makes it slower to service connection/disconnections.

Overall, they are both great technologies but PostgreSQL is much more feature full, so I would recommend it for most workloads b/c 99.9% of operators will not need the features MySQL is better at.

I can answer that definitively. We chose Postgres because it was operationally more reliable than MySQL and at the time the founders of company believed in the portability of SQL databases.

As the years wore on, we discovered that basically Postgres was this diamond in the rough which had a set of features and a development community which was one of the most unrecognizedly awesome open source projects we'd ever seen, and had been quietly building an incredibly awesome tool without making a big fuss.

Similarly, we learned that SQL databases are not portable, not really, and that trying to build around database portability was a recipe for disaster. We learned from those experience and decided that we would invest heavily in taking Postgres and making it totally awesome to use by bringing the Heroku focus on user experience to bear.

Sure, it's awesome that the Postgres Project isn't controlled by a giant evil mega-corporation, but it was probably that independence and decentralized community that made it hard for them to self-promote in the face of funded companies with a real stake in growing market share. For a long time with Postgres was nobody out there *selling* the database, just an ever-growing community of happy users who were, admittedly, pretty smug about their database. (I should note that EnterpriseDB been around promoting Postgres to an enterprise community for a while now.)

Now Postgres is more or less the first choice of people with experience running production databases. It's more powerful, more reliable, and has a better set of features than any other open source data storage layer out there today. It's extensible via projects like PL/V8 (worthy of entire topic all to itself) and it has tools built into it like full text search that require you to take on whole other *services* when you use other databases.

Don't get me wrong. I'm answering the question: why Postgres, so I'm talking about its strengths here and I want to provide a little balance by pointing out that it's not a silver bullet or a panacea. Other database tools can provide unique advantages. Specifically, I'm a huge fan of Redis, and I don't know which of the many so-called "big data" multi master solutions is going to shake down in the long run, but it's not an area that Postgres is strong today.

There isn’t anything MySQL can do that PostgreSQL can’t do.

However, there are things that PostgreSQL can do that MySQL can’t do. For example:

• In MySQL, CHECK constraints (as of this writing) aren’t supported. The syntax is allowed, but they’re ignored. That’s pretty serious, because database management systems are all about ensuring the integrity and correctness of data in the database, and CHECK constraints are a means to ensure the integrity and correctness of data in the database. See 13.1.18 CREATE TABLE Syntax and search for ‘CHECK’.
• In MySQL, and compared to PostgreSQL, the behaviour of DDL statements with regard to transactions is complex and unpredictable under certain conditions. See 6.6 Additional Notes on DDL and the Normal Transaction

And so on. MySQL is fairly easy to use and it’s often the first SQL DBMS that developers are exposed to, which makes it an easy — or lazy — choice, but that’s rarely a good excuse for using it when better choices — like PostgreSQL — are available.

The answer has two components: Fear of Oracle, and the rise of PostgreSQL as a fully competent alternative.

Fear of Oracle:

While Oracle has - to this point - been a reasonable steward of the main MySQL codebase since it acquired it by buying Sun Microsystems - who bought it from Swedish company MySQL AB - it’s still the Big Bad Wolf in databases as far as many people are concerned.

While Oracle doing something sneaky with licensing - which is the root of most people’s fears - is unlikely with an open-source product, it still means that “Oracle” MySQL has a certain odor around it that drives many away.

Some people who still want to use MySQL but avoid Oracle will use MariaDB, while others use Percona Server.

The Rise of PostgreSQL:

PostgreSQL has improved to the point where it’s quite comparable to MySQL in raw query performance and vastly superior in its query optimizer and SQL standard support.

There are a few things that MySQL does objectively better than PostgreSQL, but not many, and you’re not likely to encounter them in the vast majority of applications, so PostgreSQL is a better choice for many environments.

PostgreSQL is the only major relational DB with an increasing market share: DB-Engines Ranking, but MySQL is still a solid #2, only behind “real” Oracle and in terms of aggregate usage, still quite a ways ahead of PostgreSQL.

One potential reason for PostgreSQL gradually taking over former MySQL users, which I don’t see mentioned yet in the other answers, is the fact that it’s promoting itself as “the only true free & open-source RDBMS”, in response to the take-over of MySQL by the Oracle company.

This is of course a bit exaggerated (since most of the MySQL code is still open source and free), but I can imagine that MySQL became somewhat less “interesting” for people searching for an open source solution. Moreover, there are two side effects of this take-over, which may explain this decreased confidence in MySQL: (1) the development (in terms of new features) of MySQL has considerably slowed down since Oracle became responsible (although it seems like MySQL 8.0 is catching up, but as of this writing it’s not GA yet …); and (2) the birth of MariaDB which is competing with MySQL for the “heritage” of the open source project “MySQL”. This forking seems to have made people hesitate (and therefore maybe have made let them consider PostgreSQL, who knows …)

Historically, the MySQL approach has always been pragmatic: implement what people need, also if the SQL standard does not yet describe it. PostgreSQL’s approach has always been: implement what the SQL standard prescribes, and nothing more than that. In terms of usefulness, for most “low-cost” users, in the early days, the MySQL approach was more attractive. Today (and actually since the more recent SQL standards like SQL:2008 and SQL:2011) standard compliance (and compatibility with other RDBMS) is becoming more important for users, and the standard now includes all useful features so PostgreSQL need not be “less functional” than MySQL, as it has been in the “early days”.

I will summaries the main differences from my point of view. I am objective because I see them both as tools, not lovers. And they both make great things to simplify my life and utterly stupid things to ruin it.

Design choices and features

• PostgreSQL doesn’t update data in place. It keeps old versions of each row. This forces it to have an autovacuum process (and a vacuum manual operation) to do some maintenance work: prevent table bloating, mark obsolete rows in the indexes, prevent transaction id wraparound. This process is CPU-consuming and hard to tune for write-intensive workloads.
• PostgreSQL has no concept of buffer pool. It uses filesystem cache instead.
• MySQL has a storage engine concept.
• The default storage engine, InnoDB, updates data in-place and has a buffer pool.
• Because of its pluggable storage engine interface, MySQL has to log changes twice: in the storage engine transaction logs, and in the binary log.
• PostgreSQL allows to create unlogged tables. MySQL 8 allows to disable the undo log for the whole instance, which is extremely unsafe.
• PostgreSQL has many more indexes.
• PostgreSQL creates a new process for each new connection, which is inefficient and limits scalability. MySQL creates threads. Also, MariaDB and Percona Server some with open source plugins to use a connection pool.
• PostgreSQL optimiser is more advanced, especially when it comes to joins and subqueries.
• MySQL support for hash joins is very recent and it doesn’t have parallel joins.
• MySQL support for CTEs, window functions and lateral queries is very recent.
• PostgreSQL replication is quite primitive, unless you pay for commercial solutions.
• MySQL and MariaDB support certain instantaneous ALTER TABLE operations. PostgreSQL does not.
• PostgreSQL can be customised in many ways.
• PostgreSQL stored procedures are advanced, MySQL stored procedures are primitive and extremely slow.
• MySQL has invisible indexes, PostgreSQL has hypothetical indexes. Each of these features is completely useless when the other is useful.
• PostgreSQL is great for GIS data. Only recently MySQL implemented reference systems.

Governance

MySQL is open source as much as Postgres is. MySQL has a non-free edition, and PostgreSQL has commercial extensions to make it usable when HA is needed. All smart professional can see that they are equivalent in this respect.

MySQL is made by a single company, which produces the most expensive competitor. This is bad. But it also has two competitor forks, and a very smart community which is capable of make criticism and influence their choices.

PostgreSQL is not owned by a company. But as mentioned, some companies in its ecosystem have interests in selling commercial solutions to deal with PostgreSQL weaknesses. Also, a single person (Tom Lane) can impose his view, and does it most of the times - check their mailing list.

Another PostgreSQL problem is that they have not a bug tracker. They have a mailing list, but it is a very bad tool to search for bugs or find out when fixes were released.

MySQL governance problems will not change because there is no way. PostgreSQL governance problems will not change because only a tiny part of its community is capable of internal criticism. If they’d spent half of the time they spend criticising MySQL to ask for a public bug trackers, they could have obtained it 20 years ago - and now they would actually be much better than MySQL.

Don’t misunderstand me, both products have a decent governance at the end of the day. But unlike PostgreSQL fans, I believe that spending hours to list their merits has no possible positive effect, while writing smart criticism could occasionally bring something good.

Conclusion

I would recommend MySQL for most OLTP workloads.

I’d recommend PostgreSQL for analytics-like workloads and applications that use some particular features, like GIS.

It is not even remotely as popular. Let’s first establish a few ground-rules:

a) Number of users

b) Number of server-installations

c) type of DBMS, and hence its purpose

On the first, far and away, by many hundreds of millions, is SQLite, because it’s baked into every Android phone.

On the second, SQL Server is way ahead of everyone, with Oracle a close second — and that’s because SQL Server deals with thousands of small-to-medium businesses, whereas Oracle’s turf is in large businesses, governments, and so on. I haven’t consulted the total revenues chart recently, so I’m not sure whether Oracle’s huge accounts outweigh Microsoft’s larger number of licenses. Oracle ain’t cheap, so from a distance it’s hard to tell.

I cannot imagine by what measure MongoDB outnumbers any of these players: MySQL, SQL Server, etc. However, these numbers are not so simply derived, since any firm of significant size undoubtedly uses a combination of these products, each tailored to a specific (I’m going to invent a term here, so forgive me) data-miniWorld, that is, a subset of the overall DataWorld that describes the firm of interest. It is not uncommon, for example, to view a web page containing results from three or even more DBMSs simultaneously; one specializes in speed; another in depth by related subject; yet another investigates your search-history, and all return their results to the single page you are viewing.

So in many cases it’s simply impossible to say that Firm xyz uses DBMS 123 and count that as in any way meaningful. In the case of a large organization — unnecessary to restrict that category to the Amazons and Googles of the planet — I would include governmental agencies, for starters, and many more.

At the risk of repeating myself, I like to employ two terms when describing a given database: they are Large and Rich, and not mutually exclusive. I define a Large database as having a relatively small number of tables with millions or possibly billions of rows, and a Rich database as having numerous tables, most of which are lookups (ISO Nation codes, Product SKUs, and such), which have relatively few rows each; there may also be a few tables with numerous rows.

It is quite possible for a database to be both large and rich. I have worked on two or three of these — not alone, certainly! In that event, you need a team, and in theory the problems can be diagrammed, but in practice the only path to success is creating a Team. Note the capital T — that is crucial, and in my opinion, this is a difficult concept to explain to Management.

I worked on a Team a while back, and it was the most rewarding experience of my working life. The database in question was ~500 tables, and drenched in security considerations. On the back end, there were seven of us; on the front end about 50 — I never could obtain a precise count. The seven of us each had a distinct role, but we all worked in a single open-office, so communication was easy and free. As might be typical in Toronto, I think I was the only one born in Canada; the other players emigrated from Russia, Rumania, Bulgaria, and Egypt. Everyone spoke English fluently, but I took the opportunity to learn at least a few basic phrases in each language. In some small sense, I think that I provided a little of the glue that turned us from a team into a Team. Depending on who I was addressing, I would switch languages, and pretty soon that became our sort of mantra. It’s also true that no matter what your first language, the language of Tech is English, so we would speak English when required, but gradually we learned the conversational aspects of each other’s languages. As it happens, I am bilingual only in English and French, so on this Team French was of no use. So instead, I learned a smattering of several other languages in the course of this project.

I was not the Team leader… somewhere between Quarterback and Coach — no, both terms are too pretentious. I was perhaps the glue that cemented the individual players into a Team. Even that sounds too lofty. I was in the middle. Whatever happened, it worked.

We had a difficult issue to deal with: the client was a governmental ministry, whose db people were Oracle-folks. The intermediary was one of the largest consulting firms in the world, which specified that the deliverables would be in SQL Server. It was my task to translate the Logical Data Models into Physical Data Models. It was our ETL specialist’s task to code the port from one to the other. Other tasks fell along the lines of converting PL/SQL to T-SQL, and so on down the line. Another role was to write the Queries and SPs.

We were a solid team, and it worked extremely well. I would consider that my most successful project ever. It’s a rare thing to find yourself on a Team and not merely a collection of workers. The difference is immeasurable.

There are plenty of technically detailed answers on the web but I'll point out one very useful feature in PostgreSQL that makes it an ideal choice for developing applications today.

PostgreSQL gives you a lot of freedom in terms of Datatypes. More correctly, Postgres allows you to have SQL and NoSQL datatypes

Say you're developing an application that accepts certain user details from a form/API and stores it in the DB. Consider one of the fields you're accepting is social_media_links. One way is to have different columns for every social media platform like Facebook, Snapchat, Instagram and so on. Another way, which seems far more appealing is to accept a JSON object as {“facebook”:”facebook.com/abc", “twitter”:”twitter.com/xyz”}. This allows you to extend to as many entries as possible while having an easy format to send in for the frontend developer as well. I can think of plenty other cases where Lists, HashMaps, etc would be useful.

NoSQL databases are pretty notorious for being inconsistent and unreliable at the cost of speed schema-free storage. No doubt, these very features allow for quick development and their blazing fast I/O speeds. They're an ideal choice for dumping logs, non critical data to run analytics on and stuff but definitely not for the core of an application you wish to scale and store critical user data in. MongoDB has also had a bunch of security breaches in the recent past

Postgres gives you the benefits of NoSQL datatypes along with full ACID compliance and transactional support. Basically, everything a good SQL database should provide with you. It also has first class support for Django which I personally prefer and better tooling than MySQL.

Footnotes

As for MySQL, my answer is based on running at least the 5.5 version and using the InnoDB storage engine. Many referential integrity and other issues mentioned in various posts are related to using the older and non-transactional MyISAM engine, which is not recommended for production use outside of very limited use-cases (such as temporary tables that can’t fit in RAM).

Modern MySQL’s best use case is in live, production environments, with carefully curated - and typically application-generated - queries that are well-hinted and hand-optimized in advance. We use MySQL to run a very large production dataworld, with over three billion rows in our biggest tables. We have 1 second search performance requirement, which is met by a combination of hand-tuned query search packages and leveraging InnoDB’s “index organized” property.

As for PostgreSQL (I’m going to call it Postgres) versus MySQL, Postgres obliterates MySQL in all manner of query-level goodness, and we use Postgres for data mining and batch analytics databases, where we run lots of very large, hand-coded queries that can take advantage of Postgres’ advantages in join methods (MySQL only supports Nested Loop and Nested Loop over Index, while Postgres supports numerous methods), generally richer querying capabilities, and other good stuff like partial indexes can be used extensively.

Where Postgres falls short in live, “always up” production environments is in its storage engine. Unfortunately, the storage engine need for a garbage collector (VACUUM) is a showstopper in environments like ours.

This and other storage engine issues are covered in this writeup from Uber on their decision to convert their production db world from Postgres to MySQL: Why Uber Engineering Switched from Postgres to MySQL.

Here’s a bit of a rant - mostly in favor of Postgres - that I wrote in 2014 on a similar topic: Greg Kemnitz's answer to Which companies have moved away from PostgreSQL and why?

MySQL does have a few very useful query-level capabilities that I wish Postgres did have:

• INSERT … ON DUPLICATE KEY UPDATE. This is especially useful if you’re updating thousands of rows at once and have the primary keys in-hand.
• INSERT IGNORE - useful for application “crash recovery” restarts.
• GROUP_CONCAT (yes, you can do this functionality in Postgres, but it’s a bit klunky)

MySQL badnesses:

• As mentioned above, very limited join methods.
• It still can’t do constant subqueries without rerunning them every time! (unless they evaluate to a single row)
• Its query optimizer is somewhat better than it used to be, but you still need to hand-hint - mostly by using STRAIGHT_JOIN - app queries on production if you want to have predictable performance.
• Unless you enable “strict mode”, its SQL is too “permissive” and allows buggy constructs such as SELECTing non-grouped columns in GROUP BY (the source of several nasty bugs for us). I’d rather the SQL be more strict and produce errors versus trying to be “clever”.
• Lots of useful “newish” SQL is nonexistent (PIVOT, etc).
• (one I dislike is you can’t do a self-join on a temporary table…)

The main PostgreSQL weakness is its storage engine issues with the “hot production environment” use case.

Another issue with Postgres is replication was implemented at the storage engine level, so it is more expensive than MySQL’s replication, which is now very mature and was implemented at the “query engine level” (partially so it can work independently of storage engine) - allowing for a terse and light-weight replication protocol with a mix of query-text and row data. See the Uber writeup for details on this.

That said, for most use-cases other than a hot production environment, I’d use Postgres.

I first discovered MySQL back in 2003, way back on the 3.23 version. Back then, MySQL was beautiful for me, it was fast, simple, easy to install, easy to implement.

I came from a ODBC Client-Server Environment, and MySQL made it sooo easy to use that I inmediately fell in love with it. I even used MySQL before I learned PHP and never looked back.

On the other hand, I tried PostreSQL. It was harder to use, more strict (which can be a good thing), but it wasn’t as friendly as MySQL. Some people are really into Postgres and believe that it has more features, and it is overall a more “Robust” database. But all in all, MySQL has been a friend to me for such a long time, and has simply gotten the job DONE. So when facing a new database choice in a new project, I only look towards MySQL or SQL Server. They do what I need, and do it well enough. There are not enough compelling reasons to learn a new RDBMS for me, at least not for the moment.

For what? By who? Where?

In my opinion, PostgreSQL is far superior to MySQL. Just general definition and use is more straight forward in PostgreSQL. Extensions make functionality wildly superior. The community that builds PostgreSQL is more active, more supportive and open. The community that supports PostgreSQL is kinder and more active. I think the technology is superior as well.

HOWEVER!!!

I have only a little experience with MySQL. My opinion could be wrong. Further, if your organization has lots of knowledge and experience with MySQL, it may be a better tool for you, just because you’re familiar with it and can make it work. Also, you may have very specific needs that MySQL just does better (personally, I don’t think there’s much in this space, but there is some).

When it comes to choosing something like a relational database management system, you really do have to evaluate a lot of variables, the most important being, what do you and your people already know how to do. That can be the deciding factor.

Short answer, because it “feels” better out of the box.

Nowadays MySQL evolved nicely into a better “SQL”-compliant database.

But PG always had that, more strict, proper “data integrity” than MySQL, by default.

And it helps that PG added support for stuff like JSON fields, proper Full text search, and other extensions.

It doesn’t help that Oracle acquired it. And it doesn’t help that you have many different forks such as MariaDB and now, what defines MySQL?

To be a serious RDBMS it has to behave like a proper RDBMS from the get-go, not after non-trivial customization.

I’d blindly take PG over MySQL. I’d have to spend a few hours coming up with research to back MySQL over PG.

MongoDB is a popular NoSQL database that has gained significant popularity in recent years. One of the reasons for its popularity is its ability to handle large amounts of unstructured data. It provides a flexible data model that can accommodate changes to data structure without the need to modify the schema.

Another reason for MongoDB's popularity is its scalability. It can easily handle a large number of requests and scale horizontally by adding more nodes to the cluster. This makes it a preferred choice for applications that require high availability and scalability.

In addition to its flexibility and scalability, MongoDB also has a large and active community of developers who contribute to its development and provide support. This community has created a rich ecosystem of tools and libraries that make it easy to work with MongoDB.

According to a recent survey conducted by DB-Engines, a website that tracks database popularity, MongoDB is the fourth most popular database in the world, behind only Oracle, MySQL, and Microsoft SQL Server. In the same survey, PostgreSQL, MySQL, and SQLite were ranked 5th, 6th, and 16th respectively.

The survey also revealed that MongoDB is the fastest growing database in terms of popularity, with a growth rate of over 38% in the past year. This indicates that more and more developers are choosing MongoDB over other databases for their projects.

A few apparently lesser known PostgreSQL features:

PostgreSQL has multi-value fields (aka arrays, aka nested tables) which can reduce the need for joins and dramatically increase the performance of storing and retrieving "multi-dimensional" data structures. (you probably do this a lot) www.postgresql.org/docs/9.0/interactive/arrays.html

PostgreSQL also has class based inheritance between tables so that for example "customers" and "suppliers" tables could both inherit common properties such as triggers, columns etc from an "organisations" table. A select on customers would return customers, obviously, and a select on organisations would return customers and suppliers. www.postgresql.org/docs/9.2/static/tutorial-inheritance.html Cool, huh ?

PostgreSQL has a very rich data type system and allows user-defined data types and composite data types. www.postgresql.org/docs/9.2/static/rowtypes.html

PostgreSQL has loadable modules with makes it possible to write stored procedures in other programming languages (C, Perl, Python and even the JavaScript V8 engine code.google.com/p/plv8js/wiki/PLV8)

PostgreSQL is orders of magnitude more sophisticated than MySQL (and it's no more difficult to use)

If you have a production website backed by a relational database, MySQL is reasonable, although you have to be well-aware of its issues.

If you have a query-heavy dataworld where you will do lots of analytics, but your Online transaction processing (OLTP) requirements are periodic or nonexistent, you want PostgreSQL as its query level kicks butt.

In a nutshell, PostgreSQL crushes MySQL in querying, but MySQL - when using the InnoDB storage engine - beats PostgreSQL in short queries and large numbers of quick updates of the sort you’d do in an OLTP workload because of InnoDB’s advantages over Postgres’ storage engine.

Also, Postgres requires a storage garbage collector process (VACUUM) that must be run occasionally or frequently - particularly if you do a lot of UPDATEs or DELETEs - and this process causes pretty significant hits to performance while it’s running.

The problems with dealing with the vacuum cleaner are sufficient that several large Postgres deployments have switched to MySQL over the years.

OLTP workloads are what you usually have in a large DB that is the dataworld for a website or mobile phone app; queries are almost exclusively generated by app code and can be carefully curated and tested. In such a world, MySQL’s limited joins, rather poor query optimizer, and misc query oddness can be dealt with on the application side, while Postgres storage GC can’t really be handled by app-side code and has to be actively “managed” by your operations team.