[hu3]

I'll have to defend your parent commenter on this one.

Not having indexes for FKs is on average much worse for overall performance. Defaults should be reasonable.

In the great majority of cases you WANT to have indexes in FKs.

> expect the universe to magically fix all of your mistakes

This kind of derogatory hyperbole is not necessary nor productive.

I should expect tools to help me avoid mistakes. Not having an index on FKs is, more often than not, a mistake. It is reasonable to expect PostgreSQL to help me here.

[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.