404 — SQL for DevOps

Advanced

Apply SQL skills to real DevOps operations in PostgreSQL: write database migrations, monitor health, analyze query performance, back up and restore databases, and build operational dashboards. These are the queries you'll run at 3 AM during incidents.

Learning Objectives

Understand database migrations and why they're essential for team workflows

Write, apply, and roll back a database migration

Monitor PostgreSQL connections, table sizes, and long-running queries

Use pg_stat_activity and pg_stat_user_tables for operational visibility

Back up and restore databases with pg_dump and pg_restore

Write alerting queries for operational thresholds

Automate database tasks with shell scripts

Simulate and resolve a production incident using SQL

Interpret monitoring queries by understanding what one row in each system view represents

Step 1

Database migrations — why and how

Database migrations are versioned, repeatable changes to your database schema. They let teams evolve the database safely, just like Git lets teams evolve code.

Commands to Run

docker exec -it pg-sql-course psql -U postgres -d devops_sql

-- Create a migrations tracking table:
CREATE TABLE schema_migrations (
  version VARCHAR(14) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- This table tracks which migrations have been applied.
-- Migration tools (Flyway, Alembic, Prisma) create a similar table automatically.
SELECT * FROM schema_migrations;

What This Does

Without migrations, database changes are manual, error-prone, and impossible to reproduce. Migrations solve this by recording every schema change as a versioned script. Each migration has an 'up' (apply) and 'down' (rollback) direction. The schema_migrations table tracks which versions have been applied, preventing double-application. Every serious project uses migrations — they're as essential as version control.

Expected Outcome

schema_migrations table created and empty — no migrations applied yet.

Pro Tips

1
Migration naming convention: YYYYMMDDHHMMSS_description (e.g., 20240115100000_add_status_to_orders)
2
Always write both 'up' and 'down' for every migration
3
Never edit a migration after it's been applied to production — create a new one instead
4
Popular tools: Flyway (Java), Alembic (Python), Prisma Migrate (Node.js), golang-migrate (Go)

Common Mistakes to Avoid

⚠️Making schema changes directly in production without a migration
⚠️Editing a migration that's already been applied — other environments will be inconsistent
⚠️Forgetting to write a rollback — you'll need it at 3 AM

Was this step helpful?