17 ms·

Why we're ending support for MySQL

397 points by bjoko 7y ago

deftnerd 7y ago
I mean, I'm not too upset that they're focusing on one DB engine, but their reasons are a bit facetious. > 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 https://en.wikipedia.org/wiki/Nested_set_model
- Spivak 7y ago
  Yeah, I think it's funny that they're claiming that MySQL can't represent a tree structure performantly. What they should have said instead is that their db structure assumes the existence of WITH RECURSIVE and they don't want to change it.
  - johannes1234321 7y ago
    MySQL supports WITH RECURSIVE. https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive https://dev.mysql.com/doc/refman/8.0/en/with.html#common-tab... (I'm on the MySQL team)
    - mutt2016 7y ago
      I miss the OG team, pre Sun acquisition. Not sure if you were a part of it, but a solid group to be sure.
      - johannes1234321 7y ago
        I joined October before the Sun deal was announced
    - Spivak 7y ago
      True, but it's an 8.0+ feature. It looks like from the thread that they needed to support older versions as well. Seems odd to drop all of MySQL rather than have a min. version. The more you pry in the thread the more it seems like their developers already wanted to drop MySQL and just wanted an excuse.
      - johannes1234321 7y ago
        > they needed to support older versions as well. 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 ;)
        7y ago
- koolba 7y ago
  >> MySQL doesn't support partial indexes > 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.
  - Roritharr 7y ago
    > 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!
    - oswamano 7y ago
      I'm stealing this as well
    - wale 7y ago
      There’s no way I’m not stealing it, too.
- ktpsns 7y ago
  I understand https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage in a way that Postgres encourages users always to stick to the text type. Which is, from a non-DB-centric view, certainly the most convenient thing to do: Tell the database just to store a string, don't worry about length or so. Having worked with MySQL a lot in the past, I wonder if this is a kind of paradigm shift, whether PostgreSQL tries to be programmer-friendly or whether this is just the way how to do SQL nowadays...?
  - pizza234 7y ago
    There have always been low-level differences in the way MySQL treats the two (VARCHAR <> TEXT/BLOB) data types, although some have been removed over the time. 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.
  - derefr 7y ago
    Postgres built up a whole separate infrastructure for BLOB storage, with a streaming API in/out of BLOBs et al; but after landing the TOAST-table logic, it became basically† obsolete—you can just use a regular TEXT or BYTEA column to achieve the same things now. † 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.
- necovek 7y ago
  >> MySQL doesn't support partial indexes > 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.
- dtech 7y ago
  I'm not a MySQL expert, but I've been told that TEXT has very different storage and performance characteristics than VARCHAR, because it is always stored outside of the database tuple and that TEXT can have bad performance. I can imagine that is a reason for them not wanting to use TEXT variants in MySQL. In PostgreSQL, TEXT/VARCHAR (same type) is only stored outside the tuple if it's large.
  - pizza234 7y ago
    It's a bit more complicated :-) I wrote the details in another comment. You can find references here: https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-dynamic 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.html#data-types-storage-reqs-strings 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 :-)
    - astrodust 7y ago
      This change wasn't widely advertised and is a radical change from earlier versions of MySQL where they were pretty awful in terms of performance. I still feel dirty using TEXT though.
      - pizza234 7y ago
        Can you add some details about which change, the performance loss you describe as awful (and the use case(s)), and the version where it happened?
        astrodust 7y ago
        It used to be that TEXT/BLOB data was stored outside of the table row in a separate area of the database. Any retrieval of this involved scanning to a separate spot on the disk. 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.
- namibj 7y ago
  Partial indexes allow you to do stuff like keep session tokens for some longer time to facilitate logging of attempted use of expired credentials, without significantly hurting your authentication-validation performance. In most cases you will not hit the disk, as the partial index is in memory. 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...
- sterkekoffie 7y ago
  Excellent points--if I may, I think you meant "specious," not "facetious."
  - js2 7y ago
    If one disagrees with an argument because it's superficial, then one might call it "specious." If one disagrees because the argument is invalid, then the word is "spurious." I think based on the counter argument, "spurious" is what was wanted. https://www.merriam-webster.com/words-at-play/specious-vs-spurious-usage-guide https://www.merriam-webster.com/words-at-play/specious-vs-sp...
    - sterkekoffie 7y ago
      I think either would be appropriate but I suggested specious because it rhymes with facetious and I often mix up rhyming words myself.
- YorickPeterse 7y ago
  > All they had to do was implement a nested set pattern for their groups The nested set pattern was considered at the time we added support for nested groups, or improved it with CTEs (not sure which one of the two it was). The biggest drawback of nested sets is that adding sub groups can now become expensive. The storage needs are also far from ideal. 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. It's not ignorance, it's MySQL coming up with bizarre limits for the "TEXT" type. In MySQL, the limit for TEXT is 64 KB. In PostgreSQL, IIRC it is 1 GB. Looking back there may have been better decisions, but it's always easy to judge in hindsight. 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! At the cost of requiring more storage space, and writes taking (potentially) much longer. You may want to mention that, instead of acting as if nested sets are a silver bullet. > This is true, but is it really a show stopper? Yes. > Wrong schema specifications and not knowing to implement nested set > patterns is a sign that they don't have a knowledgeable DBA on staff. You may want to do some more research before going down the path of suggesting GitLab employees lack knowledge. For the last two years or so we've had various engineers with excellent database knowledge working on GitLab, myself included (though I don't consider myself a PostgreSQL expert). 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 https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/10885
  - MrStonedOne 7y ago
    >> This is true, but is it really a show stopper? >Yes. No, its not. Not everybody has to run gitlab in the same production environment that gitlab.com does. it is 100% not a show stopper. Please stop being fictitious. I could pop the last mysql gitlab version on my ARM rock64 single board computer and get by running it as a 300 page views a month internal git server for personal projects. It will work fine, with or without partial indexes. So. Again. It is 100% not a show stopper. Stop fucking lying. These technical issues are not why you want to drop mysql
- jrochkind1 7y ago
  Eh, maybe all of those things CAN be done in MySQL, but they are done differently. I think it's totally legit that it takes a lot more development hours to support two DBs than one. 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).
- Someone1234 7y ago
  > I mean, I'm not too upset that they're focusing on one DB engine, but their reasons are a bit facetious. 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.
  - munk-a 7y ago
    As a bostonian I'd mention that facetious has a gained meaning in that area of "misleading with malintent" I believe this meaning is pretty wide spread over the east coast in general but it might also be an artifact of word collision due to the local accent. The pronunciation can be quite close /fækˈtɪʃəs/ /fəˈsiːʃəs/
    - Izkata 7y ago
      Over in the midwest, I thought that simply was its meaning.
      - deleted 7y ago
        [deleted]
  - Noumenon72 7y ago
    Thanks for the clear statement of the distinction! I only knew "facetious" (and always with the sense you state), so my vocabulary is bigger now.
