Migration Strategy

On this page

A successful migration to CockroachDB requires planning for downtime, application changes, observability, and rollback.

This page outlines key decisions, infrastructure considerations, and best practices for a resilient and repeatable high-level migration strategy:

Develop a migration plan.
Evaluate your downtime approach.
Size the target CockroachDB cluster.
Implement application changes to address necessary schema changes, transaction contention, and unimplemented features.
Prepare for migration by running a pre-mortem, setting up metrics, loading test data, validating application queries for correctness and performance, performing a migration dry run, and reviewing your cutover strategy.

Tip:

For help migrating to CockroachDB, contact our sales team.

Develop a migration plan

Consider the following as you plan your migration:

Who will lead and perform the migration? Which teams are involved, and which aspects are they responsible for?
Which internal and external parties do you need to inform about the migration?
Which external or third-party tools (e.g., microservices, analytics, payment processors, aggregators, CRMs) must be tested and migrated along with your application?
When is the best time to perform this migration to be minimally disruptive to the database's users?
What is your target date for completing the migration?

Create a document that summarizes the intent of the migration, the technical details, and the team members involved.

Approach to downtime

It's important to fully prepare the migration in order to be certain that the migration can be completed successfully during the downtime window.

Scheduled downtime is made known to your users in advance. Once you have prepared for the migration, you take the application offline, conduct the migration, and bring the application back online on CockroachDB. To succeed, you should estimate the amount of downtime required to migrate your data, and ideally schedule the downtime outside of peak hours. Scheduling downtime is easiest if your application traffic is "periodic", meaning that it varies by the time of day, day of week, or day of month.
Unscheduled downtime impacts as few customers as possible, ideally without impacting their regular usage. If your application is intentionally offline at certain times (e.g., outside business hours), you can migrate the data without users noticing. Alternatively, if your application's functionality is not time-sensitive (e.g., it sends batched messages or emails), then you can queue requests while your system is offline, and process those requests after completing the migration to CockroachDB.
Reduced functionality takes some, but not all, application functionality offline. For example, you can disable writes but not reads while you migrate the application data, and queue data to be written after completing the migration.

The MOLT tools enable migrations with minimal downtime. Refer to Cutover strategy.

Capacity planning

Determine the size of the target CockroachDB cluster. To do this, consider your data volume and workload characteristics:

What is the total size of the data you will migrate?
How many active application connections will be running in the CockroachDB environment?

Use this information to size the CockroachDB cluster you will create. If you are migrating to a CockroachDB Cloud cluster, see Plan Your Cluster for details:

For CockroachDB Standard and Basic, your cluster will scale automatically to meet your storage and usage requirements. Refer to the CockroachDB Standard and CockroachDB Basic documentation to learn about how to limit your resource consumption.
For CockroachDB Advanced, refer to the example that shows how your data volume, storage requirements, and replication factor affect the recommended node size (number of vCPUs per node) and total number of nodes on the cluster.
For guidance on sizing for connection pools, see the CockroachDB Cloud Production Checklist.

If you are migrating to a CockroachDB self-hosted cluster:

Refer to our sizing methodology to determine the total number of vCPUs on the cluster and the number of vCPUs per node (which determines the number of nodes on the cluster).
Refer to our storage recommendations to determine the amount of storage to provision on each node.
For guidance on sizing for connection pools, see the CockroachDB self-hosted Production Checklist.

Application changes

As you develop your migration plan, consider the application changes that you will need to make. These may relate to the following:

Handling transaction contention.
Unimplemented features and syntax incompatibilities.

Schema design best practices

Follow these recommendations when converting your schema for compatibility with CockroachDB.

Define an explicit primary key on every table. For more information, refer to Primary key best practices.
Do not use a sequence to define a primary key column. Instead, Cockroach Labs recommends that you use multi-column primary keys or auto-generating unique IDs for primary key columns.
By default on CockroachDB, INT is an alias for INT8, which creates 64-bit signed integers. PostgreSQL and MySQL default to 32-bit integers. Depending on your source database or application requirements, you may need to change the integer size to 4. For more information, refer to Considerations for 64-bit signed integers.

Handling transaction contention

Optimize your queries against transaction contention. You may encounter transaction retry errors when you test application queries, as well as transaction contention due to long-running transactions when you conduct the migration and bulk load data.

