CYBERTEC Logo

Why favor PostgreSQL over MariaDB / MySQL

04.2017 / Category: / Tags:

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 / MariaDB PostgreSQL (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 / MariaDB PostgreSQL (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 / MariaDB PostgreSQL (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.

0 0 votes
Article Rating
Subscribe
Notify of
guest
62 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Aleksander Alekseev
7 years ago

From what I know:

* MySQL doesn't support cursors
* MySQL doesn't support functional indexes
* MySQL doesn't support partial indexes
* There is no real full text search in MySQL
* You can't work with geographical data in MySQL ("Find me 10 KFS's around", "In which country am I now?")
* Not sure if MySQL has an analogue of JSONB
* MySQL can't optimize queries like SELECT ... WHERE x = 1 or x = 2 (bitmap scans)
* You can't write procedures for MySQL in Python
* There is no NOTIFY/LISTEN in MySQL

With all do respect to MySQL and MariaDB communities, I don't know a single reason why these days anyone will choose MySQL or MariaDB instead of PostgreSQL.

Miles
Miles
7 years ago

* MariaDB does support cursors, but they are read-only, non-scrollable, and asensitive. Obviously inferior to PostgreSQL, but it does in fact have basic support for cursors.
* The equivalent of functional indexes can be generated from virtual columns.
* You can limp along with a poor man's partial index using various hacks, but on this one, MariaDB fails.
* PostgreSQL's full text search support is obviously superior, but it's not accurate to say MariaDB has no full text search any more than to say PostgreSQL has no full text search because Solr is obviously superior.
* Regarding GIS, see note about full text search.
* MariaDB does not support check constraints (silently ignores, which is for SQL-99 compatibility but very poor behavior), but these can be emulated (barely) with triggers.
* MySQL/MariaDB still handles clustering/sharding better though PostgreSQL is rapidly closing that gap.

I agree that PostgreSQL kicks MySQL/MariaDB's butt in almost all areas, but fair is fair.

pzzcc
pzzcc
6 years ago

thank you for pointing out the real differences , what brought me here was postgis.

I would be really interested in reading a blog post written by you covering those differences and their practical usages ( like postgis example you put above.)

Aleksander Alekseev
6 years ago
Reply to  pzzcc

Hi,

Actually I wrote one back in 2013 http://eax.me/postgresql-vs-mysql/ It's in Russian but I think that Google Translate should help with that. If not I think I could translate it for my blog in English.

Joshua Kramer
Joshua Kramer
7 years ago

The only question I have here is this: which storage engine are you using for these examples? I haven't used MySQL in ages (or MariaDB, ever) but apparently the use of Innodb storage engine solves a lot of these kinds of issues.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
7 years ago
Reply to  Joshua Kramer

this is the default version coming with Fedora core 23 (mariadb.x86_64, 1:10.0.28-1.fc23).
if correct data type handling depends on the storage engine you use, there is clearly something very wrong. i assume that the rules of mathematics should be independent of any storage engine.

Tomas Edwardsson
Tomas Edwardsson
7 years ago

This is true for the InnoDB engine.


MariaDB [test]> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_NAME='data' AND TABLE_SCHEMA='test';
--------
| ENGINE |
--------
| InnoDB |
--------
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM data;
---- -------
| id | data |
---- -------
| 1 | 99.99 |
---- -------
1 row in set (0.00 sec)

rnsmith2@gmail.com
7 years ago

One of my personal pet peeves with mysql: It silently ignores check constraints. You can add them in mysql, and it will happily accept them, but it will never actually *check* that the data conforms to your constraint or do anything except just throw away your request while telling you that it's been applied.

Tampa Saint
Tampa Saint
7 years ago

Next release of mariadb has check constraints finally.

archenroot
archenroot
6 years ago
Reply to  Tampa Saint

I wish mariadb and mysql developers will join postgres on its great mision :-)))

ic0de
ic0de
3 years ago

