29 ms·

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 spe

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

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