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 diagram
Swipe sideways to see all of it, or tap to open it full screen.
- Expand: add the new column or table (nullable, no default rewrite), deploy code that writes both old and new.
- Migrate: backfill existing rows in small batches.
- Switch reads to the new column; deploy.
- 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 diagram
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_sslfor PostgreSQL) and keep databases in private subnets, with security groups allowing only the app's security group. NeverPubliclyAccessible: truein 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.