- oblio 7y ago
  > Wrong schema specifications and not knowing to implement nested set patterns is a sign that they don't have a knowledgeable DBA on staff. 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.
  - hotsauceror 7y ago
    This seems to be a pretty common attitude here on HN, that specialists like DBAs are a waste of money. Just a couple of days ago someone posted (only slightly paraphrased) "it's a waste of money to hire skilled DBAs. You should hire DevOps engineers who can solve the most common problems with your database. DBAs have outlived their usefulness." 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.
    - LIV2 7y ago
      Having a DBA certainly would've helped them when they accidentally deleted their prod database with no backups too, that probably wouldn't have happened if they had someone who knew how to properly manage DBs
    - derefr 7y ago
      It’s two different approaches to the same problem. Given the problem “the business layer wants to do X, and that’s inefficient in storage layer Y”, DevOps engineers solve the problem by adding another storage-layer component Z that follows a paradigm where the queries from X are “automatically” efficient; while DBAs solve the problem by informing the design of the business layer to make queries that are optimal given the storage layer in use. 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.
- dahart 7y ago
  If the real reason is they don’t want it, who cares what the other reasons are? 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 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.”
  - MrStonedOne 7y ago
    Gitlab lied, parent was calling out the lies. Everybody should call out bad arguments, regardless of rather or not those arguments have a impact in the end. >who cares what the other reasons are? People who don't like liars. >In your critique of their argument, I don’t see a single reason why they should use MySQL. 1: This isn't even about rather or not they should use mysql. This is strictly about rather or not they should support mysql. I am not sure why you are bringing up using mysql. 2: Parent was never arguing in favor of mysql, their entire comment is strictly about how the arguments put out by gitlab were lies. I am not sure why you are bringing this up at all. >I admit I have a sensitivity to knee-jerk “can’t you just” comments You should work on that.
  - nkozyra 7y ago
    > If the real reason is they don’t want it, who cares what the other reasons are? Is this comment directed at the parent comment or the blog post itself? :)
    - dahart 7y ago
      The parent comment. I'm referring to "I think this is the real reason." but I could have quoted that to make it more clear. And to be clear, they have more reasons, I'm asking why they need more reasons.
- enobrev 7y ago
  > All they had to do was implement a nested set pattern for their groups To be fair, the nested set pattern is the opposite of fun - especially at scale.
- kstrauser 7y ago
  > All they had to do was implement a nested set pattern for their groups In my experience, "all you have to do is..." is always followed by something heinous.
  - ken 7y ago
    My manager used to do that so much with outrageous over-simplification that I instituted a rule. If, in any budget/estimation/planning meeting, anyone said "all you have to do is ..." or "it's just ...", they were volunteering for that task to be assigned to them. 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.
    - justinclift 7y ago
      All you have to do is tell your manager their code will only be accepted once it meets the team standards. ;)
      - tremon 7y ago
        ...at which point such managers will usually point out that they are the team standards.
- perlgeek 7y ago
  > > 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] 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.
  - mst 7y ago
    I've always found materialized path does me proud for this sort of use case. Also nicely easy to back-populate after you add the column using a CTE, if you didn't have it originally.
    - dspillett 7y ago
      Cached paths is usually pretty cheap for adding new nodes, but it can be quite expensive if you need to be able to move non-leaf nodes around (caveat: I've not looked at their use-cases here, I don't know if moving non-leaf nodes is an important matter) especially if the forest is deep rather than (or as well as) wide.
- dspillett 7y ago
  > > 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 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...
- dragonwriter 7y ago
  > All they had to do was implement a nested set pattern for their groups 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.
- ken 7y ago
  > All they had to do was implement a nested set pattern for their groups 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.
  - jayd16 7y ago
    You're conflating the nitty gritty of schema design (which I agree with you is not that declarative) with the usual case of "I know what I mean why doesn't the machine know what I mean" which is different. You're still expected ask for what you want specifically.
    - ses1984 7y ago
      > It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries. "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?"
- oarabbus_ 7y ago
  Look up the TEXT specification in Postgres. None of these problems exist with postgres. Sorry, but MySQL just kind of sucks in comparison.
- nullwasamistake 7y ago
  If they want multi-db support they should really be using an ORM. Hibernate for example is completely DB agnostic as long as you don't get too fancy.
- stonogo 7y ago
  "All they had to do was memorize all of MySQL's weird engineering quirks!" Sounds like a good reason to drop MySQL.
- mortonpe 7y ago
  > 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. 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 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.
  - brodock 7y ago
    Only around 0.25% of paid customers: https://gitlab.com/gitlab-org/gitlab-ce/issues/52442#rationale https://gitlab.com/gitlab-org/gitlab-ce/issues/52442#rationa...
- brodock 7y ago
  When GitLab started supporting MySQL officially (on the paid version), there was a market reason to that. Big corporations used to by expensive Oracle support licenses. 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.
- ben509 7y ago
  > All they had to do was implement a nested set pattern for their groups [1] 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.
- sburkett 7y ago
  Both are great platforms, but ... how many of those customers who reported running on PostgreSQL did so because they didn't know they could use MySQL, or just took the defaults? Image from: https://scalegrid.io/blog/2019-database-trends-sql-vs-nosql-top-databases-single-vs-multiple-database-use/ https://scalegrid.io/blog/2019-database-trends-sql-vs-nosql-... https://uploads.disquscdn.com/images/36de94d737212dfc0daad179f63f0c43def301811941738e85446299b93a63e0.png https://uploads.disquscdn.com/images/36de94d737212dfc0daad17...
deleted 7y ago
[deleted]
noneeeed 7y ago
All their more general points in the post are perfectly valid arguments for focusing on a single database that I've heard from other projects, but naming specific technical issues like that actually makes it seam more nit-picky and less valid. I'm not sure many people would have found anything to argue with them about if they had simply said "Tagetting multiple DBs requires more resources to develop and test, limits our ability to make the most of what Postgres offers, and increases the cost of support. The proportion of users using MySQL is decreasing, so we have decided to focus on Postgres to give the maximum benefit to the largest group. We understand this may cause issues for some users so here is a comprehensive migration guide/toolset".
- em-bee 7y ago
  right, but by naming all the issues they had, they also opened up the discussion for learning how to solve those problems as the posts here demonstrate. 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.
