<story><title>Cases where full scans are better than indexes</title><url>https://www.jefftk.com/p/you-dont-always-need-indexes</url></story><parent_chain><item><author>jameshart</author><text>“We’ll add an index when it gets slow” is saying “screw the guy who’s on call the night this system starts timing out”.&lt;p&gt;Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering.&lt;p&gt;If you deliberately aren’t implementing indexing, know at what point indexing would begin to matter. Put in place guard rails so the system doesn’t just blow up unexpectedly.&lt;p&gt;There are 100% cases where &lt;i&gt;you shouldn’t use indexes&lt;/i&gt;. That’s not the same thing as &lt;i&gt;being able to get away without an index&lt;/i&gt;.</text></item></parent_chain><comment><author>bob1029</author><text>&amp;gt; Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering.&lt;p&gt;I think this describes the general shape of problem seen in any engineering domain. After a certain point of time (i.e. unknown), we can no longer guarantee certain properties of the system. This is why we add all kinds of qualifiers and constraints in our discussions with the customer.&lt;p&gt;Certainly, you can make some predictions about how tables will grow over time, but there are also some potential headaches that can be incurred if you arbitrarily add indexing. &amp;quot;We &lt;i&gt;might&lt;/i&gt; need to expire records to manage growth&amp;quot; so you go ahead and index that CreatedUtc column. Turns out, the usage patterns of your app changed such that that table is now experiencing 20x more insert volume than you could have ever anticipated and your prediction is now a gigantic liability. With 20x insert volume you would have _never_ indexed that column. You would have used some alternative path involving temporary tables, etc. Now, you are stuck with essentially same problem - a judgement call at 2am regarding whether or not you should drop an index while the rest of your team is in bed.&lt;p&gt;Since I am unable to predict the future I feel like waiting until the &lt;i&gt;actual&lt;/i&gt; problem arises and then dealing with it at that moment is the best option.  Strategically, &lt;i&gt;not&lt;/i&gt; having an index will likely fail more gracefully than if an index is bottlenecking requests during peak load (i.e. instantaneous vs cumulative data volume).</text></comment>
<story><title>Cases where full scans are better than indexes</title><url>https://www.jefftk.com/p/you-dont-always-need-indexes</url></story><parent_chain><item><author>jameshart</author><text>“We’ll add an index when it gets slow” is saying “screw the guy who’s on call the night this system starts timing out”.&lt;p&gt;Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering.&lt;p&gt;If you deliberately aren’t implementing indexing, know at what point indexing would begin to matter. Put in place guard rails so the system doesn’t just blow up unexpectedly.&lt;p&gt;There are 100% cases where &lt;i&gt;you shouldn’t use indexes&lt;/i&gt;. That’s not the same thing as &lt;i&gt;being able to get away without an index&lt;/i&gt;.</text></item></parent_chain><comment><author>plugin-baby</author><text>&amp;gt; Invisible cliffs in your code that it will fall off at some unknown point in the future are the antithesis of engineering.&lt;p&gt;On the other hand, they are the essence of _software_ engineering.</text></comment>