🔄 CDC & Schema Migration Best Practices

⏱️ 11–13 min read · 🎯 Intermediate · 📚 Part 16 of 20 👉 Missed Part 15? SQL for DevOps — Queries You'll Actually Run in Production

🤔 Two Problems Every Growing Backend Hits

As your application grows past a single database, two new problems appear that Parts 13–15 did not cover.

Problem 1: Other systems need to know when your data changes — a search index, a cache, a reporting warehouse — but querying your production database repeatedly to check is slow and fragile.

Problem 2: Your schema needs to evolve constantly — new columns, new tables — but changing a live production database without breaking anything requires discipline most teams learn the hard way.

This post covers the tool for each: CDC (Change Data Capture) for the first, and schema migration tools for the second.

📡 What Is CDC?

Change Data Capture turns your database into an event stream. Every INSERT, UPDATE, and DELETE becomes a message that other systems can react to — instantly, without your database being queried repeatedly.

Without CDC vs with CDC — batch polling delay vs real-time event stream

The old way — batch polling:

-- A cron job runs every hour
SELECT * FROM orders WHERE updated_at > last_run;

This is slow (up to an hour stale), adds load to your production database on every run, and can silently miss rows if updated_at is not set correctly everywhere.

The CDC way — reading the transaction log directly:

CDC does not query your tables at all. It reads the database's own internal log of every change — a log that already exists and is already being written for crash recovery purposes. This means zero extra query load on your production database, and changes arrive within milliseconds of being committed.

📝 The WAL — Where CDC Gets Its Data

PostgreSQL already writes every change to a Write-Ahead Log (WAL) before applying it to the actual table — this is how PostgreSQL guarantees Durability, one of the ACID properties from Part 13. CDC tools simply read this log instead of your tables.

WAL to logical decoding to replication slot flow

Your app runs:  UPDATE orders SET status='shipped' WHERE id=501;
                              ↓
WAL records the change FIRST, before it's visible to other queries
                              ↓
Logical Decoding reads the WAL, converts it into a row-level change event
                              ↓
A Replication Slot tracks how far the CDC consumer has read

One-time setup to enable this on PostgreSQL (requires a restart):

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;

On AWS RDS, this is a parameter group setting — rds.logical_replication = 1 — rather than a direct ALTER SYSTEM command, since RDS manages the underlying configuration.

🔧 Debezium — The Standard CDC Platform

Debezium is the most widely used open-source CDC tool. It runs as a connector inside Kafka Connect, reads the PostgreSQL WAL, and publishes every change as a message to a Kafka topic — typically one topic per table.

Debezium architecture — PostgreSQL to Kafka Connect to Kafka topics to consumers

PostgreSQL (wal_level=logical)
        ↓ reads WAL
Debezium Connector (inside Kafka Connect)
        ↓ publishes
Kafka Topic: orders_db.public.orders
        ↓ consumed by
Search index, cache invalidator, analytics warehouse

A real Debezium change event looks like this:

{
  "op": "u",
  "before": { "status": "pending" },
  "after":  { "status": "shipped" },
  "source": { "table": "orders", "ts_ms": 1735689600000 }
}

The op field tells you the operation type: c for create, u for update, d for delete, and r for an initial snapshot read when Debezium first starts and needs to capture existing rows.

# Set up the required PostgreSQL permissions for Debezium
CREATE USER debezium WITH REPLICATION LOGIN PASSWORD '[your-password]';
GRANT USAGE ON SCHEMA public TO debezium;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO debezium;

💡 A newer alternative worth knowing: Redpanda Connect now offers native CDC inputs for PostgreSQL, MySQL, and other databases without requiring Kafka Connect or a JVM runtime at all — deployed as a single binary with YAML configuration. If your team wants CDC without managing a full Kafka Connect cluster, this is worth evaluating alongside Debezium.

🌍 Real Use Cases for CDC

Three real CDC use cases — search sync, cache invalidation, analytics warehouse

Search index sync — when a product's price changes in PostgreSQL, a CDC event triggers an immediate update to Elasticsearch, so search results are never stale.

Cache invalidation — when an order's status changes, a CDC event clears the corresponding Redis cache key, avoiding the stale-data problem that fixed time-to-live caching alone cannot solve cleanly.

Analytics warehouses — every change streams continuously into a reporting warehouse, replacing slow nightly batch jobs that used to compete with production traffic for database resources.

🛠️ Schema Migration Tools — Flyway vs Liquibase vs Atlas

Now for the second problem: safely changing your database structure over time. Every serious backend needs a migration tool — writing raw ALTER TABLE statements by hand across environments does not scale and is impossible to track reliably.

Flyway vs Liquibase vs Atlas comparison with strengths and weaknesses

Flyway — the simplest option. Migrations are plain, numbered SQL files, and the order they run in is determined entirely by the filename.

db/migration/
  V1__create_users.sql
  V2__create_orders.sql
  V3__add_orders_index.sql

Flyway's open-source edition strictly rolls forward — it does not support automated rollback (undo migrations exist but are a paid feature). It fits teams that want plain SQL and minimal overhead.

Liquibase — offers more advanced features: built-in rollback support written by you, drift detection between environments, and support for 50+ database engines through a database-agnostic changelog format.