avitzurel 7y ago
IMHO, this comes down to one thing. They lack the talent (and/or the will/resources to recruit it) to operate MySQL. I can TOTALLY get the reasoning behind supporting only a single engine but the reasoning they write there are either wrong or misguiding.
- hmmIsee 7y ago
  This comment is a baseless accusation if you do not provide more information on WHY you think the same.
- avitzurel 7y ago
  I realize my comment hit a sensitive spot but it's really not meant to be negative. 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).
  - benatkin 7y ago
    Or they want to use that talent elsewhere. It's great that the simplicity argument doesn't really apply. It's a testament to developers getting better at programming in the large over the years. The database layer can be neatly abstracted away from the business rules.
mutt2016 7y ago
Unpopular opinion, and anecdotal data point: I stopped using Solaris and MySQL when oracle took ownership. Linux and postgres is my new standard.
- petepete 7y ago
  I'm not sure that's an unpopular opinion in these parts.
  - mritchie712 7y ago
    yeah, I'd drop "unpopular opinion" along with "hot take", just speak your mind bud.
  - chessturk 7y ago
    > these parts. The keywords here. Even though every dev on my team knows postgres equally or better than mysql, management chose mysql. I'm one of the equally camp, and don't possess a strong personal preference, but I was surprised that it was the favorite of the devs and not trusted by the business.
    - petepete 7y ago
      If it makes you feel any better, in the GOV.UK department I'm contracting in, just about every project is using PostgreSQL. It might take some time but the tide is flowing in PostgreSQL's direction.
- yjftsjthsd-h 7y ago
  ... where is that an unpopular opinion? Really crusty enterprise?
  - jniedrauer 7y ago
    I was even able to sell my last BigCorp™ on using MariaDB instead of Oracle MySQL
  - 7y ago
nickcw 7y ago
> MySQL can't add TEXT type column without length specified Mysql does have a TEXT type with a max limit of 65536 chars (you can have MEDIUMTEXT or LONGTEXT if you want more). You don't supply a length when you use TEXT https://dev.mysql.com/doc/refman/8.0/en/blob.html https://dev.mysql.com/doc/refman/8.0/en/blob.html
- pizza234 7y ago
  Their statement is somewhat ambiguous. They may have tried to express that "you can't add a column of the TEXT class without specifying the exact type". 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.
  - striking 7y ago
    It doesn't matter, because they're looking for a different thing entirely. PostgreSQL TEXT is just without a limit. Simple as that.
    - sbov 7y ago
      Not true, from: https://www.postgresql.org/docs/current/datatype-character.html https://www.postgresql.org/docs/current/datatype-character.h... > In any case, the longest possible character string that can be stored is about 1 GB.
    - michelpp 7y ago
      TEXT, like all Postgres varlena types, is limited to 1GB. Which for many people is effectively without limit.
xvilka 7y ago
Next step would be to rewrite everything from Ruby to Go (they already did for some parts of GitLab infrastructure).
- hu3 7y ago
  I wonder if Ruby is the reason why it requires so much memory.
- rswail 7y ago
  All of the MySQL "they just shoulda" and "they just coulda" arguments here are irrelevant. 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.
  - xvilka 7y ago
    It wasn't sarcasm. I totally support their decision to drop MySQL. I prefer PostgreSQL in my projects too, especially with JSON/JSONB queries, it is amazing.
xenator 7y ago
I think here can be a good place to share my story why I never use MySQL in my projects. Once I was working for the company where I was an architect for some new project. And we have few gigabytes of data from a few sources. But with plans to gather much more in the near future. I came up with some database structure for the first draft of the early stage of development. It was nether big nether complicated for my scale comparing to the other projects I was working before. Since I'm python expert I choose SQLAlchemy for ORM layer and use its features to create the initial database schema. Postgres was a natural choice for me and almost fit to my requirements. I was worried about performance in the future but decided that we always can optimize when we have something. As I said it was early stage and we were on the research stage. Everything was smooth development-wise but the database was pretty slow since we most use desktop-grade computers for prototyping. 3 weeks before the first internal release owners ask me to change the database to MySQL. I was against this step, we don't have time or resources for the experiments. But point was that company have MySQL experts that have more than 10 years of the optimization experience. I simply have no choice. They force me to do this against my will. Since I wasn't sure that in production we will use Postgres I don't allow to use any special database dialect specific capabilities for the developers. And switching to another backend from Postgres to the MySQL was literally equal to a connection string change. MySQL wasn't ready to handle 40-60 tables and queries with 5-10 simple joins. On our data, every request just hang server (they quickly provided a server with a lot of memory and storage for this). A few days later we found that actually MySQL is working but the same simple queries run for 4 or more hours. So-called "experts with 10 years of optimization" spent a week trying to fix indexes and other things but it never happened. After release, I left the company, because of the toxic atmosphere, but it is another story. But since that, I lost all my faith in MySQL. Maybe for others, it is an option but never for me.
- guggle 7y ago
  It baffles me when people treat RDBMS as swappable dumb datastore.
  - y4mi 7y ago
    It can be, if you're using an orm which abstracts everything sql away. You'll lose a lot of functionality and performance however. This might be an acceptable trade-off in some cases.
