MySQL vs PostgreSQL

When considering database migration from MySQL to PostgreSQL or backward, database specialists should understand primary pros and cons of each DBMS before they begin. This whitepaper explores strong and weak sides of MySQL and PostgreSQL as well as differences in features or capabilities that may become bottleneck of the migration process.

MySQL pros:

  • Easy to learn and use
  • Each table can have a different storage engine.
  • Each storage engine has particular behavior, features, and properties.
  • Tight integration into the web

MySQL cons:

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

PostgreSQL pros:

  • 100{0869658f5eb2b3e6517d509ddd28845c49986e33639eac98a83b2734215e1b2c} implementation of SQL standard
  • Support for advanced data types, such as multi-dimensional arrays, user-defined types, etc
  • Sophisticated locking mechanism
  • Point-in-time recovery

PostgreSQL cons:

  • It is quite complicated to learn and use
  • It is less popular than MySQL that means it is a little harder to get community support or find all required answers in knowledge base

Now it is possible to answer the question: is it good to switch from MySQL to PostgreSQL. It is reasonable for a large data warehouse that must be scaled or deployed into more complicated system. At the same time, it does not make sense for small and medium databases with a simple semantics.

When planning a database migration from MySQL to PostgreSQL, it’s important to have in mind primary differences between these DBMS.

Types

MySQL and PostgreSQL have different sets of data types, however there is straightforward mapping between them according the table below (there are only distinguished types listed):

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

Unlike MySQL, PostgreSQL does not have property ‘auto_increment’ for integer columns, which increases the value of the field automatically each time when new row is inserted. Instead, it uses SERIAL type and its variations for the same purpose:

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

Built-in Functions

Those functions are used in views and stored procedures, each of them must be converted into PostgreSQL equivalent before passing it to the destination DBMS. Here is conversion of the most popular MySQL built-in functions into 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)

MySQL pattern IF($a,$b,$c) can be converted into PostgreSQL equivalent: CASE WHEN $a THEN $b ELSE $c END

Douglas Rollo