Horizontally scaling the Rails backend of Shop app with Vitess - Shopify

When a Single MySQL Stops Being Enough

Shop app’s launch brought rapid user growth, and with it, mounting pressure on a Ruby on Rails backend backed by a single MySQL database (Shopify’s managed KateSQL system). Early scaling efforts—horizontally scaling background jobs, caching, and a message bus—helped, but the primary datastore remained the critical bottleneck.

The team first split the primary database by moving groups of large tables onto separate databases using GhostFerry. Rails’ built-in support for multiple databases made this straightforward, and new databases were created for separate domains, which also limited the blast radius when one domain had issues.

That approach worked until the database hit new limits. Disk usage grew to many terabytes, schema migrations took weeks, and background jobs were throttled when the database was busy. Further splitting was no longer viable—it would require cross-database transactions and more application-layer complexity. Incremental enhancements had run their course.

Introducing Vitess

Vitess is a database clustering system for horizontal scaling of MySQL. The team chose it to shard the primary database because it allows the application to treat the sharded database as a single logical MySQL instance, while internally partitioning data across multiple MySQL nodes.

The deployment model aligns with Shopify’s Kubernetes infrastructure: each shard runs as its own StatefulSet, with Vitess components handling routing and query execution. Vitess supports both vertical sharding—splitting tables across databases—and horizontal sharding, where rows of a single table are distributed across shards by a sharding key.

Sharding Keys in a Rails Schema

A fundamental requirement for horizontal sharding is choosing a sharding key. Rails defaults to integer primary keys, which cannot be used as sharding keys for the main tables in the Shop app because the application does not always know the primary key of a record before it is created.

The team’s solution was to introduce universally unique identifiers (UUIDs) as sharding keys for new databases. The application generates a UUID before inserting a record, so the sharding key is available to Vitess at insert time. The Rails schema for new Vitess-sharded tables therefore includes a uuid column as the sharding key, while the primary key remains an integer.

From a Single Database to Vitess

Migrating from a single MySQL database to Vitess began with a proof of concept: two distinct problem tables in the app were moved to a Vitess-managed database. This validated the approach before wider rollout.

The process had several phases. First, new tables were created directly in Vitess. Then existing data was migrated. Rather than attempting live migration, the team used a dual-write strategy: the application wrote to both MySQL and Vitess during a transition period, backfilled historical data, and then switched reads over to Vitess after verifying consistency.

After the team ensured all reads and writes were hitting Vitess, the MySQL instance was retired for those tables. The team notes this worked well because the two tables in the proof of concept were well understood and stable.

Schema Migrations and Tooling

Rails schema migrations assume a single database. With Vitess, the team had to address this by making migrations idempotent and reversible, and by running them through the Vitess workflow rather than expecting them to run natively on the underlying MySQL instances.

One issue involved the schema_migrations table used by Rails: with multiple databases, the team had to verify that Rails was not attempting to write to the Vitess-sharded schema during application boot. Similarly, the application’s use of database connection pools needed adjustment to work correctly with Vitess’ pooling behavior.

Unique Constraints in a Sharded Environment

Because sharding distributes data by sharding key, the database can no longer enforce uniqueness across shards for a column that is not the sharding key. The team found that this manifests primarily in two ways.

• Global unique indexes: unique indexes on columns other than the UUID were dropped. Uniqueness must now be enforced in the application layer.
• Join tables: for tables that associate two records (for example, a customer and a order), the sharding key was set based on which side of the association is queried most frequently.

When queries must access data across shards—for example, a query filtering on a column that is not the sharding key—Vitess processes this as a scatter query across all shards. The team noted that this is acceptable for reads, but the design goal is to minimize that pattern.

Partitioning by Logical Group

Shop app’s domain model contains entities that are naturally grouped. One of those groups is merchants—when a customer visits a store, requests touch that merchant’s data.

Adopting that pattern, if data is sharded by merchant UUID, requests involving a single merchant can be served by a single shard, which is efficient. The team instead recommends sharding by a “master” concept in the domain (such as shop or merchant) that captures the majority of queries, so that most reads and writes are localized.