lifeisstillgood 7y ago
They just decided to use boring technology (http://boringtechnology.club/ http://boringtechnology.club/). Given they have limited resource spreading it across two databases seems a miss, and taking that time and attention and putting it back into git-ty stuff looks like a win.
- mattbessey 7y ago
  Does this really count as using boring technology? they're choosing the younger of the two DBs and to use some of the latest features e.g lateral joins
  - dijit 7y ago
    I think when the difference in age is less than 2% of the age of the project then you can’t claim one to be more trendy based on age. Postgres Initial release: 8 July 1996; 23 years ago MySQL Initial release 23 May 1995; 24 years ago 1yr2mo difference. Not exactly earth shattering for a pair of projects two and a half decades old.
    - mrami 7y ago
      Funny, since you mention Postgres: 1996 is only PostgreSQL's first release. POSTGRES goes back even further [0]: > POSTGRES has undergone several major releases since then. The first "demoware" system became operational in 1987 and was shown at the 1988 ACM-SIGMOD Conference. Version 1, described in The implementation of POSTGRES , was released to a few external users in June 1989. In response to a critique of the first rule system ( A commentary on the POSTGRES rules system ), the rule system was redesigned ( On Rules, Procedures, Caching and Views in Database Systems ), and Version 2 was released in June 1990 with the new rule system. Version 3 appeared in 1991 and added support for multiple storage managers, an improved query executor, and a rewritten rule system. For the most part, subsequent releases until Postgres95 (see below) focused on portability and reliability. Your point stands, nonetheless. There are no whippersnappers present. [0] https://www.postgresql.org/docs/9.3/history.html https://www.postgresql.org/docs/9.3/history.html
    - 7y ago
rhacker 7y ago
I remember like 10 years ago it was all the rage to support 45 different databases. I'm so glad that time has passed. It wasn't feasible. Yes you can orm the crap out of things so you never see a line of SQL but you end up with index tuning on multiple database platforms. I noticed the trend thankfully just sort of walked itself away about the time non-traditional databases entered the picture and well it was pretty much impossible to support Mongo and Mysql (reasonably). I'm assuming more things are going back to Mysql/Postgres/Sybase/DB2 again, but I hope more companies and OSS projects stick to just one this time? The bottom line argument is this: When you make the decision to support multiple databases think of the 10-20 years of changes your product will exist through (and possibly a lot longer) and realize how much extra work that is going to be even if you use ORM. Then if you come to the decision Gitlab is - are you going to want to write the porting software for your users or just let them be shit-out-of-luck?
shanemhansen 7y ago
MySQL is a decent nosql option with mature replication. If you care about your data, expect MySQL to be your foe rather than your friend. Do you expect your triggers to fire? You are SOL https://bugs.mysql.com/bug.php?id=11472 https://bugs.mysql.com/bug.php?id=11472 Do you expect to be able to insert utf8 into utf8 columns? Don't be naive. Read the docs and you'll discover utf8mb4 or as I like to refer to it: mysql_real_utf8. How about transactions? Surely a real SQL database supports transactions right? Or at the very least if it doesn't it would complain loudly when you use a transaction where not supported right? Once again this behavior is helpfully documented https://dev.mysql.com/doc/refman/8.0/en/commit.html https://dev.mysql.com/doc/refman/8.0/en/commit.html Do you expect to be able to put a value into MySQL and get that same value back out? You are SOL. But it's documented. I can honestly say that the only appropriate time to use MySQL is when you can't use postgres and you are willing to move from a RDBMS to something that requires significantly more work to prevent data corruption. I respect the hell out of MySQL engineers in terms of raw performance and the engineering of Innodb. I'm sure they aren't happy there's a million broken applications relying on defaults they can't change. I'm sure they aren't happy that fundamental limits in how pluggable storage engines work make ACID transactions not possible in the general case. Postgres, for all of it's issues, feels like a database that has your back.
- stingraycharles 7y ago
  This seems overly negative and a lot of FUD. Some of these issues are minor, and/or when aware of it are easy to work around. MySQL is much more than a "nosql with mature replication".
  - tracker1 7y ago
    The UTF8 thing is NOT fud.. the fact that "utf8" still doesn't default to REAL utf8 across major version changes is ridiculous.
    - datasage 7y ago
      utf8mb4 is the default in mysql 8.
aeyes 7y ago
Why is such a major change implemented in a minor version?
- sytse 7y ago
  It was announced in the major version of 12.0 I think we wanted to make it easy to upgrade to 12 before making the switch.
  - valtism 7y ago
    That isn't how semver is supposed to work though.
- silverwind 7y ago
  Probably because marketing drives their versioning (e.g. major versions are reserved to shiny new features), not semver.
bdcravens 7y ago
Doesn’t removing support for something violate breadth over depth? https://about.gitlab.com/company/strategy/#breadth-over-depth https://about.gitlab.com/company/strategy/#breadth-over-dept...
- perlgeek 7y ago
  I think that motto refers to features more than underlying technologies.
  - sytse 7y ago
    Correct, see our see our ‘boring solutions’ subvalue https://about.gitlab.com/handbook/values/#boring-solutions https://about.gitlab.com/handbook/values/#boring-solutions
  - bdcravens 7y ago
    I agree, but if you’re a MySQL shop that’s a feature.
    - mgbmtl 7y ago
      I guess I'm in a MySQL shop, but when it comes to Gitlab, I just want it to run its regular upgrades without me having to do anything (and it does! I love Gitlab). I never considered running Gitlab on MySQL. We rarely have to fiddle in the database itself (it may have happened once or twice during our migration from Redmine), for the rest we use the Gitlab API. If you are at the level where you need strong control on your Gitlab install, then you're a Gitlab shop, and you should either use the recommended dependencies, or assume that you will be helping maintain the niche features.
deleted 7y ago
[deleted]
oarabbus_ 7y ago
Good, PostgreSQL is superior to MySQL in almost every way.
- HatchedLake721 7y ago
  Are you sure? https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/
  - dahdum 7y ago
    They are using mysql innodb as a NoSQL store with fast replication instead of as a conventional database. They even call it "Schemaless".
  - StreamBright 7y ago
    Yes it is sure. Uber migrates between Postgres and MySQL every two years.
  - oarabbus_ 7y ago
    Absolutely sure. MySQL is nice for engineers who want to make analysts' lives miserable.
msla 7y ago
OK, but do they support MariaDB?
js4ever 7y ago
Just look how slow gitlab is on a 8cpu with 32gb or ram instance ... It's crazy ! I'm not sure about which part is because of Rails or the way gitlab is implemented. Issue is not about MySQL but more about the lack of knowledge / talent at gitlab
ShakataGaNai 7y ago
Good for them. Both MySQL and PostgreSQL are Good^. But supporting both doesn't work and it totally makes sense to focus on one. Personally, I don't generally use PGSQL but my GitLab instance does. Why? It's easy to run a dedicated PGSQL instance for GitLab using Docker. Or... Using AWS RDS. Or... Any number of ways. (^ Good is relative. People have their opinions on which is subjectively better but at the end of the day, lots of big sites and applications use both options to great success.)
- why-el 7y ago
  It also creates huge discrepancy between what a developer using an ORM like ActiveRecord writes and how the engines end up optimizing, meaning that should you want to write a performant one, you have to learn both, or, short of that, notify your DBAs of what you did, and in both cases huge resources are wasted. I never understood combining both from a business perspective anyway.
tbarbugli 7y ago
If you never used or heard of LATERAL [JOIN] I suggest checking that out because it is pretty awesome and handy. https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL https://www.postgresql.org/docs/current/queries-table-expres...
just_myles 7y ago
I have used postgres for so long that a lot of the baked in functions and features that were super convenient for me. I recall wanting to change a data type in my query and having to actually use the cast function. Also it was mentioned but when I was using it CTEs were not implemented yet. However, they weren't with postgres until 9.x as I recall either. Regarding performance, I mean, that is as much design as it is rdbms choice. Also i'm no DBA.
deleted 7y ago
[deleted]

397 points by bjoko 7y ago

I mean, I'm not too upset that they're focusing on one DB engine, but their reasons are a bit facetious. > 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 https://en.wikipedia.org/wiki/Nested_set_model

Spivak 7y ago
Yeah, I think it's funny that they're claiming that MySQL can't represent a tree structure performantly. What they should have said instead is that their db structure assumes the existence of WITH RECURSIVE and they don't want to change it.
- johannes1234321 7y ago
  MySQL supports WITH RECURSIVE. https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive https://dev.mysql.com/doc/refman/8.0/en/with.html#common-tab... (I'm on the MySQL team)
  - mutt2016 7y ago
    I miss the OG team, pre Sun acquisition. Not sure if you were a part of it, but a solid group to be sure.
    - johannes1234321 7y ago
      I joined October before the Sun deal was announced
  - Spivak 7y ago
    True, but it's an 8.0+ feature. It looks like from the thread that they needed to support older versions as well. Seems odd to drop all of MySQL rather than have a min. version. The more you pry in the thread the more it seems like their developers already wanted to drop MySQL and just wanted an excuse.
    - johannes1234321 7y ago
      > they needed to support older versions as well. 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 ;)
      - 7y ago
