Opscraft
Database operations · Lesson 5 of 6 · 4 min read

Changes and upgrades

Schema migrations without downtime

Keep migrations as versioned files in the application repository, applied by a tool (Flyway, Liquibase, Alembic, Rails or Django migrations) from the deploy pipeline, never by hand.

Use expand and contract so old and new app versions both work during a rolling deploy:

Enlarge diagramExpandadd nullable column,write bothBackfillold rows in smallbatchesSwitchread the new columnContractdrop old column later1234
Figure 1. Expand and contract: every step works with both the old and the new app version.
Swipe sideways to see all of it, or tap to open it full screen.
  1. Expand: add the new column or table (nullable, no default rewrite), deploy code that writes both old and new.
  2. Migrate: backfill existing rows in small batches.
  3. Switch reads to the new column; deploy.
  4. Contract: once nothing uses the old column, drop it in a later release.

Dangerous operations and safer versions:

Risky Safer
CREATE INDEX (blocks writes) CREATE INDEX CONCURRENTLY (PostgreSQL), online DDL or gh-ost (MySQL)
Adding a column with a volatile default on an old engine Add nullable, backfill in batches, then set the default
Renaming a column used by running code Add new, dual-write, switch, drop old
Any DDL with no lock timeout SET lock_timeout = '5s' so it fails fast instead of queueing everything behind it

Engine upgrades

  • Minor versions (security and bug fixes) can be applied automatically in the maintenance window, with a brief restart; Multi-AZ reduces the impact.
  • Major versions (PostgreSQL 15 → 17) change on-disk formats and behaviour. Test in staging first: extensions, query plans and drivers.
  • RDS Blue/Green Deployments create a synchronised copy (green) running the new version, kept in sync by replication. You test it, then switch over, typically in under a minute, with guardrails that stop the switch if replication isn't caught up.
  • Without Blue/Green: logical replication to a new-version instance and a planned cut-over, or an in-place upgrade during a maintenance window with a snapshot taken first.
  • Keep parameter groups in code (Terraform) and change them through review; some parameters need a reboot.
Enlarge diagramblue: PostgreSQL 15production trafficgreen: PostgreSQL 17kept in sync, testedswitchoverunder aminutereplication
Figure 2. RDS Blue/Green: a synchronised copy on the new version, tested, then switched over in about a minute.
Swipe sideways to see all of it, or tap to open it full screen.

Security

  • Encryption at rest with KMS is chosen at creation; to encrypt an existing unencrypted instance, snapshot, copy the snapshot with encryption, and restore.
  • Require TLS (rds.force_ssl for PostgreSQL) and keep databases in private subnets, with security groups allowing only the app's security group. Never PubliclyAccessible: true in production.
  • Credentials in Secrets Manager with automatic rotation (RDS can manage the master password for you), or IAM database authentication with short-lived tokens.
  • Separate roles: the app user has only the privileges it needs; migrations run with a more privileged user; humans get read-only access by default.
  • Turn on audit logging where required (pgAudit, MySQL audit plugin) and ship logs to CloudWatch.