Optimizing Numeric Data Types in Oracle to PostgreSQL Migration

The talk I gave at PGConf India 2023 was focused on providing a guide for organizations migrating from the legacy commercial engine (Oracle) to Google Cloud, with a specific emphasis on the NUMBER data type. The key takeaways were as follows:

Providing guidance on best practices for data types during the migration process of relational databases
Enabling customers to refactor schema during the conversion phase by selecting the optimal numeric family and avoiding failure in later stages
Demonstrating an automated method for obtaining recommended data types from Oracle when the number declaration lacks precision or scale.

Transcript

1. Don’t let Number data type be the villain in your migrating journey. Deepak Mahto Feb 2023
2. Meet the Speaker Deepak Mahto Cloud Migration Consultant - Google Cloud
3. Oracle Number Characteristic
4. Number Data type and Tools. Ispirer Ora2pg CompilerWorks
5. PostgreSQL - Numeric vs Integer
6. Number Recommender - Metadata based Oracle Data type Oracle PRECISION(p) Oracle SCALE(s) PostgreSQL NUMBER(p) p < 5 0 OR NULL SMALLINT NUMBER(p) p >=5 and p<10 0 OR NULL INTEGER NUMBER(p) p >=10 and p<19 0 OR NULL BIGINT NUMBER(p) p>=19 and P < 30 0 OR NULL NUMERIC(p) NUMBER(p) p>=30 0 OR NULL NUMERIC(p) NUMBER(p,s) p is not null s < 10 NUMERIC(p,s) NUMBER(p,s) p is not null s > 10 NUMERIC(p,s)
7. Number Recommender - Sampling based Oracle Data Type Max Scale length calculated Data value sampled PostgreSQL Data Type NUMBER 0 BETWEEN -9223372036854775808 AND 9223372036854775807 BIGINT NUMBER max_left_significant_digit + max_right_significant_digit <=15 DOUBLE PRECISION NUMBER > 38 NOT NULL NUMERIC NUMBER <10 NOT NULL NUMERIC NUMBER >10 NOT NULL NUMERIC NUMBER NULL NULL DOUBLE PRECISION
8. Number Recommender Recommender SQL File Connect and scan as per Flags Generate Recommendation MODIFY_TYPE SAMPLE_NUMBER_DATA_TYPE:COL4:INTEGER,SAMP LE_NUMBER_DATA_TYPE:COL5:BIGINT,SAMPLE_NU MBER_DATA_TYPE:COL6:BIGINT,..........
9. Github repo - link
10. Thank you! deepakmahto@google.com