koolba 7y ago
>> MySQL doesn't support partial indexes > 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.
- Roritharr 7y ago
  > 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!
  - oswamano 7y ago
    I'm stealing this as well
  - wale 7y ago
    There’s no way I’m not stealing it, too.
ktpsns 7y ago
I understand https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage https://wiki.postgresql.org/wiki/Don't_Do_This#Text_storage in a way that Postgres encourages users always to stick to the text type. Which is, from a non-DB-centric view, certainly the most convenient thing to do: Tell the database just to store a string, don't worry about length or so. Having worked with MySQL a lot in the past, I wonder if this is a kind of paradigm shift, whether PostgreSQL tries to be programmer-friendly or whether this is just the way how to do SQL nowadays...?
- pizza234 7y ago
  There have always been low-level differences in the way MySQL treats the two (VARCHAR <> TEXT/BLOB) data types, although some have been removed over the time. 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.
- derefr 7y ago
  Postgres built up a whole separate infrastructure for BLOB storage, with a streaming API in/out of BLOBs et al; but after landing the TOAST-table logic, it became basically† obsolete—you can just use a regular TEXT or BYTEA column to achieve the same things now. † 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.
necovek 7y ago
>> MySQL doesn't support partial indexes > 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.
dtech 7y ago
I'm not a MySQL expert, but I've been told that TEXT has very different storage and performance characteristics than VARCHAR, because it is always stored outside of the database tuple and that TEXT can have bad performance. I can imagine that is a reason for them not wanting to use TEXT variants in MySQL. In PostgreSQL, TEXT/VARCHAR (same type) is only stored outside the tuple if it's large.
- pizza234 7y ago
  It's a bit more complicated :-) I wrote the details in another comment. You can find references here: https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-dynamic 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.html#data-types-storage-reqs-strings 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 :-)
  - astrodust 7y ago
    This change wasn't widely advertised and is a radical change from earlier versions of MySQL where they were pretty awful in terms of performance. I still feel dirty using TEXT though.
    - pizza234 7y ago
      Can you add some details about which change, the performance loss you describe as awful (and the use case(s)), and the version where it happened?
      - astrodust 7y ago
        It used to be that TEXT/BLOB data was stored outside of the table row in a separate area of the database. Any retrieval of this involved scanning to a separate spot on the disk. 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.
namibj 7y ago
Partial indexes allow you to do stuff like keep session tokens for some longer time to facilitate logging of attempted use of expired credentials, without significantly hurting your authentication-validation performance. In most cases you will not hit the disk, as the partial index is in memory. 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...
sterkekoffie 7y ago
Excellent points--if I may, I think you meant "specious," not "facetious."
- js2 7y ago
  If one disagrees with an argument because it's superficial, then one might call it "specious." If one disagrees because the argument is invalid, then the word is "spurious." I think based on the counter argument, "spurious" is what was wanted. https://www.merriam-webster.com/words-at-play/specious-vs-spurious-usage-guide https://www.merriam-webster.com/words-at-play/specious-vs-sp...
  - sterkekoffie 7y ago
    I think either would be appropriate but I suggested specious because it rhymes with facetious and I often mix up rhyming words myself.
YorickPeterse 7y ago
> All they had to do was implement a nested set pattern for their groups The nested set pattern was considered at the time we added support for nested groups, or improved it with CTEs (not sure which one of the two it was). The biggest drawback of nested sets is that adding sub groups can now become expensive. The storage needs are also far from ideal. 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. It's not ignorance, it's MySQL coming up with bizarre limits for the "TEXT" type. In MySQL, the limit for TEXT is 64 KB. In PostgreSQL, IIRC it is 1 GB. Looking back there may have been better decisions, but it's always easy to judge in hindsight. 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! At the cost of requiring more storage space, and writes taking (potentially) much longer. You may want to mention that, instead of acting as if nested sets are a silver bullet. > This is true, but is it really a show stopper? Yes. > Wrong schema specifications and not knowing to implement nested set > patterns is a sign that they don't have a knowledgeable DBA on staff. You may want to do some more research before going down the path of suggesting GitLab employees lack knowledge. For the last two years or so we've had various engineers with excellent database knowledge working on GitLab, myself included (though I don't consider myself a PostgreSQL expert). 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 https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/10885
- MrStonedOne 7y ago
  >> This is true, but is it really a show stopper? >Yes. No, its not. Not everybody has to run gitlab in the same production environment that gitlab.com does. it is 100% not a show stopper. Please stop being fictitious. I could pop the last mysql gitlab version on my ARM rock64 single board computer and get by running it as a 300 page views a month internal git server for personal projects. It will work fine, with or without partial indexes. So. Again. It is 100% not a show stopper. Stop fucking lying. These technical issues are not why you want to drop mysql
jrochkind1 7y ago
Eh, maybe all of those things CAN be done in MySQL, but they are done differently. I think it's totally legit that it takes a lot more development hours to support two DBs than one. 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).
Someone1234 7y ago
> I mean, I'm not too upset that they're focusing on one DB engine, but their reasons are a bit facetious. 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.
- munk-a 7y ago
  As a bostonian I'd mention that facetious has a gained meaning in that area of "misleading with malintent" I believe this meaning is pretty wide spread over the east coast in general but it might also be an artifact of word collision due to the local accent. The pronunciation can be quite close /fækˈtɪʃəs/ /fəˈsiːʃəs/
  - Izkata 7y ago
    Over in the midwest, I thought that simply was its meaning.
    - deleted 7y ago
      [deleted]
