How to Introduce Composite Primary Keys in Rails - Shopify

What this post added

This post details the implementation of composite primary keys in Shopify's main operational Rails application database. It explains the problem of data interleaving with simple auto-incrementing integer primary keys in a multi-tenant architecture, leading to inefficient data access. The solution involves changing the primary key to a composite key (e.g., `(shop_id, id)`), which clusters data by shop. The post covers the challenges and solutions for adapting the Rails application layer (using `self.primary_key = :id`), database migration infrastructure (LHM's deadlock issues with unique constraints), live data migration (Ghostferry's support for alternate pagination columns), and data warehousing extraction. It also presents performance results, showing significant improvements in query elapsed time and tail latency, alongside a notable degradation in insert performance.