Data Store Selection and Usage

5/12/2026

This post details the migration of Shopify's inventory reservation system from Redis to MySQL. Key technical contributions include: implementing a one-row-per-unit design in MySQL, utilizing `SKIP LOCKED` for concurrent reservations, establishing a bounded pool of 1,000 reservation units per item/location to manage contention and query performance, switching transaction isolation to `READ COMMITTED` to avoid gap locks during replenishment, standardizing lock acquisition order between reserve and claim operations to prevent deadlocks, and batching reservation queries using `UNION ALL` for reduced round trips. The post also highlights the discovery of connection exhaustion as the primary bottleneck, addressed by implementing per-caller connection usage tagging and aggregation, and optimizing MySQL configuration (InnoDB thread concurrency).

8/14/2024

This post details how Shopify mitigated deadlocks in high concurrency environments by introducing composite primary keys in MySQL. By changing the primary key to include the `account` column alongside the existing `id`, data is physically ordered on disk by account, compartmentalizing gap locks required by concurrent processes and preventing overlaps that caused deadlocks. This solution was implemented at the database layer without application logic changes, significantly increasing import job throughput.

10/4/2022

This post details an investigation into the safety of adding NOT NULL columns to MySQL tables using the Large Hadron Migrator (LHM) gem. It outlines the LHM shadow-table mechanism and defines schema change safety based on backward compatibility and data loss prevention. The investigation focuses on the impact of DEFAULT values, UNIQUE indexes, and MySQL's strict/non-strict modes on the safety of adding NOT NULL columns. A matrix of results is presented, concluding that adding NOT NULL columns without DEFAULT values is risky, and UNIQUE indexes require extreme caution due to potential data loss.

12/21/2021

This post introduces Shopify's unique Data Science Hierarchy of Needs, which prioritizes impact and a tool-agnostic approach over specific technologies. It details five levels: Collect & Model, Describe, Predict/Infer, Prescribe, and Influence. The post emphasizes starting with simple solutions before moving to more complex ones and uses the COVID-19 pandemic impact analysis as a case study to illustrate how this philosophy enabled rapid insights and informed critical business decisions, such as extending free trials, expanding funding programs, and enhancing shipping options. It highlights that advanced techniques like AI/ML were not always necessary for significant impact.

12/2/2021

This post details the process of upgrading Shopify's MySQL instances from Percona Server 5.7.21 to 5.7.32. It describes the development of the Declarative Database Topologies project, an extension of the Mason self-healing service, to automate the upgrade of thousands of VM-based database shards. The post highlights the discovery and resolution of a critical rollback issue where downgrading from 5.7.32 to 5.7.21 caused unexpected table rebuilds due to changes in `innodb_index_stats` and `innodb_table_stats` column lengths and the interaction of `ALTER TABLE` commands with `mysql_upgrade`. This involved patching Percona Server's `mysql_system_tables_fix.sql` and debugging the MySQL server source code to identify the root cause of the incorrect index change detection.

10/21/2021

This post details the investigation and resolution of a performance issue impacting MySQL instances running on Shopify's KateSQL platform, which is built on Google Cloud's Kubernetes Engine (GKE). The issue manifested as slow Pod creation times and degraded query performance. The investigation involved deep dives into Kubernetes Pod startup, MySQL initialization, and Linux kernel memory cgroup behavior. Key findings included a bug in the Linux kernel's `mem_cgroup_commit_charge` function, exacerbated by a potential cgroup leak, leading to slow memory allocations. The post outlines immediate mitigation strategies (node replacement), further investigation steps (resource analysis, strace, perf captures), the breakthrough discovery of the cgroup issue, and the eventual fix through GKE version upgrades (to 1.18) which included kernel patches addressing the bug.

9/24/2021

This post details the shard balancing process for MySQL database shards. It explains the architecture of Shopify's podded infrastructure where each pod contains a MySQL shard. The strategy for rebalancing involves identifying which shops to move based on historical utilization data and then executing the move using Ghostferry. Ghostferry's process involves batch copying data using `SELECT...FOR UPDATE` to maintain data integrity and tailing MySQL's binary log to replicate ongoing changes to the target shard. The cutover phase involves updating the control plane to direct traffic to the new shard and cleaning up stale data.

7/9/2021

This post details the optimization of Shopify's Trino query execution infrastructure by addressing issues related to JVM settings, CPU and memory allocation, and workload-specific cluster configurations. This includes implementing workload-specific clusters for ad-hoc, scheduled, and experimental queries, reducing coordinator lock contention by setting a hard concurrency limit, and tuning JVM settings and resource allocation to improve cluster throughput and query latency.

6/10/2021

Introduced a schematization system for analytical events, defining structure, ownership, and privacy context (data controller, data subject, PII type). Implemented obfuscation (masking/generalizing PII like IP addresses and user agents) and tokenization (exchanging PII with consistent, random tokens mapped in a secure vault) as PII transformation techniques. The tokenization vault service ensures consistency and multi-controller properties for tokens, enabling efficient PII deletion by removing mappings rather than directly manipulating large datasets.

4/28/2021

This post introduces a five-step guide for conducting Exploratory Data Analysis (EDA) for data scientists. It covers understanding data context, checking for missing data and imputation strategies, classifying features into continuous, discrete, and categorical types, and identifying the shape and distribution of data through visualizations and statistical measures like mean and variance. The post uses a synthetic dataset of merchant behavior to illustrate these steps, providing practical examples and encouraging note-taking throughout the process.