- Noumenon72 7y ago
  Thanks for the clear statement of the distinction! I only knew "facetious" (and always with the sense you state), so my vocabulary is bigger now.
oblio 7y ago
> Wrong schema specifications and not knowing to implement nested set patterns is a sign that they don't have a knowledgeable DBA on staff. 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.
- hotsauceror 7y ago
  This seems to be a pretty common attitude here on HN, that specialists like DBAs are a waste of money. Just a couple of days ago someone posted (only slightly paraphrased) "it's a waste of money to hire skilled DBAs. You should hire DevOps engineers who can solve the most common problems with your database. DBAs have outlived their usefulness." 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.
  - LIV2 7y ago
    Having a DBA certainly would've helped them when they accidentally deleted their prod database with no backups too, that probably wouldn't have happened if they had someone who knew how to properly manage DBs
  - derefr 7y ago
    It’s two different approaches to the same problem. Given the problem “the business layer wants to do X, and that’s inefficient in storage layer Y”, DevOps engineers solve the problem by adding another storage-layer component Z that follows a paradigm where the queries from X are “automatically” efficient; while DBAs solve the problem by informing the design of the business layer to make queries that are optimal given the storage layer in use. 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.
dahart 7y ago
If the real reason is they don’t want it, who cares what the other reasons are? 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 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.”
- MrStonedOne 7y ago
  Gitlab lied, parent was calling out the lies. Everybody should call out bad arguments, regardless of rather or not those arguments have a impact in the end. >who cares what the other reasons are? People who don't like liars. >In your critique of their argument, I don’t see a single reason why they should use MySQL. 1: This isn't even about rather or not they should use mysql. This is strictly about rather or not they should support mysql. I am not sure why you are bringing up using mysql. 2: Parent was never arguing in favor of mysql, their entire comment is strictly about how the arguments put out by gitlab were lies. I am not sure why you are bringing this up at all. >I admit I have a sensitivity to knee-jerk “can’t you just” comments You should work on that.
- nkozyra 7y ago
  > If the real reason is they don’t want it, who cares what the other reasons are? Is this comment directed at the parent comment or the blog post itself? :)
  - dahart 7y ago
    The parent comment. I'm referring to "I think this is the real reason." but I could have quoted that to make it more clear. And to be clear, they have more reasons, I'm asking why they need more reasons.
enobrev 7y ago
> All they had to do was implement a nested set pattern for their groups To be fair, the nested set pattern is the opposite of fun - especially at scale.
kstrauser 7y ago
> All they had to do was implement a nested set pattern for their groups In my experience, "all you have to do is..." is always followed by something heinous.
- ken 7y ago
  My manager used to do that so much with outrageous over-simplification that I instituted a rule. If, in any budget/estimation/planning meeting, anyone said "all you have to do is ..." or "it's just ...", they were volunteering for that task to be assigned to them. 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.
  - justinclift 7y ago
    All you have to do is tell your manager their code will only be accepted once it meets the team standards. ;)
    - tremon 7y ago
      ...at which point such managers will usually point out that they are the team standards.
perlgeek 7y ago
> > 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] 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.
- mst 7y ago
  I've always found materialized path does me proud for this sort of use case. Also nicely easy to back-populate after you add the column using a CTE, if you didn't have it originally.
  - dspillett 7y ago
    Cached paths is usually pretty cheap for adding new nodes, but it can be quite expensive if you need to be able to move non-leaf nodes around (caveat: I've not looked at their use-cases here, I don't know if moving non-leaf nodes is an important matter) especially if the forest is deep rather than (or as well as) wide.
dspillett 7y ago
> > 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 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...
dragonwriter 7y ago
> All they had to do was implement a nested set pattern for their groups 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.
ken 7y ago
> All they had to do was implement a nested set pattern for their groups 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.
- jayd16 7y ago
  You're conflating the nitty gritty of schema design (which I agree with you is not that declarative) with the usual case of "I know what I mean why doesn't the machine know what I mean" which is different. You're still expected ask for what you want specifically.
  - ses1984 7y ago
    > It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries. "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?"
oarabbus_ 7y ago
Look up the TEXT specification in Postgres. None of these problems exist with postgres. Sorry, but MySQL just kind of sucks in comparison.
nullwasamistake 7y ago
If they want multi-db support they should really be using an ORM. Hibernate for example is completely DB agnostic as long as you don't get too fancy.
stonogo 7y ago
"All they had to do was memorize all of MySQL's weird engineering quirks!" Sounds like a good reason to drop MySQL.
mortonpe 7y ago
> 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. 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 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.
- brodock 7y ago
  Only around 0.25% of paid customers: https://gitlab.com/gitlab-org/gitlab-ce/issues/52442#rationale https://gitlab.com/gitlab-org/gitlab-ce/issues/52442#rationa...
brodock 7y ago
When GitLab started supporting MySQL officially (on the paid version), there was a market reason to that. Big corporations used to by expensive Oracle support licenses. 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.
ben509 7y ago
> All they had to do was implement a nested set pattern for their groups [1] 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.
sburkett 7y ago
Both are great platforms, but ... how many of those customers who reported running on PostgreSQL did so because they didn't know they could use MySQL, or just took the defaults? Image from: https://scalegrid.io/blog/2019-database-trends-sql-vs-nosql-top-databases-single-vs-multiple-database-use/ https://scalegrid.io/blog/2019-database-trends-sql-vs-nosql-... https://uploads.disquscdn.com/images/36de94d737212dfc0daad179f63f0c43def301811941738e85446299b93a63e0.png https://uploads.disquscdn.com/images/36de94d737212dfc0daad17...

deleted 7y ago

[deleted]

noneeeed 7y ago

All their more general points in the post are perfectly valid arguments for focusing on a single database that I've heard from other projects, but naming specific technical issues like that actually makes it seam more nit-picky and less valid. I'm not sure many people would have found anything to argue with them about if they had simply said "Tagetting multiple DBs requires more resources to develop and test, limits our ability to make the most of what Postgres offers, and increases the cost of support. The proportion of users using MySQL is decreasing, so we have decided to focus on Postgres to give the maximum benefit to the largest group. We understand this may cause issues for some users so here is a comprehensive migration guide/toolset".

em-bee 7y ago
right, but by naming all the issues they had, they also opened up the discussion for learning how to solve those problems as the posts here demonstrate. 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.

