10 ms·

That was the expectation but the Oracle camp surprised everyone by actually giving it some attention, and meanwhile MariaDB's promise of being a "100% compatibl

That was the expectation but the Oracle camp surprised everyone by actually giving it some attention, and meanwhile MariaDB's promise of being a "100% compatible drop-in replacement" came with more and more caveats with each release (as is to be entirely expected with any fork). 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.

munk-a 5y ago
I carried out a (quite ill-advised in retrospect) migration from MySQL to Postgres on a mature codebase with absolutely no automated test coverage and it went pretty smoothly. We had some bumpy performance directly after the production migration but we were able to reach parity after about two months of observing and adding targeted indices. 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.
- lucideer 5y ago
  I am very much an example of one of those previously MySQL devs that now uses Postgres for new projects, though I've never done a migration between the two.
- code_duck 5y ago
  I had an app with a fairly complex and large set of tables and data and migrated it from mySQL to pg with very little trouble. It was a few years back, but from what I recall a re-wrote a few queries in my data layer and that’s about it. Much easier than it was to setup nginx to take over some Apache duties.
- tapoxi 5y ago
  We also did a MySQL 5.7 to PG 9.6 migration (thanks to https://pgloader.io/ https://pgloader.io/) and we got some pretty immediate speed improvements (2-5x). We shot ourselves in the foot a few weeks later with some poorly written RLS policies, but it was overall a smooth experience.
  - dijit 5y ago
    pgloader is great. 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.
    - evanelias 5y ago
      > 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?
- twic 5y ago
  > I absolutely adore Postgres's tweaks to the SQL language 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!
  - eatonphil 5y ago
    MySQL does not conform to the standard in a number of ways. On the one hand, thankfully you can turn on ANSI MODE. On the other hand if you're using MySQL you've probably got a bunch of devs used to their non-ANSI SQL and turning on ANSI MODE may break stuff they do. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
  - jacobmischka 5y ago
    I assume they mean non-standard additions in PostgreSQL. A lot of the very handy pg functions are non-standard, as well as the rich text matching capabilities, many data types, and even the RETURNING clause, I believe.
  - felixfbecker 5y ago
    There is definitely stuff that is non-standard, like the casting with :: in addition to CAST AS. 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...
    - petergeoghegan 5y ago
      It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation. 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.
sltkr 5y ago
I was curious so I looked up the differences here: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ 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).
- hodgesrm 5y ago
  Binlog incompatibility makes migration a lot more difficult at scale.
- hellcow 5y ago
  The issues I encountered while using MariaDB in place of MySQL were subtle -- most things worked as expected, which makes the issues so much more unexpected. But at the end of the day, the same migrations produced a different schema. 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.
  - PeterZaitsev 5y ago
    I think this is a key difference - where MariaDB continues to evolve MySQL 5.6/5.7 codebase Oracle chose the "big leap" with MySQL 8 - introducing data dictionary and many other internal architecture changes. This is one of the reasons migration to MySQL 8 can be more painful than releases before it but for good reason
freedomben 5y ago
On Fedora/RHEL you get maria when you "install" mysql. I've never had something fail to "just work." That said I don't do anything crazy, so I'm sure there are cases where they don't work.
skrebbel 5y ago
Anyone on the in-crowd who can share a bit about Oracle's motivations here? 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 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.
- takeda 5y ago
  I guess I'm cynical, but to me looks like Oracle renewed interest in MySQL after PostgreSQL gained momentum. MySQL is not really a competitor to Oracle but PostgreSQL overlaps with both of them.
  - beberlei 5y ago
    Oracle is selling Enterprise features and Support, i am sure they make a lot of money with MySQL.
- brianwawok 5y ago
  No it’s 100% still correct.
- truffdog 5y ago
  Mr. Cantrill might be engaging in hyperbole. Big companies are big though- lots of people in charge of lots of things, and often they wander off in different directions. It doesn't make sense to model their behavior as if directed by a single hive mind.
  - tonyedgecombe 5y ago
    Companies do have a culture though, the reason Amazon warehouse employees have to piss in a bottle can be traced back to Bezos and his laser like focus on controlling costs.
pyrophane 5y ago
I like Postgres but still turn to MySQL for production use most of the time for a few reasons: * 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.
- brianwawok 5y ago
  Do you really need a connection pooler for most PG installs? I run a SaaS company with a lot going on, and haven’t had to touch a pooler. Average about 500 open connections. That’s a few, but on a DB server with 200GB of memory it’s nots really breaking anything.
- hans_castorp 5y ago
  > * Permission management on Postgres is much more painful. 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.
  - skeletal88 5y ago
    I joined a company where most stuff is done in Java and tried to explain to colleagues that sn external connection pool would ne good. They didn't know such things existed at all, and then refused because in their mind the application-specific intetnal pools were all they needed. Which was and is totally wrong - the application server internal pools, like HikariCP only share connections between threads inside the server. What if now we have 4 instances of the same server running (because of a deployment, where 2 new ones are spawned, started and the 2 old ones aren't taken down until the loadbalancer determines that the new o es are running ok. Now you need 2x the number of connections available than you would ususlly need. Once we had run out of connections when for some reason aws went crazy and started deploying too many instances of the application.