3/12/2021

This post details the limitations of Shopify's existing batch-query-based data extraction service, Longboat, including its inability to capture hard deletes, reliance on the `updated_at` field, missing intermediate row states, consistency issues, and missed records. It also explains why simply increasing Longboat's query frequency is not feasible due to performance impacts on source datastores. The post sets the stage for a new unified data extraction pipeline that leverages Change Data Capture (CDC) to address these challenges and improve data freshness and accuracy.

2/22/2021

Introduces a solution for monotonic read consistency in database replicas by modifying ProxySQL. This involves using a `consistent_read_id` in query comments to hash and deterministically select a replica for a series of related reads, ensuring a coherent data timeline. Addresses two key issues: handling server unavailability by indexing into the full list of configured servers and then disqualifying unavailable ones, and disabling ProxySQL's secondary load balancing for consistent read requests to prevent overriding the hash-based selection.

12/8/2020

This post details the approach taken by the Data Platform Engineering (DPE) team to scale Shopify's data platform for high-volume events like BFCM. It outlines a 5-step process: 1. Identifying primary objectives of services (e.g., data freshness SLO for ingestion, latency SLO for query serving). 2. Pinpointing service knobs and levers (e.g., job frequency, overlap interval, memory/CPU requests for Longboat; BigTable node pool size, ProxySQL connection pool/queue size for Reportify). 3. Running load tests to simulate extreme throughput and identify constraints. 4. Confirming and updating mitigation strategies with actionable alerts and clear instructions, assuming recovery by someone sleep-deprived. 5. Turning the knobs back and making informed decisions about scaling infrastructure or planning resiliency projects. The post also mentions the tiered services taxonomy (Tier 1-4) used for prioritizing reliability and infrastructure budgets.

10/29/2020

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.

8/27/2020

This post introduces and details the implementation of Type 2 dimensional models for tracking historical state in data, specifically for use cases like tracking user language preferences over time. It contrasts Type 1 (current state) and Type 2 (historical state) models, outlines three options for implementing Type 2 models (modifying core application design, stitching database snapshots, and adding database event logging), and provides a detailed PySpark implementation recipe for building Type 2 dimensions from event logs. The post also discusses the use of dbt for data modeling and the challenges and trade-offs associated with each implementation option.

7/21/2020

This post introduces Artifact, Shopify's data discovery and management tool, built to address challenges in data curation, governance, and accessibility. It details the tool's user experience, including its search and browse capabilities, data asset details page with lineage visualization powered by a graph database, and its high-level architecture involving metadata ingestion, Elasticsearch indexing, and GraphQL APIs. The post also discusses trade-offs made during development, such as choosing to build over buying an existing solution and opting for a metadata pull strategy over a push strategy.

6/18/2020

This post introduces foundational approaches to data warehousing and analysis at Shopify. It details the adoption of dimensional modeling (Kimball methodology) for data schemas, a unified data modeling platform built on Spark within a single GitHub repo, and open access to modelled data via a Presto cluster. The post also highlights rigorous ETL processes with unit testing, centralized vetted dashboards, reproducible vetted data points, a culture of peer review for all data work, deep product understanding within specialized data teams, effective communication of insights with recommendations, collaboration across data teams, and a positive philosophy about data's impact. The core technical contributions lie in establishing standardized data modeling practices, ensuring data consistency and accessibility, and implementing robust data pipeline testing and validation.

10/15/2019

This post provides a foundational overview of five common data store types: Relational Databases (MySQL), Non-relational Databases (Document Stores like MongoDB, Wide Column Stores like Cassandra/Bigtable), Key-Value Stores (Redis, Memcached), Full-Text Search Engines (Elasticsearch), and Message Queues (Kafka). It details their characteristics, typical use cases, and specific examples of their usage or consideration at Shopify. The post emphasizes the importance of choosing the right data store based on data structure, persistence needs, and performance requirements.

12/14/2018

This post details the use of the `job-iteration` library to build a Rails background job for deleting a large volume of historical records from MySQL. It explains the enumerator/iterator pattern, the use of an internal throttling enumerator to prevent database overload and replication lag, and the importance of proper indexing and cursors for efficient batch deletion. The post also quantifies the impact of the deletion by showing a 3x reduction in database page scans and highlights the cost savings and resilience improvements achieved.

8/8/2018

This post details the evolution of Shopify's data architecture from sharding to a pod-based system, where each pod has isolated datastores (MySQL, Redis, Memcached). This change was driven by the need to eliminate single points of failure, such as the "Redismageddon" incident caused by a shared Redis instance. The adoption of Docker, Kubernetes, and GKE is highlighted for orchestrating these pods. The post also briefly mentions the evolution of the client-side stack to React and TypeScript, and the CI/deployment infrastructure (BuildKite, ShipIt).

7/28/2011

This post introduces the `statsd-instrument` gem, a Ruby client for StatsD developed and used at Shopify. It details how StatsD is used for high-level overviews of custom data (e.g., cache hits/misses) and for proving assumptions about system performance. The post provides examples of using metaprogramming methods to inject instrumentation for parsing/rendering Liquid templates, updating Google Base products, and performing Webhook jobs. It also shows direct usage for incrementing storefront request counts and cache hit/miss metrics.