Migrating in-place from PostgreSQL to MySQL
The Yelp Reservations service (yelp_res) is the service that powers reservations on Yelp. It was acquired along with Seatme in 2013, and is a Django service and webapp. It powers the reservation backend and logic for Yelp Guest Manager, our iPad app for restaurants, and handles diner and partner flows that create reservations. Along with that, it serves a web UI and backend API for our Yelp Reservations app, which has been superseded by Yelp Guest Manager but is still used by many of our restaurant customers.
This service was built using a DB-centric architecture, and uses a “DB sync” paradigm – a method where clients maintain a local database with a copy of data relevant to them – to sync data with legacy clients. It also relies on database triggers to enforce some business logic. The DB used is PostgreSQL which is not used anywhere else at Yelp, which meant that only a small rotation of long-tenured employees knew Postgres well enough to do outage response. This caused issues in maintenance, visibility, and outage response times. The teams working on the Restaurants products are not infra teams, and the Yelp-wide infra teams (understandably) focus on Yelp-standard infrastructure. As a result, when we did see issues with Postgres it was often a scramble to find people with relevant expertise.
As restaurants rely on our product to run their business, this system can’t be taken offline for maintenance, and any data loss is unacceptable—we can’t have someone make a reservation and then have it disappear. This led to much of the complexity of this project, as switching gradually between two data stores on the fly introduced new challenges. Much of the existing documentation we could find on this used toy examples or assumed a clean stop, migration, and restart, so this was also somewhat unexplored territory (hence this blog post!).
