Migrasi MongoDB → Postgres JSONB: 200GB

TL;DR

• Migration effort: 6 minggu untuk 200GB (validated 1 project klien Jakarta Q3 2025).
• Saving cost: 60% (MongoDB Atlas Rp 4.5 juta → Supabase Postgres Pro Rp 1.8 juta/bulan).
• Verdict: Conditional Recommend untuk SaaS Indonesia yang Atlas bill > Rp 3 juta/bulan dan butuh relational query.

Konteks

Saya migrate 1 klien SaaS Jakarta dari MongoDB Atlas ke Supabase Postgres Q3 2025:

• Klien: SaaS HR + payroll untuk SMB Indonesia, 8K user, 12 collection
• Data size: 220GB (dengan history 3 tahun)
• MongoDB bill sebelumnya: Rp 4.5 juta/bulan (M30 cluster Atlas Singapore)
• Postgres bill sekarang: Rp 1.8 juta/bulan (Supabase Pro + tambahan storage)
• Migration duration: 6 minggu (Juli - Agustus 2025)
• Downtime customer-facing: 0 menit (dual-write strategy)

Plus 2 project migrate smaller scale: MongoDB self-host 30GB → Postgres self-host (1 minggu effort), MongoDB 80GB → Postgres Neon (3 minggu effort).

Pricing comparison (Juni 2026)

MongoDB Atlas

• Shared (M0): Free hingga 512MB
• Serverless: $0.10/GB-hour ~ Rp 480rb/bulan untuk 200GB at idle
• Dedicated M10: $57/mo = Rp 912rb
• Dedicated M30: $290/mo = Rp 4.6 juta (untuk klien saya 200GB)
• Dedicated M40: $560/mo = Rp 9 juta

Postgres (Supabase Pro)

• Pro: $25/mo = Rp 400rb baseline
• Storage extra: $0.125/GB-bulan
• Untuk 200GB: Rp 400rb + (200 × $0.125 × Rp 16rb) = Rp 800rb
• Tambah Compute upgrade XL ($110/mo) untuk match M30 performance: total Rp 2.5 juta/mo

Saving real

Klien HR saya: Rp 4.6 juta → Rp 1.8 juta (Supabase Pro + Large Compute). Saving Rp 2.8 juta/bulan = Rp 33.6 juta/tahun.

Migration cost (6 minggu × 1 senior dev × Rp 25 juta/bulan equivalent rate): Rp 37 juta. ROI break-even di 13 bulan.

Why migrate

1. Cost

60% reduction validated. Bill scale Postgres lebih predictable (tier-based) dibanding MongoDB Atlas (resource-based).

2. Relational query

HR SaaS klien butuh banyak JOIN: employee × department × payroll × attendance × leave. MongoDB pattern: lookup aggregation (lebih lambat). Postgres JOIN native — query 3-5x lebih cepat.

3. Ekosistem

• Drizzle ORM lebih matang untuk Postgres
• pgvector untuk AI feature future (RAG document HR policy)
• RLS untuk multi-tenant (klien plan add tenant support)
• Supabase Auth + Storage native (replace Auth0 + S3 saving Rp 600rb/bulan extra)

4. Tim skill

2 dev tim klien lebih familiar SQL dari MongoDB aggregation pipeline. Productivity setelah migrate naik 20-30%.

Why NOT migrate (kondisi)

Don’t migrate jika:

1. Tim deep di MongoDB: re-training cost > saving
2. Schema benar-benar document-shaped: nested 5+ level, dynamic schema heavy
3. Workload time-series ultra-high: 100K+ writes/second per collection — MongoDB sharding lebih mature
4. Bill MongoDB masih < Rp 1 juta/bulan: saving tidak material
5. Atlas-specific feature heavy: Atlas Search, Atlas Vector Search, Atlas Triggers — Postgres alternative ada tapi different ergonomics

Migration phase-by-phase

Phase 1: Schema design (Week 1)

Untuk setiap MongoDB collection, decide:

• Convert ke table relational (most case)
• Keep sebagai JSONB column (nested document yang sebenarnya document-shaped)
• Split ke 2-3 table (denormalized field di MongoDB jadi normalized di Postgres)

Example HR SaaS:

MongoDB collection "employees":
{
  _id: ObjectId,
  name: "Asti L",
  department: { id: 5, name: "Engineering" },  // embedded
  payroll_history: [...],  // array nested
  custom_fields: { ... },  // dynamic
}

Postgres schema:
- table employees (id, name, department_id, custom_fields JSONB)
- table departments (id, name)
- table payroll_history (id, employee_id, period, amount, ...)

Decision rules:

• Embedded yang stable + referenced often → normalize ke table
• Nested array yang sering query/aggregate → normalize ke table
• Custom fields dynamic per tenant → keep JSONB

Effort: 1 minggu untuk 12 collection.

Phase 2: Tool + script development (Week 2)

Write migration script:

// migrate/employees.ts
import { MongoClient } from 'mongodb';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { employees, departments } from '@/db/schema';

const mongo = new MongoClient(process.env.MONGO_URL!);
const pg = postgres(process.env.PG_URL!);
const db = drizzle(pg);

async function migrateEmployees() {
  const cursor = mongo.db('hr').collection('employees').find({});
  let batch: any[] = [];

  for await (const doc of cursor) {
    batch.push({
      id: doc._id.toString(),
      name: doc.name,
      department_id: doc.department.id,
      custom_fields: doc.custom_fields ?? {},
      created_at: doc.createdAt,
      updated_at: doc.updatedAt,
    });

    if (batch.length === 1000) {
      await db.insert(employees).values(batch).onConflictDoNothing();
      batch = [];
      console.log('Migrated 1000 employees');
    }
  }

  if (batch.length > 0) {
    await db.insert(employees).values(batch).onConflictDoNothing();
  }
}

Batch 1000 record. Estimated throughput 5K-10K record/menit. Untuk 200GB ~10 juta record: 17-33 jam total migration time.

Effort: 1 minggu (development + test).

Phase 3: Dual-write (Week 3-4)

Modify application code untuk write ke MongoDB + Postgres simultaneously:

// services/employee.ts
export async function createEmployee(data: NewEmployee) {
  // Write ke MongoDB (existing)
  const mongoResult = await db.mongo.collection('employees').insertOne(data);

  // Mirror write ke Postgres (new)
  try {
    await db.pg.insert(employees).values({
      id: mongoResult.insertedId.toString(),
      ...data,
    });
  } catch (err) {
    // Log error tapi don't fail — Postgres write best-effort during dual-write phase
    console.error('Postgres mirror failed', err);
    // Send to monitoring
  }

  return mongoResult;
}

Deploy. Monitor:

• Mismatch rate (MongoDB count vs Postgres count)
• Error rate Postgres write
• Performance impact (additional 5-15ms latency per write)

Run dual-write 1-2 minggu untuk validate. Plus run backfill historical data parallel.

Phase 4: Read switching (Week 5)

Gradually shift read traffic:

const useReadFromPostgres = await featureFlag('use_postgres_read', { userId });

if (useReadFromPostgres) {
  return await db.pg.select().from(employees).where(eq(employees.id, id));
} else {
  return await db.mongo.collection('employees').findOne({ _id: new ObjectId(id) });
}

Canary: 10% user → 50% → 100% over 3-5 hari. Monitor:

• Query latency comparison
• Error rate
• Data consistency (sample compare MongoDB vs Postgres)

Phase 5: Cutover + cleanup (Week 6)

Setelah 100% read dari Postgres dan stable:

1. Stop write ke MongoDB (remove dual-write code)
2. Keep MongoDB read-only untuk 2-4 minggu sebagai backup
3. Setelah confident, shutdown MongoDB cluster
4. Cancel Atlas subscription

Gotcha (lesson learned)

1. ObjectId vs UUID

MongoDB ObjectId tidak native di Postgres. Opsi:

