[stouset]

I'll play devil's advocate. To be clear I generally agree that foreign keys should essentially always have a corresponding index, and that not including an index is a mistake far more often than it isn't.

My only counterargument is that—especially in production—adding indexes is expensive. Adding foreign keys is cheap. Latching a potentially expensive operation that can result in downtime to what should be (and often is expected to be) a cheap operation can cause an unexpected immediate loss of service. Though I believe (but am not certain) that in PostgreSQL's case, it should be relatively easy to recover from since DDL is transactional, and I can't see why you wouldn't be able to abort the index-creating transaction in progress. In MySQL I believe it's much more difficult to recover from this type of situation.

Not having an index on a foreign key can cause problems, but they tend to be of the long-term performance-reducing kind rather than the immediate outage kind. And in the event that adding a foreign key causes an issue, removing it is as simple as creating it.

That's all I've got: you're latching a slow, table-locking operating to what is expected to be an immediate one. Yes, I understand you're only suggesting this be the default, but I wouldn't expect most developers to predict the possible implications. Especially if the migration worked quickly in staging, where there's less data.

Edit: Actually, another one: if the index is created automatically, should it be removed when the foreign key is removed? This isn't a "problem" so much as a design issue with—I think—no necessarily clear, great answer. Just different choices with potentially-awkward tradeoffs.

[hu3]

Correct me if I'm wrong but, FKs are rarely created for existing columns.

You usually create the column and the FK in the same script. And usually starting with a NULL value for existing rows.

And if it's a new table then there's no rows anyway.

So the most common operations when creating FK's aren't expensive as far as I know.

You know what's expensive? Creating an index on a large table because you or your RDMS forgot to create the index when the FK was created and now JOINS are crawling to halt.

[stouset]

FK indexes by necessity need to be placed on the foreign table, which is just as likely to be a preexisting table that already contains data.

To be clear I 100% agree that adding indexes later is extremely painful. A little care when first creating tables goes a long way, and I’ve never seen a database fall over due to preemptive over-indexing but I’ve seen countless do so thanks to being underindexed.

Still, taking a DDL operation which is presumed to be essentially instantaneous and adding a default behavior that requires locking completely separate tables for a potentially-lengthy update does give me pause.