How to Introduce Composite Primary Keys in Rails - Shopify

This post details the implementation of composite primary keys in Shopify's core Rails application to improve database efficiency. It explains the problem of data interleaving with simple auto-incrementing IDs in a multi-tenant architecture, the benefits of composite keys (e.g., `(shop_id, order_id)`) for data clustering and reduced buffer pool reads, and the implementation challenges in Active Record and direct data infrastructure access (migrations, Ghostferry, data warehousing). The results show significant query time improvements (5-6x for common queries, 500x for egregious ones) and reduced buffer pool reads, with a notable downside of a 10x performance degradation on inserts.