However, an earlier decision from the pre-sharding era made this awkward: tables were named customers and were not clearly associated with a merchant. This meant that if the team sharded by merchant UUID, much of the data would scatter. The solution was a database migration to rename the field so that the sharding key could be used consistently across related tables.

Database requests were profiled before sharding to verify that the vast majority of queries would be single-shard after choosing the merchant UUID as the sharding key.

Results and Ongoing Work

With the proof of concept complete, the team horizontally scaled the database layer using Vitess. This removes the single-server ceiling and means the backend can continue to scale out by adding more shards. The rollout was completed with minimal impact to the app’s users, and the team’s future focus is on tuning Vitess’ behavior, including its query planner and connection behavior with Rails’ ActiveRecord connection pool.

The full engineering post includes diagrams on key milestones.

What it took to adopt Vitess

Before settling on a scaling strategy, we compared a few common paths. A multi-tenant pod architecture, which works well for Shopify’s millions of merchants, doesn’t suit Shop app, where a single user has far less impact than a large merchant. Moving frequently written data to a key-value store drops support for custom indexes and sacrifices the durability we need. Federation—splitting tables across separate MySQL instances—extends Rails’ native multi-database support but causes slow migrations on big tables, makes cross-database joins painful, and adds application-level complexity around table placement. Running on cloud-provided horizontally scaled systems was ruled out because we prefer to operate our own primary datastores.

Vitess became the clear choice. It handles sharding and resharding, coordinates online schema migrations across shards, provides connection pooling and query protection, and is mostly SQL-compatible—important for a mature Rails app.

Key Vitess concepts

• Shard: a subset of a database, consisting of one writer and multiple replicas.
• Keyspace: a logical database holding a set of tables on one or more shards; sharded keyspaces require a sharding key for each table.
• VSchema: describes keyspace and shard organization so Vitess can route queries.
• VTGate: the proxy performing query planning, transaction coordination, and routing. The query path is App → VTGate → VTTablet → MySQL.
• VTTablet: runs beside each MySQL server and handles connection pooling.

Choosing the sharding key and reshaping the data model

Since nearly every table related to a user, user_id was the natural sharding key. We moved user-owned data into a sharded users keyspace, while the rest lived in an unsharded global keyspace. Enforcing that only user-owned tables entered the sharded keyspace kept testing and reasoning simple.

The biggest prerequisite was reworking the data model. Much of the data originally belonged to an Account (tied to a User), so many tables only had account_id. Adding user_id to those tables and backfilling billions of rows while the database was already near capacity was slow. Do this early—it takes longer than expected.

Query verifiers were the backbone

We built verifiers in the application layer to catch queries incompatible with a sharded Vitess setup. They validated query correctness, routing, and data distribution, preventing errors from cross-shard queries and inconsistencies from partially committed transactions. The verifiers’ accuracy was the main factor in a successful migration.

These verifiers could model the future state of the system—for example, treating a not-yet-existing keyspace as present, or marking a keyspace as sharded before it actually was. Five verifier types covered the critical failure modes:

• Missing Sharding Key: ensures queries against sharded tables include the sharding key in the WHERE clause so Vitess routes to the correct shard.
• Cross Database Transaction: verifies all writes inside a transaction hit the same database, sharded or not, by inspecting each connection used.
• Cross Shard Transaction: confines writes within a transaction to a single shard with a matching sharding key. Vitess’s default atomicity model permits cross-shard transactions, but a commit on one shard can survive an error on another, leaving partial changes. We removed all such transactions.
• Cross Shard Write: applies the same constraint to nontransactional bulk writes like update_all() and delete_all().
• Cross Keyspace Query: flags joins between tables in different keyspaces.

Rolling out verifiers without breaking production

We introduced verifiers in stages. Unit and manual tests validated them across many query types and edge cases. A list of known offending queries let us enable verifiers for new code immediately while fixing existing issues incrementally.

