Postgres 9.1 vs Mysql 5.6 InnoDB?

Question

Simple question - what would better for a medium/big size database with requirement for compatibility with ACID in 2012.

I have read it all (well most) about mySQL vs pgSQL but most of those posts relate to version 4,5.1 and 7,8 respectively and are quite dated (2008,2009). Its almost 2012 now so I guess we could try and take a fresh look at the issue.

Basically I would like to know if there is anything in PostgreSQL that out-weights ease of use, availability and larger developer/knowledge base of MySQL.

Is MySQL's query optimizer still stupid? Is it still super slow on very complicated queries?

Hit me! :)

PS. And don't send me to goggle or wiki. I am looking for few specific points not an overview + I trust StackOverflow more than some random page with 'smart guy' shining his light.

Addendum

Size of the project: Say an ordering system with roughly 10-100 orders/day per account, couple of thousand accounts, eventually, each can have several hundred to several thousand users.

Better at: being future proof and flexible when it comes to growing and changing requirements. Performance is also important as to keep costs low in hardware department. Also availability of skilled workforce would be a factor.

OLTP or OLAP: OLTP

Enterogastrone

You'd need to define a few things more accurately and precisely for any answer to be useful. Words like "better" and "moderately". Better at giving your DBA's time to sleep, or better for your new hires with a recent MySQL exam in their head? Moderately for a CD collection index, or a messaging application with 10 million users? OLTP, OLAP? – Echolocation

what is a a medium/big size database for you ? – Muticous

Answer 1

EDIT: I answered this question over ten years ago. People apparently still read it and occasionally click on the upvote button. While I think that broadly the "PostgreSQL is a bit smarter than MySQL" statement is true, please don't depend on answers this old for details about current versions of software.

---

Is MySQL's query optimizer still stupid? Is it still super slow on very complicated queries?

All query optimizers are stupid at times. PostgreSQL's is less stupid in most cases. Some of PostgreSQL's more recent SQL features (windowing functions, recursive WITH queries etc) are very powerful but if you have a dumb ORM they might not be usable.

Size of the project: Say an ordering system with roughly 10-100 orders/day per account, couple of thousand accounts, eventually, each can have several hundred to several thousand users.

Doesn't sound that large - well within reach of a big box.

Better at: being future proof and flexible when it comes to growing and changing requirements.

PostgreSQL has a strong developer team, with an extended community of contributors. Release policy is strict, with bugfixes-only in the point releases. Always track the latest release of 9.1.x for the bugfixes.

MySQL has had a somewhat more relaxed attitude to version numbers in the past. That may change with Oracle being in charge. I'm not familiar with the policies of the various forks.

Performance is also important as to keep costs low in hardware department.

I'd be surprised if hardware turned out to be a major component in a project this size.

Also availability of skilled workforce would be a factor.

That's your key decider. If you've got a team of experienced Perl + PostgreSQL hackers sat around idle, use that. If your people know Lisp and MySQL then use that.

OLTP or OLAP: OLTP

PostgreSQL has always been strong on OLTP.

My personal viewpoint is that the PostgreSQL mailing list are full of polite, helpful, knowledgeable people. You have direct contact with users with Terabyte databases and hackers who have built major parts of the code. The quality of the support is truly excellent.

Venerable

@Richard another point Postgres alter queries run in background – Torino

Answer 2

PostgreSQL is a lot more advanced when it comes to SQL features.

Things that MySQL still doesn't have (and PostgreSQL has):

• deferrable constraints

• check constraints (MySQL 8.0.16 added them, MariaDB 10.2 has them)

• full outer join
  MySQL silently uses an inner join with some syntax variations:
  https://rextester.com/ADME43793
  

• lateral joins

• regular expressions don't work with UTF-8 (Fixed with MySQL 8.0)

• regular expressions don't support replace or substring (Introduced with MySQL 8.0)

• table functions ( select * from my_function() )

• common table expressions (Introduced with MySQL 8.0)

• recursive queries (Introduced with MySQL 8.0)

• writeable CTEs

• window functions (Introduced with MySQL 8.0)

• function based index (supported since MySQL 8.0.15)

• partial index

• INCLUDE additional column in an indexes (e.g. for unique indexes)

• multi column statistics

• full text search on transactional tables (MySQL 5.6 supports this)

• GIS features on transactional tables

• EXCEPT or INTERSECT operator (MariaDB has them)

