13 Considerations When Supporting Both MySQL and PostgreSQL

By Charlie Ozinga in GET/technical Posted Jul 24, 2014

Nineteen years ago, MySQL was a fast and easy to use implementation. Then, the needs imposed on database systems were smaller and much more manageable; Developers were writing simpler applications that didn’t demand as much in terms of complexity. 

MySQL vs. PostgreSQL
 
Then, MySQL and the minimal feature set it provided was perfectly acceptable and still is for simple applications. Today, applications have grown to be extremely complex, and implementation systems have grown right alongside them. The shortcomings of MySQL’s feature set become very apparent when attempting to perform more complex data operations. For this reason, many companies have elected to use the PostgreSQL (or Postgres) system instead. Though some companies are lucky enough to operate within one system, many find that they switch interchangeably. Switching from MySQL to Postgres is an easy transition, but working in the opposite direction, from Postgres to MySQL, is a different story.

Working in the provisioning process with Elements’ database systems that are managed by different document providers- some who use MySQL and others who use Postgres- we’ve experienced this headache first hand. The overarching problem is that Postgres has more functionality, keywords, and flexibility that is not easily transferred over to a MySQL system. Switching from Postgres to MySQL is like switching from an automatic to a manual transmission: you are suddenly confronted with functionality that you have taken for granted. Over the course of this post, I’ll discuss the differences between the two, the 13 issues that will arise when switching from Postgres to MySQL, as well as some specific pitfalls to avoid. *Note: These are not the ONLY problems you might encounter, and you most certainly will run into some new ones.*

The 13 problems you might encounter when switching from Postgres to MySQL are split into three categories:

The first category of issues is a result of the following: Postgres is a far more powerful and flexible system. As a result, there are things that are understood and processed in Postgres that MySQL doesn’t understand. In order to remedy this problem, you must be mindful of the complex features present in Postgres that are absent from MySQL:

  1. ‘Key’ keywords: Certain words in MySQL are reserved for internal use. This means that you can’t use the word ‘key’ if you want to create a table or a column named key. MySQL offers a way out of that: If you type ‘key’, then mySQL will recognize it as what you are trying to use it as. Although MySQL offers a way out of that, Postgres is intelligent enough to recognize your intention; If you use a keyword in a place where it looks like you’re trying to use it as your own, then it recognizes that you are trying to do that and allows you to do so.

  2. Concats (|| vs concat): Postgres allows you to concatinate strings together using a double line: “my name is Charlie” = ‘my name is ’ || firstname. MySQL concinates text in a clunkier way: concat(‘my name is ’, firstname)

  3. CTE versus On Duplicate Key: Within database systems, there is a mechanism called an upsert, which is a combination of an update and an insert. This updates a record if it previously exists and inserts one if it does not. MySQL uses a mechanism called On Duplicate Key to perform an upsert, whereas Postgres uses one called CTE. The two are completely different processes.

The next category has to do with syntax. The syntax differs between the two systems, so you have to change not only what you’re saying but the order in which you’re saying it. (Ex. blue water, water blue). This problem isn’t fatal because it reports a syntax error or grammar error and thus you know to fix it then and avoid future complications. To avoid this issue, however, keep in mind the commands that use a different syntax:

  1. If exists: When deleting a column from a database in Postgres you can use the “if exists” clause which tells it to delete the column only if it actually exists. If the column doesn’t exist, it silently ignores the request. Using MySQL, your only option is to delete a column; if the column doesn’t exist, you get a syntax error.

  2. Timezone: In Postgres, you can create a timestamped column, indicating that you want the timezone to be taken into consideration. The concept of a timezone doesn’t exist in MySQL, however.

  3. Returning: When you input information into a database, Postgres allows you to pick out certain pieces of information that you just inserted to view in an easy, one-step process. The process for doing so in MySQL is a clunky 2-step process.

  4. Tsvector: If you have large chunks of text (logging something, big description of something, etc.), searching through that information can be slow. Postgres uses tsvector to internally optimize fields so that you can search quickly and easily. MySQL does not have that functionality.

  5. Sequences: Sequences are mechanisms used to give a unique ID for every record in a database. Postgres has a robust sequence system that allows you to get current value of a sequence, change the value of sequence, etc. MySQL has an auto_increment column that is similar but not nearly as flexible and powerful as Postgres.

  6. PL/pgSQL: Postgres created this language as a superset of SQL to write functions. In MySQL you don’t have that language so you have to use regular ANCI standard SQL or the MySQL standard.

The last category is by far the most challenging set of problems: The syntax looks roughly the same, but the behavior is different. No syntax error is reported at the time, so later down the line, your application doesn’t behave the way you expected it to so you have to backtrack from step one to figure out where you went wrong. To avoid this issue, be aware of things that look the same but have a different meaning:

  1. Case sensitivity: MySQL is not case sensitive (Dog and dog are the same), so when you query, it’ll find both records. Postgres is case sensitive. If you’ve written a whole bunch of queries in Postgres to be case sensitive, they won’t be differentiated when using MySQL. The result is a query that returns more records than what you’d expect or that more entries are changed than what you had intended. It’s important to note that when this happens, there is no error message, so you might continue on and months later realize your error.

  2. Empty update: When updating, you set a value, and can set that value to another query so that a subquery can be used to do an update in one step. What happens is sometimes the subqueries don’t return anything. Postgres tries to grab that value and when it doesn’t find anything it stops. If MySQL finds no value, it deletes what was there and replaces it with null. The problem is that if you’re used to updating in Postgres and then you switch over to MySQL you will find that there are a bunch of “nulls” in your data that you didn’t expect.

  3. Date handling: MySQL handles dates by using an internal timezone and time stamps that are not time zone sensitive. Postgres handles dates differently.

  4. Transactional DDL: DDL is the data definition language, or language used to create and modify the structure that the DML works on. DML is always transactional; You can capture ten different commands in a single transaction. If you want to undo, you can rollback the entire transaction. Postgres supports transaction in their DDL, but MySQL does not. MySQL will implicitly commit what you have told it to do so far, and then start a new transaction for the next steps, making it more difficult to roll back. Worst of all, it does everything without telling you so if you realize a mistake at the end, it means that you may have already permanently committed these things.

Hopefully this post has convinced you to use the Postgres system if you have the flexibility to choose. And for those of you, like us, that operate within both systems interchangeably, we hope this will equip you to overcome some daily frustrations.

To read more from Cloud Elements developers, subscribe to our blogfind out what we do, or check out these recent posts: