How to Introduce Composite Primary Keys in Rails

Summary

Shopify addressed inefficient data access in its multi-tenant Rails application, where auto-incrementing primary keys interleaved records from many shops even though queries usually targeted one shop. InnoDB stores rows in B+ trees and loads pages into a buffer pool, so the team changed orders and other suitable tables to composite primary keys such as [shop_id, order_id] while keeping an auto-incrementing id secondary key and configuring Active Record to use id. Supporting Ghostferry, migration tooling, and data extraction systems also had to accommodate the schema change, with unique secondary constraints avoided in some cases because LHM migrations could deadlock. On the most queried table, common queries improved 5–6x, median buffer pool reads fell from 1.8 to 1.2, and elapsed database time dropped roughly one hour per day per shard, while inserts became about 10x slower.

Takeaways

1. 1

   Composite clustering produced 5–6x faster common queries on the most queried table and reduced median MySQL buffer pool reads from 1.8 to 1.2 per query.

2. 2

   Rails application assumptions about integer primary keys could be preserved by retaining id as an auto-incrementing secondary key and setting self.primary_key = :id.

3. 3

   Composite primary keys improved read performance but made inserts roughly 10x slower because more database pages needed to be read and flushed.