Verifiers first ran in development and test environments, so any new violation failed those pipelines. We also ran a parallel CI job with Vitess as the backend alongside the normal MySQL run. Fixing violations meant adding the sharding key wherever possible and rewriting queries to be Vitess-compliant. Some transactions crossing sharded and unsharded keyspaces were split and analyzed for self-healing on partial failure. Complex cross-shard flows were rewritten; for example, an UPDATE <table> SET <sharding_key>= ? WHERE id = ? would require moving the row to another shard, so we replaced it with an insert-then-delete sequence.

Under specific conditions, query verifiers could be bypassed through dedicated “Danger” helper methods, useful for maintenance tasks spanning users or queries lacking user context. In production, verifiers initially only logged violations instead of raising exceptions. Weekly reports summarized violations by callsite to catch anything missed in tests.

Enforcing sharding key inclusion

In a sharded Vitess setup, VTGate must route each query before MySQL executes it. SELECT * FROM orders WHERE id = ? forces a scatter across all shards; adding user_id = ? lets Vitess target a single shard. Verifiers caught three common patterns needing fixes: initial data loads, association loads, and mutations.

Working with Rails 7.0, we preferred not to patch ActiveRecord, but it was temporary—future Rails composite key support would make it easier. First, we built an abstraction to identify the sharding key for any ActiveRecord object. Then an ActiveRecord patch propagated that key to update, delete, and lock/reload statements. Rails’ default has_many, belongs_to, and has_one associations generated queries Vitess couldn’t scope to a single shard, so we introduced a join_condition option to pass the key down through association queries.

Faster schema migrations and schema caches

Sharding was in part to escape migration times that stretched to weeks on our largest tables. Vitess supports multiple migration strategies—native vitess and gh-ost. Initially in Vitess V14, the native strategy was experimental. Tests on large tables hit a bug where migrations throttled for over 10 minutes were terminated. The Vitess community confirmed and fixed it. In V15, the native strategy is recommended and works reliably in production.

We built a custom UI on top of Vitess’s SQL migration management commands. For Rails schema caches (which avoid hammering the database during boots), Vitess offers no application-level migration hooks. We added a background job that triggers on migration submission, polls Vitess, and dumps the schema only when the migration is complete on all shards and no other migrations are running. This prevents reading an inconsistent schema if some shards finish before others.

Making the cluster live: rolling out VTGate

With the primary database running as a single unsharded keyspace inside a Vitess cluster, the next step was getting application traffic onto it. The existing ProxySQL path remained untouched during this phase, but the application was extended so it could also establish connections through VTGate. Because this was Shopify’s first production Vitess deployment, the rollout strategy prioritized reversibility over speed.

A dynamic connection switcher, built on an existing staged-rollout primitive, made it possible to change routing behavior at runtime. This let the team direct a controlled percentage of requests—from zero up to 100%—through VTGate while keeping the rest on the legacy ProxySQL connections. With that control, traffic could be increased gradually while monitoring for performance regressions or unexpected behavior.

The first traffic through VTGate came from the lowest-risk components in production. Background jobs, for example, were an ideal starting point because they already had built-in retry semantics if anything went wrong during processing. Once VTGate had absorbed 100% of the traffic without issue, ProxySQL was decommissioned.

The underlying mechanics of Vitessifying

Vitessifying describes the transformation of an existing MySQL deployment into a keyspace without explicit data movement. Each mysqld process gained a co-located VTTablet, configured to serve as the sole shard of a new keyspace. The two processes communicate over a Unix socket, although separate hosts with a network connection are also supported. Resource planning needs to account for the VTTablet’s CPU footprint: Vitest’s guidance is roughly one CPU for VTTablet per CPU allocated to mysqld, with VTTablet memory consumption generally low.

The new keyspace was then exposed through VTGates, making it addressable from the application layer. The entire transformation happened with no downtime and remained invisible to the running application.

Splitting tables into multiple keyspaces

After Vitessifying, all tables still lived together in one keyspace. The next phase divided them into three unsharded keyspaces according to ownership and access patterns:

• Users: all user-related data
• Global: data not owned by a single user
• Configuration: tables that are rarely written, plus the sequence tables used later in Phase 3

