I'm a huge PG fan but switching your database on an existing project is a non-small undertaking.
If you're starting from scratch, then yes, you take PG every time. There's a lot...a lot that goes into saying that and if you haven't made the jump, a lot of the reasons aren't going to seem important because you currently "don't do" the things that PG lets you do with your data.
So the surface level stuff that you're experiencing with your database is going to be MySQL relative.
Probably the one that connects the most with the people who are working with MySQL is that you no longer have to worry about TEXT, MEDIUMTEXT, LONGTEXT.
Without really thinking about it, most people and ORM's just use TEXT and every now and then you'll get some data to put into the field that doesn't fit...and it won't generate an error it will just truncate the data. You'll end up needing to go and increase the field size wherever the problem happens (and then hope it doesn't happen again).
With PostgreSQL it's just TEXT. Done. Doesn't matter how big or small it is, it will fit the whole document. If the data is larger than 8kb, it will be transparently compressed with TOAST.
I've tested by dropping in a 2mb XML document and it stored as 80kb.
That. PG won't silently truncate your texts. It also won't accept 0000-00-00 dates.
MySQL has enough data corruption bugs to last for a lifetime. It is also maintained by Oracle, what means the bugcount is likely going up instead of down.
Text column type is not where I'd advertise pg differences. Rather a much richer SQL query feature set and a smarter (though sometimes smartass) query optimizer.
MySQL is dumb (though predictably dumb) and queries are single threaded, and the feature set is limited. Some queries have no efficient representation, and you need to trade off cost of materialising derived table vs repeated correlated queries.
If you're starting from scratch, then yes, you take PG every time. There's a lot...a lot that goes into saying that and if you haven't made the jump, a lot of the reasons aren't going to seem important because you currently "don't do" the things that PG lets you do with your data.
Like this: https://www.brightball.com/articles/why-should-you-learn-pos...
So the surface level stuff that you're experiencing with your database is going to be MySQL relative.
Probably the one that connects the most with the people who are working with MySQL is that you no longer have to worry about TEXT, MEDIUMTEXT, LONGTEXT.
Without really thinking about it, most people and ORM's just use TEXT and every now and then you'll get some data to put into the field that doesn't fit...and it won't generate an error it will just truncate the data. You'll end up needing to go and increase the field size wherever the problem happens (and then hope it doesn't happen again).
With PostgreSQL it's just TEXT. Done. Doesn't matter how big or small it is, it will fit the whole document. If the data is larger than 8kb, it will be transparently compressed with TOAST.
I've tested by dropping in a 2mb XML document and it stored as 80kb.
That's just a tiny piece though.