Re: POSTGRES/MYSQL - Mailing list pgsql-general

If course you will get a lot of pro-Postgres answers here.  I am going to try to give a balanced one.

I:  Performance

MySQL with InnoDB performs differently than PostgreSQL.  Primary key lookups are marginally less expensive.  Secondary index lookups are significantly more expensive.  Sequential scans are much more expensive.  If all you are ever doing is primary key lookups, MySQL might perform better.  For most real-world workloads, PostgreSQL does better.

Also MySQL has a query cache that allows the results of very common queries to be much faster.  PostgreSQL has a more complex cache system which performs better on complex workloads.  So in most cases, Postgres is better (assuming appropriate tuning on both).

For updates, MySQL avoids a lot of index write overhead.  PostgreSQL has more overhead per update.  In some cases this is a big deal.  However I have never seen such a situation that made PostgreSQL unsuitable.

I suspect parallel query is faster on MySQL but I have seen internal parallelism lead to deadlocks with only a single session running.  In other words, bulk inserts deadlocking against themselves.

II:  Porting

 

MySQL has per-client modes of query which affect what data can be properly stored and how data can be retrieved.  The positive side is that MySQL is ok at pretending to be other database systems but not so good at ensuring data integrity (strict mode can be turned off by any writer, so you can't always trust what is written).  The downside is that MySQL doesn't have as much of a rich feature set of compliant features, so what you gain from being able to use a supported dialect you may lose in having to rewrite queries anyway.

PostgreSQL is fairly strict about data insertion and does not support multiple dialects of SQL, so porting non-ANSI-SQL queries to PostgreSQL can sometimes take more effort, but the feature set supported is much higher so....  Six of one, half a dozen of the other.

III:  Licensing

MySQL is owned by Oracle and GPL licensed.  PostgreSQL is BSD-licensed and owned by the individual contributors.  If you are considering MySQL you might want to use MariaDB instead.  But PostgreSQL avoids most of these issues and ensures that even if you are distributing the db with a proprietary application, there are no licensing implications of doing that.