PostgreSQL vs MySQL

For many years, MySQL and PostgreSQL were competing databases, which addressed slightly different audiences. In my judgement (which is of course a bit biased) PostgreSQL always served my professional users, while MySQL had a strong standing among web developers and so on. But, after Oracle took over MySQL I had the feeling that the star of MySQL was somewhat fading.

Since the introduction of MariaDB I have the personal feeling that the technology is not really finished yet and that people are still asking for MySQL / MariaDB and are interested in comparing PostgreSQL to those technologies. Many people are not happy with statements such as “PostgreSQL is just better”. Arguments like that tend to be no brainers and we (the PostgreSQL community) are really able to do a lot better. So I thought I’d bring up some good ideas, why it is actually more desirable to use PostgreSQL in favor of MySQL / MariaDB.

The idea of this post is to collect some examples, which show on a technical level, why you might want to go for PostgreSQL. Keep in mind: This posting is simply a “best of” and by far not a complete list. However, it should be enough to convince people and help decision makers:

PostgreSQL vs. MySQL / MariaDB: Data type handling

MySQL / MariaDBPostgreSQL (any version)
MariaDB [test]> CREATE TABLE data (

id         integer NOT NULL,

data     numeric(4, 2)

);

Query OK, 0 rows affected (0.02 sec)

 

MariaDB [test]> INSERT INTO data

VALUES (1, 1234.5678);

Query OK, 1 row affected, 1 warning (0.01 sec)

 

MariaDB [test]> SELECT * FROM data;

+—-+——-+

| id | data  |

+—-+——-+

|  1 | 99.99 |

+—-+——-+

1 row in set (0.00 sec)

test=# CREATE TABLE data (

id         integer NOT NULL,

data     numeric(4, 2)

);

CREATE TABLE

test=# INSERT INTO data

VALUES (1, 1234.5678);

ERROR:  numeric field overflow

DETAIL:  A field with precision 4, scale 2 must round to an absolute

value less than 10^2.

What you see here, is that PostgreSQL errors out because the data you are trying to put into the table does not conform to the data type of the column, while MySQL / MariaDB will silently change your data. Yes, MySQL / MariaDB do issue a warning but at the end of the day the data in the table is not what it is supposed to be.

Why does that matter? Suppose you have won the lottery. Instead of one million you just ended up getting 9999.99 because the field was not large enough. Unfortunately nobody noticed. Would you be angry? I would. The behavior shown by PostgreSQL in this example is correct for a variety of reasons:

  • There is most likely a reason why the column was defined the way it is.
  • A warning is not the same as an error.
  • Storing data is not about “tolerance” – it is about correctness.

If you shot yourself in the head there is no point in having a warning that “you might be dead now” – it is better to prevent the thing from happening in the first place.

Lesson learned: PostgreSQL takes your data structures seriously.

PostgreSQL vs. MySQL / MariaDB: Taking existing data seriously

MySQL / MariaDBPostgreSQL (any version)
MariaDB [test]> ALTER TABLE data

MODIFY data numeric(3, 2);

Query OK, 1 row affected, 1 warning (0.06 sec)

Records: 1  Duplicates: 0  Warnings: 1

 

MariaDB [test]> SELECT * FROM data;

+—-+——+

| id | data |

+—-+——+

|  1 | 9.99 |

+—-+——+

test=# INSERT INTO data VALUES (1, 34.5678);

INSERT 0 1

test=# SELECT * FROM data;

id | data

—-+——-

1 | 34.57

(1 row)

 

test=# ALTER TABLE data

ALTER COLUMN data TYPE numeric(3, 2);

ERROR:  numeric field overflow

DETAIL:  A field with precision 3, scale 2 must round to an absolute

value less than 10^1.

Again, MySQL / MariaDB will actually change your data. Remember, the original value inserted into the table was 1234.5678. Now we got 9.99. Do you notice the “minor difference”? If this was your bank account? Would you care? If this database was powering the machine keeping a sick child alive – would you still trust it, or would you demand a replacement? If your life depends on data – would you accept a silent change in the content of a table? Maybe I am too conservative, but I would not.

PostgreSQL does what I personally expect from a database. It will error out and inform us that the change is really not possible because there is data, which simply has to be protected. If you ever want the data in the table to be changed (in case the new rules are violated), you have to tell PostgreSQL explicitly what you want:

PostgreSQL
test=# ALTER TABLE data

ALTER COLUMN data

TYPE numeric(3, 2)

USING (data / 10);

ALTER TABLE

In this case we told PostgreSQL extactly how data has to be treated. PostgreSQL does not try to be smart (and fail such as MySQL / MariaDB), it does not try to do something fancy – it simply does what YOU want and what is good for your data.

PostgreSQL vs. MySQL / MariaDB: NULL handling does matter

MySQL / MariaDBPostgreSQL (any version)
MariaDB [test]> UPDATE data

SET id = NULL WHERE id = 1;

Query OK, 1 row affected, 1 warning (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 1

 

MariaDB [test]> SELECT * FROM data;

+—-+——+

| id | data |

+—-+——+

|  0 | 9.99 |

+—-+——+

1 row in set (0.00 sec)

test=# UPDATE data

SET id = NULL

WHERE id = 1;

ERROR:  null value in column “id” violates not-null constraint

Do you remember that there is actually a NOT NULL constraint on the “id” column? MySQL / MariaDB don’t care and just set the value to 0. The point: 0 and NULL are definitely not the same thing. NULL means “undefined” or “we don’t know the value”. This makes a lot of sense in the real world: If you don’t know how much money you got in your pocket, it definitely does not mean that your pocket is empty. If you don’t know how many beers you had, it does not mean that there is “zero” alcohol in your blood – you might actually have just fallen off a chair.

PostgreSQL carefully distinguishes between 0, empty string, and NULL. Those things are simply not the same and PostgreSQL will always respect your constraints.

Deciding on a database engine

For me there is no question whether to use MySQL / MariaDB or PostgreSQL. The arguments outlined in this posts are strong evidence for me personally that MySQL is not an option. However, this is a free world and people can make decisions for themselves and will hopefully decide wisely.