Best IT Recipes

Postgres 9.1 vs Mysql 5.6 InnoDB?

A simple question is what is best for a medium / large database requiring ACID compatibility in 2012.

I read everything (and most importantly) about mySQL vs pgSQL, but most of these posts are for versions 4.5.1 and 7.8 respectively and are quite dated (2008,2009). Its almost 2012 now so I think we could try to take a look at this issue.

Basically, I would like to know if there is anything in PostgreSQL that provides ease of use, accessibility and a large MySQL developer / knowledge base.

Is MySQL query optimizer still dumb? Is it still too slow on very complex queries?

Kill me!:)

PS. And don't send me in the eyes or wiki. I'm looking for a few specific points, not a review +. I trust StackOverflow more than some random page with a smart guy shining with his light.

Adding

Project size: tell the order system with approximately 10-100 orders per day for one account, several thousand accounts, in the end, each can have from several hundred to several thousand users.

Better: be future and flexible when it comes to growing and changing demands. Performance is also important in order to reduce hardware costs. An important factor will also be the availability of skilled labor.

OLTP or OLAP: OLTP

+69
mysql postgresql
Nov 18 '11 at 11:23
source share
5 answers

Is MySQL query optimizer still dumb? Still super slow very complex queries?

All query optimizers are sometimes dumb. PostgreSQL is in most cases less stupid. Some of the later PostgreSQL SQL functions (window functions, recursive queries, etc.) are very efficient, but if you have a dumb ORM, they may not be used.

Project size: Say the order system from about 10-100 orders / day to the account, several thousand accounts, in the end, each can have from several hundred to several thousand users.

Doesn't sound so loud - well within reach of a large box.

Better: future proof and flexibility when it comes to growth and changing requirements.

PostgreSQL has a strong development team with an extended community of contributors. The release policy is strict, with corrections - only in point releases. Always keep track of the latest version 9.1.x for fixes.

In the past, MySQL was a little more relaxed about version numbers. This may change due to the fact that Oracle is responsible. I am not familiar with the politics of various forks.

Performance is also important in order to reduce hardware costs.

I would be surprised if hardware were the main component in a project of this size.

In addition, the availability of skilled labor will be a factor.

This is your key. If you have a team of experienced Perl + PostgreSQL, hackers are sitting idle, use this. If your people know Lisp and MySQL, then use this.

OLTP or OLAP: OLTP

PostgreSQL has always been strong in OLTP.

My personal point of view is that the PostgreSQL mailing list is full of polite, helpful, knowledgeable people. You have direct contact with users with Terabyte databases and hackers who created the main parts of the code. The quality of the support is really excellent.

+57
Nov 18 '11 at 15:05
source share

PostgreSQL is much more advanced when it comes to SQL functions.

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

  • deferred restrictions
  • check restrictions (MySQL 8.0. 16 added them, MariaDB 10.2 has them)

  • full external connection
    MySQL silently uses an internal join with some syntax variations:
    https://rextester.com/ADME43793

  • side connections

  • regular expressions do not work with UTF-8 (Fixed with MySQL 8.0 )
  • regular expressions do not support substitution or substring (introduced in MySQL 8.0 )
  • table functions ( select * from my_function() )
  • common table expressions (introduced in MySQL 8.0 )
  • recursive queries (Submitted with MySQL 8.0 )
  • recordable CTE
  • window functions (Presented with MySQL 8.0 )
  • function based index
  • partial index
  • multi-column statistics
  • full-text search in transactional tables (MySQL 5.6 supports this)
  • GIS functions on transactional tables
  • EXCEPT or INTERSECT operator
  • you cannot use a temporary table twice in the same select statement
  • You cannot use a mutable table (update / delete / insert) in an additional selection

  • you cannot create a view that uses a view (possibly starting from MySQL 8.0)

      create view x as select * from (select * from y);
  • Consistency of reading at the statement level. It is necessary, for example, for:
    update foo set x = y, y = x or
    update foo set a = b, a = a + 100
  • transactional DDL
  • DDL launches
  • exclusion restrictions
  • key / value store
  • Indexing full JSON documents
  • range types
  • domains
  • arrays (including indexes on arrays)
  • roles (groups) for managing user privileges (MariaDB has them; introduced in MySQL 8.0 )
  • concurrent requests (starting with Postgres 9.6 )
  • user-defined data types (including validation restrictions)
  • materialized views
  • custom aggregates
  • custom window functions
  • correct boolean data type
    (processing any expression that can be converted to a nonzero number as true is not a valid boolean type)

Regarding spatial / GIS functions, Postgres with PostGIS also have much more features. Here is a good comparison.

Not sure what you call "ease of use," but there are a few modern SQL functions that I would not want to miss (CTE, window functions) that would define "ease of use" for me.

Now PostgreSQL is not perfect and probably the most annoying thing might be to set up the awful VACUUM process for a heavy write database.

+73
Nov 18 '11 at 13:16
source share

In addition to the @a_horse_with_no_name answer , I want to name some of the features that I like so much about PostgreSQL:

+10
Sep 04 '13 at 11:09 on
source share

Here are some benchmarks using these exact versions:

http://posulliv.imtqy.com/2012/06/29/mysql-postgres-bench/

My guess was that MySQL was 30% faster for simple tasks and 500% slower for more complex queries, probably because of the query optimizer.

+5
Jun 02 '13 at 8:50
source share

PostgreSQL is a more mature database, it has a longer history, it is more compatible with ANSI SQL, the query optimizer is much better. MySQL has different storage mechanisms, such as MyISAM, InnoDB, in memory, all of them are incompatible in the sense that an SQL query that is executed on one engine can cause a syntax error when executed on another engine. Stored procedures are better in PostgreSQL.

+2
Jul 07 '13 at 20:48
source share


More articles:


All Articles