• Store as text (preserve original): id text primary key
• Convert ke UUID: butuh mapping table during migration
• Generate new IDs di Postgres: hilang foreign reference

Saya pilih store as text untuk preserve compatibility selama transition phase.

2. Date handling

MongoDB Date BSON → Postgres timestamp with time zone. Watch out untuk:

• Timezone implicit di MongoDB (server default) vs explicit Postgres
• Date string format inconsistent — sanitize sebelum insert

3. Decimal precision

MongoDB Decimal128 → Postgres numeric. Conversion harus explicit. Float-via-double-precision lose precision untuk financial data.

4. Array of subdocument

MongoDB nested array → Postgres normalize ke separate table dengan foreign key.

Example: employee.payroll_history array → payroll_history table dengan employee_id FK.

5. Text search

MongoDB $text index → Postgres tsvector + GIN index. Different syntax, similar capability.

6. Index drift

Postgres index creation lambat untuk table 100GB+. Create index AFTER bulk migration, BEFORE go-live. Plan 2-12 jam untuk index creation per large table.

7. Foreign key cascade

MongoDB tidak ada FK constraint. Postgres bisa. Saat migrate: build FK relationship — sometimes reveal data inconsistency (orphaned record di MongoDB). Plan cleanup phase.

8. Query refactor effort

Application code refactor 20-40% untuk most app. Aggregation pipeline → SQL JOIN/CTE. Pakai Claude Code untuk batch refactor — prompt: “Convert this MongoDB aggregation pipeline to Postgres SQL with Drizzle ORM.”

Performance comparison (post-migration)

Same workload klien HR (200GB, 8K user):

Query type	MongoDB	Postgres
Single document lookup	8ms	5ms
List query (filter + sort + limit 50)	35ms	25ms
Aggregation (join 3 collection)	280ms	85ms
Full-text search	95ms	45ms (tsvector)
Insert single	6ms	4ms
Bulk insert (1000 record)	180ms	250ms

Postgres menang clear untuk JOIN + complex query. MongoDB slight edge untuk bulk insert (write throughput).

Cost breakdown post-migration

Before (MongoDB Atlas M30)

• Atlas M30: Rp 4.6 juta/bulan
• Auth0: Rp 800rb/bulan
• S3 storage: Rp 200rb/bulan
• Total: Rp 5.6 juta/bulan

After (Supabase Pro + Large Compute)

• Supabase Pro + storage 200GB + Compute Large: Rp 1.8 juta/bulan
• Supabase Auth: included
• Supabase Storage: included (up to 100GB)
• Total: Rp 1.8 juta/bulan

Saving: Rp 3.8 juta/bulan = Rp 45.6 juta/tahun.

Plus tim productivity gain dari Drizzle + SQL skill: ~20-30% faster feature development.

Verdict

Conditional Recommend untuk SaaS Indonesia 2026.

Migrate jika:

• MongoDB Atlas bill > Rp 3 juta/bulan
• Workload butuh relational query (JOIN multi-collection sering)
• Tim sudah / bisa skill Postgres + SQL
• Future plan butuh ekosistem Postgres (pgvector, RLS, PostGIS)
• Customer Indonesia (Supabase Singapore lebih dekat)

Stay MongoDB jika:

• Bill < Rp 1 juta/bulan (saving tidak material)
• Workload truly document-shaped (nested deep, schema dynamic per record)
• Tim deep di MongoDB workflow
• Atlas-specific feature critical (Atlas Search, Atlas Vector, Triggers)
• Time-series ultra-high throughput (>100K writes/sec sustained)

Migration playbook:

1. Audit cost + bill projection 12 bulan
2. Schema design 1-2 minggu
3. Dual-write strategy untuk zero downtime
4. Canary read switching
5. Monitor 2-4 minggu sebelum decommission MongoDB

Untuk DB comparison: Postgres vs MySQL vs MariaDB. Untuk ORM modern: Drizzle vs Prisma vs Kysely.