The key that is two columns

A composite primary key is a primary key made of more than one column. Rails has defaulted to a single auto-incrementing id for so long that the alternative feels exotic. But in some tables, a row is identified by a pair of values, and pretending otherwise costs something.

The join table pattern

This application has ten tables shaped the same way: enrollments, lesson_completions, lesson_notes, bookmarks, user_badges, solution_reveals, partner_course_sponsorships, identity_authentications, experience_points and partner_handoff_nonces.

Each has a surrogate id and a unique index over the columns that identify it. In every case it's a pair:

t.index [ "user_id", "lesson_id" ],
        name: "index_lesson_completions_on_user_id_and_lesson_id",
        unique: true

The id column never answers a question anyone asks. Nobody looks up completion 4,812. They ask whether this user completed this lesson, and the unique index is what answers that.

What the surrogate key costs

It costs eight bytes per row for the column, plus an index on it that nothing queries: the primary key index Postgres builds automatically.

On 2519 lessons and the completions stored against them, that's nothing. On a table with a hundred million rows, it's gigabytes of index that exist to satisfy a convention.

flowchart TB
  T["lesson_completions"] --> I["id: bigint, primary key"]
  T --> U["user_id"]
  T --> L["lesson_id"]
  I --> X["indexed, never queried"]
  U --> Y["unique index on the pair"]
  L --> Y
  Y --> Z["this is the real identity"]

What it buys

It buys a stable single-column handle. URLs, foreign keys from other tables and Active Record's own machinery all get simpler when identity is one value.

DELETE FROM lesson_completions WHERE id = $1 is unambiguous, while the composite version needs both values everywhere.

And it's what every Rails tool expects. find, update, destroy, form helpers and dom_id were all written assuming one column.

The honest position

The surrogate key is usually right, and it's right here. This application shouldn't adopt composite keys, and that's not why they're worth learning.

Some tables you'll meet elsewhere already have them: legacy schemas, partitioned tables, sharded tables, anything where a tenant id is part of every key. Rails 7.1 gained real support for them, which changed what's possible.

Knowing the trade lets you recognize the tables where the default is wrong. That set is smaller than enthusiasts claim, and larger than "never".

Key takeaways

• A composite primary key is a primary key made of more than one column.
• This application has ten join tables with a surrogate id and a unique index on the real identity.
• The surrogate costs eight bytes per row and an index nothing queries.
• It buys a stable single-column handle and compatibility with every Rails tool.
• Learn composite keys to recognize the tables where the default is wrong, not to convert these.