Latest version of MySQL8.x has check constraints.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
7 years ago

I don't think that is unfair at all. most people will use the default setup and just start coding / working. if something behaves so badly in default mode, many people will face the horrible consequences. it is like using a gun, which is designed to blow up in your face, unless you explicitly add a safety device. isn't it better to NOT blow up by default? even that already shows that the MySQL philosophy is flawed. In my world data is correct by default - not wrong by design.
I think a good design does good things by default - having a "bugs off" switch feels somewhat wrong.

Miles
Miles
7 years ago

The default for strict mode is now true, so "bugs off." Whether a distro is up to date is secondary to the point that the current, stable release of MariaDB is strict by default. I have always preferred PostgreSQL over MySQL/MariaDB, but fair is fair.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
7 years ago
Reply to  Miles

if your washing machine used to blow up for 20 years by default but it is now ok - would you still buy from this vendor? one more master piece from the docs: "CHECK constraints are parsed but ignored." - now: how do you see that?

Miles
Miles
7 years ago

I'm not making value judgements about MariaDB. Why do you feel that I need to defend it, especially when I've already made it clear that I strongly prefer PostgreSQL? I was merely highlighting current features. I would do the same if someone trotted out the old canard that PostgreSQL was slow or that it didn't support replication.

Miles
Miles
7 years ago

By the way, here is a citation of my advocacy for PostgreSQL from 2002. In other words, I've been in the PG camp about as long as you have. No argument that you have contributed far more than I however. https://developers.slashdot.org/comments.pl?sid=46453&cid=4785557

I am a long-time fan of PostgreSQL, both the software and the community. That said, and speaking as someone who has actually purchased a couple of your books, you'll get a lot better response from people by being polite instead of just trying to point out how they're wrong or how their using a metaphorical washing machine that blows up. You're the CEO of a PG-based company after all. While I agree with the admiration for quality code, I can't help but feel your advocacy skills require some attention. Thank goodness Lane, Momjian, et al. do not have the same demeanor. I practically learned SQL from one of Momjian's early edition PostgreSQL books, and yet I have never seen him take such a strident and alienating tone, even to those who prefer MySQL let alone those who share his preference for PostgreSQL.

As the saying goes, "You catch more flies with honey than vinegar." MySQL/MariaDB have more than enough deficiencies and PostgreSQL enough advantages that civil, reasoned discourse should be easier. There is more to PostgreSQL than just a good database engine. PostgreSQL is also about a healthy, productive, and polite community. I'm not going anywhere, but your strident, dismissive tone could very well drive newcomers away. That's certainly not good business.

Wutikrai Pornchai
Wutikrai Pornchai
7 years ago

Agree. Why do I have to config so many optoins to use such mandatory constraints. With PosgreSQL , my application have strong ultra-thin full-controll business rule and datbase constraint baclend while frontend focus on UI

Norman Yamada
Norman Yamada
7 years ago

I have to agree with Miles above -- I'm a fierce advocate of Postgres in almost every case above MySQL, but most companies these days use strict mode; and strict mode catches all of your examples...

Hans-Jürgen Schönig
Hans-Jürgen Schönig
7 years ago
Reply to  Norman Yamada

maybe for my samples but it does not fix things. consider the following: "CHECK constraints are parsed but ignored." ... so, how does that feel?

Norman Yamada
Norman Yamada
7 years ago

Agreed... but your examples are a little exaggerated for the typical MySQL set up today.

In general, MySQL's horrific management of schema changes; its obtuse and difficult to manage binlog syncing and lots of other things make it far worse than Postgres... but you'll alienate the audience you want to convince if you use cheap examples... that's all I'm saying....

Scott Brickey
6 years ago
Reply to  Norman Yamada

"most" is subjective... was randomly looking for an alternative to GA and piwik (used it for a bit, but it left a bad taste based on some experience)... came across this : https://github.com/padams/Open-Web-Analytics/wiki/Technical_Requirements

