I once had a friendly debate with a database professional. I argued that these days with storage being so cheap, databases should automatically create indexes. E.g.: anything referenced in a view or a foreign key, and also anything that occurs regularly in production. He said he thought that was a bad idea, because a tiny fraction of the time it could cause a performance regression.
Now, years later, Azure SQL Database has automatic indexing and I feel a little bit vindicated. He does have a point that this can go wrong, but compared to what the typical developer does, it basically can't make things worse.
Probably because they used their gut feeling/intuition which stems from experience, so they started suggesting arguments to support their gut feeling. Their gut feeling may be correct, it's just that they might not be able to see all the arguments or edge cases that may make this indexing a bad idea so they are concerned about implementing out of the box.
It's very typical when suggesting ideas that go against the usual best practices, status quo, but it's understandable since you actually should consider all the edge cases before moving away from known ways of doing things.
Yes, you have a point. I think there’s no fundamental limitation today why everything can’t be indexed. There’s definitely some overhead to updating the indices on every insert of course, but that’s probably a relatively fixed ~O(1) cost in most cases.
Now, years later, Azure SQL Database has automatic indexing and I feel a little bit vindicated. He does have a point that this can go wrong, but compared to what the typical developer does, it basically can't make things worse.