z3t4 5y ago
When I started working with databases about 20 years ago talking to peers it was common to have a layer in-front of the database so that you could easily replace the database. It felt stupid until I actually had to do a database migration... So do you still put a layer infront of the database so that you can for example easily change from mySQL to Postgress? Given there are so many open source database I think there is less need to do so... Or do you go all in vendor lock in? Or some middle ground?
- astrange 5y ago
  That would be an ORM or something similar, I think. It's not realistic unless you're always testing with two databases from the start, because their implementation details leak through. 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.
  - davecanderson 5y ago
    [ServiceStack OrmLite][1] does just that, all our development is done locally using SQLite but then deployments use MySQL. We do fall into the 'program is simple enough' category though. [1]: https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/ServiceStack/ServiceStack.OrmLite
- cubano 5y ago
  I believe you are talking about the Microsoft ODBC API which was language and platform agnostic data acquisition layer. Early versions of it had pretty bad performance issues if I remember correctly, but eventually it became somewhat useful. 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.
- ris 5y ago
  You can indeed have that and many people do, but the problem with going that route is you can only use the lowest common denominator of features of any of the target databases, which wipes out a lot of the performance or convenience features you'd get from either. (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)
agumonkey 5y ago
sounds like their java strategy until recently
tomxor 5y ago
> meanwhile MariaDB's promise of being a "100% compatible drop-in replacement" came with more and more caveats with each release 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.
- Shorel 5y ago
  I have a lot of stored procedures and functions that were converted from Oracle to MySQL, and they also work just fine in MariaDB. I guess any actual incompatibility is in fringe cutting edge features, which is where both databases have diverged.
- cknoxrun 5y ago
  We migrated a high-traffic, complex site with a huge amount of data to MariaDB about 5 years ago as well, and it's been solid so far. We use Galera as well, which has performed and scaled really admirably.
- lucideer 5y ago
  I think it's still pretty good. I ran into some issues with json support some years ago, but querying json may be an edge case (as opposed to storing text & creating your own indices)
scrapcode 5y ago
I can see the business of open source software support coming to play as well. I went from freelance development to more of a govt contracting role and just recently pivoted my career back towards tech. I never saw this side of the business but it is thriving from where I sit.

munk-a 5y ago

I carried out a (quite ill-advised in retrospect) migration from MySQL to Postgres on a mature codebase with absolutely no automated test coverage and it went pretty smoothly. We had some bumpy performance directly after the production migration but we were able to reach parity after about two months of observing and adding targeted indices. 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.

lucideer 5y ago
I am very much an example of one of those previously MySQL devs that now uses Postgres for new projects, though I've never done a migration between the two.
code_duck 5y ago
I had an app with a fairly complex and large set of tables and data and migrated it from mySQL to pg with very little trouble. It was a few years back, but from what I recall a re-wrote a few queries in my data layer and that’s about it. Much easier than it was to setup nginx to take over some Apache duties.
tapoxi 5y ago
We also did a MySQL 5.7 to PG 9.6 migration (thanks to https://pgloader.io/ https://pgloader.io/) and we got some pretty immediate speed improvements (2-5x). We shot ourselves in the foot a few weeks later with some poorly written RLS policies, but it was overall a smooth experience.
- dijit 5y ago
  pgloader is great. 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.
  - evanelias 5y ago
    > 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?
twic 5y ago
> I absolutely adore Postgres's tweaks to the SQL language 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!
- eatonphil 5y ago
  MySQL does not conform to the standard in a number of ways. On the one hand, thankfully you can turn on ANSI MODE. On the other hand if you're using MySQL you've probably got a bunch of devs used to their non-ANSI SQL and turning on ANSI MODE may break stuff they do. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-combo https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
- jacobmischka 5y ago
  I assume they mean non-standard additions in PostgreSQL. A lot of the very handy pg functions are non-standard, as well as the rich text matching capabilities, many data types, and even the RETURNING clause, I believe.
- felixfbecker 5y ago
  There is definitely stuff that is non-standard, like the casting with :: in addition to CAST AS. 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...
  - petergeoghegan 5y ago
    It is practically essential for any RDBMS to provide non-standard statements and features. For example, even CREATE INDEX isn't in the SQL standard, since the SQL standard deliberately avoids talking about what it considers to be implementation details. This is probably a good goal in many ways. But what counts as an implementation detail is open to interpretation. 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.

sltkr 5y ago

I was curious so I looked up the differences here: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/ 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).

