Skip to content
SysAndDB
Go back

Escaping Oracle & SQL Server Licensing Costs: A Senior DBA’s Playbook for Migrating to PostgreSQL

For mid-sized and enterprise engineering organizations, commercial database licensing rarely scales linearly with traffic. One hardware refresh, a vCPU increase on virtualized hosts, or a routine vendor license audit can trigger six-figure true-up bills—on top of mandatory 22% annual support renewals.

Over the past decade, PostgreSQL has matured into the default landing zone for teams exiting Oracle Database and Microsoft SQL Server. However, treating an enterprise database migration as a simple export/import job is the fastest way to cause silent data truncation, broken application transactions, and severe post-cutover latency.

Below is the structured, four-phase playbook I use as a production DBA to move mission-critical workloads off commercial engines onto PostgreSQL with predictable performance and minimal downtime.


1. The Enterprise Licensing Cliff (TCO Reality Check)

Before committing engineering sprints to a migration, frame the financial case clearly for leadership using Total Cost of Ownership (TCO) over a 3-year horizon:

Cost ComponentCommercial Enterprise Engines (Oracle EE / SQL Server Enterprise)Open-Source PostgreSQL (Self-Managed or Managed Cloud)
Core / vCPU LicensingPer-processor / per-2-core pack licensing (escalates rapidly on multi-core modern CPUs & virtualization clusters)$0 (PostgreSQL License — permissive, free for any commercial scale)
Mandatory Annual Support~20%–22% of list license fee every year (even if you never open a support ticket)$0 (or optional enterprise SLA subscriptions only on active nodes)
High Availability & Read ReplicasPassive/active standbys and reporting replicas frequently require full additional core licenses$0 additional license fees for streaming standbys, read replicas, or dev/QA clones
Partitioning & DiagnosticsOften locked behind Enterprise Edition or paid add-on packs (e.g., Diagnostics & Tuning Packs)Built-in natively (declarative partitioning, pg_stat_statements, EXPLAIN ANALYZE)

2. Phase 1 — Compatibility & Schema Assessment

Never start converting DDL until you inventory proprietary engine features embedded in your database layer. Run a discovery audit first to classify tables into Clean Lift-and-Shift versus Application Refactor Required:

Critical Behavioral Differences to Catch Early

  1. Empty Strings vs. NULL (Oracle Trap): Oracle treats '' (empty string) as NULL. PostgreSQL strictly follows the SQL standard where '' IS NOT NULL. Applications relying on WHERE col IS NULL to catch empty strings will silently break unless normalized during ETL or handled in queries.
  2. Case Sensitivity & Identifier Folding: Oracle folds unquoted identifiers (SELECT emp_id FROM employees) to UPPERCASE; PostgreSQL folds unquoted identifiers to lowercase. Standardize all converted DDL to lowercase unquoted names to avoid double-quoting every column in application queries.
  3. Data Type Mapping Precision:
    • Oracle NUMBER (without precision/scale) → Avoid blindly mapping to NUMERIC everywhere; map integer keys to BIGINT or INTEGER for 3x–5x faster index scans and joins, reserving NUMERIC(p,s) strictly for financial/decimal columns.
    • Oracle VARCHAR2(4000) / SQL Server NVARCHAR(MAX) → Map cleanly to PostgreSQL TEXT (in PostgreSQL, VARCHAR(n) and TEXT share identical storage and performance internals).
    • SQL Server DATETIME / Oracle DATE (which includes time) → Map to TIMESTAMPTZ (timestamp with time zone) rather than DATE.
  4. Transaction & Error Handling Inside Routines: Oracle PL/SQL packages with PRAGMA AUTONOMOUS_TRANSACTION and SQL Server nested TRY...CATCH blocks behave differently under PostgreSQL’s subtransaction (SAVEPOINT) model. Heavy business logic inside stored procedures should be refactored into application services or idiomatic PL/pgSQL.

3. Phase 2 — Automated Schema Conversion & Diffing

Use proven open-source converters to generate your baseline PostgreSQL DDL, then inspect the output carefully rather than trusting automated defaults:

  • From Oracle: Use ora2pg to extract tables, constraints, sequences, and views, generating a migration complexity report before writing a single row.
  • From SQL Server: Use pgloader (which handles schema introspection, type casting, and high-speed bulk loading in a single streaming pipeline) or AWS SCT (Schema Conversion Tool) for complex T-SQL translation.

