I can help shed light here. Postgres doesn’t make you choose between two storage engines and their compromises. It’s been years, but last time I looked the trade offs between MySQL storage engines were material and the newer engine was missing some valuable features of the older one. The advice was “pick the right engine for the job” - but I don’t want to, that another decision I have to make, why make me do that? At least until they come out with a new storage engine that’s “objectively better than Postgres”, I will enjoy the simplicity of using Postgres with its single storage engine. Choice isn’t always better.
I searched to see if anything has changed and found that InnoDB doesn’t support full text search - give me break, after it’s been the default for years, you gotta be kidding. https://hevodata.com/learn/myisam-vs-innodb/
I disagree. Search engines put more weight on recent articles, and no reputable source is writing about this topic in years, for one simple reason: there is no valid reason to choose the MyISAM storage engine in 2021. This was true even a decade ago.
Hevodata.com appears to be selling an ETL product. This post you're linking to is effectively SEO / content marketing. It is not written by MySQL experts.
Regarding fulltext support in InnoDB, there are many results when searching for "innodb fulltext".
I wouldn't say it is uniformly a virtue, especially for user-facing practical purposes when comparing MySQL to e.g. Postgres. It's a major architecture difference which has pros and cons.
Most MySQL users should just stick with InnoDB for everything. It's an extremely battle-hardened choice with excellent performance for the vast majority of OLTP workloads.
However, if you're a large company and storing a massive amount of relational data, MyRocks has significantly better compression than any other comparable relational database I'm aware of. FB poured a ton of engineering effort into it, because those compression benefits provide ludicrous cost savings at their scale. For most companies and use-cases though, the benefits may not be significant enough to justify using a less common engine.
Aside from these two engines, AFAIK there's currently no other popular general-purpose modern storage engine that is widely used for MySQL. There are a lot of random third-party ones, but historically it's risky to tie your business to an uncommon storage engine.
I'm not a DB internals engineer, but from what I understand, some of MySQL's downsides are a direct result of the added complexity of having a pluggable storage engine architecture. Mixing-and-matching multiple engines in one DB instance can also be risky (it affects crash-safety guarantees of logical replication on replicas). So given that most companies should just use InnoDB anyway, for practical purposes pluggable engines are not a huge advantage for most users today.
But in the future, who knows; it's good to know the flexibility is there.
And had MySQL never supported pluggable engines originally and MyISAM had been the only option, MySQL would be dead and forgotten. InnoDB was originally developed by a third-party company that Oracle acquired. Interestingly, Mongo followed a very similar trajectory, replacing their initial engine with WiredTiger, developed by a third-party company they acquired.
InnoDB and Memory are really the only ones widely used these days. After Oracle bought MySQL they threw their full backing behind InnoDB and recommended all MyISAM users migrate. They fixed a number of small problems like the missing full text support.
InnoDB is more fault tolerant and modern feature rich (e.g. Foreign keys) than MyISAM. MyISAM is faster in practice despite Oracle’s continuous claims to the contrary.
MyISAM is largely undeveloped at this point. MariaDB hard forked MyISAM into Aria and have included better fault tolerance and other general improvements. It’s what I use for my personal projects.
Memory tables are strictly in memory and useful for doing very quick processing on things you are certain will fit entirely into system memory. Their data is lost when the system is rebooted or MySQL is restarted.
I can help shed light here. Postgres doesn’t make you choose between two storage engines and their compromises. It’s been years, but last time I looked the trade offs between MySQL storage engines were material and the newer engine was missing some valuable features of the older one. The advice was “pick the right engine for the job” - but I don’t want to, that another decision I have to make, why make me do that? At least until they come out with a new storage engine that’s “objectively better than Postgres”, I will enjoy the simplicity of using Postgres with its single storage engine. Choice isn’t always better.
I searched to see if anything has changed and found that InnoDB doesn’t support full text search - give me break, after it’s been the default for years, you gotta be kidding. https://hevodata.com/learn/myisam-vs-innodb/