Thread: Independent comparison of PostgreSQL and MySQL

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

Stephen,

* Stephen Cook (sclists@gmail.com) wrote:
> I have a client who is looking to move from SQL Server to MySQL,
> however they are open to considering PostgreSQL instead.

Glad to hear that!

> Can anyone link me to any white papers, studies, comparisons, etc
> that are independent / unbiased (i.e. not written by MySQL or
> PostgreSQL organizations)?

While it's not quite exactly what you're looking for, I always found it
very interesting and enlightening..:

http://sql-info.de/mysql/gotchas.html

vs.

http://sql-info.de/postgresql/postgres-gotchas.html

    Thanks,

        Stephen

On 9 October 2014 08:28, Stephen Frost <sfrost@snowman.net> wrote:
\> While it's not quite exactly what you're looking for, I always found it
> very interesting and enlightening..:
>
> http://sql-info.de/mysql/gotchas.html
>
> vs.
>
> http://sql-info.de/postgresql/postgres-gotchas.html

+1 for those links

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 is available from independent vendors.

Data consistency and integrity are also high priorities. PostgreSQL is fully ACID-compliant (atomicity, consistency, isolation, durability.) It has strong security for controlling access to the database, making good use of enterprise security tools such as Kerberos and 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.
[...]

Stephen Cook wrote on 08.10.2014 20:26:
> 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)?
>

I maintain a high level feature comparison here:

    http://www.sql-workbench.net/dbms_comparison.html

There are several quirks in MySQL which might make real life harder than a plain feature comparison might express.

One of the really annoying things is that it actually lies about what it is doing.
Officially it does not support a full outer join, and something like:

   select  *
   from t1
     full outer join t2 on t1.id = t2.id

will be rejected with an error message (which is acceptable)

But, using a slightly different syntax:

   select *
   from t1
     full join t2 using (id);

the outer join is accepted(!) but it is silently executed as an inner join

   http://sqlfiddle.com/#!9/96d1e/2

It's locking behaviour is also a bit weird. Take the following example:

   create table foo
   (
      id integer not null primary key,
      c1 integer not null
   );


Then insert 10 rows into that table (id = 1...10) and some random values into c1.

Then in one session (autocommit off) do this:

   update foo
     set c1 = c1 + 1
   where id between 1 and 5;

and in a second sesson do this:

   update foo
     set c1 = c1 + 1
   where id between 6 and 10;

The second session is updating completely different rows than the first one, yet it is blocked by the first one
nevertheless(using InnoDB which is supposed to do row level locking) 


But my "favorite" example, is this:

    delete from orders
    where '1x';

    --> deletes all rows from the table


    delete from orders
    where 'abc';

    --> will not delete anything


The lack of modern features like window functions or recursive queries might seem like just a little annoyance, but I
haveseen queries that had to work around that, which ran several times slower on MySQL than the comparable solution
usinge.g. window functions. 

If you monitor Stackoverflow, you'll notice that questions regarding MySQL that might require recursive queries pop up
therevery frequently 
http://stackoverflow.com/questions/tagged/recursive-query+mysql

So that isn't an "exotic" feature.

Or the "greatest-n-per-group" problem which can most of the time be solved quite efficiently using window functions:
http://stackoverflow.com/questions/tagged/mysql+greatest-n-per-group


Thomas

Thomas Kellerer wrote on 08.10.2014 22:41:
>> 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)?
>>
>
> There are several quirks in MySQL which might make real life harder than a plain feature comparison might express.

Oh, and I forgot this one:

http://www.sql-workbench.net/consistent_updates.html

Thomas Kellerer <spam_eater@gmx.net> wrote:

> There are several quirks in MySQL which might make real life
> harder than a plain feature comparison might express.
>
> One of the really annoying things is that it actually lies about
> what it is doing.

