Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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.

More Decks by Azure Database for PostgreSQL

Other Decks in Technology

Transcript

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

    View full-size slide

  2. 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?

    View full-size slide

  3. 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

    View full-size slide

  4. 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

    View full-size slide

  5. 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

    View full-size slide

  6. 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!

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  9. 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

    View full-size slide