Database Medium severity

Unindexed Foreign Keys

A belongs_to association backed by a *_id column that has no database index, so every JOIN, cascade delete, or parent.children lookup performs a sequential scan on the child table, growing linearly with row count.

Before / After

Problematic Pattern

# Migration added the column, no index.
class AddUserToComments < ActiveRecord::Migration[7.1]
def change
  add_column :comments, :user_id, :bigint
  # Nothing else. 30M rows, no index.
end
end

# Every User#comments query: seq scan 30M rows.
# Delete cascade: locks comments for minutes.

Target Architecture

class AddIndexToCommentsUserId < ActiveRecord::Migration[7.1]
disable_ddl_transaction!

def change
  add_index :comments, :user_id,
    algorithm: :concurrently
end
end

# Prefer generating new foreign keys with:
add_reference :comments, :user,
foreign_key: true, index: true
# add_reference includes an index by default.

Why this hurts

PostgreSQL cannot answer WHERE user_id = ? without an index scan when the column has no index, so it falls back to a sequential scan that reads every page of the table. On a 30M row comments table at 8 KB per page with ~100 rows per page, a single user.comments.limit(10) must read 300,000 pages from disk or cache. Even if the working set fits in shared buffers, the scan consumes significant CPU on type-casting and predicate evaluation for rows it will immediately discard.

Cascade deletes through unindexed foreign keys are catastrophic. ON DELETE CASCADE requires PostgreSQL to find every referencing row, and without an index it issues a sequential scan per cascading delete. Deleting a single user with 30M comments in the table can take many minutes and holds an ACCESS EXCLUSIVE lock on the comments table for the duration, blocking reads and writes for every other user’s comments too. The operation cascades through lock contention into unrelated code paths.

The problem hides during development because small seed databases respond in milliseconds regardless of index presence. Production latency appears abruptly when traffic hits the scale threshold, usually after a business milestone rather than a code change. EXPLAIN plans show Seq Scan where the developer expected Index Scan, and the first indicator is often a support ticket about a slow dashboard.

Rails migrations generated by older versions of the framework (and migrations written manually) frequently add foreign key columns without indexes. The strong_migrations gem can catch new cases in CI, but the backlog of existing unindexed columns requires an audit against pg_catalog. Building the index retroactively must use CONCURRENTLY to avoid locking the now-large table during creation.

Get Expert Help

Inheriting a legacy Rails codebase with this problem? Request a Technical Debt Audit.