DEV Community

The Ultimate Postgres vs MySQL Blog Post

Dian Fay on April 11, 2018

I should probably say up front that I love working with Postgres and could die happy without ever seeing a mysql> prompt again. This is not an u...
Collapse
 
rhymes profile image
rhymes

Now that we're all basically over the collective hallucination of a "schemaless" future

LOOL

Postgres actually goes a step further by offering a JSONB type which processes input data into a binary format. This means it's a little bit slower to write, but much faster to query.

JSONB is awesome, I built an entire filtering system on top of it with Rails's Arel (relational algebra engine) to compose standard WHERE filters with filters in JSON columns at runtime. Great performance. And now in PostgreSQL 10 they enabled full text search on JSON columns, can't wait to have a use case to try it!

Unless you're using MySQL, in which case the only function you can reference in a DEFAULT clause is now().

What?! I didn't know they differed on this. I always design the tables putting checks and defaults inside the DB first.

However, MySQL's boolean is actually an alias for TINYINT(1).

Been bitten by this once! It's lol. I still remember back in the day when people used to tell me "I use MySQL because it's faster and easier to install than PostgreSQL". Yeah, if you want to have weird data in the DB (but it's gotten way better than those days), I guess after MySQL 5.5

Inlining arrays makes many tasks -- such as tagging records -- much easier.

Also, they have operators which lets you operate on those columns inside the database, without having to do such operations in the code. The fact that I can ask the DB if the array typed columns contains this other array it's awesome!

There are drawbacks to using enums, to be sure: adding new values requires DDL, and you can't remove values at all. But appropriate use cases for them are still reasonably common.

Yeah, that's more or less why they are seldom used. A lot of times you don't know in advance all the possible enum values so you tend to keep them in the code.

About lateral queries, I didn't know about them! Thanks!

I want to share an horror story: a client that had a MySQL database they used to store data scraped with Python. Everything went well for a while, until it didn't. After a bit of research (I had no experience with the database) I found out that MySQL's default UTF-8 type uses 3 bytes, not 4 which meant that a LOT of text was not going in the database. I was not happy. I couldn't convert the tables so I had to write a function in Python that converted 4 bytes utf-8 to 3 bytes. Insane.

Best post BTW, it's definitely the ultimate comparison.

ps. you really hate tinyints :P

pps. thanks for the link to multicore, i didn't know about that as well!

Collapse
 
kspeakman profile image
Kasey Speakman • Edited

Speaking of TINYINT(1), I found a bug in Innodb years ago where this type could not be returned.

Collapse
 
rhymes profile image
rhymes

I'm sure mysql devs were not happy for such design decision, using ints instead of a proper bool...

Collapse
 
elmuerte profile image
Michiel Hendriks • Edited

You forgot to mention partial indexes. Every index you create decreases performance a bit. But being able to create small indexes which could fit in a few memory pages adds a lot of performance when you use them.

In our case we have a huge table with order data. For some report we needed to query on a date range of an optionally filled timestamp field. The index which would only contain non-null rows was only a few kB for a table containing millions of rows. If all null rows would have been included it would have been quite a lot of megabytes instead.

PS, Besides JSON, PostgreSQL also supports XML as datatype, or simply process text to XML.

Collapse
 
msyvr profile image
Monica Spisar

Thank you so much for writing this, Dian. In contrasting the two, you've highlighted some important database-related considerations more generally, so it's educational (and/or a valuable reminder) on a whole other level as well. Really appreciate the effort you put into this.

Collapse
 
kspeakman profile image
Kasey Speakman

I got burned on MySQL a long time ago. I'm sure the situation has improved since then. But I still avoid using it. With the exception of a few ergonomic things, Postgres has been great. I'm also quite familiar with MSSQL, but I would pick Postgres over it primarily for the easier text search and jsonb features.

Collapse
 
dmfay profile image
Dian Fay

