> 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.
I'll have to steal that one, great quote!
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.
Same for foreign keys and check constraints.
Queries are pretty intelligent, also, and mostly just work efficiently. You are right that some queries don't, and you have to know about the internals to make it do what you want. But here's what you are missing in that story: your data lives for a long time across many different applications. If you program everything yourself imperatively, you are bound to either over-engineer or under-engineer a lot of queries. The over-engineered pieces will look like premature optimization, clutter your code and tests, and be a time sink. The under-engineered pieces will come back to haunt you when your data grows and changes. Declarative relational systems actually do a pretty darn good job of handling this problem with a cost-based optimizer. Is it perfect? No, but it's an improvement in an awful lot of cases.
Also, you are ignoring places where databases do a good job with queries, like parallelization. It's easy to say that you'll just write all the queries imperatively yourself, but a lot of people don't want to spend the time to do the harder stuff even if it really matters for performance.
Because 99% of the time you don't notice that it just works. 99% of the time you don't have to bend your query or schema to make it work.
> If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing.
Thats very unspecific, but getting 5000 rows from a table should take a few milliseconds on modern hardware.
There is zero SQL code in our app above a very low level. All high-level code calls through a DB API, and all SQL code is behind that abstraction layer.
That said, each one of these transitions was a “pull up stakes and move” kind of thing. We never tried to support multiple back-ends at once. So on those grounds, I think this is probably a good move for GitLab.
Supporting multiple databases at this point is usually not economically feasible.
That's why I don't regret putting all of our SQL behind that DB layer: to port to the new DBMS, we just had to rewrite parts of that layer, rather than go on a hunt through the whole application for one-off SQL calls, as would probably occur if we didn't enforce this discipline.
It's not just about portability, or even primarily. It's simply good application layering to put the DB code behind a layer. Intermingling SQL code in your application logic is just as bad as intermingling GUI code in your core app logic.
But even that aside, we haven't felt the lack from putting such code a single function call layer away. I suppose it may give us a slight amount of pause when we consider whether it's worth creating another function in that helper library, but that's probably a good thing. If we decide we can get our work done by calling one of the existing functions, then why write yet another DB access function?
Example: if a table is known to have at most 5 rows, and the query that would allow the DB to return just the one we actually want is complicated, and it's almost never called, why not call the existing "SELECT all" one and iterate over the rows? The performance hit of being inefficient will be immeasurable.
> Why did you move databases so often?
That's once every 8 years on average. A lot changes in the computing world over that span.
The first change did coincidentally happen about 8 years after the company was founded, and it was because much better DBMS tech became available to us, within the constraints we had on choosing.
That DBMS lasted us for the next 14 years. By that time, enough had changed in the computing world that it was worth swapping again. The new one runs about twice as fast, takes far less code space, and is simpler to use all around than the prior one.
We'd have to switch again this year in order to maintain the mean of once every 8 years. My sense is that this is more likely a nonlinearly increasing growth curve, so that the next switch will be closer to 20 years than 14, if it happens at all.
What we're discussing isn't the SQL code, it's SQL syntax and features of a particular database over another. JSONB, partial indexing, text search, extensions, etc. in Postgres are not available in MySQL, and not using those features just to have the ability to switch or run on multiple databases is almost never worth the effort.
Simple CRUD apps with an ORM can probably do so, but otherwise most business apps shouldn't avoid using functionality for some "what-if" scenario. Having to switch databases after a decade is just a normal development item if it needs to happen, and usually coincides with heavy rewrites anyway, so I don't see much value in pre-planning for that at the architectural level.
I have also seen instances where the decoupling helped us abstract out differences between different versions of the same database product during upgrades.
If you don't need performance or responsiveness though you can save a lot of effort except in very simple cases. I've seen it shine in quickly built proof of concept applications and in ad-hoc analytics queries.
Whenever a viewer showed up to the website, we’d do a relatively complicated query against this database to figure out which server to direct them to for the channel they wanted to watch. The net result was a system that showed video sooner, with less latency, and more cheaply than any of our competitors were able to achieve. I don’t know what their setup looks like these days, but this basic setup took them from before the Twitch.tv rebrand all the way to their Amazon acquisition.
There was a lot of tuning that we had to do to make this work (like running off of a ramdisk, and having a pool of read-only clones), and it certainly had its hair-tearing-out moments, but in retrospect it was absolutely the right choice. We kept expecting it to fall over, but the plan to shard the database onto multiple servers collected dust for years because there was always a way to get the performance gains we needed through basic tuning.
I don't blame them, and despite supporters, mySQL is constantly the most inconsistent non-standard SQL database of them all. I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Much like Oracle, I have no intention of ever using mySQL unless managing the thing is something I never have to do.
I'd rather use MS-SQL or PostgreSQL than mySQL.
Regarding the historical defaults and consistency, they fixed that 4 years ago in MySQL 5.7. And last year's release of MySQL 8.0 added more modern SQL features (CTEs, window functions, etc).
There's still some non-standard idiosyncrasies, but that's true of every relational DBMS :) And while it's certainly possible that MySQL is arguably still more idiosyncratic than MS-SQL or Postgres, it's not like an order-of-magnitude difference anymore.
COBOL is also English like.
I'm not negative about SQL. I'd rather write queries in SQL than having to learn for the nth time how to write SQL in language X with ORM/module Y. I'm doing it with Django, Rails and Phoenix in the same week since a long time now and it feels a bit pointless. But then there is the deserialization in the data structures of the language. I just wish those libraries would be smarter and know how to handle some hypothetical code like
sql("select * from users").each |user| do
something(user)
end
They're most used in F#, which calls them type providers:
You're still expected ask for what you want specifically.
"I know how to express what I mean in such a way that I can target two very different platforms like JS and the JVM, why isn't there something as powerful for SQL?"
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.”
Which statements are incorrect? Someone from the team responded directly to all points above here https://news.ycombinator.com/item?id=20344575 If you want to debate the reasons why, maybe respond to that thread? I don't, but I assume they're competent and telling the truth. The claims here about the reasons being wrong and the team not being competent appear to be incorrect and emotionally based. I can understand people being upset about losing support for something they use, but the assumptions and accusations seem misplaced, and fwiw, two years late.
> It’s not clear why they had to publish a blog post in the first place
Why shouldn't they? What's the functional difference between a blog post and a FAQ anyway? I don't understand your complaint - are you asking why they published anything or asking why it's not on a different web page? Your critique, like the one above it, is still not making any case for why they should continue to support MySQL.
I would assume that having announced this move two years ago, they've had a few customers ask why. I would assume that a company that cares about it's users but needs to drop support for something some of them still use, they would take some time to elaborate on their reasons, precisely because they care.
Is this comment directed at the parent comment or the blog post itself? :)
> 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.
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).
> 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
Nobody supports mysql for scaling workloads. they support mysql purely to make it easier for their smaller users to setup and manage instances.
Every single time I've used mySQL, I come across some odd quirk from historical context. Limiting TEXT and creating LONGTEXT instead of increasing the bounds for TEXT. Keeping utf8 as some wierd non-final version of utf that isn't a spec, instead of aliasing it to the actual utf8 spec version in new configurations. Not sure if this is still an issue, not being able to create foreign key relationships with ansi-quoted table names (backticks only here). "BINARY" fields being collated as case invariant by default (led to some really bad bugs on a primary key field).
I can't think of a single time I've used mysql and didn't get odd behavior. I can't think of a single time I've used PostgreSQL, or even MS-SQL that I did. Oracle and DB2 have had some oddities as well, but at least consistent, unlike mysql.
With the huge rise in ready to run container images and managed database services, database ops is no longer a serious problem for small users and multiple DB choices shouldn't really be a major design consideration for software providers.
Have you read "Thou Shalt Not Run a Database Inside of a Container"? See https://patrobinson.github.io/2016/11/07/thou-shalt-not-run-...
This is a great setup and effectively provides a database compute "shell" that sits on top of persistent block storage so backups and upgrades are simple to run. Add in orchestration like Kubernetes and you can also run replicas easily.
That's not a real reason to support mysql, which is no more pleasant for non-DBAs to set up than any other traditional RDBMS. Rather, that's a reason to support sqlite. (See also, the reason sqlite is the default DB in Rails.)
Sqlite is also an inappropriate database engine for any system that requires concurrent read/write access for multiple processes. While most people probably think they can work around this most people forget things like event logging and last checked timestamps which is a requirement for production applications.
I also want to mention that I did not read it in the comments here but I feel like MySQL and MariaDB still haven't settled their differences while postgres (to me) feels more stable. Then you also have the feature compatible DBaaS like AWS RDS. Also knowing you have PL/perl or PL/python in the back pocket seems hard to give up.
To be fair, these days it might not be easier, for me user management was easier to understand.
Though these days I'm looking at Postgres and seeing only advantages: 1. Compliant 2. Not split into a billion versions 3. JSON
† BLOBs are still relevant in the cases where you have such huge data in each BLOB that you need to work with it in a streaming manner. But people don’t tend to build systems with this concern (i.e. object stores) on top of RDBMSes, so it’s pretty darn rare that anyone actually turns to Postgres’s BLOBs to solve a problem in practice.
A notable example is that before version 8, internal temporary tables didn't support BLOBs when in-memory¹, so that they would swap to disk, causing a performance penalty.
Regarding the length, an important concept is that it can be used for optimizations.
InnoDB adds a certain number of bytes for each value stored, depending on the size. For example, a TINYTEXT consumes 3 bytes less than LONGTEXT for the same value.
Another difference is the location of the data. TEXT/BLOBs up to 40 bytes long will be stored in line, while the limit is 768 bytes for VARCHARs. A 100 bytes long TEXT will require one page access more than the corresponding VARCHAR.
Of course, it's up to the engineer to choose whether to consider optimizations like this or not, but as a matter of fact, those differences exist.
¹=note that this concept is different from tables backed by the `MEMORY` engine, and different from user temporary tables.
In my experience, "all you have to do is..." is always followed by something heinous.
Epilogue: it was a Pyrrhic victory. He wrote absolutely terrible code which was difficult to read, impossible to maintain, had no tests or docs, and ignored all coding standards. Every task looked easy to him because all it needed was a drive-by hack to make it look good for a quick demo on Friday, and he wasn't going to be the one to fix the inevitable bugs found in it next week.
>> 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.
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.
The pronunciation can be quite close
/fækˈtɪʃəs/
/fəˈsiːʃəs/
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...
Sounds like a good reason to drop MySQL.
In PostgreSQL, TEXT/VARCHAR (same type) is only stored outside the tuple if it's large.
I wrote the details in another comment. You can find references here:
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.ht...
and here:
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements...
"Bad performance" is not exact. I'd say "worse" performance, in the cases where TEXTs cause extra page accesses. The distinction is important because in some cases, a performance that is "negligibly worse" can still be "good" - as in general, in software engineering :-)
I still feel dirty using TEXT though.
In the days of spinny-disks this was a pretty huge penalty, you couldn't do linear reads to get the data, the heads would have to veer over to that other sector, read a bit of data, then skip back to read the next row. That was especially punishing as no amount of disk cache could help buffer against these reads that, to the operating system, seemed completely random.
MySQL moved the first X bytes of this data into the table row structure a while back (5.6? 5.7?) for performance reasons. It will only skip to the blob-data storage area if the data is longer than can fit in the size put in-row.
If you really cared you'd do your own homework.
> 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.
Also nicely easy to back-populate after you add the column using a CTE, if you didn't have it originally.
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.
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.
Given the prevalance of that attitude, I'm a little surprised to see that the general tone of responses here is "This wouldn't have been a problem if they'd just hired people who knew what they were doing." The point is, they didn't. They hired devops engineers who knew enough to solve common problems, in line with the conventional wisdom here. Their stated reasons for ending MySQL support are perfectly in line with the view that specialists are a waste of money. Indeed, I'm not sure what other outcome should have been expected.
For example, a DevOps engineer solves “we need to do search queries” by standing up an ElasticSearch instance and writing code to ingest data from the DBMS into it. A DBA solves the problem by using the search-enabling features of the existing DBMS, and suggesting constraints on the way search is exposed at the business layer that make those queries easier on the DB.
Both approaches “work”, in the sense that you can do either and have a profitable company.
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.
To be fair, the nested set pattern is the opposite of fun - especially at scale.
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.
Image from: https://scalegrid.io/blog/2019-database-trends-sql-vs-nosql-... https://uploads.disquscdn.com/images/36de94d737212dfc0daad17...
> 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...
https://www.merriam-webster.com/words-at-play/specious-vs-sp...
https://dev.mysql.com/doc/refman/8.0/en/with.html#common-tab...
(I'm on the MySQL team)
According to Wikipedia, MySQL 8 was released about a year later, in April 2018 (there was also “MySQL Server 8.0.0-dmr (Milestone Release)”, in September 2016, but scanning https://lists.mysql.com/announce/1106 and the two other parts, that didn’t seem to have CTE’s)
Apparently they don't need the support from their pov at all. If will is there they could build up MySQL 8. MySQL 8 is out for a while and ahs seen good testing in different production workloads.
> developers already wanted to drop MySQL and just wanted an excuse
This seems to be the true reason. And it's valid. Supporting many variations of systems and architectures is hard. Supporting combinations you don't use yourself, while using something else intensively is hard.
Personally I think a system like this would benefit from many of the recent replication improvements in MySQL 8, which help in scaling and HA. But I'm obviously biased ;)
We do indeed have SRID support, but there are still some functions that only support Cartesian spatial reference systems. Is that what you're thinking about? I'm curious to know which functions you're trying to use that don't support your use case.
We are working to close these holes. In many cases it involves extending Boost Geometry (the library that we use) with more geographical computations, and that is hard and time consuming work. But we're doing it. The work we have to do to support it in MySQL once it's in Boost Geometry is pretty simple and straight forward.
Since extending Boost Geometry is such a time consuming task, people not working on those tasks have time to extend MySQL in other directions, maybe giving the impression that we're avoiding adding the missing parts. But it's just a result of some task being simple and fast to implement, while others (especially adding geography support) are more long running. In any case, the result is an uneven trickle of new functionality in more or less every new version of MySQL.
I hope that explains a bit what you're seeing. We are filling in the blanks, but it takes time to get to 100%.
Geographic ST_Distance is currently limited to points and multipoints because of limitations in Boost Geometry, but we've got that fixed in Boost now, so full type support should soon arrive also in MySQL.
Norvald has a few slide sets, which might be good addition to docs: https://www.slideshare.net/mobile/NorvaldRyeng There are also blogs on https://mysqlserverteam.com/category/gis/
Without details about your experiments it's hard to find the issue ...
You could use an association matrix.
Or cached paths, but that has a list of disadvantages that make me consider it an anti-pattern these days (I maintain a legacy system that uses this model a lot).
> without blowing up the cost of writes?
It is a long time since I went over the relative performance of common operations on these structures, so I'll not try compare them (some research for you, dear reader!).
The performance of updates to an association matrix depend largely on the depth of the forest: when adding a new node you insert one row per level it is away from its trees root. Removing or moving a leaf node is the same amount of deletes or updates respectively. Moving a branch node can be pretty expensive as you have to make changes to the matrix for all the nodes under it.
> There are lots of great use cases for MySQL, our specific needs just didn't seem to be a good fit. Our use of MySQL had a number of limitations. In most cases, it wasn't as simple as adding support to MySQL, but that by bending MySQL we typically broke PostgreSQL. To name a few limitations:
> We can't support nested groups with MySQL in a performant way
All they had to do was implement a nested set pattern for their groups [1]
> We have to use hacks to increase limits on columns and this can lead to MySQL refusing to store data
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.
> MySQL can't add TEXT type column without length specified
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.
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!
> MySQL doesn't support partial indexes
This is true, but is it really a show stopper?
> 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.
[1] https://en.wikipedia.org/wiki/Nested_set_model