Running MoveTables in production

Vitess’ MoveTables workflow handles the mechanics of relocating tables between keyspaces. Before attempting anything in production, every step was rehearsed in staging—a practice that consistently paid off, surfacing real Vitess bugs and setup problems early. The rehearsal produced a detailed checklist that also documented bail-out commands for aborting the operation if anything went wrong.

Production preparation began by blocking schema migrations and disabling schema caches. The cache freeze prevented a class of errors where Rails would raise when querying tables absent from its schema cache. With the two new keyspaces created, the team relied on a precompiled list of tables to move and a “Cross Keyspace Query” verifier that flagged any future queries spanning keyspaces. In database.yml, each keyspace was configured as a separate database with its own entry.

Staging exercises had already revealed that failed or canceled operations could leave behind journal entries and artifacts capable of interfering with a subsequent run; cleanup was required before retrying. Once the production move began and table data was fully relocated, integrity was validated with Vdiffs. Manual checks covered collation and character_set consistency.

Traffic was then switched, and the operation was finalized using --keep_data and --keep_routing_rules. The source keyspace was not immediately cleaned: tables there were renamed with a _old suffix instead of dropped, because dropping large tables on MySQL 5.7 can stall the database. Routing rules were removed once the rename was complete.

Sequences and Vindexes: Prerequisites for Sharding

Before the users keyspace could be sharded, two fundamental issues had to be solved. Rails' default integer auto-increment primary keys don't work across multiple shards—IDs must be globally unique. Additionally, maintaining global uniqueness for non-sharding-key columns requires a Vitess-managed indirection layer.

Vitess provides Sequences to replace MySQL's auto_increment. A Sequence is backed by a regular MySQL table living in an unsharded keyspace. The VTTablet reserves and caches blocks of IDs from the Sequence table to reduce writes to the underlying table; in production, Shopify set this cache to 1000. The cache size is a trade-off between write throughput and the number of IDs "lost" when a VTTablet restarts.

Migrating existing tables to Sequences required a careful three-step process performed from the application layer: reading the current max ID, updating the Sequence table's next_id to that value plus a buffer, and finally updating the VSchema to route auto-increment through the Sequence.

Lookup Vindexes address two distinct concerns: enforcing uniqueness across shards and reducing cross-shard queries. A per-shard unique MySQL index only guarantees uniqueness within that shard. Lookup Vindexes are MySQL tables Vitess maintains, allowing it to verify uniqueness globally while also mapping non-sharding-key values to their shards.

Shopify standardised on the consistent_lookup_unique Vindex over the older lookup_unique type (which requires two-phase commit and is slower). The choice carries real costs, though. Writes become slower because Vitess must maintain the lookup table in addition to the main row. Test suites also slow down because consistent_lookup Vindexes restrict transactional operations, forcing some tests to be non-transactional.

Lookup Vindexes are not always required. A random collision-safe key (like a UUID) is inherently globally unique. Similarly, if a unique MySQL index includes the sharding key—for example ["user_id", "name"] on a table sharded by user_id—uniqueness is guaranteed because all rows for a given user_id live on the same shard. Shopify's philosophy was therefore to avoid Lookup Vindexes unless strictly necessary.

Shopify chose to place its required Lookup Vindexes in a separate sharded lookup keyspace. This had several benefits: it provided experience running a sharded keyspace before the real migration, it enforced that tables were sharded on the correct key, it exercised observability tooling, and it avoided making an unsharded database a bottleneck for inserts.

Resharding the users keyspace

With Sequences, Vindexes, and the lookup keyspace in place, the team moved to add more shards to the users keyspace. This was the riskiest phase. A week-long project hackathon was organised to build collective understanding of Vindexes, Sequences, and the resharding process itself. In total roughly 25 bugs surfaced—several in Vitess itself, the rest in application or internal infrastructure code.

