alt Hacker NewsI recently encountered a query that deadlocked on itself because it used a parallel execution plan and updated multiple indexes in a manner that the different threads could conflict with each other.
Naively one would expect that no individual UPDATE or INSERT statement could deadlock in isolation… but there you go.
If that is possible, the possibilities across multiple concurrent data mutating queries are beyond human comprehension!
Serializable should absolutely be the default!
Similarly, all columns should be automatically indexed to at least some degree, like Postgres BRIN indexes at a minimum.
Time and experience have shown that the vast majority of developers are pathologically unable to properly define all required indexes ahead of time.
Replies
You are supposed to define indexes based on how you query the data, not ahead of time.
I've definitely experienced the opposite, where indexes run rampant and are NN times the size of data (for the entire database!)
That being said also "required indexes ahead of time" is impossible - because query patterns change and things get released unless you mean something like "the required indexes for our obvious query patterns we just freaking released.
I dont think most columns need to be indexed because that is mostly nonsense, most columns never have a where clause applied to them or are sorted on themselves, so an index provides ??? value.