so I agree w/ author that the default install should be used (though as others have said, default mode is changing, which is a good thing)

dash-68120
dash-68120
7 years ago

Why should you stay on MySQL/MariaDB ?
--------------------------------------------------
Because this kind of behaviour can be easily avoided by configuring the system variable: SQL_MODE (see below)
In a productive environment, you can't go live without having it configured because so many tests are made with the application during the test phase.
mysqld7-(root@localhost) [employees]>create table data(id integer not null, data numeric(4,2));
Query OK, 0 rows affected (0.04 sec)
mysqld7-(root@localhost) [employees]>insert into data values (1,1234.5678);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysqld7-(root@localhost) [employees]>show warnings;
--------- ------ -----------------------------------------------
| Level | Code | Message |
--------- ------ -----------------------------------------------
| Warning | 1264 | Out of range value for column 'data' at row 1 |
--------- ------ -----------------------------------------------
1 row in set (0.00 sec)
mysqld7-(root@localhost) [employees]>set sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.01 sec)
mysqld7-(root@localhost) [employees]>insert into data values (1,1234.5678);
ERROR 1264 (22003): Out of range value for column 'data' at row 1

By the way, in MySQL 8, this variable is now set per default
Server version: 8.0.0-dmr-log MySQL Community Server (GPL)
mysql> show variables like 'sql_mode';
--------------- -------------------------------------------------------------------------------------------------------------------------------------------
| Variable_name | Value |
--------------- -------------------------------------------------------------------------------------------------------------------------------------------
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
--------------- -------------------------------------------------------------------------------------------------------------------------------------------

Wutikrai Pornchai
Wutikrai Pornchai
7 years ago
Reply to  dash-68120

I was a new developer using MySQL 3 years ago. I don't think it's not good idea I have to config to get such mandartoy constraint live above samples. I change from MySQL to PosgreSQL after I realize it do not suport recursive call as it claim. That's main issue I can not go with MySQL since that day.

Sammy
Sammy
6 years ago
Reply to  dash-68120

"Easily"?

Which SQL modes do I need to set, exactly, in order for it to never change my data? I just looked at the "SQL Modes" section of the MySQL manual, and it's over 6000 words long. You mentioned one mode, and then listed 7 others, and it's not at all clear which of these I need to set for my data to be safe. It looks like there's a few others I need, in addition to those 1 7.

This doesn't seem easy at all to me.

"so many tests are made with the application during the test phase"

So during the test phase I need to also exhaustively test my database? Does your application test suite also contain tests for your compiler, your C library, your kernel, and your hardware, too? I find it much easier to build upon systems that don't require me to do their testing work for them.

"By the way, in MySQL 8, this variable is now set per default"

MySQL 8 hasn't been released yet (and certainly wasn't 4 months ago when you said that). You must have meant to say "In the future, this variable set WILL BE the default".

Or I could just use Postgres, which never modifies my data without my permission, and has behaved this way for decades. Somehow, that seems a lot easier to me.

Roger
Roger
6 years ago
Reply to  Sammy

I like myql, but you make a @#$@#$ good point.

Sveta Smirnova
Sveta Smirnova
6 years ago
Reply to  Sammy

It is default in 5.7

Hans-Jürgen Schönig
Hans-Jürgen Schönig
6 years ago
Reply to  Sveta Smirnova

yes, they figured that allowing the 126th of december is not a good idea by default ...

Cuthbert Allgood
Cuthbert Allgood
6 years ago
Reply to  dash-68120

Exactly. If the guy doesn't know how to use the database, he shouldn't be writing about it. Yes, there are legacy options that have to be set. Read the manual and move on. Typical PostgreSQL idiocy.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
6 years ago

of course there is a setting for that. however, "bug = on" is not a wise default mode. is it? and yes, the changes it in recent versions but it was definitely broken in the default installation. it is like a car blowing up its gastank unless a certain switch is set ... so - in my judgement the stupid default setting, which has been there for years, says a lot about how MySQL thinks ...

