Auto Trader: Charting the road from Oracle to PostgreSQL

Transforming mindset with migration

Our long-term goal is to move away from consolidated database architecture where services have to share a database engine. A service should only be able to access its own data stores, and not the databases of other services. All other access should be through a service layer.

Auto Trader was well-positioned for migration, with over 60% of our services already “cloud native,” running on our private cloud prior to moving to GKE clusters. The remaining services were re-engineered for the cloud, removing dependencies on local stateful storage and ensuring horizontal scalability. We have a clear set of rules around how services of any tier or criticality need to run in our cloud environment. Thus far, we have 14 MySQL-backed instances supporting 63 services and 11 PostgreSQL instances running 17 services. These instances support our critical Vehicle Data Service, which contains details on every vehicle and power our inventory service. What’s impressive is that we’ve seen strong performance improvements since we migrated. We also recently migrated our registration and single sign-on service to Postgres with very little fuss or drama, and have since scaled resources on the Cloud SQL instance for this service with ease within a five-minute window.

As part of this migration, we’re also trying to change behavior for our users. We restrict direct programmatic access for anything other than the owning service to the Cloud SQL databases to help avoid unknown external dependencies, something which has caused us pain historically while on Oracle.

Instead, we now facilitate access to data through Google’s data cloud, which is centered around moving data from operational data stores, usually in Cloud SQL databases, using Kafka as the stream processing framework to land data in BigQuery, Google Cloud’s enterprise data warehouse. The source data stored in BigQuery is then processed using a tool called dbt (data build tool) to clean and join to other useful datasets and stored back into BigQuery. Looker, which is our business intelligence (BI) tool, is then connected to BigQuery to allow colleagues to explore, analyse and share business insights.

Cloud SQL delivers speed, freedom, and innovation

Moving to Cloud SQL has significantly impacted the way our teams work and has helped us create a seamless development experience.

For instance, it has removed the burden of maintenance from our team. We used to schedule maintenance outside of business hours, which would take away the database engineer for days at a time. Adding memory and CPU and generally scaling up instances has become a non-event and allows us to move at a much faster pace from the point of decision making to actioning. Cloud SQL is much easier to manage and the team no longer needs to worry about spending hours on maintenance patches, which has improved overall team productivity.