PostgreSQL Tutorial: Top Mistakes when Migrating Oracle / SQL Server

Summary: In this tutorial, we’ll go over some key pitfalls and tips to avoid them when migrating from Oracle/SQL Server to PostgreSQL.

Table of Contents

Core Pain Points & Root Cause Analysis

In database migration projects, the root cause of most mistakes boils down to: rushing to launch, lacking native PostgreSQL experience, over-relying on automated tools, and assuming PostgreSQL behaves identically to Oracle or SQL Server.

Typically, code conversion accounts for only about 25% of the total migration effort, while testing, performance tuning, and validation take up over 50%. Copying the source database design directly in the early stages leads to multiplied refactoring costs later during performance tuning and production operations.

Early Development & Migration: Syntax & Standard Pitfalls

Mistake 1: Identifier Casing and Double-Quote Misuse

• Issue: Oracle folds unquoted metadata to uppercase by default, whereas PostgreSQL folds to lowercase. To avoid errors, migration tools often wrap uppercase table/column names from the source database in double quotes when importing them into PostgreSQL (e.g., "USERS", "FIRST_NAME").
• Consequences: Every subsequent SQL query must consistently include double quotes and uppercase names. If a developer later adds a new column without double quotes (which defaults to lowercase), query failures occur due to unrecognized fields, making code maintenance extremely tedious.
• Solution: Convert all identifiers (tables, columns, etc.) to PostgreSQL’s native all-lowercase during early migration stages, eliminating double quotes entirely.

Mistake 2: Confusing Empty Strings ('') and NULL Logic

• Issue: Oracle treats empty strings ('') as NULL, whereas PostgreSQL (following ANSI standards) strictly distinguishes between empty strings and NULL. In PostgreSQL, concatenating any string with NULL (using ||) yields NULL.
• Solution: Beyond using COALESCE to handle potential NULL fields, it is recommended to use native PostgreSQL functions (e.g., concat_ws) for string concatenation to improve code readability and maintainability.

Mistake 3: Ignoring Unique Constraint Differences with NULL Values

• Issue: In Oracle’s multi-column unique indexes, rows with NULL values can trigger uniqueness constraints. In PostgreSQL, however, since NULL != NULL, rows containing NULL can be inserted repeatedly, leading to unintended duplicate data and logic errors.
• Solution: When migrating unique indexes on nullable columns, explicitly specify NULLS NOT DISTINCT (supported in PostgreSQL 15+) to preserve business constraints consistent with the source database.

Mistake 4: Rigidly Copying Business Code While Ignoring Native PostgreSQL Features

• Issue: Attempting a line-by-line rewrite of complex stored procedures or functions in PL/pgSQL without utilizing PostgreSQL’s native capabilities.
• Case Study: A team previously maintained a 2,000+ line Oracle Package for handling IP address and subnet calculations. During migration, they scrapped the entire package and adopted PostgreSQL’s native inet/cidr data types and built-in operators, saving weeks of code conversion and testing.
• Solution: Tactically leverage PostgreSQL features (e.g., native networking/JSON data types, PL/V8, PL/Perl) to avoid reinventing the wheel.

Testing & Architecture Design: Architecture & Performance Pitfalls

Mistake 5: Over-Engineering “Bi-Directional Replication” Fallback Plans

• Issue: Management often insists on building bi-directional real-time replication between Oracle and PostgreSQL to enable a seamless rollback to the source database if cutover fails.
• Reality: Bi-directional replication across heterogeneous databases is notoriously difficult due to conflict resolution. Experience shows that almost all cutover aborts happen within the first few hours of switchover. Virtually no team successfully cuts back months after launch because new application code deployed on PostgreSQL is no longer compatible with the source database.
• Solution: Adopt a Fail Forward strategy (e.g., Oracle -> PostgreSQL -> secondary target/fresh source copy) and invest effort into thorough pre-launch load testing rather than building fragile and complex bi-directional replication setups.

Mistake 6: Improper Data Type Selection (The Biggest Performance Killer: Misuse of NUMERIC)

• Issue: Oracle’s NUMBER type supports up to 38 digits of precision. To guarantee zero overflow, migration tools typically map all numeric columns (including primary keys, foreign keys, and IDs) to PostgreSQL’s NUMERIC type.

