4 ms·

No it's not ok. But the people who didn't add indices maybe even had all the basic training on DS and algos. But they were simply either incompetent or more li

No it's not ok. But the people who didn't add indices maybe even had all the basic training on DS and algos. But they were simply either incompetent or more likely simply careless and didn't give a sh_t. Because adding indices in the proper places requires a few minutes of PLANNING. Who has few minutes nowadays, when you can read HN? On top of that their management/leadership was careless, and didn't give a sh_t. In the end, the poor database tables were left all alone, in the dark, without indices :).

jiggawatts 5y ago
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.
- raspasov 5y ago
  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.
- redis_mlc 5y ago
  What you're saying doesn't usually make sense because: - indexes can be unique or non-unique - covering indexes use multiple columns - indexes take space and increase time for most non-read operations. For AWS EBS, there can be a non-trivial network latency. However, if you mean that the database should monitor for table scans and usually add indexes, then maybe. MariaDB (MySQL) has some new features that I suggested that allow exploration of indexes performance by enabling and disabling them. My understanding with Postgres is that write amplification is noticeable as indexes are added (every index has to be touched for changes, while MySQL's Innodb never did that.) Source: DBA.
- mewpmewp2 5y ago
  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.

jiggawatts 5y ago

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.

raspasov 5y ago
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.
redis_mlc 5y ago
What you're saying doesn't usually make sense because: - indexes can be unique or non-unique - covering indexes use multiple columns - indexes take space and increase time for most non-read operations. For AWS EBS, there can be a non-trivial network latency. However, if you mean that the database should monitor for table scans and usually add indexes, then maybe. MariaDB (MySQL) has some new features that I suggested that allow exploration of indexes performance by enabling and disabling them. My understanding with Postgres is that write amplification is noticeable as indexes are added (every index has to be touched for changes, while MySQL's Innodb never did that.) Source: DBA.
mewpmewp2 5y ago
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.