Speedup of deletes on PostgreSQL

Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle.<p>This problem is common.<p>&quot;Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one.<p>&quot;The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table: a) If I update the parent table’s primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. b) If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well...<p>&quot;So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met: a) You do not delete from the parent table. b) You do not update the parent table’s unique&#x2F;primary key value (watch for unintended updates to the primary key by tools! c) You do not join from the parent to the child (like DEPT to EMP). If you satisfy all three above, feel free to skip the index – it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ‘over-lock’ data.&quot;<p>-Tom Kyte, <i>Expert One-on-One Oracle</i> First Edition, 2005.

Find missing indexes, return SQL to create them.<p><pre><code> SELECT CONCAT(&#x27;CREATE INDEX &#x27;, relname, &#x27;_&#x27;, conname, &#x27;_ix ON &#x27;, nspname, &#x27;.&#x27;, relname, &#x27; &#x27;, regexp_replace( regexp_replace(pg_get_constraintdef(pg_constraint.oid, true), &#x27; REFERENCES.*$&#x27;,&#x27;&#x27;,&#x27;&#x27;), &#x27;FOREIGN KEY &#x27;,&#x27;&#x27;,&#x27;&#x27;), &#x27;;&#x27;) AS query FROM pg_constraint JOIN pg_class ON (conrelid = pg_class.oid) JOIN pg_namespace ON (relnamespace = pg_namespace.oid) WHERE contype = &#x27;f&#x27; AND NOT EXISTS ( SELECT 1 FROM pg_index WHERE indrelid = conrelid AND conkey::int[] @&gt; indkey::int[] AND indkey::int[] @&gt; conkey::int[]);</code></pre>

What popular SQL databases need is an option&#x2F;hint to return an error instead of taking a slow query plan.<p>That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don&#x27;t have the right indices, your test fails. Simple.<p>In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned something. Would be so much easier to newcomers... instead of a giant footgun and obscure lore that only becomes evident after you have a oot of data in production.<p>Google Data Store does this, just assumes that _of course_ you did not look to do a full table scan. Works great. Not SQL, but no reason popular SQL DBs could not have an option to have query planners throw errors at certain points instead of always making a plan no matter how bad.<p>SQL has a reputation for a steep learning curve and I blame this single thing -- that you get a poor plan instead of an error -- a lot for it.

I had problems with the &quot;no foreign key indexes by default&quot; issue, and as much as I love Postgres I think this is an unfortunate foot gun.<p>I think it would be much better to create indexes for foreign keys by default, and then allow skipping index creation with something like a `NO INDEX` clause if explicitly desired.

This is DBA 101 stuff. If a database is part of your software, you really need someone on the team who knows how it works.

Similar to &#x27;If an article title poses a question, the answer is no&#x27;, if an article promises a significant speedup of a database query, an index was added.

I wonder how common it is to learn to add FK indexes in Postgres after watching a system be surprisingly slow. I learned the same lesson in a very similar way.

Out of curiosity, would placing all DELETE queries within a single transaction also help? Or does that still cause PostgreSQL to process each of the queries sequentially?

Coming across that early in my freelancing career, I create indexes on foreign keys by default now via auto-configuring it with ORM.<p>It&#x27;s almost always needed, and it&#x27;s easier to remove them if they somehow become a problem.

Obligatory MySQL Big Deletes: <a href="https:&#x2F;&#x2F;mysql.rjweb.org&#x2F;doc.php&#x2F;deletebig" rel="nofollow">https:&#x2F;&#x2F;mysql.rjweb.org&#x2F;doc.php&#x2F;deletebig</a>

Nice post! Love the unique insight here. TL;DR Create indexes on the foreign key columns of the table on which the foreign keys are defined.