Databases in Production

Running a database for a real application adds concerns beyond writing queries: keeping it available, backed up, fast as it grows, and observable.

Backups — and restores

• Logical backups (pg_dump, mysqldump, SQLite's .backup) export data as SQL or an archive — portable, good for smaller databases.
• Physical backups with continuous write-ahead log (WAL) archiving allow point-in-time recovery: restoring the database to any moment, such as just before an accidental DELETE.
• Managed services (Amazon RDS, Cloud SQL, Azure Database, Supabase, Neon) automate both.

A backup you've never restored is only a hope. Test restores regularly, and know how long a restore takes.

High availability and replication

• Replication copies changes from a primary database to one or more replicas. If the primary fails, a replica can be promoted.
• Replicas can also serve read-only queries (reports, dashboards), taking load off the primary — though they may lag slightly behind.

Scaling

Before scaling out, scale smart: good indexes, efficient queries, and caching fix most performance problems. Then:

• Vertical scaling — a bigger server; simple and surprisingly far- reaching.
• Connection pooling — tools such as PgBouncer let thousands of application connections share a small number of database connections.
• Partitioning — splitting a huge table into pieces (for example, one partition per month) so queries and maintenance touch less data:

-- PostgreSQL
CREATE TABLE events (id BIGINT, created_at TIMESTAMPTZ NOT NULL, payload JSONB)
  PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_04 PARTITION OF events
  FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

• Sharding — spreading data across several database servers. Powerful but complex; most applications never need it.

Monitoring

Watch the signals that predict trouble:

• Slow queries (pg_stat_statements, the MySQL slow query log).
• Connection counts, CPU, memory, disk space, and disk I/O.
• Replication lag.
• Lock waits and deadlocks.
• Table and index growth — and, in PostgreSQL, vacuum activity, which reclaims space from updated and deleted rows.

Operational habits

• Change schemas only through migrations (previous lessons).
• Keep production credentials restricted, and use read-only access for investigations.
• Wrap manual fixes in a transaction, and check row counts before committing.
• Document runbooks: how to restore, fail over, and add capacity.

Congratulations

You've reached the end of Level 3. Build one of the Advanced Projects, then read What You Should Know Now — and What's Next for where to go from here.