<story><title>SQL Keys in Depth</title><url>https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html</url></story><parent_chain><item><author>foreigner</author><text>At what scale does all this stuff start to actually matter?  I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems.  We use auto-incrementing integers as primary keys and try not to expose them externally.  Indexes are added as necessary to enable specific queries.  We don&amp;#x27;t enforce any other constraints (e.g. not-null or foreign keys) at the database level.&lt;p&gt;... and it all works and performs just fine?  The considerations the author mentions all make some sense to me in theory, but when do they actually matter in practice in a modern system?</text></item></parent_chain><comment><author>mannykannot</author><text>You might not have any observable effects until the day you do, at which point you may be faced with an arbitrarily large problem.&lt;p&gt;What you are doing is passing up on the opportunity of catching various errors (you are also passing up the opportunity for some optimizations, but that is probably a secondary issue.) In particular, you are passing up on some opportunities to catch inconsistencies in how different applications (or different parts of the same application) create and use data.&lt;p&gt;One argument made against putting these sort of rules in effect is that they constrain what application developers can do, but that is the wrong way to look at it: any such conflict is an indication of a misunderstanding (not necessarily on the part of the application developers) that has been caught before it can lead to bigger problems, such as a database full of irretrievably inconsistent or incomplete data.&lt;p&gt;When the problems finally do arise, it is often the case that some sort of workaround is the only practical solution. This, in my experience, is one of the common ways by which systems accrue gratuitous complexity, which in turn has at least two real-world consequences: an increased time to make changes, upgrades and extensions, and an increased frequency of errors, especially WTF-type errors (and probably also efficiency&amp;#x2F;performance hits.)</text></comment>
<story><title>SQL Keys in Depth</title><url>https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html</url></story><parent_chain><item><author>foreigner</author><text>At what scale does all this stuff start to actually matter?  I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems.  We use auto-incrementing integers as primary keys and try not to expose them externally.  Indexes are added as necessary to enable specific queries.  We don&amp;#x27;t enforce any other constraints (e.g. not-null or foreign keys) at the database level.&lt;p&gt;... and it all works and performs just fine?  The considerations the author mentions all make some sense to me in theory, but when do they actually matter in practice in a modern system?</text></item></parent_chain><comment><author>amarkov</author><text>As you can see leaking through in a few of the examples, the scenarios where it matters are the ones where non-experts are reading to and writing from the database. If a data entry clerk is just typing values into a form thinly wrapping an INSERT, you&amp;#x27;d better make sure any input that would break implicit assumptions is rejected. If you have a legion of Bobs from marketing who &amp;quot;know some SQL&amp;quot;, you can&amp;#x27;t hand-optimize every single query they try to run.&lt;p&gt;When the database is just serving a webapp you control end to end, the only part that&amp;#x27;ll really have a huge impact is making sure you can partition by the primary key effectively. Which is good, because complex indexing schemes and foreign key constraints actually scale very badly.</text></comment>