How We Migrated a 1.2TB PostgreSQL Database with Zero Downtime: A Vietnam Offshore Case Study

Nobody wakes up excited to migrate a petabyte-scale database.

But sometimes, you don’t have a choice. Your RDS instance is hitting I/O limits. Connection pools are saturated. Your primary key sequence is about to wrap around. And your CEO just announced a new feature launch that depends on a schema you can’t deploy because the migration would take 14 hours of downtime.

Vietnam Outsourcing: Why Southeast Asia’s Tech Hub Is Engineering the Future

TL;DR: Vietnam outsourcing offers top-tier engineering talent at 40% lower costs than China or Eastern Europe. With a… ...

That was our client’s situation six months ago.

A US-based fintech company processing over 2 million transactions daily had outgrown their PostgreSQL 12 instance on AWS. Their 1.2TB database was running on a single `db.r5.24xlarge` instance, and it was screaming for help.

Why Your Multi-Agent System Needs a Shared Memory Layer: Practical Lessons from Production

Why Your Multi-Agent System Needs a Shared Memory Layer: Practical Lessons from Production I’ve seen it happen more… ...

The requirement? Migrate to a new, sharded PostgreSQL 15 cluster with a completely different schema design. Zero downtime. No maintenance window longer than 30 seconds.

Our team in Ho Chi Minh City took it on. Here’s exactly how we pulled it off.

The Problem: Why a Simple pg_dump Would Kill Us

Let’s be honest about the math.

A `pg_dump` of a 1.2TB database takes roughly 4-6 hours over a 10Gbps connection. The restore takes another 8-12 hours. That’s a full day of downtime.

For a fintech processing ~$50M in transactions daily? Not happening.

We considered the standard approaches:

pg_dump/pg_restore: Too slow. 18+ hours of downtime.
AWS DMS (Database Migration Service): Tried it. LOB columns caused replication lag that hit 6+ hours. Not reliable.
Logical replication with pglogical: Good option, but the schema changes were too drastic. The target had 40% different table structures.
Physical replication: Can’t cross major PostgreSQL versions (12 to 15) with physical replication.

We needed a hybrid approach. Here’s what we built.

The Strategy: Dual-Write + Logical Replication with a Twist

Phase 1: Set up logical replication to a staging database.

We created a PostgreSQL 15 staging instance and used native logical replication to stream changes from the source. But here’s the trick—we didn’t replicate the entire schema.

sql
-- On source (PostgreSQL 12)
CREATE PUBLICATION migration_pub FOR ALL TABLES;

-- On staging (PostgreSQL 15)  
CREATE SUBSCRIPTION migration_sub
CONNECTION 'host=source-host port=5432 dbname=sourcedb user=replicator password=****'
PUBLICATION migration_pub
WITH (copy_data = true);

This took 14 hours to copy the initial data. But during those 14 hours, the source database kept running. Users didn’t feel a thing.

Phase 2: Build the transformation layer.

The problem with direct logical replication? Schema mismatch. Our target had:

New partitioning on `created_at`
Renamed columns for consistency
New indexes for the upcoming feature
Data type changes (e.g., `text` to `jsonb` for metadata)

We couldn’t use `pglogical`’s built-in transformation. Instead, we built a Go-based middleware service that consumed the WAL changes and applied them to the target schema.

go
// Simplified transformation logic
func transformInsert(walRecord WALRecord) ([]string, error) {
    switch walRecord.TableName {
    case "transactions":
        // Map old column names to new schema
        sql := fmt.Sprintf(
            `INSERT INTO transactions (id, user_id, amount_cents, metadata, created_at, updated_at)
             VALUES ($1, $2, $3, $4::jsonb, $5, $6)
             ON CONFLICT (id) DO NOTHING`,
            walRecord.NewValues["id"],
            walRecord.NewValues["customer_id"], // renamed
            walRecord.NewValues["amount"],       // was decimal, now cents (int)
            walRecord.NewValues["metadata"],
            walRecord.NewValues["created_at"],
            walRecord.NewValues["updated_at"],
        )
        return []string{sql}, nil
    }
    // ... 40 more table mappings
}

This service ran in Kubernetes with 8 replicas. It processed ~3,000 WAL events per second at peak. We monitored lag using a custom Prometheus metric.

Phase 3: Dual-write for the final cutover.

This was the critical part. For the last 48 hours before cutover, we enabled dual-writes in the application layer.

The application wrote to both the old PostgreSQL 12 database and the new PostgreSQL 15 cluster. If the write to the new cluster failed, we logged it and retried via a dead-letter queue.

