Migrations: composite keys
This course covers composite primary keys in Rails and Postgres, using this application's schema as its example. It compares a surrogate id plus a unique index with a key made of two columns, and names the cases that force a composite key: partitioned tables, sharded or multi-tenant data and legacy schemas. It explains Rails 7.1 support, including query_constraints, a five-deploy sequence for changing an existing key, and how column order and leftmost prefixes decide which queries a multi-column index can serve.
What you'll learn
- ✓The costs and benefits of a surrogate id beside a unique index on the pair
- ✓When partitioning, sharding or a legacy schema requires a composite key
- ✓Declaring composite keys in Rails 7.1 and using query_constraints
- ✓A five-deploy sequence for changing an existing primary key
- ✓Column order, leftmost prefixes and covering indexes
- ✓Reading the decisions behind an existing schema
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.
Read the full lesson free →Curriculum
7 lessonsThe key that is two columns
Compares a surrogate id with a composite key across this application's ten join tables and their costs.
When the key must be composite
Explains why partitioned tables, sharded data and legacy schemas require a composite key, and why uniqueness alone does not.
How Rails supports them
Shows how Rails 7.1 declares composite keys, how query_constraints works, and where an array id causes trouble.
Migrating to one safely
Describes a five-deploy sequence for changing a primary key and the queues, caches and foreign keys holding old ids.
The index you already have
Explains the leftmost-prefix rule, how to order index columns and when a covering index answers a query alone.
What this schema chose
Reads this schema's choices: consistent join tables, a redundant user_id index and globally unique lesson slugs.
Putting it together
Reviews the course and sets a challenge: implement index_serves?, a check based on the leftmost-prefix rule.