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

Oracle to PostgreSQL is one of the most common database migrations in recent times. For numerous reasons, we have seen several companies migrate their Oracle workloads to PostgreSQL, both in VMs or to Azure Database for PostgreSQL. Table partitioning is a critical concept to achieve response times and SLAs with PostgreSQL. While a few open-source and third-party tools migrate the table schema and packages, there are not out-of-the-box tools that migrate partitions. So, partitioning strategy has become a very important topic for migration. As part of the migration, it is very important to have a good partitioning strategy due to the difference between Oracle and PostgreSQL. A bad partitioning strategy can lead to bloating and the vacuuming can take time.

As part of this session, you will get familiar with Oracle and PostgreSQL partitioning. We will discuss tools and extensions that you can use to migrate partitions from Oracle to PostgreSQL. We will also discuss partitioning strategies compared to Oracle, and how you can choose alternatives.

Transcript

2. Agenda • Problem statement • Importance of a good strategy • Oracle vs PostgreSQL partitioning • Migrating partitioned tables from Oracle to PostgreSQL • Demo
3. 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?
4. 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
5. 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
6. 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
7. 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!
8. Data migration Use ora2pg or Azure Data Factory Steps to migrate partitioned tables from Oracle to PostgreSQL
9. Mange partitions • A super cool partition manager • Can handle Time series and serial partitions Steps to migrate partitioned tables from Oracle to PostgreSQL
10. Demo
11. 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
12. Thank you My email: adkumara@microsoft.com