A colleague of mine today suggested that we go through all of the queries in our application and to add indices accordingly. I feel this is premature optimisation because our application is not even released yet. I suggested to monitor for slow queries once we go live and then add indices accordingly.What is the general consensus when designing your database, should you add a matching index every time you write a new query? Or is it better to just monitor and see how it goes?
Is it premature optimization to add database indices?
database;database design;indexing
Premature optimization is optimizing something because of a vague, intuitive sense that, y'know, this will probably be slow, especially to the detriment of code readability and maintainability.  It doesn't mean willfully not following well-established good practices regarding performance.Sometimes that's a difficult line to draw, but I'd definitely say that not adding any indices before you go live is too-late optimization; this will punish early adopters--your most eager and most important users--and give them a negative view of your product, which they will then spread around in reviews, discussions, etc.  Monitoring queries to find pain points that need indexing is a good idea, but I'd make sure to do that no later than the beta.