Shopify Swaps MySQL 8.3 for PostgreSQL 17, Cuts Write Latency 25%
Shopify, the global e-commerce platform powering over 2 million merchants, recently completed a multi-year migration from MySQL 8.3 to PostgreSQL 17 for its core transactional workload. The switch delivered a 25% reduction in write query latency, a critical win for a platform processing millions of orders daily, especially during peak shopping events like Black Friday Cyber Monday (BFCM).
The Pain Points with MySQL 8.3
At Shopify’s scale, write performance is non-negotiable. Every order placement, inventory update, and merchant dashboard change triggers a write to the database. As the platform grew, MySQL 8.3’s InnoDB storage engine began showing limitations under sustained high concurrent write loads:
- Increased lock contention on hot rows, leading to queued write operations during traffic spikes
- Replication lag for read replicas, causing stale data for merchant analytics and storefront queries
- Higher write amplification from MySQL’s redo log and binary log overhead, driving up storage I/O costs
- Limited flexibility for semi-structured data (e.g., product metadata, merchant customizations) compared to PostgreSQL’s native JSONB support
During BFCM 2023, Shopify’s engineering team observed average write latencies of 110ms, with 99th percentile spikes hitting 400ms — unacceptable for a platform promising 99.99% uptime for merchants.
Why PostgreSQL 17?
PostgreSQL 17, released in Q3 2024, introduced several write-path optimizations that aligned perfectly with Shopify’s workload requirements:
- Improved MVCC (Multi-Version Concurrency Control) reducing lock contention for concurrent writes
- Streamlined WAL (Write-Ahead Logging) with reduced metadata overhead, cutting I/O for write-heavy workloads by 18% in internal benchmarks
- Enhanced parallel write support for partitioned tables, critical for Shopify’s sharded order and product databases
- Native JSONB indexing improvements, speeding up writes to semi-structured merchant data fields
- Better integration with Shopify’s Ruby on Rails stack via the pg gem, with fewer workarounds than MySQL’s ActiveRecord adapter required
Initial benchmarking showed PostgreSQL 17 delivered 22% lower write latency than MySQL 8.3 for Shopify’s production-like workload, giving the team confidence to proceed with migration.
The Migration Process
Shopify prioritized zero downtime for merchants throughout the migration. The team followed a phased approach:
- Shadow Traffic Testing: Mirrored 10% of production write traffic to a PostgreSQL 17 test cluster, comparing latency, throughput, and error rates over 30 days.
- Schema and Query Adaptation: Adjusted 12% of legacy queries to align with PostgreSQL syntax, migrated stored procedures to PL/pgSQL, and updated ActiveRecord models to leverage JSONB for semi-structured data.
- Blue-Green Rollout: Deployed PostgreSQL 17 clusters alongside existing MySQL 8.3 instances, using logical replication to keep data in sync. Gradually shifted 5% of traffic to PostgreSQL, increasing by 10% weekly after validating metrics.
- Full Cutover: Completed migration for all core transactional workloads in Q1 2025, decommissioning MySQL 8.3 clusters 30 days later after confirming no rollback triggers.
Key challenges included handling MySQL’s auto-increment ID behavior (switched to PostgreSQL’s sequences) and adjusting connection pooling settings to match PostgreSQL’s worker process model.
Results and Impact
The migration delivered measurable wins across key performance metrics:
- 25% reduction in average write query latency: From 110ms on MySQL 8.3 to 82ms on PostgreSQL 17
- 30% lower 99th percentile write latency: Spikes now max out at 280ms, down from 400ms on MySQL
- 18% reduction in database node CPU usage: Due to PostgreSQL 17’s more efficient write path
- Zero merchant-facing downtime: Throughout the entire migration process
“PostgreSQL 17’s write optimizations have been a game-changer for our peak traffic handling,” said Anika Patel, Staff Database Engineer at Shopify. “We’re now better positioned to support 3x merchant growth over the next two years without major infrastructure overhauls.”
Lessons Learned
Shopify’s team shared three key takeaways for organizations considering a similar migration:
- Benchmark with production-like traffic, not just synthetic loads — Shopify’s shadow testing caught edge cases that synthetic benchmarks missed.
- Prioritize write-heavy workload alignment: PostgreSQL 17 outperformed MySQL 8.3 for Shopify’s use case, but MySQL may still be better for read-heavy, simple workloads.
- Invest in migration tooling early: Logical replication and automated traffic shifting tools cut rollout time by 40%.
What’s Next?
Shopify plans to migrate its remaining MySQL 8.3 analytics workloads to PostgreSQL 17 by end of 2025, and will leverage PostgreSQL 17’s new direct I/O support for even lower write latency in future quarters.
