GCP – Auto Trader: Charting the road from Oracle to PostgreSQL

Editor’s note: We’re hearing today from Auto Trader, UK’s largest online automotive marketplace. Auto Trader aims to improve the process of buying and selling vehicles in the UK, providing a platform for consumers to connect with retailers and manufacturers. Here’s how Auto Trader used Google’s Cloud SQL on their database migration journey from on-premises into the cloud. 

You could say innovation is in our DNA at Auto Trader—we’ve spent nearly 40 years growing and evolving our business solutions alongside our customers. Established as a print magazine in 1977, Auto Trader went completely digital in 2013 and has since become one of the leading digital brands in the UK. Currently, Auto Trader brings in around 50 million cross-platform impressions a month and holds a 75% share of all minutes spent on automotive classified platforms. 

As we’ve grown, we found ourselves needing to move faster. Over the years we had invested a lot in our on-premises infrastructure and as we started shifting to the cloud we had made significant strides in ensuring our estate was cloud-native. Still, there were capabilities that were becoming increasingly difficult to realize without a significant overhaul. In 2018, we decided to move to Google Cloud, adopting Google Kubernetes Engine (GKE) as we felt we could unlock some of our development goals faster by leveraging these solutions. For our team, this meant focusing more on the services and databases rather than the day-to-day building and management of the infrastructure. 

From proprietary to open source

Historically, we had a massive on-premises Oracle database where we consolidated all of our services—around 200 in total. While this worked well for monolithic application development, it became clear we needed to break up these large databases into smaller chunks, more tightly integrated with their owning service. Our long-term vision has always been to be more database-agnostic to avoid getting locked into a single vendor. As a result, we already had a very low PL/SQL footprint. Google Cloud SQL was a natural fit for us and now sits at the heart of our data store strategy.

Cloud SQL’s fully managed services took away the headache of database maintenance that would typically take up a lot of our energy. We could rely on Google to deal with the behind-the-scenes management of upgrades, backups, patches, or failures, enabling our data engineers to invest more time in learning and performance tuning.

To date, we have migrated around 65% of our Oracle footprint to Cloud SQL, with approximately 2TB (13% of pre-migration size) of data still left to shift across several services. Migrating and moving off of our Oracle footprint remains a strategic priority for us in 2021.

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.

Cloud SQL has also changed how we provision new instances for developers. Before we migrated, we couldn’t even offer developers the option of having their own instance. They had to use a consolidated instance, regardless of what else was running on it. Now, we can provision a new dedicated instance in under an hour with a couple of lines of Terraform code. Developers have their own space and freedom to work without the risk of impacting other services. We are also able to troubleshoot issues more quickly and can even link developers directly to our Grafana dashboard to give them better visibility.

To find out more about how Auto Trader improved visibility, agility, and security by migrating to Google Cloud, make sure to read the Auto Trader UK case study.