Migration Best Practices

A successful migration to CockroachDB requires planning for downtime, application changes, observability, and cutover. This page outlines key decisions, infrastructure considerations, and best practices for a resilient and repeatable high-level migration strategy:

• Develop a migration plan.
• 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.

​

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 minimize disruption to database users?
• What is your target date for completing the migration?

Create a document summarizing the migration’s purpose, technical details, and team members involved.

​

Capacity planning

​

Cluster sizing

To size the target CockroachDB cluster, consider your data volume and workload characteristics:

• What is the total size of the data you will migrate?
• How many active 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 for details:

• For CockroachDB Standard and Basic, your cluster will scale automatically to meet your storage and usage requirements. Refer to the and documentation to learn about how to limit your resource consumption.
• For CockroachDB Advanced, refer to the 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 .

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

• Refer to our 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 to determine the amount of storage to provision on each node.
• For guidance on sizing for connection pools, see the CockroachDB self-hosted .

​

Memory allocation

MOLT Fetch buffers data in memory regardless of the used. For memory sizing requirements, refer to .

​

Application changes

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

• Handling transaction contention.
• Unimplemented features and syntax incompatibilities.

​

Schema design best practices

Convert the source table definitions into CockroachDB-compatible equivalents. CockroachDB supports the PostgreSQL wire protocol and is largely .

• The source and target table definitions must match. Review to understand which source types can be mapped to CockroachDB types. For example, a PostgreSQL source table defined as CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY); must have a corresponding schema and table in CockroachDB:
  CREATE SCHEMA migration_schema;
  CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY);
  
  MySQL tables belong directly to the database specified in the connection string. A MySQL source table defined as CREATE TABLE tbl (id INT PRIMARY KEY); should map to CockroachDB’s default public schema:
  CREATE TABLE tbl (id INT PRIMARY KEY);
  
  For example, an Oracle source table defined as CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY); must have a corresponding schema and table in CockroachDB:
  CREATE SCHEMA migration_schema;
  CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY);
  
  • MOLT Fetch can automatically define matching CockroachDB tables using the option.
  • If you define the target tables manually, review how MOLT Fetch handles . You can use the to create matching table definitions.
  • By default, table and column names are case-insensitive in MOLT Fetch. If using the flag, schema, table, and column names must match Oracle’s default uppercase identifiers. Use quoted names on the target to preserve case. For example, the following CockroachDB SQL statement will error:
    CREATE TABLE co.stores (... store_id ...);
    
    It should be written as:
    CREATE TABLE "CO"."STORES" (... "STORE_ID" ...);
    
    When using --case-sensitive, quote all identifiers and match the case exactly (for example, use "CO"."STORES" and "STORE_ID").
• Every table must have an explicit primary key.
  Avoid using sequential keys. To learn more about the performance issues that can result from their use, refer to the . If a sequential key is necessary in your CockroachDB table, you must create it manually, after using to load and replicate the data.
• Review to understand how computed columns and partitioned tables can be mapped to the target, and how target tables can be renamed.
• 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 .

​

Handling transaction contention

Optimize your queries against . You may encounter when you test application queries, as well as transaction contention due to long-running transactions when you and bulk load data. Transaction retry errors are more frequent under CockroachDB’s default . If you are migrating an application that was built at a READ COMMITTED isolation level, you should first 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 . For full compatibility with CockroachDB, you may need to implement workarounds in your schema design, , or application code. For more details on the CockroachDB SQL implementation, refer to .

​

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”:

1. Clearly describe the roles and processes of each team member performing the migration.
2. List the likely failure points and issues that you may encounter as you .
3. Rank potential issues by severity, and identify ways to reduce risk.
4. 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 match those used in production or be specific to your migration goals.

​

Load test data

It’s useful to load test data into CockroachDB so that you can test your application queries. MOLT Fetch 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 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 by shadowing or manually testing them. Note that CockroachDB defaults to the transaction isolation level. If you are migrating an application that was built at a READ COMMITTED isolation level on the source database, you must 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 or to find the longest-running queries that are part of that application request. If necessary, tune the queries according to our best practices for .
• Compare the results of the queries and check that they are identical in both the source database and CockroachDB.

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

1. 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.
2. Run stress tests at or above production concurrency and rate to verify the system can handle unexpected load spikes. This can also reveal issues that may arise during peak usage and require application design changes.

​

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.

​

See also