python
# Simplified dual-write logic in the application
def create_transaction(transaction_data):
    # Write to old database (primary)
    old_id = db_old.execute(
        "INSERT INTO transactions ... RETURNING id"
    )
    
    # Write to new database (async, fire-and-forget with retry)
    try:
        db_new.execute(
            "INSERT INTO transactions_new ...",
            transaction_data
        )
    except Exception as e:
        # Log to dead-letter queue for later replay
        dead_letter_queue.send({
            "type": "transaction",
            "data": transaction_data,
            "error": str(e)
        })
    
    return old_id

Phase 4: The cutover.

At 2:00 AM PST on a Wednesday (our client’s lowest traffic period), we executed the switch.

Stopped application writes to the old database
Waited for replication lag to hit zero
Ran a final reconciliation script comparing row counts and checksums
Switched the DNS endpoint to point to the new cluster
Enabled writes on the new cluster

Total downtime: 22 seconds.

The reconciliation script found 47 discrepancies out of 1.2TB. All were resolved within 5 minutes using the dual-write dead-letter queue.

The Results: What We Actually Achieved

Metric	Before	After	Improvement
Query latency (p95)	340ms	87ms	74% faster
Write throughput	4,200 tps	12,500 tps	3x increase
Storage cost (monthly)	$18,400	$11,200	39% reduction
Maintenance window	14+ hours	22 seconds	99.96% reduction

But the real win? The feature that required the new schema launched on schedule. The client’s revenue increased by 22% in the following quarter.

The Hard Lessons We Learned

1. Test the WAL transformation at scale.

Our Go middleware worked perfectly at 100 events/second. At 3,000 events/second? We found three race conditions in the first hour. Always stress-test your transformation layer at 10x expected load.

2. Monitor replication lag with business context.

PostgreSQL’s `pg_stat_replication` shows WAL lag in bytes. That’s useless for a fintech. We built a custom metric that tracked “unreplicated transactions” by status. If a “pending settlement” transaction hadn’t replicated in 5 seconds, we alerted.

3. Your team matters more than your tools.

Honestly, the technology was straightforward. The hard part was coordination. Our Vietnamese team in Ho Chi Minh City worked overlapping shifts with the client’s US-based team. We had daily standups at 9:00 PM ICT (8:00 AM EST). Every decision was documented in a shared Notion page with timestamps.

The senior database engineer on our side, Nguyen, had previously migrated a 3TB MongoDB cluster for a logistics company in Can Tho. That experience was invaluable. When we hit a WAL retention issue on day 3, he recognized the pattern immediately.

4. Have a rollback plan that actually works.

We built a rollback script that could reverse the entire migration in under 2 minutes. We tested it in staging three times. We never needed it, but knowing it existed kept the team calm during the cutover.

Why This Matters for Your Next Database Migration

Database migrations don’t have to be terrifying.

The key is accepting that you can’t move a 1.2TB database atomically. You have to break it into phases, use logical replication for the bulk data, and then handle the final cutover with dual-writes.

But more importantly, you need a team that’s done this before. Not just read about it in a blog post. Actually done it at 3 AM with a client’s production data on the line.

Our team in Vietnam has completed 47 database migrations in the last 18 months. PostgreSQL, MySQL, MongoDB, even a legacy Oracle database. We’ve seen every failure mode and built playbooks for each one.

The cost of getting this wrong? For a fintech processing $50M daily? Catastrophic.

The cost of getting it right with ECOA AI? A senior PostgreSQL engineer at $3,000/month, a middle engineer at $2,000/month, and a 3-week project timeline.

Think about that math.

Frequently Asked Questions

Q: Can logical replication handle schema changes during migration?

A: No, native PostgreSQL logical replication requires identical schemas. If you’re changing the schema (like we did), you need a middleware layer to transform the WAL events. We used a Go service for this, but you could also use Debezium with Kafka Streams. Just don’t expect pglogical to handle column renames or data type changes automatically.

Q: How do you verify data integrity after a zero-downtime migration?

A: We ran three verification steps: (1) row count comparison per table, (2) checksum comparison using `pg_hashtext` on concatenated row values, and (3) a random sample of 10,000 transactions verified against the source. The dual-write dead-letter queue captured any writes that failed during the final cutover window. We replayed those after verification completed.

Q: What’s the maximum database size you can migrate with zero downtime?

A: We’ve successfully migrated databases up to 3.5TB using this approach. The limiting factor isn’t the database size—it’s the WAL generation rate. If your database generates more than 500MB of WAL per minute, the replication lag becomes hard to manage. For those cases, we recommend sharding before migration or using a CDC tool like Debezium instead of native logical replication.

Q: How long does a typical zero-downtime migration take with a Vietnamese team?

A: For a 1TB-2TB database with moderate schema changes, expect 3-4 weeks. Week 1 is planning and staging setup. Week 2 is the bulk data copy and transformation layer development. Week 3 is dual-write testing and cutover preparation. Week 4 is the actual cutover and monitoring. The actual downtime is usually under 60 seconds.