What is PostgreSQL and why it's better than MySQL?

PostgreSQL is a general purpose object-relational database management system (ORDBMS) developed by a worldwide team of volunteers. It is a powerful and most advanced open source database system. PostgreSQL was developed based on POSTGRES 4.2 at Berkeley Computer Science department, University of California.

PostgreSQL is not controlled by any corporation or other private entity and the source code is available under PostgreSQL license, a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form.

PostgreSQL was designed to run on UNIX-like platforms. However, PostgreSQL was then also designed to be portable so that it could run on various platforms such as Mac OS X, Solaris, and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures. It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl,Python, Ruby, Tcl, ODBC.

PostgreSQL's advanced features -

1. User-defined types
2. Table inheritance
3. Sophisticated locking mechanism
4. Foreign key referential integrity
5. Views, rules, subquery
6. Nested transactions (savepoints)
7. Multi-version concurrency control (MVCC)
8. Asynchronous replication
9. Tablespaces
10. Point-in-time recovery

What make PostgreSQL better than MySQL

Relational databases have been in use for a long time. They became popular thanks to management systems that implement the relational model extremely well, which has proven to be a great way to work with data. But PostgreSQL is the first database management system that implements multi-version concurrency control (MVCC) feature, even before Oracle. The MMVC feature is known as snapshot isolation in Oracle. Let's take a look other advantages of PostgreSQL.

1. ANSI Standard Compatible

Compared to earlier versions, MySQL has made progress in the standards area - the philosophy behind MySQL is that they'll support non-standard extensions if the customers like them. But with PostgreSQL, the standards were built into the platform from the start, whereas MySQL added them later.

2. Fully ACID Compliant

PostgreSQL has one storage engine. MySQL has nine, but only two of those really matter to most users: MyIsam and InnoDB. MyIsam was the original engine, built for speed, but it lacked transactions; InnoDB has transactions and is speedier than MyIsam, which is why it's the default storage engine. Both MySQL's InnoDB and PostgreSQL are fully ACID compliant

3. Table Changes Without Locking

MyIsam uses table-locking to gain speed. That's fine if many sessions involve reading - but when writing to a table, the writing session gets exclusive access and other sessions must wait until its finished. But PostgreSQl and InnoDB both use row-level locking.

4. Subqueries Can Be Problematic

Subqueries were one of MySQL's major weaknesses for a long time - it was notorious for losing its way with two or more levels of sub-queries. Since 5.6.5, though, there have been major improvements but PostgreSQL is still considered better for joins especially as MySQL doesn't support Full Outer Joins.

5. JSON Support and NoSQL

This is a recent addition to PostgreSQL, and it does make the platform more appealing to anyone who wants to try out NoSQL and store JSON files in the database. It gives greater flexibility on how data is stored compared to traditional relational databases; with PostgreSQL, you can have the best of all worlds.