avitzurel 7y ago

IMHO, this comes down to one thing. They lack the talent (and/or the will/resources to recruit it) to operate MySQL. I can TOTALLY get the reasoning behind supporting only a single engine but the reasoning they write there are either wrong or misguiding.

hmmIsee 7y ago
This comment is a baseless accusation if you do not provide more information on WHY you think the same.
avitzurel 7y ago
I realize my comment hit a sensitive spot but it's really not meant to be negative. 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).
- benatkin 7y ago
  Or they want to use that talent elsewhere. It's great that the simplicity argument doesn't really apply. It's a testament to developers getting better at programming in the large over the years. The database layer can be neatly abstracted away from the business rules.

mutt2016 7y ago

Unpopular opinion, and anecdotal data point: I stopped using Solaris and MySQL when oracle took ownership. Linux and postgres is my new standard.

petepete 7y ago
I'm not sure that's an unpopular opinion in these parts.
- mritchie712 7y ago
  yeah, I'd drop "unpopular opinion" along with "hot take", just speak your mind bud.
- chessturk 7y ago
  > these parts. The keywords here. Even though every dev on my team knows postgres equally or better than mysql, management chose mysql. I'm one of the equally camp, and don't possess a strong personal preference, but I was surprised that it was the favorite of the devs and not trusted by the business.
  - petepete 7y ago
    If it makes you feel any better, in the GOV.UK department I'm contracting in, just about every project is using PostgreSQL. It might take some time but the tide is flowing in PostgreSQL's direction.
yjftsjthsd-h 7y ago
... where is that an unpopular opinion? Really crusty enterprise?
- jniedrauer 7y ago
  I was even able to sell my last BigCorp™ on using MariaDB instead of Oracle MySQL
- 7y ago

nickcw 7y ago

> MySQL can't add TEXT type column without length specified Mysql does have a TEXT type with a max limit of 65536 chars (you can have MEDIUMTEXT or LONGTEXT if you want more). You don't supply a length when you use TEXT https://dev.mysql.com/doc/refman/8.0/en/blob.html https://dev.mysql.com/doc/refman/8.0/en/blob.html

pizza234 7y ago
Their statement is somewhat ambiguous. They may have tried to express that "you can't add a column of the TEXT class without specifying the exact type". 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.
- striking 7y ago
  It doesn't matter, because they're looking for a different thing entirely. PostgreSQL TEXT is just without a limit. Simple as that.
  - sbov 7y ago
    Not true, from: https://www.postgresql.org/docs/current/datatype-character.html https://www.postgresql.org/docs/current/datatype-character.h... > In any case, the longest possible character string that can be stored is about 1 GB.
  - michelpp 7y ago
    TEXT, like all Postgres varlena types, is limited to 1GB. Which for many people is effectively without limit.

xvilka 7y ago

Next step would be to rewrite everything from Ruby to Go (they already did for some parts of GitLab infrastructure).

hu3 7y ago
I wonder if Ruby is the reason why it requires so much memory.
rswail 7y ago
All of the MySQL "they just shoulda" and "they just coulda" arguments here are irrelevant. 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.
- xvilka 7y ago
  It wasn't sarcasm. I totally support their decision to drop MySQL. I prefer PostgreSQL in my projects too, especially with JSON/JSONB queries, it is amazing.

xenator 7y ago

I think here can be a good place to share my story why I never use MySQL in my projects. Once I was working for the company where I was an architect for some new project. And we have few gigabytes of data from a few sources. But with plans to gather much more in the near future. I came up with some database structure for the first draft of the early stage of development. It was nether big nether complicated for my scale comparing to the other projects I was working before. Since I'm python expert I choose SQLAlchemy for ORM layer and use its features to create the initial database schema. Postgres was a natural choice for me and almost fit to my requirements. I was worried about performance in the future but decided that we always can optimize when we have something. As I said it was early stage and we were on the research stage. Everything was smooth development-wise but the database was pretty slow since we most use desktop-grade computers for prototyping. 3 weeks before the first internal release owners ask me to change the database to MySQL. I was against this step, we don't have time or resources for the experiments. But point was that company have MySQL experts that have more than 10 years of the optimization experience. I simply have no choice. They force me to do this against my will. Since I wasn't sure that in production we will use Postgres I don't allow to use any special database dialect specific capabilities for the developers. And switching to another backend from Postgres to the MySQL was literally equal to a connection string change. MySQL wasn't ready to handle 40-60 tables and queries with 5-10 simple joins. On our data, every request just hang server (they quickly provided a server with a lot of memory and storage for this). A few days later we found that actually MySQL is working but the same simple queries run for 4 or more hours. So-called "experts with 10 years of optimization" spent a week trying to fix indexes and other things but it never happened. After release, I left the company, because of the toxic atmosphere, but it is another story. But since that, I lost all my faith in MySQL. Maybe for others, it is an option but never for me.

guggle 7y ago
It baffles me when people treat RDBMS as swappable dumb datastore.
- y4mi 7y ago
  It can be, if you're using an orm which abstracts everything sql away. You'll lose a lot of functionality and performance however. This might be an acceptable trade-off in some cases.

lifeisstillgood 7y ago

