I absolutely adore Postgres's tweaks to the SQL language, that dialect is amazing and has served us extremely well. And, since we passed pg10 a while back, the performance tuning you can do on it is pretty amazing.
Do you mean additions to the SQL standard, or bits of the standard that MySQL doesn't implement, or something else?
The reason i ask is that i also enjoy writing SQL for PostgreSQL, but as far as i know, i am sticking to standard SQL. Perhaps there are things i'm missing, or things i like which i haven't realised are nonstandard!
But it's true that PSQL sticks very closely to the standard or only adds on top of it, while MySQL is very uncomformant.
Funnily enough, I actually first used Postgres over MySQL a long time ago when I moved from PHP to NodeJS, and was annoyed that MySQL uses backticks for identifiers while PSQL uses SQL standard double quotes, because in JS multi-line strings (for SQL queries) are also use backticks and I was annoyed by escaping. Sometimes it's the little things...
You could say the same thing about the isolation levels. They are supposed to be platonic ideals, free from any implementation baggage. But the reality is that you can tell that the people that originally defined how they work were mostly (perhaps entirely) thinking about old school two-phase locking.
I'm not a critic of the standard -- it's imperfections (which are arguably contradictions) reflect real world differences that are hard or impossible to resolve. Just pointing out that this is how it is.
The best way to learn the SQL standard is to read the Postgres docs since they clearly call out non-compliances - there is no other easy to access and clear explanation of the SQL standard.
PostgreSQL documentation is very good about comparing each feature to the standard.
For example, the SQL standard specifies that triggers fire in the historical order they are added to the table.
PostgreSQL chose to fire them alphabetically.
Technically nonconformant, but in practical terms, far better.
This was surprising for my old tech lead who was sheepish on moving away from MySQL.
He would later refer to the MySQL bindings as “brain dead”
Additionally, modifying a trigger by drop/creating it would change behavior, unless you also drop/create every trigger (in the original order)
And then any second trigger that relies on that value
But I also think that it's unnecessarily problematic to make the order non-deterministic.
It just makes easier to understand what happens when triggers are invoked, without having to actually force specific cases by direct experimentation.
I did not even know that the standard prescribed a FIFO order, looks like a pretty bad idea to me, actually.
(To be fair, table columns share this characteristic.)
The table columns order is meaningless, but the triggers order is very relevant.
``` INSERT INTO foo SELECT * FROM bar ```
The harder problem however, is order in composite types.
Just using a BSON column for such attributes hits the Pareto 80/20 sweet spot, _and_ I can query against them much more easily than in an EAV schema (far fewer joins).
They're great for when the schema is mostly normalized, but there's just a little bit of denormalized stuff that doesn't warrant completely architecting the schema around it.
And since postgres supports indices on arbitrary expressions you can use CREATE INDEX for the json-queries in your WHERE:
CREATE INDEX ON publishers((info->>'name'));
It would be horribly painful to stick to _just_ SQL/JSON for manipulations, and I have absolutely zero idea what the coverage is in other platforms, but portability isn't completely off the table like it used to be
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
Other folks have mentioned :: instead of CAST AS (which is really nice when you need to write it a bunch of times) but it's mostly the diverse built-in function set and constraint support that keeps me an enthusiastic Postgres supporter.
We shot ourselves in the foot a few weeks later with some poorly written RLS policies, but it was overall a smooth experience.
Sadly for me when I tried to make a PoC of Postgres for our eCommerce platform I found that we had violated our constraints and lost data.
That was the day I went from MySQL agnostic to MySQL hating.
It largely doesn’t matter that the defaults could be configured to be more strict. We live in a world where people are trying to avoid hiring sysadmin and that database was set up by a developer. It should not have been a default.
Everyone agrees with this. That's why they changed the default nearly 6 years ago.
Yes, the old default was terrible. But there's no way to change the past, so why complain about it for years and years?
All versions with this bad default have hit end-of-life, what more can be done?
https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
It doesn't sound too bad. The incompabilities seem to be mostly stuff like not being able to generally use replication from MySQL to MariaDB or vice versa. But I would guess that's not a very common case anyway: most users are either developers that want their software to work with both MariaDB and MySQL (which can be achieved by sticking to the very large intersection of supported features), or they are users that want to switch from MySQL to MariaDB (which seems well-supported). Running a mix of MariaDB and MySQL servers and expecting to be able to replicate between them seems like a particularly uncommon setup (though maybe it's useful during a migration).
Arguably MariaDB made the correct decision to fix some MySQL 5.7 bugs that we were unwittingly relying upon, but the different behavior caused issues for us all the same.
Rather than worry about MySQL vs MariaDB and whether the one I need is packaged and available for my distro/OS of choice, I reach for Postgres on new projects.
Anecdata: I've been using mariadb as a drop in mysql replacement for over 5 years in production, and it's been working seamlessly.
I'm no DB expert, and perhaps my use case isn't complex enough or written in a highly mysql dependent way - but I felt like someone should chime in since there's not much positivity toward mariadb in this thread so far. I'd be interested to hear from some other people with actual mariadb experience.
I guess any actual incompatibility is in fringe cutting edge features, which is where both databases have diverged.
I recall a Bryan Cantrill talk about how Oracle is this completely amoral machine that just wants to make money, and how that drives their every decision [0]. Is this no longer the case? Or is it, and if so, how does improving MySQL and giving it away for free making Oracle money? Or was it always hyperbole and Oracle is improving MySQL because they want to be nice?
[0] https://www.youtube.com/watch?v=-zRN7XLCRhc&t=2047s, that link starts at the beginning of what's popularly known as "the lawnmower rant", it's delightful.
In Bryan's defense, the lawnmower rant is pretty true- they are a simple company. They focus heavily on making money and abandoning things they aren't in a good position to do so from.
Selling per-cpu-core licenses and support contracts, great! Experimenting on or converting small projects into new markets? Not great. Fleshing out OpenSolaris into a product that could rival linux? Dead end.
Maybe it could have worked out, but the lawnmower didn't care about a product that had too few users to make money from. OTOH, even Facebook uses MySQL... All they need to do is invest enough to maintain market share to get support contracts.
Bringing MaximeVM out of research labs into the market as GraalVM.
First RDMS to support Perl and Java as stored procedures.
Join venture with Sun for Network Computer thin clients.
> Fleshing out OpenSolaris into a product that could rival linux?
Every OpenSolaris sales is one sale less from Unbreakable Linux, so in a day and age where UNIX === Linux, a company like Oracle has chosen what makes more sense monetarly.
I doubt that IBM is getting lots of new AIX customers.
And in the end, except for a timid offer from IBM, no one cared to rescue Sun.
But I guess they must have had at least sane management that tried to keep the existing mindshare of acquired projects (as far as I know many people went from Sun to Oracle directly for example), as well as finding new talent. And also, the acquisitions made sense and were done at a comparatively good price so I guess they are good lawnmowers with long-term plans (but terrible PR)?
The talk was in 2011, Oracle purchased Sun in 2010. So basically their continuous work on OpenJDK has nothing to do with the talk at all.
and yes, Oracle is 110% focused on the bottom line. Preferably this quarter
* Permission management on Postgres is much more painful. * The need for an external connection pooler makes postgres more annoying to set up. * Better quality docs on performance tuning MySQL.
but way more flexible and powerful
I only work in the Java world where all application servers have the connection pooling built in, so I never had the need for an external connection pooler.
I believe that developers back in the 90's wanted a way to "drop in" various low and higher cost DBMS systems to their client-server based apps without the need of hiring full-time database sysops.
It can be useful if your program is simple enough to work with both MySQL and SQLite, but often you should just stick with one DB and get to use all its features.
(arguably SQL itself is/was supposed to be that common layer in the first place, but db vendors always want to be able to present users with new features that are not yet standardized)
When you consider the network effect of all the stacks already heavily invested in MySQL, all Oracle really needed to do was put in a modicum of effort to MySQL to stave off the MariaDB migrations (and from my understanding they've put in quite a bit more than a modicum).
Lastly, Postgres is an interesting player. Migrating from MySQL to Postgres might be a scarier prospect than to MariaDB, but since the Oracle acquisition a lot of MySQL-veterans were looking for something non-MySQL for new projects and in that I get the sense Postgres beat out MariaDB.