databaseChangeLog:
  - changeSet:
      id: 4
      changes:
        - addColumn:
            tableName: users
            columns:
              - column: { name: email_address, type: varchar(255) }
      rollback:
        - dropColumn: { tableName: users, columnName: email_address }

Atlas — a newer, schema-as-code tool that auto-generates migrations by diffing your desired schema against the live database, rather than requiring you to hand-write every ALTER statement. It integrates directly with ORMs like Prisma, GORM, and Drizzle, making it popular in Go and TypeScript projects.

Simple rule: default to Flyway for straightforward SQL-first teams. Choose Liquibase when you need rollback support or must support multiple database engines. Consider Atlas if your stack already uses a compatible ORM.

📂 Flyway in Practice

# Migration files follow a strict naming convention
V1__create_users.sql
V2__create_orders.sql
V3__add_orders_index.sql
V4__add_email_unique.sql

Flyway tracks which migrations have run in its own table, flyway_schema_history, so it never re-applies a migration that already succeeded.

| version | description       | success |
|---------|--------------------|---------|
| 1       | create users       | true    |
| 2       | create orders      | true    |
| 3       | add orders index   | true    |

# Apply all pending migrations, in order
flyway migrate

# Verify applied migrations match your files (checksum validation)
flyway validate

⚠️ Never edit a migration file that has already been applied. Flyway stores a checksum of each migration when it runs. If you edit V2__create_orders.sql after it has already run in production, flyway validate will fail because the checksum no longer matches. Always create a new migration file to fix a mistake — never modify history.

🔀 Safe Schema Migrations — Expand and Contract

Here is the mistake that catches almost everyone at least once: renaming or dropping a column in a single migration breaks your app during a rolling deployment.

Unsafe one-step rename vs safe expand-then-contract pattern

Why this breaks: During a rolling deployment, old and new versions of your application run simultaneously for a few minutes. If a migration renames email to email_address instantly, the still-running old app code — which queries email — crashes immediately.

The safe pattern is called expand and contract:

-- Step 1: Expand — add the new column, keep the old one
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Step 2: Backfill existing data, and update app code to write to BOTH columns
UPDATE users SET email_address = email;

-- Step 3: Deploy new app code that reads from email_address only
-- (old code still works fine — the "email" column still exists)

-- Step 4: Contract — once ALL app instances are on the new code, drop the old column
ALTER TABLE users DROP COLUMN email;

Four small, safe migrations instead of one breaking change. This same pattern applies to dropping tables, changing a column's data type, or splitting one table into two.

🛠️ Hands-On Lab

# Check if logical replication is enabled on your PostgreSQL instance
SHOW wal_level;
-- Should return: logical

# Install Flyway locally (macOS)
brew install flyway

# Create your first migration
mkdir -p db/migration
echo "CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT);" > db/migration/V1__create_products.sql

# Run it against your database
flyway -url=jdbc:postgresql://localhost:5432/mydb -user=postgres migrate

# Check the tracking table Flyway created
psql -d mydb -c "SELECT * FROM flyway_schema_history;"

# Try the expand-and-contract pattern
echo "ALTER TABLE products ADD COLUMN price NUMERIC;" > db/migration/V2__add_price.sql
flyway -url=jdbc:postgresql://localhost:5432/mydb -user=postgres migrate

📖 Quick Reference

CDC and migrations cheat sheet — key terms, tools, safe practices

Term	Plain English
CDC	Change Data Capture — turns database changes into an event stream
WAL	Write-Ahead Log — PostgreSQL's internal record of every change
Logical decoding	Converts WAL entries into readable row-level change events
Replication slot	A bookmark tracking how far a CDC consumer has read
Debezium	The most popular open-source CDC platform, runs on Kafka Connect
op field	c=create, u=update, d=delete, r=snapshot in a Debezium event
Flyway	Simple, SQL-first migration tool — filename determines order
Liquibase	Migration tool with built-in rollback and 50+ database support
Atlas	Diff-based migration tool with ORM integrations
Expand and contract	Safe pattern for renaming/dropping columns without breaking deploys

✅ What You Learned Today

✅ CDC turns database changes into a real-time event stream instead of slow batch polling

✅ PostgreSQL's WAL already records every change — CDC tools just read that log

✅ wal_level=logical and replication slots are required to enable CDC

✅ Debezium is the standard CDC platform, publishing changes to Kafka topics

✅ CDC powers search index sync, cache invalidation, and analytics warehouses in real time

✅ Flyway is the simplest migration tool — plain SQL files ordered by filename

✅ Liquibase adds rollback support and multi-database compatibility

✅ Atlas auto-generates migrations by diffing schema, fits Go/TypeScript ORM stacks

✅ Never edit an already-applied migration file — always create a new one

✅ Use expand-and-contract for renames or drops to avoid breaking rolling deployments

📚 References

• Debezium Official Documentation

• PostgreSQL Logical Replication Documentation

• Flyway Documentation

⏭️ Up Next — Part 17: Database Migrations, Backups & Disaster Recovery

You now know how to evolve a schema safely and stream changes in real time. Part 17 covers what happens when things go wrong — RDS automated backups, point-in-time recovery, snapshots, and Multi-AZ failover, so your data survives even a complete server failure.