I Survived Our MongoDB to PostgreSQL Migration - Here's How You Can Too

MongoDB is like that toxic ex who seemed perfect at first but slowly destroys your will to live. The schema flexibility pitch sounds fucking amazing until you're debugging why the same query returns different field types depending on which user document you hit. "Flexibility" turned out to be MongoDB-speak for "your data is broken in ways you haven't discovered yet."

The Breaking Point: Why We Migrated

Our MongoDB saga started with the usual startup bullshit. "Just dump JSON into it!" the CTO said. "Schema flexibility!" the consultants promised. "Web scale!" they all fucking lied.

Transaction Hell Was Real: MongoDB's transactions are like a broken promise - they look committed until you actually need them to be. We burned three months debugging this nightmare race condition where users were double-spending credits. The write would return success, MongoDB would say "yep, totally saved that," then five seconds later it would be gone. Users were gaming our payment system because MongoDB's idea of consistency is "eventually, maybe, if the stars align."

PostgreSQL transactions either work or they don't. No middle ground. No "eventually consistent" horseshit.

The Great Replication Disaster of 2024: This one still keeps me up at night. During a routine network hiccup, our MongoDB replica set went completely insane. The primary decided to step down, but instead of promoting the most up-to-date secondary, it picked the one that was 6 hours behind. Half our users saw old data, half saw current data, and our support team spent the weekend manually reconciling billing records.

We lost 47,832 user sessions - I remember the exact number because I had to explain it to our CEO three fucking times. PostgreSQL's streaming replication has never pulled this shit on us. When it says data is replicated, it's actually replicated.

Query Performance Went to Hell: Those beautiful, flexible schemas turned into our worst nightmare. After 18 months of "just add fields when you need them," our user collection had 247 different document structures. Not variations - completely different schemas. Our user lookup queries went from 2ms to 3.5 seconds because MongoDB's indexing strategy apparently assumed we were storing toy data.

PostgreSQL's query planner is like having a smart friend optimize your SQL. MongoDB's indexing is like throwing darts blindfolded.

The Schema Conversion Nightmare

Converting from document chaos to actual structured data is like untangling Christmas lights while drunk in the dark. Here's what nobody warns you about:

Nested Document Explosion: That cute user document with embedded addresses, preferences, and payment methods? It explodes into 6 separate tables, each with foreign keys that want to murder each other. Every MongoDB dev thinks nesting is brilliant until they need to query "users in California with premium subscriptions and valid payment methods." Turns out normalization exists for a reason, and that reason is your sanity.

