Partitioning strategy for Oracle to PostgreSQL migrations on Azure | Citus Con 2023

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

Agenda • Problem statement • Importance of a good strategy • Oracle vs PostgreSQL partitioning • Migrating partitioned tables from Oracle to PostgreSQL • Demo

Slide 3

Slide 3 text

Problem statement I have partitioned tables in Oracle. I am migrating to Azure Database for PostgreSQL Flexible Server. How do I migrate them, manage them in Azure?

Slide 4

Slide 4 text

Importance of a good strategy Query performance improvement Short rebuilding of indexes Easy to manage bulk loads and deletes Reduces vacuum overhead Reduces bloat Define objective

Slide 5

Slide 5 text

Good partitioning strategy • Is not a magic solution to solve all your performance problems • Vertical partitioning/Shards is a different idea • Can break your database if not done right • Choose right partition key based on data access patterns and ingestion rate • Data retention requirements • Devise mechanism to offload/delete data

Slide 6

Slide 6 text

Oracle vs PostgreSQL partitioning • Range – for date fields • List – categorical values • Hash – bucketing • Composite – sub partitioning by another partition method • List-Range partitioning • List-List partitioning • Range-Hash partitioning • PostgreSQL does not create partitions automatically • No merge partition or split partition Image source: docs.oracle.com

Slide 7

Slide 7 text

Steps to migrate partitioned tables from Oracle to PostgreSQL Migrate schema (ora2pg) Deploy partitioned tables in Azure Database for PostgreSQL Flexible Server Migrate data A super cool open-source tool!

Slide 8

Slide 8 text

Data migration Use ora2pg or Azure Data Factory Steps to migrate partitioned tables from Oracle to PostgreSQL

Slide 9

Slide 9 text

Mange partitions • A super cool partition manager • Can handle Time series and serial partitions Steps to migrate partitioned tables from Oracle to PostgreSQL

Slide 10

Slide 10 text

Demo

Slide 11

Slide 11 text

Important links • Azure Database for PostgreSQL Flexible Server • Ora2pg-Moves Oracle and MySQL database to PostgreSQL • pg_partman- PG Partition Manager • Migrate from Oracle to Azure Database for PostgreSQL Flexible Server • Oracle to Azure Database for PostgreSQL Flexible Server Cookbook • Oracle to Azure Database for PostgreSQL Flexible Server migration workarounds • Steps to install ora2pg on Windows or Linux • Build large scale data copy pipelines using Azure Data Factory

Slide 12

Slide 12 text

Thank you My email: adkumara@microsoft.com