The production migration followed the same pattern established during earlier table moves. Schema caches and migrations were disabled, and the Vitess tablet throttler was switched off due to observed issues during traffic switching in testing. New shards were created matching the source specifications, and the Reshard workflow was started. The data copy took roughly a week, with replication pausing whenever MySQL history list length (HLL) rose above healthy thresholds.

Data integrity verification via VDiff proved stressful. During one VDiff run, HLL grew to over one million within 15 minutes. Transient connection errors occurred, and eventually a VDiff against a larger table stopped the workflow—though the workflow itself recovered once VDiff was aborted. The underlying VReplication engine proved resilient: it survived both an unplanned failover and a configuration change requiring instance replacement.

The critical moment came after read traffic was switched to the new shards and then primary writes followed. Approximately one hour after the switch, reverse replication stopped with a duplicate key error:

Duplicate entry REDACTED for key 'index_name_on_table' (errno 1062) (sqlstate 23000) during query: insert into table_name(<REDACTED>) values (REDACTED)

The failure stemmed from a specific multi-step pattern — an update, then insert, then delete of rows sharing a unique column value. On a single shard, MySQL executes these serially and enforces the constraint correctly. Once sharding meant those rows could land on different shards, event ordering across shards is not guaranteed, and the insert could appear before the update, violating the uniqueness constraint. Vitess has a skew-detection feature to minimise this, but it only reduces the problem. Because no issues appeared on the new shards aside from this replication error, the decision was made to complete the reshard operation.

Post-sharding maintenance

Life after sharding brought new operational considerations. Schema migrations now complete at different times on different shards. Shopify mitigates this by requiring any newly added or removed column to also be added to Rails' ignored_columns list, ensuring queries never reference them prematurely. Migrations run with the --singleton flag to prevent concurrent migrations within a keyspace.

Additionally, MySQL auto-increment was removed from all tables via schema migration, now that Sequences handle ID generation. This eliminates a low-probability but high-impact failure where a shard could fall back to local auto-increment instead of the shared Sequence table.

Scaling beats tuning

With Vitess as the primary datastore layer for Shop's Rails backend, the team has moved out of a cycle of incremental optimizations. Schema migrations that once stretched across weeks now run in hours. Background jobs no longer need throttling when replicas lag or MySQL threads max out. When capacity is needed, the answer is adding shards rather than squeezing more from the existing infrastructure.

The trade-off is added complexity. Developers working with Vitess must learn a few extra abstractions, but the team argues the comparison is similar to understanding indexes in MySQL—a baseline requirement for building on the database. For a typical Rails-style application, that knowledge reduces to Sequences, Vindexes, and VSchemas.

• Sequences coordinate auto-incrementing primary IDs across shards. Rails defaults to auto-incrementing keys, so these are almost always necessary, and they are straightforward to implement.
• Primary Vindexes determine the sharding key, that is, how data is distributed across shards. Understanding them is essential to avoiding cross-shard transactions.
• Lookup Vindexes enforce uniqueness globally and reduce the number of cross-shard queries.
• VSchemas define keyspace organization and are mandatory for sharded keyspaces.

Lessons from the migration

The team offers several practical takeaways for anyone considering a similar move to Vitess:

• Decide on a sharding strategy earlier rather than later. Reorganizing the data model and backfilling large tables after the fact was tedious and slow. In hindsight, linters should have been set up in advance to require a non-nullable sharding key on all new tables.
• Stage everything. Vitess is powerful, but failure modes can be intimidating. The team ran two staging environments and practiced each step before production, which surfaced bugs that would have been painful in a live setting. Staging should also be populated with high-volume dummy data to surface issues early.
• Invest in query verification. The goal is to eliminate cross-keyspace and cross-shard transactions, and to minimize cross-keyspace or cross-shard queries. Keep a running list of queries and reduce it continuously, and double-check that verifiers are actually catching the right problems.

What's ahead

The immediate focus is stability and simplification. Some issues from the migration trace back to Vitess itself, others to Shopify's particular configuration, and both are being worked through. Once the application moves to Rails v7.1—which introduces native composite key support—the team expects to remove most custom patches and align with the framework's evolving approach to multi-key primary IDs.