Why we're ending support for MySQL

*edit: I recognize a lot of what you mentioned though, and MySQL sure has its draw backs. Still it seems to work fine for a lot of people.

> This is true, but is it really a show stopper?

I can’t comment on their data model but in my own experience partial indexes are an invaluable feature. In particular partial unique indexes as it both expands the guarantees of your data model that can be enforced by the database while taking up (what could be) considerably less space.

>> As a side note – we don't use MySQL ourselves

> I think this is the real reason. They just didn't have the necessary talent to implement the features correctly. Wrong schema specifications and not knowing to implement nested set patterns is a sign that they don't have a knowledgeable DBA on staff.

This must be the big one.

Given enough experts and a drive to make it work, technology finds a way.

Given no experts and not enough users to complain or care about breaking compatibility, technology finds the door.

> This is true, but is it really a show stopper?

They are invaluable for performance as well (somebody mentioned unique partial indexes as constraints). They generally let you avoid partitioning your data (eg. partioned tables) and getting the same performance benefits by simply having conditional indexes.

They are sometimes the difference between a sequential scan of a 150M row table and an index lookup in <10ms (especially if the index is in the memory cache).

Once you try them and get the benefit, you can't go back to not having them.

In PostgreSQL, TEXT/VARCHAR (same type) is only stored outside the tuple if it's large.

In general, you can keep selective indexes in memory, possibly even with supplementary columns to eliminate disk access for the actual data you're trying to fetch by the key the index is based on.

Even Sqlite has partial index support...

    > All they had to do was implement a nested set pattern for their groups

Using PostgreSQL CTEs allowed us to work around all of this, at the cost of not supporting MySQL. This seems like a fairly reasonable trade-off, but I might be biased as I implemented it [1].

    > A hack? Their DB creation schema specified a TEXT column when it should
    > have been a LONGTEXT column. Using LONGTEXT is not a hack, it's a choice
    > when your data is more than 65535 characters, and they made the wrong
    > choice out of ignorance.

More importantly, moving away from MySQL allows us to stop worrying about this at all.

    > Alternatively, since this is filepaths and filenames, they could have used
    > a nested set pattern again and gotten 255 characters for each component of
    > the path and a lot more feature options for their search system!

    > This is true, but is it really a show stopper?

    > Wrong schema specifications and not knowing to implement nested set
    > patterns is a sign that they don't have a knowledgeable DBA on staff.

Some of the weirder decisions were made before the right people were hired, and often these decisions are difficult to improve upon. Sometimes removing support for something is a much more efficient way of spending your time. Removing MySQL support in GitLab is one such case.

[1]: https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/10885

And they said this clearly, they're not hiding that this is the motivation.

