<story><title>Database Learning: Toward a Database That Becomes Smarter Every Time</title><url>https://arxiv.org/abs/1703.05468</url></story><parent_chain><item><author>Roritharr</author><text>I recently wondered why we still don&amp;#x27;t have self optimizing databases, creating their own indexes and tweaking their settings when needed. Are there such products out there or is there no demand?</text></item></parent_chain><comment><author>jandrewrogers</author><text>Good databases do self-optimize quite a bit but they select where and how they do that optimization carefully. There are two big constraints on practical dynamic optimization: the cost must be less than the benefit and it must be applied to the system incrementally.&lt;p&gt;These constraints have effects that are not always obvious. For example, architecting a database kernel so that it &lt;i&gt;can&lt;/i&gt; be dynamically optimized slows down computational throughput in the general case! This means that the benefits of dynamic optimization have to be sufficiently large that it offsets the performance tax of doing dynamic optimization at all. Also, because the performance tax is general, there is an implication that the optimization needs to be general across the set of runtime operations as well i.e. you can&amp;#x27;t optimize one operation at the cost of slowing down others. Nonetheless, there many areas such as cache replacement algorithms where dynamic optimization has big performance payoffs for the added complexity.&lt;p&gt;Second, dynamic optimization needs to be applicable incrementally, otherwise it tends to have a &amp;quot;stop the world&amp;quot; effect on performance when it is being applied, which is bad. A good example of this is dynamically optimizing storage for the actual distribution of queries it sees. Dynamically adding a new secondary index imposes a huge background cost that is visible in performance, so databases don&amp;#x27;t do it. However, dynamically optimizing individual &lt;i&gt;pages&lt;/i&gt; for queries is common because it can be applied incrementally for pages you are already accessing anyway for a small one-time cost per page distributed over time as pages are accessed. And it only applies to pages you actually touch; building a new index pulls a lot of cold storage through the cache. Page level query optimization may be less effective overall than adding a second index but the fact that it can be applied incrementally makes it a preferred form of dynamic optimization.&lt;p&gt;In short, dynamic optimization has been studied and tried for many decades. Sophisticated databases do a lot of it but the mechanisms are chosen carefully to minimize adverse side effects in real world conditions.</text></comment>
<story><title>Database Learning: Toward a Database That Becomes Smarter Every Time</title><url>https://arxiv.org/abs/1703.05468</url></story><parent_chain><item><author>Roritharr</author><text>I recently wondered why we still don&amp;#x27;t have self optimizing databases, creating their own indexes and tweaking their settings when needed. Are there such products out there or is there no demand?</text></item></parent_chain><comment><author>bladecatcher</author><text>MonetDB[0] is an open source OLAP engine which does cracking.&lt;p&gt;From the Wikipedia page:&lt;p&gt;&amp;quot;Cracking is a technique that shifts the cost of index maintenance from updates to query processing. The query pipeline optimizers are used to massage the query plans to crack and to propagate this information. The technique allows for improved access times and self-organized behavior.&amp;quot;&lt;p&gt;[0] &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.monetdb.org&amp;#x2F;content&amp;#x2F;column-store-features&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.monetdb.org&amp;#x2F;content&amp;#x2F;column-store-features&lt;/a&gt;</text></comment>