Cuthbert Allgood
Cuthbert Allgood
6 years ago

If you want to argue that a particular database setting should be flipped the other way, that's a different debate than claiming the entire database is broken and is incapable of functioning with strong checks. The latter is simply wrong and your article is disingenuous. Either you knew that the switch existed, in which case this article is intentionally misleading, or you didn't know it existed, in which case my point stands that you shouldn't be writing about things you know nothing about. Either way, I notice that haven't made any corrections to the article.

And this is all too typical for PostgreSQL advocates. If you have to baldfaced lie about how MySQL works, what does that say about your advocacy of PostgreSQL? What is it about PostgreSQL that makes advocates so insane about making inane claims about MySQL?

jgmitzen
jgmitzen
6 years ago

Your database is broken by design. It's like a submarine whose default atmospheric setting is "cyanide" and whose fanboys scream "But you can switch it to oxygen!" Would you want to trust your life on that submarine?

When I'm sending data to a MySQL database, how do I know it's been configured for oxygen and not cyanide? As the programmer's saying goes, "The problem with defaults is that people use them." How do I know that sane options aren't being overridden on a per-connection basis? Or in the particular backend engine being used?

The whole design is just unnecessary danger and madness. There's never, ever, ever, ever a reason to accept the 126th of December as a date. None. And humans simply can't breathe cyanide and never will.

jgmitzen
jgmitzen
6 years ago

Even with all of these settings, 7 / 0 returns NULL. The only idiocy is using a product originally intended to be a dumb data store as a relational database.

Tampa Saint
Tampa Saint
7 years ago

For me the number one deciding factor is that mariadb has tablespace encryption built in and Postgres does not. As others have pointed out strict mode nullifies the authors examples.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
7 years ago
Reply to  Tampa Saint

no, it does not. because it says something about the philosophy behind a product. if the default setting is consistently "bugs on" for 20 years or so, changing that does not fix the underlying problem. even the fact that "bugs on" exists tells me that MySQL cannot be taken seriously. it is like having a washing machine explode and adding a button to turn deadly explosions off ...

Richieman
Richieman
6 years ago

Perhaps it's not a "bug on" setting but a backward compatibility setting. Both databases have a different philosophy and history, and if apply pg's philosophy on mysql and call it a bug, it kind if sounds biased and manipulative. I ended up here looking for unbiased factual balanced information. Being shamed into the authors opinions don't cut it for me.

jgmitzen
jgmitzen
6 years ago
Reply to  Richieman

"Silently change data" doesn't sound like a thing one would want backward compatibility with, and a "philosophy" that is incompatible with a RDBMS.

Doctordbx
Doctordbx
7 years ago

I'd take this seriously if it didn't just stink of fanboism.

I could cherry pick features where MariaDB is superior to Postgres like replication and clustering, but I realise both databases have their place.

Totoka
Totoka
7 years ago
Reply to  Doctordbx

Would you try to defend an inept cop saying "he sucks at gun safety, but he's an expert at donuts"?

If MySQL and MariaDB fail at crucial things (i.e. handling data correctly) then it really doesn't matter how well they manage those auxiliary tasks.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
7 years ago
Reply to  Totoka

that is the point. even the mere existence of a "bugs on mode" is already a disaster. and, guys: this has been the DEFAULT setting for how many years now?

Richieman
Richieman
6 years ago