• Performance Impact:

• NUMERIC is an arbitrary-precision numeric type with significant computational and indexing overhead.

• Benchmark Data: Joining a 10-million-row table using BIGINT instead of NUMERIC yields a 39% performance increase.

• Consequences & Advice: Discovering this during late-stage tuning requires updating types across all stored procedures, triggers, and application code. Always prefer BIGINT or INT for primary and foreign keys; reserve NUMERIC strictly for financial or high-precision currency data.

Mistake 7: Blindly Copying B-Tree Indexes While Ignoring Hash, GIN, and Special Index Types

• Issue: While 90% of standard B-Tree indexes translate directly, the remaining 10% can benefit drastically from PostgreSQL-specific index types.
• Optimization Examples:
• Wildcard Searches: For LIKE '%xxx%' pattern matching, use GIN indexes with trigrams for fast lookups.
• UUID Lookups: Searching a UUID column via B-Tree index touches 5 buffers (~106 μs); switching to a Hash index touches only 2 buffers (~76 μs). Combining a Hash index with PostgreSQL’s native uuid data type further reduces lookup time to 67 μs.

Production Operations: Hidden Disasters Exposed Post-Launch

Mistake 8: Overusing EXCEPTION Blocks in PL/pgSQL

• Issue: Habitually adding EXCEPTION blocks to every stored procedure or function (e.g., WHEN NO_DATA_FOUND THEN RETURN NULL).

• Underlying Mechanism: When executing a PL/pgSQL function containing an EXCEPTION block, PostgreSQL creates implicit Savepoints and subtransactions under the hood.

• Consequences & Failures:

• Rapid Transaction ID (XID) Consumption: Executing functions with exception blocks in loops rapidly consumes huge numbers of XIDs.

• Triggering Autovacuum Storms: Excessive XID consumption prematurely pushes the database toward the 2-billion XID wraparound threshold. This forces PostgreSQL to initiate aggressive Vacuum Freeze operations (consuming heavy I/O and CPU), or even shut down to prevent data corruption.

• Solution: Avoid adding exception blocks blindly. For “not found” queries, let the function return NULL naturally. For Upsert logic, never rely on catching unique constraint exceptions to perform updates; use PostgreSQL’s native INSERT ON CONFLICT clause instead.

Mistake 9: Frequently Creating Temporary Tables Causing Catalog Bloat

• Issue: Developers with SQL Server backgrounds frequently create and drop temporary tables inside high-frequency stored procedures and transaction paths.
• Underlying Mechanism: Temporary tables in PostgreSQL write actual metadata entries to system catalog tables (such as pg_class and pg_attribute).
• Consequences & Failures: Rapidly creating temporary tables generates millions of dead tuples in system catalogs, causing tables like pg_attribute to bloat severely (sometimes reaching tens of GBs or TBs), slowing down SQL execution across the entire database.
• Solution: Prohibit temporary tables in high-frequency OLTP logic. Use CTEs (Common Table Expressions / WITH clauses) or inline subqueries instead. Limit temporary table usage strictly to low-frequency batch processes or quarterly reporting.

Summary & Best Practices

1. Golden Time Allocation Rule: Spending an extra two days early on establishing proper data type mappings and indexing strategies will save weeks of code refactoring during testing and production.
2. Shift Mindset Away from Legacy Assumptions: Understand PostgreSQL’s underlying mechanics (e.g., MVCC, XID wraparound protection, catalog bloat) and write SQL and procedures “the PostgreSQL way.”
3. AI / LLM-Assisted Migration Advice: When using Large Language Models (LLMs) to generate or convert SQL/PLpgSQL code, explicitly include a negative prompt/rules list (e.g., “Do not use NUMERIC for primary keys,” “Avoid unnecessary EXCEPTION blocks,” “Do not use temp tables in high-frequency code,” “Use INSERT ON CONFLICT instead of catching exceptions”). This guides the LLM to generate production-ready code aligned with best practices.

Reference

Top mistakes when migrating from Oracle and SQL Server to PostgreSQL

See more

PostgreSQL Administration