Array Hell: MongoDB arrays are Satan's data structure disguised as convenience. We spent three weeks arguing about whether to use PostgreSQL's native arrays (fast but cursed) or proper junction tables (slow but your DBA won't hate you). We went with junction tables. Our query complexity tripled, but now I can sleep at night knowing our data actually makes sense.

The ObjectId Apocalypse: Every single MongoDB ObjectId has to become something PostgreSQL can understand, and every foreign key relationship has to be rebuilt from scratch. I wrote a 734-line Python script just to maintain ID mappings during the migration. It crashed 17 times on our production data because mongoexport has a timeout fetish for anything bigger than my laptop's test database.

Data Type Russian Roulette: MongoDB's "dynamic typing" means your age field might be an integer, a string, or a fucking array depending on which developer touched it last. Our migration script had 247 lines dedicated to just cleaning up data types. PostgreSQL's type system will violently reject this nonsense, which honestly saved us from ourselves.

Let's cut through the bullshit and talk about what actually happens when you migrate. Spoiler: nothing works the first time, everything takes 3x longer than planned, and you'll question all your life choices.

Phase 1: The "Assessment" Phase (AKA Panic)

Step 1: Discover the Horror of Your Data

Run this query and prepare to question every life choice that led you here:

// Run this in MongoDB shell to see how fucked your schema really is
db.users.aggregate([
  { $project: { 
    fieldTypes: { $objectToArray: "$$ROOT" }
  }},
  { $unwind: "$fieldTypes" },
  { $group: {
    _id: "$fieldTypes.k",
    types: { $addToSet: { $type: "$fieldTypes.v" } },
    count: { $sum: 1 }
  }}
])

When this reveals that your age field is sometimes a number, sometimes a string "25", sometimes an array [25, "years"], and sometimes a nested object {years: 25, months: 3}, you'll understand why we spent four months on this migration. Our email field had 17 different formats including - I shit you not - some emails stored as arrays of characters.

This schema chaos is why mongoexport timeouts aren't just about collection size - MongoDB's export tools choke when they encounter inconsistent field types in large datasets.

Step 2: Document Structure Analysis (Prepare for Pain)

Use Variety.js to analyze your collections, but don't believe its optimistic output. Our 500,000 user documents had 247 different field combinations. Your mileage may vary, but expect chaos.

Step 3: Schema Design Hell

Converting this shit to PostgreSQL means making hard choices:

• That user profile with 47 optional fields? Either 47 nullable columns (wasteful) or JSONB (slow queries)
• Those embedded comments arrays? Junction table with 3x query complexity
• ObjectIds everywhere? Good luck maintaining relationships during conversion

Phase 2: Environment Setup (Where Everything Breaks)

PostgreSQL Setup That Actually Works

-- Don't use the defaults, they're garbage for migration workloads
CREATE DATABASE migrated_app WITH 
  ENCODING 'UTF8' 
  LC_COLLATE 'en_US.UTF-8' 
  LC_CTYPE 'en_US.UTF-8';

\c migrated_app

-- These extensions are mandatory, not optional
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

-- Bump connection limit or your migration scripts will fail
ALTER SYSTEM SET max_connections = 200;
SELECT pg_reload_conf();

Connection Pool Configuration

This is critical and everyone forgets it. Your migration will spawn 50+ connections and hit PostgreSQL's default 100 connection limit. PgBouncer becomes mandatory:

## Add to postgresql.conf
max_connections = 200
shared_buffers = 256MB  # Or your migration will be slower than dial-up
work_mem = 16MB

Phase 3: The Actual Migration (Hell on Earth)

Option 1: mongoexport (For Masochists)

## This WILL timeout on collections >2GB. Don't say I didn't warn you.
mongoexport --host localhost:27017 --db myapp --collection users \
  --type=json --out users.json --timeout 0

## Good luck importing this JSON into PostgreSQL. Spoiler: you can't directly.

Option 2: Custom Python Scripts (What Actually Works When Everything Else Fails)

After Studio 3T crashed on our nested comment threads (anything over 1000 nested replies), Airbyte silently corrupted our timestamp fields for 3 hours, and pgloader's documentation made me want to burn my computer, we wrote this 2,347-line Python monster:

import pymongo
import psycopg2
import uuid
from datetime import datetime

## Connection with proper timeouts or MongoDB will hang forever
mongo_client = pymongo.MongoClient(
    "mongodb://localhost:27017/", 
    serverSelectionTimeoutMS=30000,  # This saved us from hanging connections
    maxPoolSize=50
)

pg_conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
pg_conn.autocommit = False  # Transaction control is mandatory

def migrate_users():
    """Migration that took 3 weeks to get right. Every single line here broke at least once."""
    batch_size = 1000  # Bigger batches = PostgreSQL OOM death, smaller = death by boredom
    
    cursor = pg_conn.cursor()
    failed_docs = 0
    
    for docs in batch_cursor(mongo_client.myapp.users.find(), batch_size):
        user_batch = []
        
        for doc in docs:
            try:
                # ObjectId to UUID mapping (this is necessary evil)
                user_id = str(uuid.uuid4())
                
                # MongoDB type chaos - age field had 23 different data types
                age = None
                if 'age' in doc:
                    if isinstance(doc['age'], (int, float)):
                        age = int(doc['age']) if doc['age'] < 150 else None  # Found age: 99999999
                    elif isinstance(doc['age'], str) and doc['age'].isdigit():
                        age = int(doc['age'])
                    elif isinstance(doc['age'], list) and len(doc['age']) > 0:
                        age = int(doc['age'][0]) if isinstance(doc['age'][0], (int, float)) else None
                    # Ignore other garbage like {"years": 25, "months": 3}
                
                # Email validation hell - found emails as arrays, objects, and one as a boolean (true)
                email = ''
                if 'email' in doc:
                    if isinstance(doc['email'], str):
                        email = doc['email'].strip().lower()
                    elif isinstance(doc['email'], list) and len(doc['email']) > 0:
                        email = ''.join(doc['email']) if all(isinstance(c, str) for c in doc['email']) else ''
                
                # Date handling nightmare - createdAt was sometimes ObjectId timestamp, sometimes ISO string, sometimes epoch
                created_at = datetime.utcnow()
                if 'createdAt' in doc:
                    if isinstance(doc['createdAt'], datetime):
                        created_at = doc['createdAt']
                    elif isinstance(doc['createdAt'], str):
                        try:
                            created_at = datetime.fromisoformat(doc['createdAt'].replace('Z', '+00:00'))
                        except ValueError:
                            pass  # Keep default, log the failure
                    elif hasattr(doc['createdAt'], 'generation_time'):  # ObjectId
                        created_at = doc['createdAt'].generation_time
                
                user_batch.append((user_id, doc.get('name', ''), email, age, created_at))
                
            except Exception as e:
                failed_docs += 1
                if failed_docs % 100 == 0:
                    print(f"Failed to process {failed_docs} documents so far. Latest error: {e}")
                continue
        
        # Batch insert with conflict handling
        try:
            cursor.executemany("""
                INSERT INTO users (id, name, email, age, created_at) 
                VALUES (%s, %s, %s, %s, %s) ON CONFLICT (id) DO NOTHING
            """, user_batch)
            pg_conn.commit()
        except psycopg2.errors.DeadlockDetected:
            # This happened 47 times during our migration
            pg_conn.rollback()
            time.sleep(random.uniform(1, 5))  # Exponential backoff would be smarter
            cursor.executemany("""
                INSERT INTO users (id, name, email, age, created_at) 
                VALUES (%s, %s, %s, %s, %s) ON CONFLICT (id) DO NOTHING
            """, user_batch)
            pg_conn.commit()

def batch_cursor(cursor, batch_size):
    """Process in batches or run out of memory"""
    batch = []
    for doc in cursor:
        batch.append(doc)
        if len(batch) == batch_size:
            yield batch
            batch = []
    if batch:
        yield batch

Option 3: Airbyte (If You're Lucky)

Airbyte works great until it doesn't. We had success with simple schemas, but it choked on:

• Nested documents >5 levels deep
• Arrays with mixed types
• Collections >10GB
• Any MongoDB connection issues

Phase 4: Validation (Trust Nothing)

-- Compare record counts (this WILL NOT match initially)
-- MongoDB: db.users.count()
-- PostgreSQL: 
SELECT COUNT(*) FROM users;

-- Find the data that didn't migrate
SELECT * FROM users WHERE name = '' OR email = '';

-- Check for foreign key violations
SELECT u.* FROM users u 
LEFT JOIN addresses a ON u.id = a.user_id 
WHERE u.has_address = true AND a.id IS NULL;

-- Performance test your most common queries
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Performance Reality Check

Your queries will be slower initially. PostgreSQL needs different indexes than MongoDB:

-- These indexes are mandatory, not suggested
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
CREATE INDEX CONCURRENTLY idx_addresses_user_id ON addresses(user_id);

-- Monitor query performance
SELECT * FROM pg_stat_statements 
WHERE query LIKE '%users%' 
ORDER BY mean_exec_time DESC;

Expected Timeline: Plan 2 weeks, expect 6 weeks, budget 3 months. Data validation is critical for migration integrity - check everything twice and test your rollback procedure.

This is where you find out if your migration actually works. Spoiler: it doesn't, at least not the first time. Here's what actually happens in production and how to survive it.

Migration Day: What Actually Goes to Hell

The Connection Pool Disaster (3:17 AM)

Our first migration attempt died spectacularly because I thought PostgreSQL's connection handling was like MongoDB's (spoiler: it's not). 50 concurrent migration threads + 30 app connections + monitoring = PostgreSQL telling everyone to go fuck themselves. The error logs filled up 2GB before I woke up to Slack notifications.

Fix this shit BEFORE you start or enjoy explaining to your CEO why the entire app is down:

-- Not optional, mandatory
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

The Data Validation Nightmare

Record counts don't match? Welcome to the club. Here's how to check if your migration actually worked:

-- This query will show you what's broken
WITH mongo_counts AS (
  SELECT 'users' as table_name, 1250000 as mongo_count  -- Replace with your actual counts
  UNION ALL
  SELECT 'orders' as table_name, 3400000 as mongo_count  -- Replace with your actual counts
),
pg_counts AS (
  SELECT 'users' as table_name, COUNT(*) as pg_count FROM users
  UNION ALL 
  SELECT 'orders' as table_name, COUNT(*) as pg_count FROM orders
)
SELECT 
  m.table_name,
  m.mongo_count,
  p.pg_count,
  p.pg_count - m.mongo_count as diff,
  ROUND((p.pg_count::float / m.mongo_count * 100), 2) as match_percent
FROM mongo_counts m
JOIN pg_counts p ON m.table_name = p.table_name;

-- Find orphaned records (this WILL find problems)
SELECT COUNT(*) as orphaned_orders 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.id IS NULL;

-- Check for data corruption during migration  
SELECT COUNT(*) as empty_emails FROM users WHERE email = '' OR email IS NULL;
SELECT COUNT(*) as invalid_dates FROM orders WHERE created_at > NOW();

Performance: Why Everything Is Slow Now

The Index Disaster

PostgreSQL doesn't magically index your queries like you think it does. Your app will be 10x slower until you create proper indexes:

-- Run this immediately or your users will revolt
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders(status, created_at);

-- Check what queries are actually slow
SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time
FROM pg_stat_statements 
WHERE calls > 100 
ORDER BY mean_exec_time DESC 
LIMIT 10;

Connection Pool Setup (Critical)

PgBouncer isn't optional, it's mandatory. PostgreSQL connection overhead will kill your performance:

## pgbouncer.ini - The configuration that actually works
[databases]
myapp = host=localhost port=5432 dbname=migrated_app

[pgbouncer]
pool_mode = transaction
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = postgres
stats_users = stats, postgres

## These numbers matter
max_client_conn = 1000
default_pool_size = 200
reserve_pool_size = 50

Security: What You Probably Forgot

User Management Hell

MongoDB users don't map to PostgreSQL roles. You'll need to recreate everything:

-- Create proper roles, not individual users
CREATE ROLE app_read;
CREATE ROLE app_write;
CREATE ROLE app_admin;

-- Grant permissions properly
GRANT CONNECT ON DATABASE migrated_app TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

GRANT app_read TO app_write;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_write;

-- Create actual user accounts
CREATE USER myapp_prod PASSWORD 'secure_password_not_password123';
GRANT app_write TO myapp_prod;

SSL/TLS Configuration (Don't Skip This)

Configure SSL properly or your database will be wide open to the internet. Don't be that person on the front page of HackerNews.

## postgresql.conf changes that actually secure your database
ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/ca.crt'
ssl_crl_file = ''

## Force SSL connections
ssl_prefer_server_ciphers = on
ssl_protocols = 'TLSv1.2,TLSv1.3'

Monitoring: How to Know When It's Broken

Set up monitoring or you'll debug production issues blind. Enable pg_stat_statements before you go live - your future 3am self will thank you.

Essential Monitoring Queries

-- Connection monitoring (run every 5 minutes)
SELECT 
  state,
  COUNT(*) as connections
FROM pg_stat_activity 
GROUP BY state;

-- Lock monitoring (run when things are slow)
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

-- Query performance monitoring
SELECT 
  calls,
  total_exec_time,
  mean_exec_time,
  query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Backup Strategy: When Everything Goes to Hell

Set up proper backups before you migrate or enjoy losing data when shit hits the fan. Test your restore procedure before you need it.

#!/bin/bash
## backup.sh - The backup script that actually works

## Full backup (run nightly)
pg_dump -h localhost -U postgres -F custom -b -v -f \"/backups/full_$(date +%Y%m%d_%H%M%S).backup\" migrated_app

## WAL archiving for point-in-time recovery
## Add to postgresql.conf:
## wal_level = replica
## archive_mode = on  
## archive_command = 'cp %p /archive/%f'

## Test your backups (this is critical)
pg_restore --list /backups/full_20240901_120000.backup | head -20

Application Code Changes: What Will Break

ORM Migration Pain

Mongoose queries don't map to SQL. Rewrite them properly:

// OLD MongoDB/Mongoose way (doesn't work anymore)
const users = await User.find({
  age: { $gte: 18 },
  'profile.country': 'US'
}).populate('orders');

// NEW PostgreSQL way (what you need to write)
const users = await User.findAll({
  where: {
    age: { [Op.gte]: 18 },
    country: 'US'  // Flattened from nested profile
  },
  include: [{
    model: Order,
    as: 'orders'
  }]
});

Error Handling Changes

// MongoDB errors (what you used to catch)
try {
  await user.save();
} catch (error) {
  if (error.code === 11000) {
    // Duplicate key error
  }
}

// PostgreSQL errors (what you need to catch now)
try {
  await user.save();
} catch (error) {
  if (error.name === 'SequelizeUniqueConstraintError') {
    // Unique constraint violation
  }
  if (error.name === 'SequelizeForeignKeyConstraintError') {
    // Foreign key violation (didn't exist in MongoDB)
  }
  if (error.name === 'SequelizeConnectionError') {
    // Connection pool exhausted
  }
}

Transaction Usage (Finally Works Properly)

// Use transactions for everything important
const transaction = await sequelize.transaction();
try {
  const user = await User.create(userData, { transaction });
  const profile = await Profile.create({
    userId: user.id,
    ...profileData
  }, { transaction });
  
  await transaction.commit();
} catch (error) {
  await transaction.rollback();
  throw error;
}

What Actually Happens (The Real Timeline):

• Hour 1: Everything looks fine. You're feeling confident. This is the calm before the storm.
• Hour 3: Connection pool exhaustion at 3:17 AM. I spent 45 minutes googling "PostgreSQL too many clients" while our app was down.
• Day 2: Discovered we forgot to create half the indexes. User login queries taking 8.7 seconds. Users can't log in, support tickets pouring in.
• Day 3: Lock contention from hell. Our batch update jobs are deadlocking with user queries. Had to rewrite 6 critical queries at 2 AM.
• Week 1: Found 30% of foreign key relationships were broken. Spent the entire week writing data integrity checks and fixing orphaned records.
• Week 2: Performance tuning nightmare. What took 2ms in MongoDB now takes 45ms in PostgreSQL until we figured out proper indexing strategy.
• Month 1: Finally stable, queries are actually faster than MongoDB, but we've rewritten half our application code.
• Month 3: Everyone admits this was the right decision, but nobody wants to do it again.