hodgesrm 5y ago
Binlog incompatibility makes migration a lot more difficult at scale.
hellcow 5y ago
The issues I encountered while using MariaDB in place of MySQL were subtle -- most things worked as expected, which makes the issues so much more unexpected. But at the end of the day, the same migrations produced a different schema. 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.
- PeterZaitsev 5y ago
  I think this is a key difference - where MariaDB continues to evolve MySQL 5.6/5.7 codebase Oracle chose the "big leap" with MySQL 8 - introducing data dictionary and many other internal architecture changes. This is one of the reasons migration to MySQL 8 can be more painful than releases before it but for good reason

freedomben 5y ago

On Fedora/RHEL you get maria when you "install" mysql. I've never had something fail to "just work." That said I don't do anything crazy, so I'm sure there are cases where they don't work.

skrebbel 5y ago

Anyone on the in-crowd who can share a bit about Oracle's motivations here? 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 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.

takeda 5y ago
I guess I'm cynical, but to me looks like Oracle renewed interest in MySQL after PostgreSQL gained momentum. MySQL is not really a competitor to Oracle but PostgreSQL overlaps with both of them.
- beberlei 5y ago
  Oracle is selling Enterprise features and Support, i am sure they make a lot of money with MySQL.
brianwawok 5y ago
No it’s 100% still correct.
truffdog 5y ago
Mr. Cantrill might be engaging in hyperbole. Big companies are big though- lots of people in charge of lots of things, and often they wander off in different directions. It doesn't make sense to model their behavior as if directed by a single hive mind.
- tonyedgecombe 5y ago
  Companies do have a culture though, the reason Amazon warehouse employees have to piss in a bottle can be traced back to Bezos and his laser like focus on controlling costs.

pyrophane 5y ago

I like Postgres but still turn to MySQL for production use most of the time for a few reasons: * 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.

brianwawok 5y ago
Do you really need a connection pooler for most PG installs? I run a SaaS company with a lot going on, and haven’t had to touch a pooler. Average about 500 open connections. That’s a few, but on a DB server with 200GB of memory it’s nots really breaking anything.
hans_castorp 5y ago
> * Permission management on Postgres is much more painful. 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.
- skeletal88 5y ago
  I joined a company where most stuff is done in Java and tried to explain to colleagues that sn external connection pool would ne good. They didn't know such things existed at all, and then refused because in their mind the application-specific intetnal pools were all they needed. Which was and is totally wrong - the application server internal pools, like HikariCP only share connections between threads inside the server. What if now we have 4 instances of the same server running (because of a deployment, where 2 new ones are spawned, started and the 2 old ones aren't taken down until the loadbalancer determines that the new o es are running ok. Now you need 2x the number of connections available than you would ususlly need. Once we had run out of connections when for some reason aws went crazy and started deploying too many instances of the application.

z3t4 5y ago

When I started working with databases about 20 years ago talking to peers it was common to have a layer in-front of the database so that you could easily replace the database. It felt stupid until I actually had to do a database migration... So do you still put a layer infront of the database so that you can for example easily change from mySQL to Postgress? Given there are so many open source database I think there is less need to do so... Or do you go all in vendor lock in? Or some middle ground?

astrange 5y ago
That would be an ORM or something similar, I think. It's not realistic unless you're always testing with two databases from the start, because their implementation details leak through. 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.
- davecanderson 5y ago
  [ServiceStack OrmLite][1] does just that, all our development is done locally using SQLite but then deployments use MySQL. We do fall into the 'program is simple enough' category though. [1]: https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/ServiceStack/ServiceStack.OrmLite
cubano 5y ago
I believe you are talking about the Microsoft ODBC API which was language and platform agnostic data acquisition layer. Early versions of it had pretty bad performance issues if I remember correctly, but eventually it became somewhat useful. 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.
ris 5y ago
You can indeed have that and many people do, but the problem with going that route is you can only use the lowest common denominator of features of any of the target databases, which wipes out a lot of the performance or convenience features you'd get from either. (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)

agumonkey 5y ago

sounds like their java strategy until recently

tomxor 5y ago

> meanwhile MariaDB's promise of being a "100% compatible drop-in replacement" came with more and more caveats with each release 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.

Shorel 5y ago
I have a lot of stored procedures and functions that were converted from Oracle to MySQL, and they also work just fine in MariaDB. I guess any actual incompatibility is in fringe cutting edge features, which is where both databases have diverged.
cknoxrun 5y ago
We migrated a high-traffic, complex site with a huge amount of data to MariaDB about 5 years ago as well, and it's been solid so far. We use Galera as well, which has performed and scaled really admirably.
lucideer 5y ago
I think it's still pretty good. I ran into some issues with json support some years ago, but querying json may be an edge case (as opposed to storing text & creating your own indices)

scrapcode 5y ago

I can see the business of open source software support coming to play as well. I went from freelance development to more of a govt contracting role and just recently pivoted my career back towards tech. I never saw this side of the business but it is thriving from where I sit.