Problem Statement

A foreign key is a database constraint which guarantees values in one column occur in another column. For example, if there are tables organizations and invoices then the fact that each invoice belongs to an organization can be represented at the database level by adding organization_id to invoices. Without a foreign key that column could contain values that don’t correspond to any organization ID, thus putting data consistency at risk. That’s where foreign keys enter the scene – their use prevents such scenarios from materializing.

The other part is that the nature of foreign keys makes them likely to occur in queries fetching related objects. For instance, in a Rails app a query like Invoice.includes(InvoiceLine).find(1) is going to use invoice_lines.invoice_id to fetch invoice lines. If that that column isn’t indexed then a sequential table scan may be necessary, hurting read performance.

Solution: Index Unless Proven Harmful

If the problem is a missing index then the solution is to create that missing index. Naturally, we shouldn’t be indexing mindlessly as indexes cost disk space and write performance. In the case of foreign keys, though, my recommendation is to create an index except in cases where it’s proven harmful.

We could end here but on many of my database performance consulting projects, the number of missing indexes approached a hundred. Creating that many indexes manually is a mundane job to put it mildly. I figured it’d be less work to automate the solution to that project’s problem than to solve it manually and that’s how active_record_doctor was born!

active_record_doctor to the Rescue

There are two steps to solving the problem. First, identify all unindexed foreign keys. Second, create migrations that add the missing indexes. Let’s start with the first step.

After installing active_record_doctor, you can identify unindexed foreign keys by running:

bundle exec rails active_record_doctor:unindexed_foreign_keys

The output will contain all instances of foreign keys that lack an index. For example:

add an index on users.company_id - foreign keys are often used in database lookups and should be indexed for performance reasons

If there are just a few missing indexes then you can create a migration manually. Otherwise, you can save the output above to a file (e.g. via > unindexed_foreign_keys.txt) and run:

bundle exec rails g add_indexes_generator unindexed_foreign_keys.txt

That command produces one migration per table and each migration creates all missing indexes on that table.

Beware creating an index locks the underlying table for writes. If the table is large then indexing it can take your app down. There are several solutions to the problem: indexing during a maintenance window, spreading the work across a longer time period (that’s why multiple migrations are created), or using concurrent index creation (CREATE INDEX CONCURRENTLY in PostgreSQL).

Summary

Foreign keys are a basic database feature and are critical for ensuring data consistency. In most circumstances, it’s a good idea to create an index on them yet that practice is often ignored. I recommend you start indexing newly added foreign keys and start working towards indexing existing foreign keys. If you use Active Record then active_record_doctor can help you automate the bulk of that mundane process.