Technology

MySQL or PostgreSQL?  

John Huson

When planning upgrade or transfer to new platform, every database specialist tries to find the best solution to store database according to particular requirements. One of the most popular need is to minimize total cost of ownership (TCO). This may lead to choosing one of open source database management systems. The most powerful and popular open source DBMS are MySQL and PostgreSQL. This whitepaper helps to understand basic pros and cons of these systems as well as differences in capabilities that could complicate the migration process. 

Benefits of MySQL: 

  • It is easy to learn and use and supplied with tons of tutorials 
  • Each table can have a different storage engine with its own features
  • Excellent support in all modern programming languages
  • Tight integration into the web

Disadvantages of MySQL:

  • It does not support user-defined types
  • It has no support for recursive queries
  • It does not have materialized views
  • It does not support sequences, although it can be emulated.
  • It does not support roll-back transactions for DDL statements such as “ALTER TABLE” or “CREATE TABLE”

Benefits of PostgreSQL: 

  • It has 100% compliance with SQL standard
  • It supports point-in-time recovery
  • It has sophisticated locking mechanism
  • It supports advanced data types such as multi-dimensional arrays, spatial, etc

Disadvantages of PostgreSQL: 

  • It is essentially slower than MySQL
  • It is quite complicated to learn and use
  • It is not so popular as MySQL, so it may be hard to get community support

This information helps to decide if it is reasonable to switch from MySQL to PostgreSQL or vice versa. PostgreSQL is good choice for large and complex corporate databases with perspective of scaling or deploying. On another hand, it does not make sense to use PostgreSQL for small and medium databases with a simple semantics. MySQL looks like a better choice for this purpose.

Everyone who consider database migration from MySQL to PostgreSQL or backward should remember the primary differences between these DBMS. 

Types

Although data types of MySQL and PostgreSQL are not equal, there is straightforward conversion between them. The table below illustrates conversion for distinguished types:

MySQL PostgreSQL
BINARY(n) BYTEA
BIT BOOLEAN
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
DATETIME TIMESTAMP [WITHOUT TIME ZONE]
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE DOUBLE PRECISION
FLOAT REAL
MEDIUMINT INTEGER
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTEA
TINYINT SMALLINT
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT TEXT
VARBINARY(n), VARBINARY(max) BYTEA
VARCHAR(max) TEXT

Another difference between two DBMS is that PostgreSQL does not have property similar to MySQL ‘auto_increment’ increasing the value of the field each time when new row is inserted. For simple auto_increment columns (with base and step equal to 1) PostgreSQL SERIAL types may be used:

MySQL PostgreSQL
BIGINT AUTO_INCREMENT BIGSERIAL
INTEGER AUTO_INCREMENT SERIAL
SMALLINT AUTO_INCREMENT SMALLSERIAL
TINYINT AUTO_INCREMENT SMALLSERIAL

More complicated variations of MySQL auto_increment can be replaced by combination of sequence and trigger on insert. 

Built-in Functions

Both MySQL and PostgreSQL have wide range of built-in functions to use in views and stored procedures. Each of these functions must be converted into the appropriate equivalent before passing it to the destination DBMS. Here is list of correlation between built-in functions of MySQL and PostgreSQL:

MySQL PostgreSQL
curtime() current_time
DAY($a) or DAYOFMONTH($a) extract(day from date($a))::integer
DATEDIFF($1, $2) $1 – $2
HOUR($1) EXTRACT(hour FROM $1)::int
IFNULL($a,$b) COALESCE($a,$b)
INSTR($a, $b) position($b in $a)
ISNULL($a) $a IS NULL
LOCATE ($a,$b) INSTR($a, $b)
minute($1) EXTRACT(minute FROM $1)::int
month($1) EXTRACT(month FROM $1)::int
SYSDATE() CURRENT_DATE
WEEK($1) extract(week from ($1))::int
YEAR($1) extract(year from $1)