4 ms·
I think the risk of creating indexes prematurely is really that you will make assumptions about how your table is going to be queried that don't turn out to be
by pyrophane 3y ago
I think the risk of creating indexes prematurely is really that you will make assumptions about how your table is going to be queried that don't turn out to be correct, so you create indexes that you will never need, even if the table gets large.
That probably isn't the end of the world, but it can make it harder for others to understand what is going on in your database (indexes can also be a form of documentation) and could make your ORM code somewhat more complex.
The worst case scenario, I suppose, would be you create an index on a table that grows large
- giraffe_lady 3y agoThis has matched my experience too. Most experienced developers can probably identify an inappropriately hot table and add an index to it. Where a DB with dozens of individually modest indexes that add up to be huge is much trickier and more labor intensive to confidently improve.
- jmcomets 3y agoFWIW, most DBMS have built-in index usage stats, and it's not too difficult to query. In my previous job we had a Postgres health dashboard that notably showed `log10(index size) / index hits`, making it pretty clear when some recently added index was useless. My opinion is that indexes should be either logical (ex: used for exclusion constraints) or purely for performance (tradeoff space for better QPS). Query patterns change, specs change, so monitoring your DB's performance is the way to go.