Database Migration Assessment With YugabyteDB Voyager

Introduction

Database migration can be a daunting and complex task, especially when transitioning from a single node database like PostgreSQL or Oracle to a distributed database like YugabyteDB. You may encounter feature incompatibilities between the source and target databases, uncertainty around optimal YugabyteDB cluster sizing, and questions on how to leverage YugabyteDB-specific features like colocation.

Open source migration tool YugabyteDB Voyager streamlines the process by enabling users to evaluate any complexity and requirements beforehand.

YugabyteDB Voyager generates a detailed assessment report, accessible via the YugabyteDB UI, which offers insights into two crucial areas: migration complexity and sizing and sharding guidance. This blog post provides a technical deep dive into these two key areas.

Migration Complexity

There may be features in the user’s existing source database that are not compatible with YugabyteDB such as Oracle’s type inheritance and reference partitioning, which are not supported by YugabyteDB.

YugabyteDB is highly compatible with PostgreSQL. However, there may be certain features (for example, exclusion constraints) that are not yet supported in YugabyteDB.

YugabyteDB Voyager detects unsupported features and syntax incompatibilities and offers potential workarounds when available. For instance, if exclusion constraints are not supported, YugabyteDB Voyager might suggest creating a TRIGGER to manage the exclusion logic manually.

Based on the incompatibilities detected and their respective impacts, ‘Migration Complexity’ is computed, and can be LOW, MEDIUM, or HIGH.

How it Works

Source Data Collected:

The user’s schema (TABLE, VIEW, FUNCTION, FUNCTIONS, PROCEDURES, TRIGGERS .. definitions) and the frequently executed DMLs are retrieved. For PostgreSQL databases, the schema is fetched from pg_dump, and the DMLs from pg_stat_statements.

Assessment:

Each DDL/DML query is then passed through a PostgreSQL parser. The parse tree is then traversed to identify specific PostgreSQL features that are not yet supported in YugabyteDB. For example, while traversing the parse tree of the query, if the JSON_TABLE function call node is detected, we report the query because JSON_TABLE is not yet supported in YugabyteDB.

This in-depth analysis allows users to evaluate the complexity of their migration and proactively develop a suitable plan. By identifying potential challenges in advance, users can mitigate them before starting the migration process, ensuring a smoother and more efficient transition.

Sizing and Sharding Guidance

The second primary element of the assessment report covers sizing and sharding guidance. This offers estimations of the resources required for the target YugabyteDB cluster, based on factors like the number of tables, table sizes, and throughput requirements.

It also suggests the optimal number of nodes, vCPU count, and memory per node, and puts forward effective sharding strategies for tables and indexes.

Below is an example of the sizing recommendations provided in the report.

How it Works

Source Data Collected:

The size and Input/Output operations per second (IOPS) for all table/view/index are fetched from the source database’s catalog. For PostgreSQL, catalog tables like pg_class, pg_index, pg_stat_user_tables, pg_relation_size, etc are queried.

Assessment:

With the help of various experiments run on YugabyteDB for different data sizes and IOPS, and the collected data, recommendations for the optimal cluster size, node vCPU count, and node memory are provided. The list of tables that can be colocated is also provided.

Conclusion

YugabyteDB Voyager’s new assessment feature streamlines migration to YugabyteDB by thoroughly analyzing migration complexity and providing sizing and sharding guidance. This allows users to effectively plan migrations and avoid potential issues. The guidance provided for provisioning YugabyteDB clusters helps ensure that they can handle the expected workload and data size.

To get hands-on with YugabyteDB Voyager, access it here.

For further information on the insights you can get from a YugabyteDB Voyager assessment, please refer to the official documentation.