PostgreSQL scaling limitations

Squarespace historically used PostgreSQL as their primary relational database. As the company expanded their presence globally, the team realized it was going to need a solution that could scale across regions, handle sudden growth (i.e. workload spikes), and manage giant tables just via vertical scaling. 

They were running mostly on-premise in their own private data centers, requiring them to think critically about how to manage disk space, power consumption, and even planning for natural disasters (hurricanes, freezes, power outages). The team knew that moving to the cloud would help alleviate some of these issues, but they were still worried about how much I/O a single machine could handle.

The team was spending a significant amount of time managing PostgreSQL. A lot of auxiliary systems were required to make PostgreSQL work for their use cases and they needed to handle replication, backups, monitoring, and interconnectivity themselves. Upgrading was a daunting process for them, but staying on the same version wasn’t feasible. 

Gavin McQuillan, a Senior Staff Software Engineer, works on the team that is in charge of the stateful systems at Squarespace, especially for the customer-facing processes and services. Along with his team, they wanted to develop a scalable system that was easy to manage, and would allow them to achieve better global reach and provide better responsiveness to users.  

This meant switching to a new database that could deliver: 

• Built-in horizontal scale 

• PostgreSQL compatibility

• Online operations (upgrades, schema changes, etc.)  

• High availability with desirable RTO

• Multi-region deployment 

Gavin heard about CockroachDB when it was first introduced (and was familiar with the Spanner whitepaper), and thought it was an impressive base to build a relational data system on top of. He followed the distributed database’s progress over the past 10+ years and saw that it was maturing really well and had a great team to support it.

Evaluating solutions

The team decided to move forward with testing CockroachDB, and they also did a side-by-side comparison of a managed version of PostgreSQL. However, some of the challenges they were trying to overcome (i.e. no downtime for maintenance) still wouldn’t be possible. Plus, the level of effort it would take to make managed PostgreSQL work for their use case was significantly higher than what it would be with CockroachDB as illustrated below:

Overall, the Squarespace team was impressed with the maturity and durability of CockroachDB, commenting that it’s “a database that definitely records your records and doesn’t lie to you about it.” The Squarespace team also liked that CockroachDB presents itself as a PostgreSQL cluster, but with a lot of topological configurations, so you can keep scaling separate from application logic. 

When it came to global performance, they looked closely at what exactly multi-regionality means in CockroachDB, how reads and writes work, and how the system handles the data distribution. Gavin mentioned that one of the critical differences between CockroachDB and many other systems is that CockroachDB has a metadata layer that is broken up into ranges and can be distributed into multiple regions which would be very beneficial to many of their use cases.

Executing a migration

After load testing CockroachDB, they felt confident in moving forward with the migration. Most of Squarespace’s services are microservices written in Java and most were backed by PostgreSQL. It was very important to the team to avoid extensive downtime while migrating services from PostgreSQL to CockroachDB. 

The Squarespace infrastructure team worked with Cockroach Labs to ensure they had a solid plan in place before executing the migration. They were focusing on staging and production environments which are the most sensitive to downtime. In total, they migrated 50 clusters across two environments which resulted in around 100 cutovers. Here’s a visual example of how it worked:

Phase 1: Sync data

They began with a schema dump, and then via Change Data Capture (using Debezium) they streamed the data out of PostgreSQL. They started with a snapshot, and continued with a real-time replication stream into Kafka. They also configured custom queue processors that take the Debezium data stream from Kafka, and write it into CockroachDB, with heavy batching and retries. For speed, they paused the foreign key constraints, but turned them back on before moving traffic over.

Phase 2: Verify data

Once the CDC streams caught up, they started running data validation to ensure that the data in PostgreSQL and the data in CockroachDB, were exactly the same.

Phase 3: Install PGProxy 

Next they moved all the clients for a given service to using the managed proxy. If the HAProxy lives in Kubernetes near applications, it can pivot between sending traffic for a given endpoint to either PostgreSQL or CockroachDB using automation. This helped with achieving their goal of  having as atomic of a cutover as possible.

Phase 4: Follow checklist

During this phase the team validated that the data sync is good, and corresponds 1:1 with PostgreSQL continually. They also made sure the applications worked with the new database which meant testing queries, setting up load tests, etc. While a majority (80%) of the applications didn’t require any app-level changes, some needed to be refactored to work with a distributed system. 

Phase 5: Cut over

Finally they performed the cutover and observed. Usually within a few minutes they had a good sense of how well the system is performing. However, they observe for a few hours before making a call about whether to fall back to PostgreSQL and try again after making adjustments. Once the cutover happens, they set up jobs that read from CockroachDB’s changefeed to stream any mutations from CockroachDB into a separate set of topics. They turn this on just after migrating writes to CockroachDB to prevent write cycles. They use this data in case they decide to cut back to PostgreSQL, so they can preserve any writes written to CockroachDB in the meantime.

The process outlined above is for a single cutover which they performed twice for each application that was migrated. For other “lower” environments, they use CockroachDB’s MOLT tooling to help assist with a faster migration process offline.