Migration Assessment Report

Database Name: mig_assessment

Database Version: 12.19

Migration Complexity: MEDIUM

Database Objects

Objects that will be created on the target YugabyteDB.

Object Type	Total Count	Object Names
SCHEMA	2
partition
public

| | SEQUENCE | 13 | partition.test_partitions_sequences_id_seq
public.another_table_id_seq
public.employees_id_seq
public.example_table_id_seq
public.example_table_with_tbsp_id_seq
public.geometricshapes_id_seq
public.locations_id_seq
public.mytable_event_id_seq
public.order_details_detail_id_seq
public.orders_order_id_seq
public.product_catalog_id_seq
public.product_sales_sale_id_seq
public.vehicles_vehicle_id_seq

| | TABLE | 43 | partition.sales_region
partition.boston
partition.customers
partition.cust_active
partition.cust_arr_large
partition.cust_arr_small
partition.cust_other
partition.cust_part11
partition.cust_part12
partition.cust_part21
partition.cust_part22
partition.emp
partition.emp_0
partition.emp_1
partition.emp_2
partition.london
partition.range_columns_partition_test
partition.range_columns_partition_test_p0
partition.range_columns_partition_test_p1
partition.sales
partition.sales_2019_q4
partition.sales_2020_q1
partition.sales_2020_q2
partition.sydney
partition.test_partitions_sequences
partition.test_partitions_sequences_b
partition.test_partitions_sequences_l
partition.test_partitions_sequences_s
public.another_table
public.vehicles
public.cars
public.employees
public.example_table
public.example_table_with_tbsp
public.geometricshapes
public.locations
public.mytable
public.object_type_mapping
public.order_details
public.orders
public.product_catalog
public.product_sales
public.trucks

| | INDEX | 3 | idx_locations_location
locations_id_idx
panother_table_idx

| | FUNCTION | 1 | public.check_order_total

|

Sharding Recommendations

Colocated Tables	Sharded Tables
public.order_details
public.orders
public.another_table
public.cars
public.vehicles
public.employees
public.example_table
public.example_table_with_tbsp
public.locations
public.object_type_mapping
public.product_catalog
public.product_sales
public.trucks
partition.test_partitions_sequences_s
partition.boston
partition.cust_other
partition.cust_part11
partition.range_columns_partition_test_p0
partition.cust_part21
partition.emp_0
partition.emp_2
partition.london
partition.sales_2019_q4
partition.sales_2020_q2
partition.test_partitions_sequences_b
partition.test_partitions_sequences_l
partition.cust_part12
partition.cust_part22
partition.emp_1
partition.range_columns_partition_test_p1
partition.sales_2020_q1
partition.sydney
public.geometricshapes
public.mytable

|

|

Sizing Recommendations

Parameter	Recommendation
Num of Nodes	3
vCPU per instance	4
Memory per instance(GiB)	16
Optimal select connections per node	8
Optimal insert connections per node	12
Parallel Voyager Jobs	1
Estimated time taken for data import	0 min

Reasoning:

Recommended instance type with 4 vCPU and 16 GiB memory could fit 37 objects (34 tables and 3 explicit/implicit indexes) with 0.00 MB size and throughput requirement of 0 reads/sec and 0 writes/sec as colocated. Non leaf partition tables/indexes and unsupported tables/indexes were not considered.

Unsupported Data Types

Data types of the source database that are not supported on the target YugabyteDB.

Schema	Table	Column	Data Type
public	product_catalog	product_data	xml
public	mytable	txid_snapshot	txid_snapshot
public	mytable	pg_lsn	pg_lsn

Unsupported Features

Features of the source database that are not supported on the target YugabyteDB.

GIST indexes

• idx_locations_location ON public.locations

Details

Inherited tables

• public.cars
• public.trucks

Details

Tables with stored generated columns

• public.employees
• public.order_details
• public.product_sales

Details

Migration caveats

Alter partitioned tables to add Primary Key

After export schema, the ALTER table should be merged with CREATE table for partitioned tables as alter of partitioned tables to add primary key is not supported.

• ALTER TABLE ONLY partition.sales_region ADD CONSTRAINT sales_region_pkey PRIMARY KEY (id, region);
• ALTER TABLE ONLY partition.customers ADD CONSTRAINT customers_pkey PRIMARY KEY (id, statuses, arr);
• ALTER TABLE ONLY partition.cust_active ADD CONSTRAINT cust_active_pkey PRIMARY KEY (id, statuses, arr);
• ALTER TABLE ONLY partition.cust_arr_large ADD CONSTRAINT cust_arr_large_pkey PRIMARY KEY (id, statuses, arr);
• ALTER TABLE ONLY partition.cust_arr_small ADD CONSTRAINT cust_arr_small_pkey PRIMARY KEY (id, statuses, arr);
• ALTER TABLE ONLY partition.emp ADD CONSTRAINT emp_pkey PRIMARY KEY (emp_id);
• ALTER TABLE ONLY partition.range_columns_partition_test ADD CONSTRAINT range_columns_partition_test_pkey PRIMARY KEY (a, b);
• ALTER TABLE ONLY partition.sales ADD CONSTRAINT sales_pkey PRIMARY KEY (id, sale_date);
• ALTER TABLE ONLY partition.test_partitions_sequences ADD CONSTRAINT test_partitions_sequences_pkey PRIMARY KEY (id, region);

Details

Unsupported Data Types for Live Migration

There are some data types in the schema that are not supported by live migration of data. These columns will be excluded when exporting and importing data in live migration workflows.

• public.geometricshapes.polygon_column (polygon)
• public.geometricshapes.box_column (box)
• public.geometricshapes.line_segment_column (lseg)
• public.geometricshapes.circle_column (circle)
• public.geometricshapes.point_column (point)
• public.locations.location (point)
• public.geometricshapes.path_column (path)