> ## Documentation Index > Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Migration Best Practices export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; 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](#develop-a-migration-plan). * [Size the target CockroachDB cluster](#capacity-planning). * Implement [application changes](#application-changes) to address necessary [schema changes](#schema-design-best-practices), [transaction contention](#handling-transaction-contention), and [unimplemented features](#unimplemented-features-and-syntax-incompatibilities). * [Prepare for migration](#prepare-for-migration) by running a [pre-mortem](#run-a-migration-pre-mortem), setting up [metrics](#set-up-monitoring-and-alerting), [loading test data](#load-test-data), [validating application queries](#validate-queries) for correctness and performance, performing a [migration dry run](#perform-a-dry-run), and reviewing your cutover strategy. For help migrating to CockroachDB, contact our [sales team](mailto:sales@cockroachlabs.com). ## 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 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. ### Memory allocation MOLT Fetch buffers data in memory regardless of the data path used. For memory sizing requirements, refer to Memory requirements. ## 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.](#handling-transaction-contention) * [Unimplemented features and syntax incompatibilities.](#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 compatible with PostgreSQL syntax. * The source and target table definitions must **match**. Review Type mapping 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE SCHEMA migration_schema; CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY); ``` * MOLT Fetch can automatically define matching CockroachDB tables using the `drop-on-target-and-recreate` option. * If you define the target tables manually, review how MOLT Fetch handles type mismatches. You can use the MOLT Schema Conversion Tool to create matching table definitions. * By default, table and column names are case-insensitive in MOLT Fetch. If using the `--case-sensitive` 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE co.stores (... store_id ...); ``` It should be written as: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 guidance on indexing with sequential keys. If a sequential key is necessary in your CockroachDB table, you must create it manually, after using MOLT Fetch to load and replicate the data. * Review Transformations 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 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](#validate-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](https://www.postgresql.org/docs/current/protocol.html) and is largely compatible with PostgreSQL syntax. For full compatibility with CockroachDB, you may need to implement workarounds in your schema design, data manipulation language (DML), or 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": 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 conduct the migration. 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](#develop-a-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](#validate-queries). 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](#load-test-data), validate your queries on CockroachDB by [shadowing](#shadowing) or [manually testing](#test-query-results-and-performance) them. 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](#test-query-results-and-performance) 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. Test performance on a CockroachDB cluster that is appropriately [sized](#capacity-planning) 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 contention issues that may arise during peak usage and require [application design changes](#handling-transaction-contention). ### 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 * Migration Overview * Schema Design Overview * Secondary index best practices * Transaction contention best practices