They just decided to use boring technology (http://boringtechnology.club/ http://boringtechnology.club/). Given they have limited resource spreading it across two databases seems a miss, and taking that time and attention and putting it back into git-ty stuff looks like a win.

mattbessey 7y ago
Does this really count as using boring technology? they're choosing the younger of the two DBs and to use some of the latest features e.g lateral joins
- dijit 7y ago
  I think when the difference in age is less than 2% of the age of the project then you can’t claim one to be more trendy based on age. Postgres Initial release: 8 July 1996; 23 years ago MySQL Initial release 23 May 1995; 24 years ago 1yr2mo difference. Not exactly earth shattering for a pair of projects two and a half decades old.
  - mrami 7y ago
    Funny, since you mention Postgres: 1996 is only PostgreSQL's first release. POSTGRES goes back even further [0]: > POSTGRES has undergone several major releases since then. The first "demoware" system became operational in 1987 and was shown at the 1988 ACM-SIGMOD Conference. Version 1, described in The implementation of POSTGRES , was released to a few external users in June 1989. In response to a critique of the first rule system ( A commentary on the POSTGRES rules system ), the rule system was redesigned ( On Rules, Procedures, Caching and Views in Database Systems ), and Version 2 was released in June 1990 with the new rule system. Version 3 appeared in 1991 and added support for multiple storage managers, an improved query executor, and a rewritten rule system. For the most part, subsequent releases until Postgres95 (see below) focused on portability and reliability. Your point stands, nonetheless. There are no whippersnappers present. [0] https://www.postgresql.org/docs/9.3/history.html https://www.postgresql.org/docs/9.3/history.html
  - 7y ago

rhacker 7y ago

I remember like 10 years ago it was all the rage to support 45 different databases. I'm so glad that time has passed. It wasn't feasible. Yes you can orm the crap out of things so you never see a line of SQL but you end up with index tuning on multiple database platforms. I noticed the trend thankfully just sort of walked itself away about the time non-traditional databases entered the picture and well it was pretty much impossible to support Mongo and Mysql (reasonably). I'm assuming more things are going back to Mysql/Postgres/Sybase/DB2 again, but I hope more companies and OSS projects stick to just one this time? The bottom line argument is this: When you make the decision to support multiple databases think of the 10-20 years of changes your product will exist through (and possibly a lot longer) and realize how much extra work that is going to be even if you use ORM. Then if you come to the decision Gitlab is - are you going to want to write the porting software for your users or just let them be shit-out-of-luck?

shanemhansen 7y ago

MySQL is a decent nosql option with mature replication. If you care about your data, expect MySQL to be your foe rather than your friend. Do you expect your triggers to fire? You are SOL https://bugs.mysql.com/bug.php?id=11472 https://bugs.mysql.com/bug.php?id=11472 Do you expect to be able to insert utf8 into utf8 columns? Don't be naive. Read the docs and you'll discover utf8mb4 or as I like to refer to it: mysql_real_utf8. How about transactions? Surely a real SQL database supports transactions right? Or at the very least if it doesn't it would complain loudly when you use a transaction where not supported right? Once again this behavior is helpfully documented https://dev.mysql.com/doc/refman/8.0/en/commit.html https://dev.mysql.com/doc/refman/8.0/en/commit.html Do you expect to be able to put a value into MySQL and get that same value back out? You are SOL. But it's documented. I can honestly say that the only appropriate time to use MySQL is when you can't use postgres and you are willing to move from a RDBMS to something that requires significantly more work to prevent data corruption. I respect the hell out of MySQL engineers in terms of raw performance and the engineering of Innodb. I'm sure they aren't happy there's a million broken applications relying on defaults they can't change. I'm sure they aren't happy that fundamental limits in how pluggable storage engines work make ACID transactions not possible in the general case. Postgres, for all of it's issues, feels like a database that has your back.

stingraycharles 7y ago
This seems overly negative and a lot of FUD. Some of these issues are minor, and/or when aware of it are easy to work around. MySQL is much more than a "nosql with mature replication".
- tracker1 7y ago
  The UTF8 thing is NOT fud.. the fact that "utf8" still doesn't default to REAL utf8 across major version changes is ridiculous.
  - datasage 7y ago
    utf8mb4 is the default in mysql 8.

aeyes 7y ago

Why is such a major change implemented in a minor version?

sytse 7y ago
It was announced in the major version of 12.0 I think we wanted to make it easy to upgrade to 12 before making the switch.
- valtism 7y ago
  That isn't how semver is supposed to work though.
silverwind 7y ago
Probably because marketing drives their versioning (e.g. major versions are reserved to shiny new features), not semver.

bdcravens 7y ago

Doesn’t removing support for something violate breadth over depth? https://about.gitlab.com/company/strategy/#breadth-over-depth https://about.gitlab.com/company/strategy/#breadth-over-dept...

perlgeek 7y ago
I think that motto refers to features more than underlying technologies.
- sytse 7y ago
  Correct, see our see our ‘boring solutions’ subvalue https://about.gitlab.com/handbook/values/#boring-solutions https://about.gitlab.com/handbook/values/#boring-solutions
- bdcravens 7y ago
  I agree, but if you’re a MySQL shop that’s a feature.
  - mgbmtl 7y ago
    I guess I'm in a MySQL shop, but when it comes to Gitlab, I just want it to run its regular upgrades without me having to do anything (and it does! I love Gitlab). I never considered running Gitlab on MySQL. We rarely have to fiddle in the database itself (it may have happened once or twice during our migration from Redmine), for the rest we use the Gitlab API. If you are at the level where you need strong control on your Gitlab install, then you're a Gitlab shop, and you should either use the recommended dependencies, or assume that you will be helping maintain the niche features.

deleted 7y ago

[deleted]

oarabbus_ 7y ago

Good, PostgreSQL is superior to MySQL in almost every way.

HatchedLake721 7y ago
Are you sure? https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/
- dahdum 7y ago
  They are using mysql innodb as a NoSQL store with fast replication instead of as a conventional database. They even call it "Schemaless".
- StreamBright 7y ago
  Yes it is sure. Uber migrates between Postgres and MySQL every two years.
- oarabbus_ 7y ago
  Absolutely sure. MySQL is nice for engineers who want to make analysts' lives miserable.

msla 7y ago

OK, but do they support MariaDB?

js4ever 7y ago

Just look how slow gitlab is on a 8cpu with 32gb or ram instance ... It's crazy ! I'm not sure about which part is because of Rails or the way gitlab is implemented. Issue is not about MySQL but more about the lack of knowledge / talent at gitlab

ShakataGaNai 7y ago

Good for them. Both MySQL and PostgreSQL are Good^. But supporting both doesn't work and it totally makes sense to focus on one. Personally, I don't generally use PGSQL but my GitLab instance does. Why? It's easy to run a dedicated PGSQL instance for GitLab using Docker. Or... Using AWS RDS. Or... Any number of ways. (^ Good is relative. People have their opinions on which is subjectively better but at the end of the day, lots of big sites and applications use both options to great success.)

why-el 7y ago
It also creates huge discrepancy between what a developer using an ORM like ActiveRecord writes and how the engines end up optimizing, meaning that should you want to write a performant one, you have to learn both, or, short of that, notify your DBAs of what you did, and in both cases huge resources are wasted. I never understood combining both from a business perspective anyway.

tbarbugli 7y ago

If you never used or heard of LATERAL [JOIN] I suggest checking that out because it is pretty awesome and handy. https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL https://www.postgresql.org/docs/current/queries-table-expres...

just_myles 7y ago

I have used postgres for so long that a lot of the baked in functions and features that were super convenient for me. I recall wanting to change a data type in my query and having to actually use the cast function. Also it was mentioned but when I was using it CTEs were not implemented yet. However, they weren't with postgres until 9.x as I recall either. Regarding performance, I mean, that is as much design as it is rdbms choice. Also i'm no DBA.

deleted 7y ago

[deleted]