---
title: "How to Introduce Composite Primary Keys in Rails"
description: "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."
---

# How to Introduce Composite Primary Keys in Rails

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · Oct 29, 2020

**Type:** Tutorial

## 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.

## Context

Shopify’s multi-tenant database stored records from many shops interleaved by auto-incrementing primary keys, while most queries accessed one shop at a time. This caused inefficient page loading and made database performance and capacity important concerns.

## Approach / What changed

Use composite primary keys such as \[shop\_id, order\_id\] to cluster records by shop, retain an auto-incrementing id as a secondary key, and configure Active Record to treat id as the primary key. Adapt migration, live data migration, and data extraction infrastructure to support the changed schema.

## Takeaways

- 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.
- 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.
- Composite primary keys improved read performance but made inserts roughly 10x slower because more database pages needed to be read and flushed.

**Tags:** [Architecture](https://yomu.fyi/topic/architecture), [MySQL](https://yomu.fyi/topic/mysql), [Performance](https://yomu.fyi/topic/performance), [Rails](https://yomu.fyi/topic/rails)

- Source: [Shopify](https://shopify.engineering/how-to-introduce-composite-primary-keys-in-rails)
- Source URL: https://shopify.engineering/how-to-introduce-composite-primary-keys-in-rails
- Ingested by Yomu: 2026-08-31T01:11:19.020Z

[Read original post](https://shopify.engineering/how-to-introduce-composite-primary-keys-in-rails)