Along those lines, I remember when that in a 2009 paper on
concurrency techniques[1] Michael J. Cahill noted that the work of
a transaction in MySQL is made visible to other transactions, and
the COMMIT request (or stand-alone statement) returns to the
caller, before the work of the transaction is guaranteed to appear
if there is a crash and subsequent recovery.  Essentially, the only
mode available in MySQL was what you get with PostgreSQL if you
request synchronous_commit = off.  PostgreSQL defaults to waiting
to make the transaction visible and returning to the caller until
after it is guarateed to persist; although it gives you the option,
on a transaction-by-transaction basis, to take the faster route of
skipping that guarantee.

(Apologies if that was covered in one of the referenced links -- I
skimmed them and didn't spot this issue, but it might be there
somewhere....)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1] Michael James Cahill. 2009.
    Serializable Isolation for Snapshot Databases.
    Sydney Digital Theses.
    University of Sydney, School of Information Technologies.
    http://hdl.handle.net/2123/5353

On 10/08/2014 02:47 PM, MARK CALLAGHAN wrote:
> Where is the claim about transactions being visible before crash-safety
> in his thesis? I didn't find it via a quick search of the pdf.
>

Page 75

"InnoDB holds a transaction’s locks until the point in transaction
commit just before the write-ahead log is flushed. In other words, locks
are released and changes become visible before log records are
guaranteed to be on stable storage"

> --
> Mark Callaghan
> mdcallag@gmail.com <mailto:mdcallag@gmail.com>


--
Adrian Klaver
adrian.klaver@aklaver.com

Michael Cahill <mjc@wiredtiger.com> wrote:

> I feel like I've waded into something here without all of the
> context, but Kevin is referring to some text in section 4.4 on
> page 63 of my thesis

Sorry for the distraction -- the original poster was asking for
references to differences between MySQL and PostgreSQL from sources
without any affiliation with either.  That paragraph of your thesis
came to mind.  (I've read it dozens of times.)

> I do not know whether this is still true of InnoDB today.

I meant to add that, but lost track before hitting send.  Thanks
for mentioning that.  As it happens, we now allow that behavior on
demand, but it is not the default or only behavior.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

P.S.  It was impressive how fast you picked up on the reference!

I might have a longer answer later. Regardless, nice thesis and it is kind of cool to see it get implemented in PG. 

On Wednesday, October 8, 2014, Kevin Grittner <kgrittn@ymail.com> wrote:

Michael Cahill <mjc@wiredtiger.com> wrote:

> I feel like I've waded into something here without all of the
> context, but Kevin is referring to some text in section 4.4 on
> page 63 of my thesis

Sorry for the distraction -- the original poster was asking for
references to differences between MySQL and PostgreSQL from sources
without any affiliation with either.  That paragraph of your thesis
came to mind.  (I've read it dozens of times.)

> I do not know whether this is still true of InnoDB today.

I meant to add that, but lost track before hitting send.  Thanks
for mentioning that.  As it happens, we now allow that behavior on
demand, but it is not the default or only behavior.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

P.S.  It was impressive how fast you picked up on the reference!

--
Mark Callaghan
mdcallag@gmail.com

Hi Mark,

> Where is the claim about transactions being visible before crash-safety in his thesis? I didn't find it via a quick
searchof the pdf. 

I feel like I've waded into something here without all of the context, but Kevin is referring to some text in section
4.4on page 63 of my thesis: 

"InnoDB holds a transaction’s locks until the point in transaction commit just before the write-ahead log is flushed.
Inother words, locks are released and changes become visible before log records are guaranteed to be on stable storage.
Thisearly release of locks creates a window of vulnerability in which a query could commit after seeing data values
thatwould be lost after a crash. For short transactions that perform no other I/O, this optimization in InnoDB reduces
theduration for which locks are held by 1-2 orders of magnitude. The reasoning given in the InnoDB source code for this
designis that as the log is flushed in order, any subsequent update based on the not-yet-flushed data could not be
flushedbefore the data it depends on. However, this reasoning does not apply to queries, which can read data that has
notyet been made durable. There are various configuration parameters that can reduce the cost of flushing the log,
includinggroup commit and skipping the flush entirely, so releasing locks early is an interesting design choice." 

I do not know whether this is still true of InnoDB today.

Michael.

> kind of cool to see it get implemented in PG

It was extremely cool for me that PG noticed my research!

Michael.