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...
For further actions, you may consider blocking this person and/or reporting abuse
LOOL
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!
What?! I didn't know they differed on this. I always design the tables putting checks and defaults inside the DB first.
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
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!
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!
Speaking of
TINYINT(1), I found a bug in Innodb years ago where this type could not be returned.I'm sure mysql devs were not happy for such design decision, using ints instead of a proper bool...
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.
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.
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.
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.
Bookmarked to read in the future. There is no way this isn't an awesome read Dian.
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..
+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 ?
@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
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 😂
I'll need to convince some client to hire you to convert a perfectly sound db made with Postgres 10 to MySQL 8 :D
MySQL also lacks proper sequences, and the build in
serialwhich mimics it has some "funny" behaviour when a table is truncated.A truncate of a table in MySQL also resets the serial 🙄
I'm not sure why this is showing up with a "Postgres vs MongoDB" thumbnail on Twitter now. @ben any ideas?
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.
Great, thanks.
MySQL's JSON type is stored using a binary format similar to Postgres' JSONB.
Blown away after knowing about TINYINT(1) for the boolean type.Nice Post.
Great article. MySQL also has the issue that it truncates varchars silently if they exceed the max length.
You could also mention the LISTEN / NOTIFY commands to enable asynchronous notifications with Posgre.
This was such an epic post O.o
Very well written! Thanks. The only thing I'm missing is about scalability/clustering?
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.