• you cannot use a temporary table twice in the same select statement

• you cannot use the table being changed (update/delete/insert) in a sub-select

• you cannot create a view that uses a derived table (Possible since MySQL 8.0)

    create view x as select * from (select * from y);
  

• statement level read consistency. Needed for e.g.:
  update foo set x = y, y = x or
  update foo set a = b, a = a + 100

• transactional DDL

• DDL triggers

• exclusion constraints

• key/value store

• Indexing complete JSON documents

• SQL/JSON Path expressions (since Postgres 12)

• range types

• domains

• arrays (including indexes on arrays)

• roles (groups) to manage user privileges (MariaDB has them, Introduced with MySQL 8.0)

• parallel queries (since Postgres 9.6)

• parallel index creation (since Postgres 11)

• user defined data types (including check constraints)

• materialized views

• custom aggregates

• custom window functions

• proper boolean data type
  _{(treating any expression that can be converted to a non-zero number as "true" is not a proper boolean type)}

When it comes to Spatial/GIS features Postgres with PostGIS is also much more capable. Here is a nice comparison.

Not sure what you call "ease of use" but there are several modern SQL features that I would not want to miss (CTEs, windowing functions) that would define "ease of use" for me.

Now, PostgreSQL is not perfect and probably the most obnoxious thing can be, to tune the dreaded VACUUM process for a heavy write database.

Markitamarkka

NIce answer - good to see that such a comparison is also being maintained! Another site which is maintaining a comprehensive up-to-date comparison appears be here - at the time of writing (2020/03/20), it was last updated 2020/03/04). – Bonnette

One you've missed, and I'm surprised - the vastly superior output of EXPLAIN (ANALYZE, BUFFERS) <text of SQL query> produced by PostgreSQL - though 8.0.20 looks like it might close the gap! – Bonnette

Answer 3

As an addition to @a_horse_with_no_name answer, I want to name some features which I like so much in PostgreSQL:

• arrays data type;
• hstore extension - very useful for storing key->value data, possible to create index on columns of that type;
• various language extensions - I find Python very useful when it comes to unstructured data handling;
• distinct on syntax - I think this one should be ANSI SQL feature, it looks very natural to me (as opposed to MySQL group by syntax);
• composite types;
• record types;
• inheritance;
• Version 9.3 features:
  • lateral joins - one thing I miss from SQL Server (where it called outer/cross apply);
  • native JSON support;
  • DDL triggers;
  • recursive, materialized, updatable views;

Maliamalice

One of my favourite things about Postgres is that (at the time of writing) it has the most accurate implemetation (to my knowledge) of the SQL Standard with regard to Grouping. You cannot group by columns unless they are contained in an aggregate function or in the group by clause, or functionally dependant on a column contained in the group by clause, so if you include a primary key column of a table in the group by clause, you don't have to include all other columns in that table. Example on SQL Fiddle – Revisionist

@Revisionist WOW! Didn't know that. Now I wish my next job be PostgreSQL one even more!!! – Maliamalice

MySQL allows you to do this as well, but it also allows you to simply leave out any columns you aren't concerned with. The value of those columns in the results is undefined except in the case you describe for, as would be expected. – Busman

Yes - DISTINCT ON is a kind of MySQL GROUP BY without sql_mode = ONLY_FULL_GROUP_BY, but at least the dev/dba is obliged to be explicit about it. Now, in MySQL, you can emulate it with FIRST_VALUE() - there shouldn't even be an option to have GROUP BY without ONLY_FULL_GROUP_BY - which is the way it should (only) have worked from day 1! – Bonnette

Answer 4

PostgreSQL is a more mature database, it has a longer history, it is more ANSI SQL compliant, its query optimizer is significantly better. MySQL has different storage engines like MyISAM, InnoDB, in-memory, all of them are incompatible in a sense that an SQL query which runs on one engine may produce a syntax error when executed on another engine. Stored procedures are better in PostgreSQL.

Precautious

Technically yes, postgresql has a longer history, but since both were first released in May 1995 (according to wikipedia), that hardly seems significant. I wouldn't agree that PostgreSQL is more mature, it's more a question of different design goals. – Epigrammatist

@mc0e: The problem is that some of mysql's original design goals ignored key tenets behind RDMS. – Modesta

@Epigrammatist MySQL had design goals? LOL! – Bonnette