Stage 3 · Build

Database Reliability Engineering

Testing & Migration Safety

Shadow tables, blue-green database migrations, and load testing queries.

7 min readDatabases for SREBuild

Database Testing Strategies

Database testing covers three areas: schema correctness, query performance, and migration safety. Each requires different tools and approaches. The goal is to catch regressions before they reach production.

Test Type	What It Validates	When to Run
Schema tests	Constraints, indexes, types	On every migration
Query tests	Performance of critical queries	On schema or data changes
Integration tests	Application-database interaction	On every deployment
Migration tests	Forward and rollback migrations	Before deploying migrations
Load tests	Performance under production load	Before major releases

Integration Testing

Schema validation tests


-- Test that constraints exist
DO $$
BEGIN
    -- Test NOT NULL constraint
    BEGIN
        INSERT INTO users (email) VALUES (NULL);
        RAISE FAIL 'email should be NOT NULL';
    EXCEPTION WHEN NOT_NULL_VIOLATION THEN
        RAISE NOTICE 'PASS: email NOT NULL constraint works';
    END;

    -- Test UNIQUE constraint
    BEGIN
        INSERT INTO users (email, name) VALUES ('test@example.com', 'Test');
        INSERT INTO users (email, name) VALUES ('test@example.com', 'Test2');
        RAISE FAIL 'email should be UNIQUE';
    EXCEPTION WHEN UNIQUE_VIOLATION THEN
        RAISE NOTICE 'PASS: email UNIQUE constraint works';
    END;

    -- Test foreign key constraint
    BEGIN
        INSERT INTO orders (user_id, total) VALUES (999999, 100);
        RAISE FAIL 'user_id should reference users(id)';
    EXCEPTION WHEN foreign_key_violation THEN
        RAISE NOTICE 'PASS: foreign key constraint works';
    END;
END $$;

Schema tests verify that database constraints work as expected. Run these against a test database after every migration. They catch cases where constraints were accidentally dropped or modified.

Load Testing Queries

pgbench for load testing


# Built-in pgbench test
pgbench -h localhost -U postgres -c 10 -j 2 -T 60 mydb

# Custom pgbench script (transaction-per-second test)
cat > custom.sql << 'EOF'
\set user_id random(1, 100000)
BEGIN;
SELECT * FROM users WHERE id = :user_id;
SELECT * FROM orders WHERE user_id = :user_id ORDER BY created_at DESC LIMIT 10;
COMMIT;
EOF

pgbench -h localhost -U postgres -c 20 -j 4 -T 120 -f custom.sql mydb

# Compare results before and after schema change
# Run before migration:
pgbench -c 20 -T 120 -f custom.sql mydb > before.txt

# Run after migration:
pgbench -c 20 -T 120 -f custom.sql mydb > after.txt

# Compare TPS
diff before.txt after.txt

Run the same load test before and after a schema change. Compare transactions per second (TPS) and latency. A TPS regression of > 5% should block the migration.

Migration Testing

Migration test procedure


#!/usr/bin/env bash
# test-migration.sh — test migration forward and backward

set -euo pipefail

DB_NAME="test_migration_$(date +%s)"
DB_URL="postgresql://localhost/$DB_NAME"

# Create test database from production schema
createdb $DB_NAME
pg_dump -s production_db | psql $DB_URL

# Apply migration forward
echo "=== Applying migration ==="
time flyway -url=$DB_URL migrate

# Run application tests against migrated schema
echo "=== Running application tests ==="
DATABASE_URL=$DB_URL npm test

# Rollback migration
echo "=== Rolling back migration ==="
time flyway -url=$DB_URL undo

# Verify schema matches pre-migration
echo "=== Verifying rollback ==="
pg_dump -s production_db > /tmp/before.sql
pg_dump -s $DB_NAME > /tmp/after.sql
diff /tmp/before.sql /tmp/after.sql

# Cleanup
dropdb $DB_NAME
echo "Migration test passed"

Always test both forward migration and rollback. A migration that cannot be rolled back leaves the database in a broken state if the application deployment fails.

Test against production-sized data

A migration that takes 1 second on 1000 rows may take 1 hour on 100M rows. Create a staging environment with production data volume (or a subset if production is very large) and measure migration duration.

Blue-Green Migrations

Blue-green database migration involves running two database instances simultaneously. The blue instance serves production traffic while the green instance receives the schema change. After validation, traffic switches to green.

Blue-green migration pattern


-- Blue database: current production
-- Green database: receiving migration

-- Step 1: Create green database with migration applied
CREATE DATABASE mydb_green;
-- Apply migration to green database

-- Step 2: Set up replication from blue to green
-- (or use logical replication to sync data)

-- Step 3: Validate green database
-- Run application tests against green

-- Step 4: Switch application traffic to green
-- Update connection strings

-- Step 5: Keep blue as rollback option for 24 hours

-- Step 6: Decommission blue database

Blue-green is the safest migration approach for large databases. The old database remains available for instant rollback. The cost is maintaining two database instances during the transition period.

Chaos Testing

Database chaos tests


# Kill a random database connection
kill -9 $(pgrep -f "postgres.*client" | shuf -n 1)

# Fill disk temporarily to test alerting
dd if=/dev/zero of=/tmp/fill_disk bs=1M count=10000

# Simulate replication lag
tc qdisc add dev eth0 root netem delay 500ms

# Simulate primary failure (stop PostgreSQL)
pg_ctl -D /var/lib/postgresql/data stop -m immediate

# Simulate connection pool exhaustion
for i in $(seq 1 500); do
    psql -h localhost -U postgres -c "SELECT pg_sleep(60);" &
done

# Verify monitoring detects issues
curl -s http://prometheus:9090/api/v1/query?query=up

Chaos testing validates that monitoring, alerting, and runbooks work correctly. Run these tests in staging environments. The goal is to find gaps in your observability and recovery procedures.

Start chaos testing in staging

Never chaos test in production without experience in staging. Start with simple tests: stop a replica, fill disk, kill connections. Gradually increase complexity. Document everything you learn.

Testing checklist for every release

1) Schema validation tests pass. 2) Critical query performance within 5% of baseline. 3) Migration forward and rollback tested. 4) Integration tests pass against migrated schema. 5) Load test shows no regression.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.