Hacker News Reader

Comment by lucideer | Hacker News Reader

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.

View on HN

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.

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.

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.

We also did a MySQL 5.7 to PG 9.6 migration (thanks to 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.

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.

> 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?

> 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!

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-mo...

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.

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...

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.

Oh, it's totally fair to criticize the standard. Those clowns lock their deliberation and standard definitions behind huge paywalls.

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.

Any “platonic ideals” mention outside its natural scope deserves attention.

You don't think the backticks lend a nice old school vibe?

If you use any of the really amazing JSON or Array stuff, you're stepping outside of the standard.. but it's worth it!

I absolutely love Postgres' JSON and array support. Its saved me from needing a document database in a few cases, which drastically simplified the overall deployment (one DB instead of two, or trying to represent relational stuff in a document DB). I really like JSON for stuff that is user defined, that I don't need to query into, but postgres' support means I still can should I ever need to. Which has happened once or twice for debugging.

100%. I've dealt with many small-to-medium sized projects where the usual approach would have been some form of EAV mess.

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.

> but postgres' support means I still can should I ever need to.

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'));

Yes, Postgres' jsonb and array support lets you not only query into these fields, but also, like you say, index into them too. Its really the best of both worlds: you get a great solid relational database, but if you need document database features, you don't really lose out on anything by using json, except that the queries are a bit more ugly. I'm super happy with postgres these days, its rock solid and full of fantastic features.

The word "any" there is mildly misleading about the JSON things, since there is now SQL/JSON which PG supports: https://www.postgresql.org/docs/13/functions-json.html#FUNCT...

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

Personally, back before JSON became a standard thing - I made heavy use of HSTORE to clean up some insanely cludgey string packing BS we'd been doing - leveraging recursive CTEs using the UNION definition allowed us to move a fixed tier system into a self-referential one that saved us a lot of headaches as well.

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.

> as far as i know, i am sticking to standard SQL

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.

Also the c++ bindings are very mature.

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”

Why would alphabetically be better? Wouldn't you want first in first out?

It means that the order they fire is imperative and determined by how you run your migrations.

The problem is that it doesn't leave you a great way to adjust order. It's just kinda weird.

(To be fair, table columns share this characteristic.)

> To be fair, table columns share this characteristic.

The table columns order is meaningless, but the triggers order is very relevant.

Table column order is not meaningless.

``` INSERT INTO foo SELECT * FROM bar ```

The harder problem however, is order in composite types.

Yeah, I do really recommend people not to do that sort of thing when they ask me.

Alphabetical makes it so two databases with the same triggers have the same behavior. FIFO breaks that, because now it depends on trigger construction order (eg when dump/restoring) and is totally invisible to the user.

Additionally, modifying a trigger by drop/creating it would change behavior, unless you also drop/create every trigger (in the original order)

Thank you most important thing I learned today.

What is an example where the order of execution is required, not contrived or bad practise. Looking for a good use case

Easiest I can imagine is a user-facing id that gets incremented by trigger (e.g. select max(col)+1 — autoincrement/serial doesn’t guarantee contiguity, or even reasonable increments…).

And then any second trigger that relies on that value

Oh, I think it's probably bad practice to knowingly depend on that.

But I also think that it's unnecessarily problematic to make the order non-deterministic.

Why was this down-voted? It is a good question. I thought exactly the same myself. And the reply from @setr is helpful. (Hat tip) I never thought about the impact of dump/restore!

Not only what @setr answered but consider also that by having a consistent ruling you can actually design or alter the order of triggers just by naming them according to a specific scheme.

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.

I was curious so I looked up the differences here:

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).

Binlog incompatibility makes migration a lot more difficult at scale.

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.

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

> 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.

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.

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.

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)

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, that link starts at the beginning of what's popularly known as "the lawnmower rant", it's delightful.

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.

Oracle is selling Enterprise features and Support, i am sure they make a lot of money with MySQL.

No it’s 100% still correct.

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.

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.

Big enough companies are the stereotypical paper clip AIs everyone fear, but with money. Anything seemingly moral action by them is simply a cost-analysis on how the public opinion will effect their profits.

Oracle paid a lot of money to buy Sun, and basically ended up with two products- selling support for Java and MySQL. Letting MySQL rot would have been pretty bad for their pocket book (especially as others have noted that postgres has been cutting into their market heavily).

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.

> Experimenting on or converting small projects into new markets?

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.

FANG would buy them if they had somehow managed to stumble forward to the mid-2010s.

Zero offers from any of them, so.

I don't think that FB were in a position back then. I was very surprised at the time that Google didn't buy them. Microsoft would never had gotten it past the regulators.

Oracle, compared to their image is quite technically adept — they did a splendid job with OpenJDK and they have plenty of long-term R&D projects like GraalVM.

Yeah so, all of that seemed to happen after that Cantrill talk. How did Oracle suddenly turn from a pure license contract squeezing company into a tech leader again? I mean, they had to hire great engineering talent to do all this. And convince them to work for Oracle. What's the narrative I'm missing?

I'm not familiar with the internal stucture of Oracle, just tired of the imo unfair bashing of them (at least compared to all other companies).

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)?

>Yeah so, all of that seemed to happen after that Cantrill talk.

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.

I heard they were poaching whole teams with a massive pay bump

likely support and keeping customers from migrating to postgres

and yes, Oracle is 110% focused on the bottom line. Preferably this quarter

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.

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.

> * 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.

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.

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.

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?

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.

[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

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.

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)

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.

sounds like their java strategy until recently

Reply on news.ycombinator.com