TechPrufer
Get started
Shopify
Shopify logoShopify · Feature trail

Data Store Selection and Usage

23 posts · Jul 2011 – May 2026

What this trail covers

Shopify has evolved its data extraction and warehousing strategies to address the limitations of batch-based CDC with Longboat. This includes developing a new change-event capture system that leverages Kafka for near real-time data ingestion and streaming transformations, enabling lower latency query response times for merchant analytics. The system aims to unify data extraction pipelines, improve data freshness, and ensure data quality by capturing hard deletes and intermediate states, moving towards a more robust and scalable data infrastructure.

All Shopify trails
  1. We replaced Redis with MySQL for inventory reservations—and it scaled (2026) - ShopifyThis 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 with a bounded pool (capped at 1,000) for inventory reservations; utilizing MySQL 8's `SKIP LOCKED` feature for scalable contention management; adopting a composite primary key (`shop_id, inventory_item_id, inventory_group_id, id`) to reduce row locks; changing the transaction isolation level to `READ COMMITTED` to avoid gap locks and deadlocks; standardizing lock acquisition order across reserve and claim operations; and batching reservation queries using `UNION ALL`. The post also describes the discovery and resolution of a connection exhaustion bottleneck by implementing per-caller connection usage tracking and optimizing the checkout path, alongside re-evaluating MySQL's InnoDB thread concurrency.
  2. Mitigating Deadlocks in high concurrency environments - ShopifyThis post details the implementation of composite primary keys in MySQL to mitigate deadlocks during concurrent up-insert operations. By including the `account` column in the composite primary key alongside the existing `id`, data is physically ordered on disk by account. This compartmentalizes gap locks within each process instance, preventing overlaps and dramatically reducing deadlock occurrences without requiring application-level logic changes. A unique index on `id` was added for backward compatibility.
  3. Safely Adding NOT NULL Columns to Your Database Tables - ShopifyThis 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 in terms of backward compatibility and data loss. 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 results matrix is presented, concluding that adding NOT NULL columns without a DEFAULT value should be avoided, and caution is advised when adding UNIQUE indexes due to the risk of data loss.
  4. Double Entry Transition Tables: How We Track State Changes At Shopify - ShopifyThis post introduces and details the implementation of double-entry transition tables as a data modeling technique for tracking state changes of entities over time. It explains the concept, its advantages (additive net_change column, identifying cause of change, preserving timing, scalability), and provides a step-by-step guide on building the pipelines using PySpark. The example focuses on tracking Shopify account and Shopify Balance account statuses, demonstrating how to query active accounts and net changes over time. This technique is applied to reliably count merchants using Shopify Balance historically.
  5. Shopify’s Unique Data Science Hierarchy Of Needs - ShopifyThis 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.
  7. Upgrading MySQL at Shopify - ShopifyThis 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.
  8. Debugging Systems in the Cloud: MySQL, Kubernetes, and Cgroups - ShopifyThis 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. Shard Balancing: Moving Shops Confidently with Zero-Downtime at Terabyte-scale - ShopifyThis 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.
  10. Shopify's Path to a Faster Trino Query Execution: Infrastructure - ShopifyThis 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.
  11. Deleting the Undeletable: Managing PII at Shopify - ShopifyThis post details the development of a schematization system to enforce event schemas, including privacy context, and the implementation of obfuscation and tokenization techniques to handle Personally Identifiable Information (PII) in event data, enabling effective deletion by managing token-to-PII mappings in a secure vault. The schematization system captures event structure, ownership, and privacy context, with over 4500 active schemas. Obfuscation masks or removes identifying data while preserving analytical value, and tokenization exchanges PII with consistent random tokens stored in a separate vault service. This approach allows for efficient deletion of PII by removing mappings from the vault, rendering tokens in the data warehouse non-detokenizable.
  12. A Five-Step Guide for Conducting Exploratory Data Analysis - ShopifyThis post details a five-step guide for conducting exploratory data analysis (EDA) on merchant behavior data. It covers understanding data context, checking for missing values and their implications, classifying features into continuous, discrete, and categorical types, and identifying the shape and distribution of data through visualizations and statistical measures. The post emphasizes the value of EDA in informing data products and provides a practical framework for data scientists.
  13. Capturing Every Change From Shopify’s Sharded Monolith - ShopifyThis post details the limitations of Shopify's existing Longboat system for data extraction, including its inability to capture hard deletes, reliance on the `updated_at` field, missing intermediate row states, consistency issues, and missed records. It introduces a new approach for the Merchant Analytics Platform that uses Kafka for change-event capture and streaming transformations, enabling near real-time data ingestion and lower latency query responses, addressing the need for fresher and more comprehensive merchant analytics.
  14. Read Consistency with Database Replicas - ShopifyIntroduces 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.
  15. How to Reliably Scale Your Data Platform for High Volumes - ShopifyThis 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.
  16. How to Introduce Composite Primary Keys in Rails - ShopifyThis 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.
  17. How to Track State with Type 2 Dimensional Models - ShopifyThis 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.
  18. How We’re Solving Data Discovery Challenges at Shopify - ShopifyThis post details the development of Artifact, Shopify's internal data discovery and management tool, to address challenges in data curation, governance, and accessibility. Artifact centralizes metadata from various data sources (tables, reports, dashboards, etc.) to improve data discovery, reduce duplication of effort, and enhance data governance. It provides a search and browse interface, displays data asset details including lineage powered by a graph database, and has a generic architecture designed for future integrations. The decision to build rather than buy was made due to the unique nature of Shopify's data stack and the need for a holistic view of data discovery processes. The metadata ingestion pipeline pulls data from various sources, with a future optimization planned for metadata APIs.
  19. Shopify's Data Science & Engineering Foundations - ShopifyThis post details Shopify's foundational approaches to data warehousing and analysis, emphasizing dimensional modeling (Kimball's approach) for queryability and team compatibility, a single data modeling platform built on Spark in a shared GitHub repo, open access to modelled data on a Presto cluster, rigorous unit-tested ETL pipelines, centralized vetted dashboards and data points for reproducibility, peer review for all work, deep product understanding by sub-teams, effective communication of insights with opinions and recommendations, collaboration across data teams, and a positive philosophy about data's impact. It highlights how these foundations enable data scientists to deliver high-quality, daily insights for internal teams, merchants, and partners.
  20. Five Common Data Stores and When to Use Them - ShopifyThis post provides an 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, use cases, and Shopify's specific usage of MySQL, Bigtable, Redis, Memcached, Elasticsearch, and Kafka. The post emphasizes the durability and ACID properties of relational databases for business-critical data, the flexibility and scalability of NoSQL for large or unstructured data, the performance of in-memory key-value stores for caching, the specialized text-searching capabilities of search engines, and the reliable data transfer of message queues.
  21. Engineering a Historic Moment: Shopify Gets Ready for Cannabis in Canada - ShopifyThis post details the creation of a new regional instance of Shopify in Montreal to meet the strict requirements for the newly regulated Canadian cannabis industry. This involved building new Google Cloud Platform projects, including PCI compliant payment processing infrastructure and a regional data warehouse. The core infrastructure, running on GKE and GCE, was extended to a new region using Terraform for infrastructure declaration and configuration. Improved resiliency features like regional clusters and segregated compute node clusters were utilized to minimize the 'noisy neighbour problem'. Significant effort was also put into ensuring personal information remained within Canada by implementing multiple layers of protection at the application, host, and network/infrastructure levels, including network request verification, strict firewall rules, minimal secrets deployment, and unique SSL certificates. Load testing and game days were conducted to ensure scalability and resilience for launch day.
  22. How an Intern Released 3 Terabytes Worth of Storage Before BFCM - ShopifyThis post details the successful deletion of over 5 billion records, freeing up 3TB of storage and improving database resilience by reducing page scans by approximately 3x. This was achieved by building a Rails background job using the `job-iteration` library for chunked deletion and an internal throttling enumerator to prevent database overload and replication lag. The job utilized cursors for efficient batch fetching and deletion, ensuring minimal impact on database performance during a critical pre-BFCM period.
  23. E-Commerce at Scale: Inside Shopify's Tech Stack - Stackshare.io - ShopifyThis 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).
  24. Kafka Producer Pipeline for Ruby on Rails - ShopifyThis post details the initial implementation of a Kafka producer pipeline for Ruby on Rails applications. It describes the challenges of integrating Kafka into a Ruby/Go ecosystem, the decision to avoid direct Ruby-Kafka links due to operational concerns and lack of mature libraries, and the use of SysV message queues as a reliable buffer between Rails and a Go Kafka producer. It also covers the subsequent adaptation to Docker by introducing a TCP-based proxy to communicate with the host-level Kafka producer, avoiding per-container producers.
  25. IdentityCache: Improving Performance one Cached Model at a Time - ShopifyIntroduced IdentityCache, an open-sourced library for caching ActiveRecord models in Memcached. IdentityCache acts as a read-through cache, fetching from Memcached first and then the database on a miss, storing the result in Memcached. It automatically handles cache expiration via `after_commit` hooks and supports caching single objects, associations (`cache_has_many`, `cache_has_one`, `cache_belongs_to`), and embedding associations within parent objects. The library allows for defining custom indexes for accessing models and aims to reduce database read operations significantly, as demonstrated by performance improvements during flash sales.
  26. StatsD at Shopify - ShopifyThis 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.