MyISAM is actually considerably faster (than InnoDB) for read heavy apps.
InnoDB is comparatively slow, but you get much better transactionality (IE; something that is much closer to ACID compliance). Row level locking is faster for inserts than table level locking, but table level locking is faster for reads than row level locking.
Regardless: Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL. (not that Postgresql is the only alternative btw).
MyISAM is not a transactional storage engine even to begin with, so saying that you get "much better transactionality with InnoDB" or "MyISAM is actually considerably faster" is either wrong or at best comparing apples to oranges.
> Both storage engines do not scale with core count as effectively as postgres due to some deadlocking on update that I have witnessed with MySQL.
Strange take since a deadlock is rather an exceptional event you want never to occur so deadlocking, in algorithm design, wouldn't be considered a reason one would say that the implementation does not "scale with the core count". Whether or not the algorithm scales with the core count is for many other different reasons but not deadlocks.
Considering the "scale with the core count" design problem, Postgres process-per-connection architecture makes it a much less viable option than, say, MySQL so this is wrong as well.
Well, I’ve literally observed it (circa 2016 and design has not significantly changed with this in that time) and confirmed my finding with percona.
deadlock was the wrong terminology to use, apologies, I keep writing from my phone as I am travelling at the moment: I meant lock contention, specifically in memory. A deadlock would be a hard stop but what I observed was a bottleneck on memory bandwidth past a certain number of cores (24) with update heavy workloads.
So, appreciate your points but I don't think I am wrong in the core thesis of my statements. x :)
You would not be able to saturate the memory bus if you have a lock contention. Having a lock contention is usually exhibited in under-utilizing your CPU compute and memory bandwidth resources. So, hitting the limit of the available memory bandwidth still sounds like a misrepresentation of the issue you stumbled upon.
