DevOps Databases

Databases store application data. In a DevOps environment, databases are not just a DBA's concern — every engineer who builds, deploys, or operates applications must understand how to manage, migrate, back up, and optimize databases as part of the delivery pipeline.

The challenges are significant: databases hold state, changes to their schema are irreversible if not handled carefully, and downtime during a migration can mean real business loss.

Relational vs Non-Relational Databases

Database Migrations

A database migration is a controlled, versioned change to a database schema. Adding a column, creating a table, or modifying an index — all of these are migrations. The critical rule: never modify production database schemas manually. Every change must be scripted, tested, and version-controlled.

Why Migrations Matter

• Multiple environments (dev, staging, production) stay in sync.
• Changes are reviewed through pull requests before reaching production.
• Rollback is possible — every migration has a corresponding down script.
• New team members can recreate the exact database schema from scratch.
• CI/CD pipelines run migrations automatically as part of deployment.

Flyway – SQL-Based Migrations

Flyway manages database migrations using versioned SQL files. Each file runs exactly once, in order, and Flyway tracks what has been applied.

db/migration/
├── V1__create_users_table.sql
├── V2__add_email_index.sql
├── V3__create_orders_table.sql
├── V4__add_shipping_address_column.sql
└── V5__create_payments_table.sql

-- V3__create_orders_table.sql
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id),
    status      VARCHAR(50) NOT NULL DEFAULT 'pending',
    total_price DECIMAL(10, 2) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status  ON orders(status);

# Run all pending migrations
flyway migrate

# Check migration status
flyway info

# Undo the last migration (requires Flyway Pro)
flyway undo

Liquibase – XML/YAML-Based Migrations

Liquibase works similarly to Flyway but supports XML, YAML, JSON, and SQL changesets. It also supports more advanced rollback scenarios.

# db/changelog/003-create-orders-table.yaml
databaseChangeLog:
  - changeSet:
      id: create-orders-table
      author: john.smith
      changes:
        - createTable:
            tableName: orders
            columns:
              - column:
                  name: id
                  type: BIGINT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: user_id
                  type: BIGINT
                  constraints:
                    nullable: false
              - column:
                  name: status
                  type: VARCHAR(50)
                  defaultValue: pending
      rollback:
        - dropTable:
            tableName: orders

Zero-Downtime Database Migrations

Deploying a migration that locks a table in production causes downtime. Large tables with millions of rows are especially risky. These strategies keep the application running during migration:

Expand-Contract Pattern

This is the safest approach for schema changes. It divides one risky migration into three safe deployments:

1. Expand: Add the new column (nullable, no constraint). Both old and new code work. Deploy application v1 that writes to both old and new columns.
2. Migrate: Backfill the new column with data from the old column. Run in batches on large tables to avoid locking.
3. Contract: Remove the old column once all code uses only the new column.

Online Schema Change Tools

• pt-online-schema-change: Percona tool that copies the table to a new structure in the background, then swaps atomically. No application downtime.
• gh-ost: GitHub's online schema change tool for MySQL. Uses binary log streaming — no triggers required.

Database in CI/CD Pipelines

Running Migrations Automatically

# GitHub Actions step: run Flyway migration before deploying the app
- name: Run database migrations
  run: |
    flyway \
      -url=jdbc:postgresql://${{ secrets.DB_HOST }}:5432/appdb \
      -user=${{ secrets.DB_USER }} \
      -password=${{ secrets.DB_PASSWORD }} \
      migrate

Test Database in CI

CI pipelines spin up a temporary database (using Docker) to run integration tests against a real database without touching shared environments:

# docker-compose.test.yml
services:
  test-db:
    image: postgres:15
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
    ports:
      - "5432:5432"
    tmpfs:
      - /var/lib/postgresql/data  # In-memory for speed

Database Backup and Recovery

Backups are a fundamental operational responsibility. A database without tested backups is a disaster waiting to happen.

PostgreSQL Backup

# Full database dump
pg_dump -U appuser -h localhost appdb > backup_$(date +%Y%m%d).sql

# Restore from dump
psql -U appuser -h localhost appdb < backup_20250315.sql

# Continuous archiving with pg_basebackup (for Point-in-Time Recovery)
pg_basebackup -h localhost -U replication -D /backups/base -Fp -Xs -P

Automated Backup with AWS RDS

AWS RDS provides automated daily backups with configurable retention (up to 35 days) and point-in-time recovery. Enable this in Terraform:

resource "aws_db_instance" "app_database" {
  identifier        = "appdb-production"
  engine            = "postgres"
  engine_version    = "15.3"
  instance_class    = "db.t3.medium"
  allocated_storage = 100

  backup_retention_period   = 14        # Keep backups for 14 days
  backup_window             = "03:00-04:00"
  maintenance_window        = "sun:04:00-sun:05:00"
  deletion_protection       = true      # Prevent accidental deletion
  skip_final_snapshot       = false
  final_snapshot_identifier = "appdb-final-snapshot"

  multi_az = true                       # Standby in second AZ
}

Database High Availability

Primary-Replica (Master-Slave) Replication

The primary database handles all writes. One or more replica databases receive a copy of all changes via replication. Reads can be distributed across replicas to reduce load on the primary. If the primary fails, a replica is promoted.

Connection Pooling

Each database connection consumes memory. Applications that open too many connections can overwhelm the database. Connection poolers like PgBouncer (PostgreSQL) sit between the application and database, reusing connections efficiently.

# pgbouncer.ini
[databases]
appdb = host=db-primary.internal port=5432 dbname=appdb

[pgbouncer]
listen_port = 5432
listen_addr = 0.0.0.0
auth_type = md5
pool_mode = transaction    # Release connection after each transaction
max_client_conn = 1000
default_pool_size = 20

NoSQL in DevOps – Redis and MongoDB

Redis – In-Memory Cache and Data Store

Redis stores data in memory for extremely fast access. Common uses: caching database query results, session storage, rate limiting, pub/sub messaging, and job queues.

# Cache a database result for 60 seconds
SET user:441:profile '{"name":"John","email":"john@example.com"}' EX 60

# Get cached value
GET user:441:profile

# Increment a counter (e.g., for rate limiting)
INCR api:ratelimit:user:441
EXPIRE api:ratelimit:user:441 60

MongoDB

MongoDB stores data as flexible JSON-like documents. Each document in a collection can have different fields. Useful for product catalogs, content management, user profiles, and any data with varying structure.

// Insert a product document
db.products.insertOne({
  name: "Wireless Keyboard",
  brand: "Logitech",
  price: 79.99,
  tags: ["electronics", "accessories", "wireless"],
  specs: {
    color: "black",
    battery_life_months: 24,
    connectivity: "Bluetooth 5.0"
  },
  in_stock: true,
  created_at: new Date()
})

Summary

• Database migrations version-control every schema change — Flyway and Liquibase are the standard tools.
• The expand-contract pattern enables zero-downtime schema changes in production.
• CI/CD pipelines run migrations automatically before deploying the new application version.
• Backups must be automated, tested, and stored offsite — untested backups are not real backups.
• RDS Multi-AZ provides automatic failover for production database high availability.
• Redis accelerates applications with in-memory caching, reducing expensive database queries.
• Connection pooling with PgBouncer prevents database connection exhaustion under high load.