Composite Primary Keys in Rails: A Storage-Level Reset

Relational databases lean on a simple rule: data that is read together should live together. In MySQL with the InnoDB engine, that "togetherness" is decided by the table's primary key, which dictates the physical order of rows on disk. Rails and Active Record make this easy by giving every table a conventional, auto-incrementing integer id column. For many applications, that insertion-order clustering is perfectly adequate.

Shopify's core API server is not one of those applications. In its multi-tenant architecture, each database instance holds records from thousands of shops. A conventional auto-incrementing key interleaves those shops' rows in insertion order, while nearly every query targets just one shop's data. The mismatch is brutal at the storage layer: retrieving all orders for a single shop can require loading nearly every page of the table into memory.

The solution Shopify applied is conceptually simple: replace the single-column primary key with a composite one, (shop_id, order_id). That small schema change reorders the table's physical storage, grouping rows by shop and dramatically cutting the number of disk pages a typical query must load.

Here is the schema pattern as applied to an orders table:

CREATE TABLE `orders` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `shop_id` bigint(20) NOT NULL, … other columns ... PRIMARY KEY (`shop_id`,`id`), KEY `id` (`id`) … other secondary keys ... )

Keeping Active Record Untouched

The primary obstacle to this change was not the database but the application framework. Active Record and Shopify's application code carry deep assumptions about a table's primary key. Rather than patching the framework with a community gem, they kept the id column as an auto-incrementing secondary index and explicitly told ActiveRecord to keep treating it as the model's primary key:

class Order < ApplicationRecord self.primary_key = :id .. remainder of order model ... end

Notice that the secondary index on id is non-unique. This introduces a theoretical risk of duplicate model ids across different shop_id values. Shopify accepted that risk because its live migration tool, the Large Hadron Migrator (LHM), relies on MySQL triggers copying rows into a shadow table. InnoDB enforces each unique constraint with an exclusive table-level lock; two tables accepting writes with two unique constraints each creates a deadlock-prone condition during migration. A unique secondary key on id is feasible if you avoid trigger-based live migrations, have low write throughput on the table, use default Rails migrations, or have write paths resilient to transaction failures.

Systems Beyond Rails

Schema changes of this type ripple beyond the ORM. Any tooling that reads the database directly must understand the new key structure. Three systems at Shopify required attention:

  • Schema migrations (LHM), already discussed for its deadlock implications.
  • Ghostferry, Shopify's live data migration system for moving shards across MySQL instances, needed a new configuration option to paginate on an alternate column instead of assuming a single primary key.
  • Data warehousing extraction, both bulk and incremental, which Shopify maintains in-house. Similar proprietary pipelines elsewhere would need to handle composite keys as well.

When It Helps—and When It Hurts

Results vary by table. The deciding factor is whether your data access patterns align with the clustering key. For a sharded application like Shopify's, clustering by shop_id was frequently ideal. A blog platform might choose (blog_id, blog_post_id) because typical queries fetch posts for one blog at a time.

Shopify also found cases where composite keys yielded no benefit. Some data was already written in the same order as it was read, such as associated records created together within a single transaction. In those rows, insertion order and query order coincide, so a composite key adds nothing.

For the orders table, where the access pattern was predominantly single-shop queries, the measured improvements were substantial:

  • Queries that consumed the most database capacity ran 5-6x faster.
  • The most frequently run query on the table saw median buffer pool reads drop from 1.8 to 1.2 per query.
  • Tail latency improved sharply: the slow-query log recorded an 80% reduction in distinct queries tied to that table.
  • Improvement varied unpredictably by query type. One particularly inefficient query ran 500x faster, while join-heavy queries gained little because related tables still lacked similar clustering.
  • Aggregate elapsed query time per shard dropped by roughly one hour per day.

The tradeoff warrants emphasis: composite keys degrade insert performance. A simple auto-incrementing key writes into a single hot page at the end of the table. The composite key scatters new rows across many pages, each of which must be read from and flushed back to disk. Shopify measured roughly 10x slower inserts on the changed table. Most data is read or updated more often than inserted, so this tradeoff favors composite keys in typical workloads, but insert-heavy tables deserve scrutiny before adopting this pattern.

Why Rails Teams Should Consider Composite Keys

Data clustering and composite primary keys are proven techniques in database design, yet the Rails ecosystem's strong conventions around single-column integer primary keys mean many applications miss out on their benefits. For large Rails applications where database performance or capacity are pressing concerns, evaluating a move to composite primary keys in selected tables is worth the effort.

Shopify's experience illustrates both the cost and the payoff. The initial introduction of composite keys was expensive because of the complexity of their data infrastructure. Once that foundational work was complete, however, adding further composite keys became a small incremental effort. The result was measurable gains in query performance and overall database capacity in one of the largest and longest-running Rails applications in production.

The Core Trade-Off

The decision hinges on a simple question: does the benefit of clustered data access outweigh the cost of breaking Rails' default assumptions? Composite primary keys change how Rails identifies records, how associations are built, and how migrations are written. The framework's tooling and generators assume a single id column, so teams must either build custom infrastructure or adopt libraries that fill the gap.

For Shopify, the upfront cost was justified by the scale of the problem. With tables holding billions of rows, the ability to cluster related data together on disk reduced the number of blocks read per query and shrank the overall storage footprint. That reduction in I/O translated directly into faster queries and better use of available database capacity.

Practical Considerations

  • Composite primary keys require a deliberate schema design phase; they are not a drop-in replacement for existing single-key tables.
  • Associations and finder methods must be adapted to handle multi-column keys, which often means custom code or a support gem.
  • Migrations need explicit definitions for each key column, and the database must support composite indexes natively.
  • Applications with a large existing data layer will face migration complexity that should be planned for well in advance.

Teams just starting a greenfield project have a distinct advantage: the cost of introducing composite keys from day one is far lower than retrofitting them later. For established applications, the analysis should focus on whether specific tables have access patterns that would benefit from clustering, rather than applying composite keys broadly across the schema.