Re: Independent comparison of PostgreSQL and MySQL

On 09/10/14 07:26, Stephen Cook wrote:
> Hello!
>
> I have a client who is looking to move from SQL Server to MySQL,
> however they are open to considering PostgreSQL instead.
>
> Can anyone link me to any white papers, studies, comparisons, etc that
> are independent / unbiased (i.e. not written by MySQL or PostgreSQL
> organizations)?
>
> Thanks!
>
> -- Stephen
>
>
>
Have searched for MySQL vs PostgreSQL comparisons 5 times over the last
12 years, each time PostgreSQL comes out a ahead for reliability,
performance, and other important considerations.

I have also noticed a trend for people to migrate to PostgreSQL, from
both MySQL and Oracle.

Personally I find PostgreSQL a lot easier to use, both to administer and
to query against.

Some references:...

https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems
[...]
PostgreSQL is /the/ advanced, open-source [object]-relational database
management system which has the main goal of being standards-compliant
and extensible. PostgreSQL, or Postgres, tries to adopt the ANSI/ISO SQL
standards together with the revisions.

Compared to other RDBMSs, PostgreSQL differs itself with its support for
highly required and integral object-oriented and/or relational database
functionality, such as the complete support for reliable transactions,
i.e. Atomicity, Consistency, Isolation, Durability (ACID).

Due to the powerful underlying technology, Postgres is extremely capable
of handling many tasks very efficiently. Support for concurrency is
achieved without read locks thanks to the implementation of Multiversion
Concurrency Control (MVCC), which also ensures the ACID compliance.

PostgreSQL is highly programmable, and therefore extendible, with custom
procedures that are called "stored procedures". These functions can be
created to simplify the execution of repeated, complex and often
required database operations.

Although this DBMS does not have the popularity of MySQL, there are many
amazing third-party tools and libraries that are designed to make
working with PostgreSQL simple, despite this database's powerful nature.
Nowadays it is possible to get PostgreSQL as an application package
through many operating-system's default package manager with ease.
[...]

http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL

http://www.scriptrock.com/articles/postgres-vs-mysql
[...]
For new projects, determine if you are going to port to closed software
later on. In that case, PostgreSQL is closer to Oracle, and any code
made for PostgreSQL will port to Oracle easily. MySQL has moved away
from the SQL standard somewhat. PostgreSQL is more reliable because it
is ACID (Atomicity, Consistency, Isolation, and Durability) compliant
which means queries will maintain data integrity, and return the same
output without error.

MySQL is less reliable and not ACID compliant: The way it handles
foreign key references, auditing and transactions make it less reliable.
[...]

http://www.openlogic.com/wazi/bid/188125/PostgreSQL-vs-MySQL-Which-Is-the-Best-Open-Source-Database
[...]
PostgreSQL is a 100% community-driven open source project, maintained by
a worldwide community of more than a thousand contributors. It provides
a single completely functional version, rather than the multiple
different community, commercial, and enterprise versions that MySQL
offers. Its license is a liberal BSD/MIT-type, which allows
organizations to use, copy, modify, and redistribute code with only a
copyright notice required.

Reliablity is PostgreSQL's top priority. It is known for being
rock-solid and well-engineered, capable of supporting high-transaction,
mission-critical applications. Documentation is first-rate, with
comprehensive manuals available for free online, along with archives of
manuals for older releases. Community support is excellent, and
commercial support <http://www.postgresql.org/support/> is available
from independent vendors.

Data consistency and integrity are also high priorities. PostgreSQL is
fully ACID-compliant <http://en.wikipedia.org/wiki/ACID> (atomicity,
consistency, isolation, durability.) It has strong security for
controlling access to the database, making good use of enterprise
security tools such as Kerberos
<http://olex.openlogic.com/packages/mitkerberos> and OpenSSL
<http://olex.openlogic.com/packages/openssl>. You can define your own
checks to ensure data quality according to your own business rules. A
favorite feature of many admins is point-in-time recovery (PITR), a
flexible high-availability feature with powers such as the ability to
create a warm standby server for fast failover, and snapshots and
restores to specific points in time. But that's not all -- the project
provides several methods to manage PostgreSQL for high availability,
load-balancing, and replication, so you can use what fits your
particular needs.
[...]
Yahoo runs a multi-petabyte modified PostgreSQL database that processes
billions of events per day
[...]
A common misconception is that MySQL is easier to learn than PostgreSQL.
Relational database management systems are all complex, finicky beasts,
and these two have comparable learning curves.
[...]

MySQL does not enforce NOT NULL properly and other data inegrity
gotchas, unlike PostgreSQL:
http://www.youtube.com/watch?v=emgJtr9tIME

http://stackoverflow.com/questions/110927/would-you-recommend-postgresql-over-mysql
[...]
We use MySQL where I work, and we've looked at PostgreSQL (it's running
for one small system, as a test). They both have their pluses and
minuses. Note that for the purposes of this discussion I refer to MySQL
5.0.x with InnoDB. While 5.1 may fix some of these things, it's not
stable yet.
[...]
Now I don't have a ton of experience with PostgreSQL. Like I said we've
started to experiment with it. The fact that it doesn't have many of
MySQL's limitations is a big plus. Just the ability to add a column on a
large table without locking the thing for a huge amount of time would be
great for us. PostgreSQL can also use multiple indexes which, again, is
a serious plus. The error messages that PostgreSQL returns can be much
more informative than MySQL. At times when you screw up a query instead
of "this is impossible" or "you can't do that", you get something more
akin to "this is impossible because of X". Again, this is just my
impression. Basically, PostgreSQL feels more like an open source Oracle
(a grown up database) than MySQL (which doesn't feel very Oracle-y).
[...]
My advice? While I have less experience with it if I was starting with a
new system I think I'd go with PostgreSQL. I've seen enough weirdness in
MySQL that I'd be willing to try it. We haven't had any problems with it
so far, and I know many people use it.
[...]

http://datachomp.com/archives/top-10-reasons-i-like-postgres-over-sql-server
[...]
2. Compression out of the box. With SQL Server, compression is an
"Enterprise Edition and Up" feature which means you are spending the
cost of at least 1 dev in order to get the ability to use compression.
Once you have paid for that ability, you still have to figure out how to
implement it. Postgres does this for you out of the box, automatically
and for free.

3. Concurrent Index Creation. You are going to find a recurring theme
there...this is yet another feature that SQL Server is capable of doing,
but only if you are able to afford the elite and affluent company of
Enterprise Edition. Postgres has your back on this even if you left your
wallet at home.
[...]