I think I read that post ages ago! SQL Server is the best product Microsoft's developed for my money. I worked with 2000-2008 extensively and I'd do it again, but Postgres is still my #1 pick.

Collapse
 
ben profile image
Ben Halpern

Bookmarked to read in the future. There is no way this isn't an awesome read Dian.

Collapse
 
blackcat_dev profile image
Sasha Blagojevic • Edited

Well, this was a great read! I haven't used Postgres but it looks really powerful. But honestly, aside from the JSON data type and maybe LITERAL JOINS, I could live without that other stuff. Why I still use MySQL and see a use case for it, is because a lot of that stuff you listed as Postgres features I don't like doing at the Database level, I prefer handling it at the Code level. It's a more future proof and flexible approach in my opinion, it's easier to alter a few lines of code than alter Databases, triggers, checks, whatever etc..

Collapse
 
anaguib profile image
Ahmed Naguib

+1 for this comment, I have worked on a monolithic project which made use of every single feature of Postgres mentioned above ( I guess the original developer really wanted to try them all).
We struggled everyday trying to debug things and figure out how they work in the database. We spent most of the time trying to remove logic from the database to code, to make the logic more understandable and easier to read.

So, Yes! Postgres is awesome, But please don't abuse these features and ask yourself first. Do I really need to this at the database level ?

Collapse
 
rhymes profile image
rhymes

@dmfay : I guess you'll need to release a new comparison soon :P

MySQL has window functions and CTEs now: modern-sql.com/blog/2018-04/mysql-8.0

Still ignores check constraints though :(

MariaDB also introduced versioned tables which I would like to see in PostgreSQL

Collapse
 
dmfay profile image
Dian Fay

I'm not in the position of needing to convert a Postgres data layer to run on MySQL anymore so I'll leave that for someone else 😂

Collapse
 
rhymes profile image
rhymes

I'll need to convince some client to hire you to convert a perfectly sound db made with Postgres 10 to MySQL 8 :D

Collapse
 
davidolrik profile image
David Jack Wange Olrik

MySQL also lacks proper sequences, and the build in serial which mimics it has some "funny" behaviour when a table is truncated.

A truncate of a table in MySQL also resets the serial 🙄

Collapse
 
dmfay profile image
Dian Fay

I'm not sure why this is showing up with a "Postgres vs MongoDB" thumbnail on Twitter now. @ben any ideas?

Collapse
 
ben profile image
Ben Halpern

Yeah this was a brain fart from our editorial, which we fixed after this comment. And before this got tweeted out from our account.

Our process is to spring up a nice social image if it's worth sharing and we just made a mistake this time. One TODO in this process is to make it clearer how we do this.

Collapse
 
cess11 profile image
PNS11

Great, thanks.

Collapse
 
ogrovlen profile image
Øystein Grøvlen

MySQL's JSON type is stored using a binary format similar to Postgres' JSONB.

Collapse
 
rahulpuroht profile image
Rahul Purohit

Blown away after knowing about TINYINT(1) for the boolean type.Nice Post.

Collapse
 
prasad_h profile image
Prasad Honavar

Great article. MySQL also has the issue that it truncates varchars silently if they exceed the max length.

Collapse
 
j3m5 profile image
J3m5

You could also mention the LISTEN / NOTIFY commands to enable asynchronous notifications with Posgre.

Collapse
 
joshcheek profile image
Josh Cheek

This was such an epic post O.o

Collapse
 
palquimista profile image
Paulo Jesus

Very well written! Thanks. The only thing I'm missing is about scalability/clustering?

Collapse
 
rcpp85 profile image
ricardo • Edited

Olá!

Yes, I missed that too. But there are plent of other places covering this issue - even here, maybe.

Where I'm working right now we are starting a new version from our main product, which includes studying another RDB that isn't MySQL/Maria, mostly because we have some issues with some data types (like the UUID field).

We also had a problem with the replication/scale of the db, however, this was more of a bad infrastructure design then a MySQL/Maria problem.