Creating the abstraction architectures to support more than one "thing" tends to more than double development effort to support two "things". (Although then lets you add additional more-than-two "things" with less incremental cost. But there's a lot of cost to more than one thing in the first place).

I believe from context you meant "factitious":

> factitious -- artificially created or developed.

Vs.

> facetious -- treating serious issues with deliberately inappropriate humor; flippant.

In general a well argued post. This easy to make typo doesn't detract from that.

In my personal experience, startups and even top tech companies don't really have DBAs on staff. They hire good generalist programmers, but they rarely get true specialists, especially DBAs.

DBAs are generally found in banks and in non-tech enterprise companies, from what I've seen.

In your critique of their argument, I don’t see a single reason why they should use MySQL. So why should they continue to use MySQL? Why should they solve nested groups and long text columns? What does that buy them?

> All they had to do was implement a nested set pattern for their groups [1]

I admit I have a sensitivity to knee-jerk “can’t you just” comments, but reading the first paragraph of your link, this doesn’t sound like an automatic win. Is it possible that they tried this and found out that it actually couldn’t be supported in a performant way? (Edit: turns out they did exactly that https://news.ycombinator.com/item?id=20344575) Either way, it sounds tricky... so back to the real question... why bother?

“Updating requires renumbering and is therefore expensive. Refinements that use rational numbers instead of integers can avoid renumbering, and so are faster to update, although much more complicated.”

To be fair, the nested set pattern is the opposite of fun - especially at scale.

In my experience, "all you have to do is..." is always followed by something heinous.

> All they had to do was implement a nested set pattern for their groups [1]

Nested sets come with significant downsides. They are a hassle to implement, but more importantly, a single insert can require writes to lots of rows. This is both problem for concurrent transactions and for row-based replication.

> All they had to do was implement a nested set pattern for their groups

Or an association matrix.

Though if their concern is performance and they are using recursive CTEs in postgres, they may have hold of the wrong end of the stick.

Because CTEs are an optimisation fence for predicate push-down in postgres they can sometimes result in much more expensive query plans than in other DB engines (for instance, MS SQL Server), resulting in excess index/table scanning. This makes them far more a code convenience feature rather than a performance one (they might improve the performance of your coders by making maintenance easier, but might not improve the performance of your system in action).

> > MySQL doesn't support partial indexes

> This is true, but is it really a show stopper?

No, but yes. On their own systems it isn't, as they don't use mySQL. On small self-installs it isn't as they are unlikely to have enough data for it to make a measurable difference. Larger self-installs can just throw hardware at the problem... But supporting a backend that doesn't have them, when they find them useful in their own large installs, means either doing without across the board or needing to maintain two code paths which means more work and more bugs.

> > As a side note – we don't use MySQL ourselves

> I think this is the real reason

I would agree there, though I'd not go as far as to suggest a lack of talent.

The fact that their dev teams and their own production environments don't routinely use MySQL means that they are less likely to hit specific issues that they code has with that back-end during early dev, instead catching them in later QA when fixes are more expensive to implement or worse not catching them at all until release.

Furthermore, supporting mySQL does not just mean supporting the latest version: 8.0 may be over a year old now but that is not nearly long enough for older releases to have been replaced globally especially as 5.7 is marked as officially supported up to 2023. This will be why recursive queries are an issue: mySQL prior to 8.x does not support them, all supported versions of postgres do.

> I think this is the real reason

A cynic (who? me?) might also suggest that an extra reason factoring into their decision is user selectivity. There is a perception that mySQL is easier to install and optimise, or that there is more support out there because it is more commonly supported out-of-the-box on managed hosting. While this perception may not be true, that it exists has an effect and it might put off certain (less experienced) admins from installing themselves, potentially reducing support burden and perhaps funnelling some of them towards GitLab's own hosted services...

Nested set is not at all performant. It has much worse read and worse write performance (also, takes more storage space) than adjacency list with in-DB recursive queries.

I've been told a million times that SQL is declarative, meaning you say what you want, not how to get it.

I've also been told a million times that I need to write my queries in a specific way, or use a specific data structure, or add hints for the query optimizer. Otherwise it'll pick the wrong "how" for your "what".

Is there any practical difference between a declarative language with a Sufficiently Dumb Compiler, and an imperative language with weak features and an awkward syntax? However I'm forced to write it, we all know I'm really trying to do is get it to LOOP first over these items here, and not those there.

What's the point of being declarative if common tasks require us to bend over backwards to design our schema/queries/indices/hints in exactly the right way, in order for it to be performant on two popular SQL databases (when that's even possible)?

Even if the vaguely-English-like syntax that's completely unlike any other computer language weren't problematic for those reasons, it seems that the lack of abstraction is a complete buzzkill. These two databases require different implementations for "nested groups", but there's no (remotely portable) way to define a CREATED NESTEDGROUP to allow for a similar interface.

Of all the languages I have to use, SQL are the ones I hate most. From decades of writing SQL, I can say GitLab got one thing absolutely right: it's easiest to just treat it as a family of incompatible proprietary languages. It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries.

Sounds like a good reason to drop MySQL.

>> That's just incorrect. What they MEANT to say is that they had a column to store filenames that was a varchar(255) column and people were running out of space with long directory paths and filenames. They could have moved to a TEXT column, but didn't because they thought it couldn't be indexed without specifying a length... But they were wrong, you CAN index a TEXT column without specifying the TEXT column length, you just have to specify the length of the substring you want to index.

Unless I am misunderstanding the SQL documentation, doesn't the prefix length specification essentially make it such that you can only partially index the field, up to the first 3072 bytes? (https://dev.mysql.com/doc/refman/8.0/en/create-index.html) After that limit, the index _may_ still be performant, but does not at scale I would imagine that YMMV. You make a fair point that there are other patterns.

IMHO The article was not about bashing MySQL rather the GitLab team decided to choose one database platform to support and provided some basic reasoning around why they chose Postgres over MySQL. Both are great platforms, but you should choose the right tool for the job. While I agree that some of their arguments are weak but I am not sure that matters given that their strategic choice was one or the other.

Frankly, I would have liked to have seen some data that represents what their install base utilizes. If it is 90% mysql and 10% postgres their choice would be strange given the weak arguments. We just don't have that data.

The demand for that have decreased by a lot. I can speculate that Heroku did a good job making PostgreSQL popular as a jack of all trades solution, and a default one for many FOSS communities. The market just followed.

Also consider that some of the issues are due to how Rails and ActiveRecord expect things to be.

Per your source, though, "Nested sets are very slow for inserts because it requires updating left and right domain values for all records in the table after the insert."

The same must generally be true for any solution that maintains its own index in order to guarantee a single* query can traverse the graph. You're memoizing the traversal, after all. It's much simpler and faster if the DBMS can simply perform a traversal, and it's what you want.

And, again, you're maintaining a lot of unnecessary code for one system, which was their chief complaint.

* You could probably implement a scheme that doesn't require materializing all adjacencies so you strike a balance between the number of queries and number of updates, but that's a lot of engineering, and again the same basic complaint.

Image from: https://scalegrid.io/blog/2019-database-trends-sql-vs-nosql-... https://uploads.disquscdn.com/images/36de94d737212dfc0daad17...

i therefore approve of the way the announcement was made, because it brought more benefits to the readers.

sure, some readers will walk away with the impression that mysql is somehow limited, but i think that's a reasonable price to pay for everyone else who gets to learn from the ensuing discussions.

So they could be "right", but expressed the concept in an imprecise form.

In a way, I think this shows their lack of expertise with MySQL - I reckon that somebody with a long experience would have phrased the concept more precisely.

I think we wanted to make it easy to upgrade to 12 before making the switch.

I am not sure MySQL was to blame here :)

It is 100% legit to give that up, it is 100% legit to go with the clear winner in your mind (it is the same in my mind too), but the technical reasoning is weak IMHO.

if you wanted to support both, you can, but you clearly don't (and that's ok too).

There's no distinct advantage of MySQL over Postgres, either in smaller or larger environments for the gitlab use case and with their development team.

My personal experience is that Postgres is much closer to operating commercial RDBMSs which makes the experience of DBAs much more portable and relevant. Moving someone from the Oracle or MSSQL world to Postgres is relatively much easier than moving them from the MySQL world.