Operating databases today
Modern teams run databases as managed cloud services, evolve schemas safely, protect data, and choose from a wide landscape of engines for each workload.
Managed databases
Cloud providers offer managed relational and NoSQL databases, such as Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Azure SQL Database, and serverless options that scale automatically. They handle provisioning, patching, backups, replication, and failover. Teams still own schema design, query performance, access control, and cost management.
Schema migrations
Schemas change as applications evolve. Migration tools such as Flyway, Liquibase, Alembic, and Rails migrations apply versioned, ordered change scripts kept in source control, so every environment can be brought to the same schema. Each migration should be reviewed and tested like code, and ideally reversible.
Zero-downtime changes
On busy systems, some changes lock tables for a long time. Safe practices include adding nullable columns or columns with defaults that do not rewrite the table, building indexes concurrently, and the expand-and-contract pattern: add the new structure, deploy code that writes both old and new, backfill data, switch reads to the new structure, and finally remove the old one.
Security
Database security includes encryption in transit with TLS and at rest, network isolation so databases are not exposed to the public internet, strong authentication, least-privilege roles for applications and staff, secrets stored in secret managers rather than code, auditing of sensitive access, and prompt patching. Row-level security policies can restrict which rows each user sees, useful for multi-tenant applications.
Privacy and compliance
Personal data is regulated by laws such as the EU's General Data Protection Regulation (GDPR, 2018) and California's Consumer Privacy Act. Good practice includes collecting only needed data, classifying sensitive columns, masking or pseudonymising data in test environments, defining retention periods, and being able to find and delete a person's data on request.
Observability and capacity
Teams monitor query latency, throughput, error rates, connection counts, replication lag, cache hit ratios, and storage growth, with alerts on unusual values. Slow-query logs and query statistics reveal what to optimise. Connection poolers such as PgBouncer prevent applications from overwhelming databases with connections. Capacity planning projects growth so upgrades happen before limits are hit.
Choosing a database
Start with the workload: data shape, query patterns, consistency needs, scale, latency targets, and team skills. A general-purpose relational database such as PostgreSQL covers a remarkable range of needs, including JSON, full-text search, and vectors through extensions. Add specialised systems, such as a cache, search engine, warehouse, or graph database, when a clear need justifies the extra operational cost: polyglot persistence.
Worked example
Renaming a column full_name to display_name on a table with 200 million rows without downtime: add display_name as a nullable column; deploy code that writes to both columns; backfill display_name in small batches to avoid long locks; deploy code that reads display_name; verify; then drop full_name in a later release. Each step is a separate, reversible migration, and the application keeps working throughout.
Key takeaways
Managed cloud databases handle operations such as patching, backups, and failover, but teams still own design, performance, security, and cost. Versioned migration tools keep schemas consistent across environments. Zero-downtime changes use non-blocking operations, concurrent index builds, and expand-and-contract. Security requires encryption, network isolation, least privilege, secret management, auditing, patching, and row-level security where needed. Privacy laws such as GDPR demand minimisation, masking, retention, and deletion capability. Monitor latency, lag, and connections, and plan capacity. Choose databases by workload, often starting with PostgreSQL and adding specialised stores when justified.
Flashcard
Cloud service handling operations
Check your understanding
Which approach renames a heavily used column with the least risk of downtime?