Bug setting => comparability setting. We can get your point after reading it once. Why you have to repeat yourself so many times? Insecurity? Move your lazy fingers and set the bloody strict mode. Totally agree with Ian, this page is a waste of time. (BTW I'm a pg user)

jgmitzen
jgmitzen
2 years ago
Reply to  Richieman

Compatibility with what? Reminds me of when Microsoft explained Excel's leap year function giving the wrong answer for 1900 as having been done on purpose for backward compatibility with Lotus 1-2-3 for DOS (!!!), whose leap year function had the same bug. A computer science journal sarcastically praised them for having achieved "bug-for-bug compatibility" with Lotus 1-2-3 for DOS.

Richieman
Richieman
2 years ago
Reply to  jgmitzen

Compatibility with older MySQL, DUH
You're living in prehistory man, strict mode is default for MANY years now.

jgmitzen
jgmitzen
6 years ago
Reply to  Richieman

"The problem with defaults is that people use them".

Sammy
Sammy
6 years ago
Reply to  Doctordbx

What good is replication to me, if the data is incorrect? "rm" is faster and has a better compression ratio than "gzip", too.

I'd say excusing a database that can't reliably store data is more "fanboism" than this article.

Wutikrai Pornchai
Wutikrai Pornchai
7 years ago

Three years ago I start new project using MySQL at its beggining, I end up change to PosgreSQL instantly after realize it do not support recursive as it claim. Posgresq support recursive perfectly. I am so happy the decison was extreme correct. Today our product of cloase-base legal system using PosgreSQL 9.6 work fine .

Sigi Kiermayer
Sigi Kiermayer
6 years ago

I agree with others. This is not a good example for why someone might/should favour Postgresql over MySQL.

That above would be one argument.

Other real arguments might be things like special features (materialized view, json data handling) or the community itself (how many), quality of documentation etc.

For a lot of people it does not matter at all what you are using. Espeically there are a lot of people who not even using indize properly. Why should they care about some minor differences?

On another level you have people who know there stuff. They know there stuff on mysql, oracle, db2 or postgresql. And they should know why they are using on or the other.

Chris Travers
6 years ago

A key point is that MySQL guarantees relatively few things, but it offers compatibility modes to a number of rdbmss, so you can enforce these or not. But the application is in charge. This is a major difference: Use MySQL if you want to have a database for your application. Use PostgreSQL if you want to have an application for your database.

Hans-Jürgen Schönig
Hans-Jürgen Schönig
6 years ago

it is about the default mode - not about configuration. if your car blows up by default, it is not good either.

Miles
Miles
7 years ago

I love PostgreSQL, but unfair comparisons without strict mode enabled in MariaDB (on by default in >10.2.3) only serve to preach to the converted. https://mariadb.com/kb/en/mariadb/sql-mode/#strict-mode

Tomáš Fejfar
Tomáš Fejfar
7 years ago

It's unfortunate that you did not do any research on this topic. MySQL can be easily configured to work this way: https://www.percona.com/blog/2016/10/18/upgrading-to-mysql-5-7-beware-of-the-new-strict-mode/

Mark R
Mark R
6 years ago

I wrote a little piece in 2010 about this ...

http://marksverbiage.blogspot.de/2010/05/mysql-what-are-you-smoking.html

Essentially, error-handling behaviour is inconsistent in multi-row inserts, it depends on whether it's the first or subsequent rows.

Lukas Eder
6 years ago

Try your examples again with MySQL 5.7's STRICT mode (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict), which is turned on by default. You'll see they work as expected.

Neo
Neo
6 years ago
Neo
Neo
6 years ago

How about you stop these comparisons and dedicate a blog on the article https://eng.uber.com/mysql-migration/

I think it is very unlikely that you would

Melroy van den Berg
6 years ago

Do you have any performance measurements? Something like that?

Or scalability features?

Nick Farmer
Nick Farmer
2 years ago

What led me to choose PostgreSQL over MySQL (MariaDB didn't exist, but while it has improved on MySQL in this regard, it still lies a long ways behind) back when data integrity wasn't one of MySQL's priorities wasn't just that, but a lot of it was MySQL's anaemic type system.

MySQL doesn't even have a boolean type. The closest seems to be BIT(1), but everyone uses TINYINT(1) which means MySQL "booleans" have one false value and 255 different true values.

It could be forced by declaring a domain type (which is where you specify a base type and then add constraints (to the type, not columns that contain the type)). But MySQL doesn't have Domain types.

MySQL doesn't have array types. It doesn't have row (i.e. struct) types. This obviously means its support for JSON is forced to be weaker as it can't turn a JSON array into a native SQL array, or vice versa, because it doesn't support SQL arrays; and can't convert between a JSON objects and rows.

Because MySQL doesn't do domains, it can't and doesn't have a distinct JSONpath type. Range types are beyond its capabilities. Durations ditto.

The lack of array and domain types mean its SETs have to be declared on a table-by-table basis (and are typed as just strings). And then its lackadaisical approach to type safety rears its ugly head again:
"If you set a SET column to an unsupported value, the value is ignored and a warning is issued:"
Sure, you get a warning (if you're sitting at a console and not on the other end of some API), but it still adds a row, just not the row you tried to add.

Ditto ENUMs. MySQL doesn't have an ENUM type as a first-class citizen, so every time you want to use the same ENUM in more than one table you have to write out the entire list of valid values (the same values in the same order) in every one. And, once again:
"The index value of the empty string error value is 0. This means that you can ... find rows into which invalid ENUM values were assigned:"
How did you manage to insert invalid values in the first place? Because the ENUM type explicitly allows you to; inserting invalid values isn't a mistake, it just means you insert an empty string instead, because that's what you really wanted to do when you tried to insert invalid data, right? Seems you don't even get a warning this time. And if knowing what the "invalid value" was might have helped diagnose where it came from — well, you've lost that information.

And one more thing, because I didn't mean to rant but the list turned out longer than I thought and when I checked the MySQL documentation my eyes started rolling. While I've personally never had to write a low-level extension (though I've used a couple that were contributed by others, most notably "cube" (which introduces a hypercube type) and "hstore" (i.e. declare a column as containing a key/value store for each row) and I don't know what MySQL/MariaDB supports along such lines, but the fact is that PostgreSQL has had a user-extensible type system longer than it has had SQL.

Alex Willisson
Alex Willisson
3 years ago

For anyone coming to this article who's wondering about this in modern (2021) times, this doesn't appear to be an issue with MariaDB, though I can't speak for MySQL. I just tested it on a copy of MariaDB installed via Homebrew on MacOS 11.2.3, the reported version of MariaDB is "10.5.9-MariaDB Homebrew". Here's the error I get from the top example, I didn't bother with the rest

MariaDB [awillisson]> INSERT INTO data VALUES (1, 1234.5678);
ERROR 1264 (22003): Out of range value for column 'data' at row 1

Eason Wu
Eason Wu
3 years ago

one very simple problem in MySQL is that you CANNOT use group by with order by queries!
this is one very stupid bug that has been reported and discuss many many times!
also in performance and availability wise, with the same hardware; pg is always faster and stabler compare to MySQL.

John Walker
John Walker
4 years ago

A: Outdated article.
B: set sql_mode = "STRICT_ALL_TABLES,STRICT_TRANS_TABLE"; and use transactions in your queries.
basically, the entire premise of the article can be debunked just by the RTFM process.

Lord Dracon
Lord Dracon
5 years ago

PostgreSQL is a piece of crap.

asyncIdea
asyncIdea
6 years ago

is it still valid? I have checked the above issues on my 10.2.14-MariaDB and it gave the correct error reports:
mysql> select version();
-----------------
| version() |
-----------------
| 10.2.14-MariaDB |
-----------------
1 row in set (0.00 sec)

mysql> insert into data values(1,1234.5678);
ERROR 1264 (22003): Out of range value for column 'data' at row 1

mysql> update data set id = NULL where id = 1;
ERROR 1048 (23000): Column 'Id' cannot be null

Rick O'Shea
Rick O'Shea
6 years ago

Good to seem some factual comparisons versus this comical "in-depth" comparison: https://blog.panoply.io/postgresql-vs-mariadb Especially the "which is right for you" section, where there are "facts" like "powerful", "time-tested", "popular", "fierce dedication". Not a single fact whatsoever, just useless marketing word salad.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram