Oracle to CloudSQL for Postgres Migration- Parameters and Feature Mapping

Purpose of the Document

The available Database Migration Assessment tools which are used to assess the source Oracle database and generate Oracle to Postgres Migration Assessment reports based on the source schema, PL/SQL Code, initialization parameters, database options used do not recommend the target Postgres configuration parameters or flags that need to be set. They also do not recommend the various extensions, functionalities that are available in Postgres to implement the various Oracle database options. Many Oracle to Postgres migrations happen without setting or modifying the default Postgres parameters which cause performance and scalability issues post migrations. The default Postgres parameters are conservative and they need to be changed based on the workload and also the source Oracle init parameters. The document discusses and recommends Best Practices for setting the target Postgres parameters to optimize performance. Many of the Oracle Database options like Partitioning, Advanced Compression, Advanced Security, etc are available in Postgres and there are many Postgres extensions available that support achieving the same functionality as provided by Oracle database options. The document discusses the mapping of these options and functionalities from Oracle to Postgres and provides a guideline on the implementation.

The document does not discuss other migration challenges, migration complexities, migrations tools from Oracle to Postgres. The purpose of the document is to supplement and complement existing Database Migration Assessment tools to optimize the post migration performance issues.

The document is useful for Database Administrators, DevOps Team, Migration Experts and Cloud Consultants. The document acts as a supplement to our Database Migration Assessment (DMA) tool or any other such Oracle to Postgres Migration Assessment tools. Anyone using such DMA tools can additionally use this document to configure the settings for target Postgres database parameters and Postgres extensions that need to be installed.

Oracle to Postgres Parameters Mapping

1. Configure Memory Related Parameters

2. Configure Checkpointing Parameters

3. Configure I/O Parameters

4. Configure Access Path Parameters

5. Configure Parallelism Parameters

6. Configure Performance related other Parameters

Checkpointing and Commit-A little more..

Checkpoints are at the heart of any RDBMS. There can be severe performance problems if checkpoints are not tuned. It is a huge topic in itself but I will discuss a bit on it, espcially checkpointing in Postgres.

Checkpoints are determined by 2 parameters- CHECKPOINT_TIMEOUT and MAX_WAL_SIZE

Checkpoint will happen whenever either of the above 2 events governed by the 2 settings happen first.

By default. checkpoint_timeout is 5 mins and max_wal_size is 1.5GB (in both CloudSQL and AlloyDB). And AlloyDB handles checkpoints very differently , and more effectively, than Postgres. AlloyDB delegates checkpoints to the storage layer which we are not discussing here.

So a checkpoint will happen either every 5 minutes or when 1.5GB of WAL Records/segments are generated.

Usually in a busy prod system MAX_WAL_SIZE should be increased to 4–10GB (and sometimes to 30GB) so that checkpoints do not happen that frequently. Ideally make checkpoints happen every 3–5 minutes.

During checkpoint the CKPT process will flush/write dirty buffers/modified data and index pages from Buffer cache to Disk (Data files). Postgres will try to finish the checkpoint based on checkpoint_completion_target (default 0.9). Setting this to a higher value will evenly spread the IOs between 2 checkpoints.

The Background Writer (BGWriter) also periodically flushes dirty pages from buffer cache to disk on a regular interval with sleeping time between 2 activities determined by bgwriter_delay (200ms default) and maximum number of LRU/Dirty pages to flush every round is determined by bgwriter_lru_maxpages (default 100 pages)

So Background writer flushes no more than 100 dirty pages per round and sleeps for 200ms between every round (this is independent of the CKPT process which flushes dirty pages during checkpoint). You can edit this parameter in Postgres running in GCE VM (but not editable in CloudSQL or AlloyDB for good reasons) to make bgwriter flushes more aggressive so that checkpoints have less work to do.

Commit- This will flush WAL Records from WAL Buffers (default 16MB) to WAL Files/Segments on disk (wal_segment_size=16MB) . If a transaction is too large and exceeds WAL Records > wal_buffer_size even uncommitted changes will get flushed to WAL Segments on disk. But during applying WAL Records to data files during crash/instance recovery only committed records since last checkpoint will get applied (the CLOG records help to identify committed transactions)

So WAL Records are flushed from WAL Buffers to Disk not only during transaction commit but also when WAL buffers get filled.

Every Checkpoint maintains a Checkpoint record in WAL Segments so that the WAL Records prior to the checkpoint record can be reused/deleted when WAL segments need to be overwritten. Also Archiving will need to archive only completely filled WAL Segments before they get overwritten/recycled. But WAL Segments can be switched without getting full either by setting archive_timeout or pg_switch_wal.

Oracle to Postgres Functionality/Options Mapping

The below is not an exhaustive list of features/options mapping from Oracle to Postgres. I have mentioned whether the feature is supported in Postgres or not and the various Postgres extensions and ways these Oracle features can be implemented in CloudSQL for Postgres or AlloyDB.

Summary of Best Practices

The suggested best practices are for guidance purposes only.There might be other Postgres parameter settings which you will need to update based on application workload. Configuring CloudSQL Postgres flags/parameters should be a part of the Migration plan from Oracle to Postgres. Based on the source Oracle database initialization parameter settings and the nature of workload the target CloudSQL Postgres parameters need to be set to optimize memory usage, I/O operations, CPU usage, connection management, performance and optimizer selection of access paths in the target Postgres Instance. Any change from the default parameters in CloudSQL should be properly tested against actual workload. It is always recommended to perform proper Load testing and tune the suggested parameters to meet your desired Non-Functional requirements of performance, durability and availability. This document will supplement your existing DMA tools to tune the target Postgres flags and also implement the various Oracle options and features in Postgres.

What’s Next

CloudSQL for Postgres is adding more features, functionalities and support for Postgres extensions. It also has the Enterprise Plus edition which provides better performance, availability and durability. It supports pgvector and you can build GenAI Apps. When migrating from Oracle, one can also choose AlloyDB-which is fully managed PostgreSQL compatible database service for the most demanding enterprise HTAP workloads that supports columnar engine and helps build generative AI applications.

There are other Google Cloud Native databases like Spanner which supports multi-write, unlimited read-write horizontal scalability in geographically distributed databases, automatic sharding and supports Postgresql interface. When migrating Oracle to GCP Databases you have the option to modernize your Oracle database to CloudSQL for Postgres , AlloyDB and Cloud Spanner.