PostgreSQL Tutorial: The impact of underlying design differences when migrating Oracle databases

August 12, 2026

Summary: In this tutorial, we’ll go over the impacts caused by differences in underlying database design when migrating from Oracle to PostgreSQL.

Table of Contents

Background & Overview

Speakers / Team: Software developers from the Revenue Accounting department at Lufthansa Group (AGBS).
Project Background: Starting with virtually zero PostgreSQL knowledge, the team successfully migrated their core airline ticketing system from Oracle to PostgreSQL.
Key Pain Point: Database migration is far more than just replacing SQL syntax; the real challenge lies in the differences in underlying DBMS design and mechanics. Ignoring these differences can lead to severe performance bottlenecks and data logic bugs.

Syntax and Function Differences & Replacement Strategies

In the early stages of migration, the team addressed basic syntax conversions and identified several subtle logic pitfalls:

1. Null Handling (NVL vs. COALESCE)

Difference: Oracle commonly uses NVL for null handling, which PostgreSQL does not support.
Solution: Use regular expressions (regex) to globally replace NVL with PostgreSQL’s standard COALESCE function.

2. Type Casting (Implicit vs. Explicit)

Difference: Oracle supports aggressive implicit type conversion (e.g., adding numbers directly to substrings), whereas PostgreSQL strictly requires explicit type casts.
Solution: Add explicit type casts in the code where necessary. The team also discovered that for specific needs (such as appending two zeros to the end of an integer), multiplying by 100 is far cleaner and more efficient than casting to text, concatenating, and casting back to an integer.

3. Conditional Matching Pitfalls (DECODE vs. CASE)

Difference: Oracle’s DECODE function treats NULL as equal to NULL. However, when rewritten as a standard CASE statement in PostgreSQL, NULL = NULL evaluates to false.
Initial Solution: Rewrite using CASE WHEN ... IS NULL or IS NOT DISTINCT FROM to establish correct null-matching behavior.
Performance Bottleneck & Workaround: In JOIN conditions, the PostgreSQL Query Planner currently cannot effectively utilize indexes for IS NOT DISTINCT FROM, causing severe performance degradation. To achieve maximum speed, the team implemented a clever workaround: convert compare values so that NULL becomes a special placeholder string (e.g., ||NULL||). This allowed them to reuse standard equality checks (=) and functional indexes, dramatically restoring query performance.

Numeric Calculation and Precision Differences

The two databases behave quite differently when handling numerical operations—especially division and high-precision calculations—which is critical in financial revenue accounting systems.

Integer Division Differences:

In Oracle, executing 1 / 3 yields 0.333....
PostgreSQL enforces strict integer types, so 1 / 3 performs integer division and truncates down to 0.

Precision Management Comparison:

Oracle: Has a single exact numeric type (NUMBER). During inexact calculations, it automatically fills up to its maximum precision (~39 to 40 digits).
PostgreSQL: Supports a maximum precision of up to 1,000 digits. For performance reasons, it does not automatically maximize precision. Instead, result precision depends heavily on the scale of the operands. Without explicitly declaring higher precision/scale, financial calculations can lose accuracy.

Solution: To avoid cluttering queries with type casts that are easy to forget, the team modified the table schemas directly, defining all financial calculation columns as high-precision NUMERIC(52, 40), permanently resolving precision issues.

Core Challenge: Rethinking UPDATEs (MVCC Mechanism Differences)

This represented the most critical performance and architecture challenge throughout the migration.

Business Scenario: The system processes a massive table containing ~200 million records monthly. Because incoming data is often incomplete, ~50% of the records (~100 million rows) need to be UPDATE-d to roll over to the next month.

Oracle Behavior (In-Place Update):

Oracle supports in-place updates managed via Undo Logs. Updating 100 million records took only about 20 minutes.

PostgreSQL Behavior (MVCC - Multi-Version Concurrency Control):

PostgreSQL does not update records in place. Every UPDATE inserts a new tuple and marks the old one as dead via the xmax transaction ID.
Updating 50% of a massive table caused immense table bloat. In PostgreSQL tests, this single batch UPDATE statement ran for 24 hours without finishing before being aborted.

Limitations of Standard Maintenance:

Frequent VACUUM or table repacking (pg_repack) introduces heavy lock contention and additional system overhead.
Table partitioning was ruled out due to strict global uniqueness constraints across the dataset.

Ultimate Strategy: Rethinking the Business Logic

Key Insight: Avoid massive batch UPDATEs in PostgreSQL whenever possible.
Redesign: Instead of updating millions of incomplete records to roll them over every month, the team changed the approach: leave completion status fields empty by default, and only update the small fraction of records that actually complete. This logic change eliminated table bloat and delivered the required performance immediately.

A 6-Step Guide for Database Migration (Key Takeaways)

Based on their migration journey, the team outlined a standard 6-step iteration model:

Make it run: Get the application running on the target database as early as possible—this is the fastest way to discover issues.
Catch basic replacement errors: Verify that simple search-and-replace transformations (e.g., regex syntax fixes) didn’t introduce hidden bugs.
Set performance targets: Define clear metrics (e.g., matching old system performance or setting new baselines).
Optimize performance: Apply traditional tuning techniques such as adding indexes and tweaking queries.
Address systematic architectural issues: When encountering performance bottlenecks caused by underlying DBMS differences (e.g., MVCC update bloat), rethink the underlying business logic or workflow rather than forcing raw SQL.
Iterate and re-optimize: Step back to evaluate updated structures and continuously tune until performance goals are met.

Additional Key Takeaways

Strictness is a feature: Oracle is very forgiving of bad SQL practices. PostgreSQL’s strictness exposed legacy code anti-patterns, forcing the team to clean up technical debt and improve overall code quality.
Migration Tooling: Rather than relying heavily on automated tools like ora2pg, the team opted for manual schema/query rewrites and built a custom data migration tool to handle specific column renaming and business rules efficiently.