Transaction retry errors are more frequent under CockroachDB's default SERIALIZABLE isolation level. If you are migrating an application that was built at a READ COMMITTED isolation level, you should first enable READ COMMITTED isolation on the CockroachDB cluster for compatibility.

Unimplemented features and syntax incompatibilities

Update your queries to resolve differences in functionality and SQL syntax.

CockroachDB supports the PostgreSQL wire protocol and is largely compatible with PostgreSQL syntax.

For full compatibility with CockroachDB, you may need to implement workarounds in your schema design, in your data manipulation language (DML), or in your application code.

For more details on the CockroachDB SQL implementation, refer to SQL Feature Support.

Prepare for migration

Once you have a migration plan, prepare the team, application, source database, and CockroachDB cluster for the migration.

Run a migration "pre-mortem"

To minimize issues after cutover, compose a migration "pre-mortem":

Clearly describe the roles and processes of each team member performing the migration.
List the likely failure points and issues that you may encounter as you conduct the migration.
Rank potential issues by severity, and identify ways to reduce risk.
Create a plan for implementing the actions that would most effectively reduce risk.

Set up monitoring and alerting

Based on the error budget you defined in your migration plan, identify the metrics that you can use to measure your success criteria and set up monitoring for the migration. These metrics may be identical to those you normally use in production, but can also be specific to your migration needs.

Load test data

It's useful to load test data into CockroachDB so that you can test your application queries. Refer to the steps in Migrate to CockroachDB in Phases.

MOLT Fetch supports both IMPORT INTO and COPY FROM for loading data into CockroachDB:

Use IMPORT INTO for maximum throughput when the target tables can be offline. For a bulk data migration, most users should use IMPORT INTO because the tables will be offline anyway, and IMPORT INTO can perform the data import much faster than COPY FROM.
Use COPY FROM (or --direct-copy) when the target must remain queryable during load.

Validate queries

After you load the test data, validate your queries on CockroachDB. You can do this by shadowing or by manually testing the queries.

Note that CockroachDB defaults to the SERIALIZABLE transaction isolation level. If you are migrating an application that was built at a READ COMMITTED isolation level on the source database, you must enable READ COMMITTED isolation on the CockroachDB cluster for compatibility.

Shadowing

You can "shadow" your production workload by executing your source SQL statements on CockroachDB in parallel. You can then validate the queries on CockroachDB for consistency, performance, and potential issues with the migration.

Test query results and performance

You can manually validate your queries by testing a subset of "critical queries" on an otherwise idle CockroachDB cluster:

Check the application logs for error messages and the API response time. If application requests are slower than expected, use the SQL Activity page on the CockroachDB Cloud Console or DB Console to find the longest-running queries that are part of that application request. If necessary, tune the queries according to our best practices for SQL performance.
Compare the results of the queries and check that they are identical in both the source database and CockroachDB. To do this, you can use MOLT Verify.

Test performance on a CockroachDB cluster that is appropriately sized for your workload:

Run the application with single- or very low-concurrency and verify the app's performance is acceptable. The cluster should be provisioned with more than enough resources to handle this workload, because you need to verify that the queries will be fast enough when there are zero resource bottlenecks.
Run stress tests with at least the production concurrency and rate, but ideally higher in order to verify that the system can handle unexpected spikes in load. This can also uncover contention issues that will appear during spikes in app load, which may require application design changes to avoid.

Perform a dry run

To further minimize potential surprises when you conduct the migration, practice cutover using your application and similar volumes of data on a "dry-run" environment. Use a test or development environment that is as similar as possible to production.

Performing a dry run is highly recommended. In addition to demonstrating how long the migration may take, a dry run also helps to ensure that team members understand what they need to do during the migration, and that changes to the application are coordinated.

Cutover strategy

Cutover is the process of switching application traffic from the source database to CockroachDB. Once the source data is fully migrated to CockroachDB, you "flip the switch" to route application traffic to the new database, thus ending downtime.

MOLT enables migrations with minimal downtime, using continuous replication of source changes to CockroachDB.

To safely cut over when using replication:

Stop application traffic on the source database.
Wait for the replication stream to drain.
When your monitoring indicates that replication is idle, use MOLT Verify to validate the CockroachDB data.
Start application traffic on CockroachDB.

When you are ready to migrate, refer to Migrate to CockroachDB or Migrate to CockroachDB in Phases for practical examples of the migration steps.

Pricing

Contact us

Sign In