Schema Conversion Hygiene Rules

  • Defer Secondary Indexes & Foreign Keys: During initial historical data loads, create only primary keys. Build secondary indexes (CREATE INDEX CONCURRENTLY) and validate foreign keys (ALTER TABLE ... VALIDATE CONSTRAINT) after bulk data lands—this cuts initial load times by 60%–80%.
  • Visual Schema Diff Verification: Open both the legacy source database and target PostgreSQL schema side-by-side in a multi-engine SQL IDE (such as JetBrains DataGrip or DBeaver) to verify nullability, default values, and check constraints match 1:1.

4. Phase 3 — Continuous Data Sync (CDC) for Low-Downtime Cutover

For databases larger than 50 GB, a weekend “stop the app, dump, and restore” cutover carries high rollback risk. Instead, use Change Data Capture (CDC):

  1. Snapshot Load: Perform a consistent initial copy of historical rows using pgloader or a CDC connector (such as Debezium, Airbyte, or cloud-native DMS).
  2. Continuous Log Streaming: Stream ongoing INSERT, UPDATE, and DELETE changes from Oracle Redo/Archive logs (LogMiner) or SQL Server CDC tables into PostgreSQL in near real time.
  3. Shadow Read Validation: Point a staging or shadow-traffic build of your application against PostgreSQL while CDC keeps it synchronized, allowing QA and performance engineers to catch query regressions under real data distributions before cutover day.

5. Phase 4 — Post-Migration Parameter Tuning & Validation SQL

A fresh PostgreSQL installation ships with conservative defaults meant for small virtual machines. Before benchmarking or cutting over production traffic, tune these core parameters in postgresql.conf (assuming modern NVMe/SSD storage):

# Memory Allocation (Example for a 32 GB RAM dedicated DB server)
shared_buffers = 8GB                  # ~25% of total system RAM
effective_cache_size = 24GB           # ~75% of total RAM (guides query planner)
work_mem = 32MB                       # Per sort/hash operation (monitor concurrency!)
maintenance_work_mem = 2GB            # Accelerates post-load CREATE INDEX & VACUUM

# Storage & Query Planner (Essential for SSD/NVMe)
random_page_cost = 1.1                # Default 4.0 assumes spinning HDDs and over-favors seq scans
effective_io_concurrency = 200        # Concurrent I/O requests for SSD arrays

# Write-Ahead Log & Checkpoints
wal_buffers = 64MB
checkpoint_completion_target = 0.9    # Spreads checkpoint I/O evenly to prevent latency spikes
max_wal_size = 4GB

Post-Migration Validation & Health Audit Script

Run this SQL audit suite in PostgreSQL immediately after initial data load and again 48 hours after cutover to verify row density, index utilization, and sequence alignment:

-- 1. Verify table sizes, live row estimates, and index vs. sequential scan ratios
WITH table_stats AS (
    SELECT 
        schemaname,
        relname AS table_name,
        n_live_tup AS estimated_rows,
        pg_total_relation_size(relid) AS total_bytes,
        idx_scan,
        seq_scan
    FROM pg_stat_user_tables
)
SELECT 
    schemaname,
    table_name,
    estimated_rows,
    pg_size_pretty(total_bytes) AS total_size,
    CASE 
        WHEN (idx_scan + seq_scan) > 0 
        THEN ROUND(100.0 * idx_scan / (idx_scan + seq_scan), 2) 
        ELSE 100.0 
    END AS index_usage_pct
FROM table_stats
ORDER BY total_bytes DESC;

-- 2. Identify unused indexes carried over from automated schema conversion
SELECT 
    schemaname || '.' || relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 3. Verify all sequences are strictly ahead of current MAX(id) values
-- (Prevents "duplicate key value violates unique constraint" errors after cutover)
SELECT 
    schemaname,
    sequencename,
    last_value
FROM pg_sequences
ORDER BY schemaname, sequencename;

6. Cutover Day Execution Checklist

  1. Freeze Application Writes: Put the application into brief maintenance mode and verify incoming sessions drop to zero on the Oracle/SQL Server source.
  2. Confirm Zero CDC Lag: Wait until the final redo/transaction log batch applies cleanly to PostgreSQL and stop the CDC pipeline.
  3. Run ANALYZE & Sequence Verification: Execute ANALYZE; database-wide so the PostgreSQL cost-based optimizer has fresh column histograms before the first production query arrives.
  4. Keep Legacy Source Read-Only for 72 Hours: Keep your old Oracle or SQL Server instance online in read-only mode (with reverse-CDC if required by compliance) as an instant safety net until you complete a full business cycle on PostgreSQL.


Previous Post
Zero-Downtime Database Failover & Point-in-Time Recovery (PITR): A Practical RPO/RTO Architecture Guide
Next Post
DataGrip vs. DBeaver Pro vs. TablePlus: Which SQL Client Survives 50-Million-Row Production Diagnostics?