Why PostgreSQL is the better MySQL

Ever since MySql has been purchased by Oracle, it has been lagging in development in the open source space. MariaDB , Percona, Aurora are spin offs that try to address it.  MySql is the original M of the LAMP stack. However, things are more becoming more LNPP. (linux, nginx, postgres, php) or just Rails with Postgres.

Anyhow here are advantages and differences of PostgreSql vs MySql.  We will use: PostgreSQL 9.3 vs MySql 5.6.

Things PostGreSQL has that MySQL doesn’t have.

  • CHECK constraints  (checks values of fields)
  • True SERIALIZABLE isolation (avoid dirty reads)
  • Arrays (like p[values])
  • hstore (hash data type)
  • Composite Data types (like structs in C)
  • Window functions (row_number,partition over)
  • set functions like : general series
  • User defined aggregates and operators
  • Common table expressions (and recursive Cte’s)
  • XML and JSON Support
  • MVCC (multi-version concurrency control)
  • PostGis (GIS support)
  • Transactional DDL (rollback schema changes)
  • Fancier Indexing (GIN, GIST)
  • Un-logged Tables (like mysql myISAM)
  • inheritance (tables inherit other tables woah!)

Things PostGreSQL doesn’t have that MySQL have.

  • Session variables (@:= hackery)
  • a ton of storage engines types for varying use.
  • Upserting aka insert on duplicate key
  • Event scheduler that Mysql has basically Unix Cron
  • GROUP BY … ROLLUP summaries in Mysql.
  • native ability to cross a query between databases. Postgres needs foreign_data_wrapper.

Things MySQL doesn’t have :

  • CTE’s, cte recursion and tree traversal.
  • Native easy to use Json and XML parsing
  • Temp table have self join limitations
  • Fancy functions written in procedural language
  • unions, intersections
  • transaction isolation.
  • Full outer joins (c’mon man)

Things to note on differences: 

  • MySQL uses nonstandard ‘#’ to begin a comment line; PostgreSQL doesn’t. Instead, use ‘–‘ (double dash), as this is the ANSI standard, and both databases understand it.
  • MySQL uses ‘ or ” to quote values (i.e. WHERE name = “John”). This is not the ANSI standard for databases. PostgreSQL uses only single quotes for this (i.e. WHERE name = ‘John’). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE “last name” = ‘Smith’).
  • MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.
  • PostgreSQL is case-sensitive for string comparisons.
  • Database, table, field and columns names in PostgreSQL are case-independent, unless you created them with double-quotes around their name, in which case they are case-sensitive. In MySQL, table names can be case-sensitive or not, depending on which operating system you are using.
  • PostgreSQL and MySQL seem to differ most in handling of dates, and the names of functions that handle dates.
  • PostgreSQL supports many standard parts of SQL that MySQL doesn’t, such as sub-queries, unions, intersections, etc.

 

If I were starting fresh a new web site/app today I’d go PostgreSQL over MySQL or NoSQL:  Mongo/Cassandra (if scaling for big data).

Advertisements
This entry was posted in data wrangling, relational databases and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s