MySQL, MongoDB & Redis

Overview

Almost every slow application is slow at the database, and almost every irrecoverable disaster is a backup that was never tested. The data layer deserves more design attention than it usually gets, because it is the hardest thing to change later — you can rewrite a front end in a quarter, but you live with a schema decision for years.

Choosing the right store

MySQL and PostgreSQL

The default, and correctly so. If your data has relationships — and business data almost always does — a relational database with real foreign keys and transactions is the right answer. ACID guarantees are not a luxury when the data is orders and payments. We use PostgreSQL where its stronger type system, JSONB and extensions add value, and MySQL where the hosting or the existing stack points that way.

MongoDB

Genuinely good for documents that vary in shape, event and activity logs, catalogues with heterogeneous attributes, and rapidly changing schemas early in a product's life. It is not a way to avoid designing your data. The most common failure we are asked to fix is a Mongo database being used relationally, with application-side joins doing what a relational engine would have done far better.

Redis

Not a primary datastore in most designs, and superb at everything else: caching, sessions, rate limiting, queues, leaderboards, pub/sub and distributed locks. Often the single highest-leverage addition to a slow application.

Polyglot persistence is normal. A typical system we build uses MySQL for transactional data, Redis for cache and sessions, and possibly a search index for full-text — each doing what it is good at.

Schema and index design

• Normalise first, denormalise deliberately and only where a measured read pattern justifies it.
• Indexes designed for the queries that actually run. Composite index column order matters enormously and is frequently wrong; covering indexes let a query be served from the index alone.
• Every index is also a write cost. Unused indexes are found and dropped.
• Correct data types — the right integer width, proper decimals for money, real date types. Storing everything as strings is a decision you pay for at every query.
• Constraints and foreign keys enforced in the database, not only in application code. Application bugs happen; the database is the last line of defence for data integrity.
• Partitioning for genuinely large tables, and an archival strategy so tables that grow forever do not.

Query optimisation

• EXPLAIN on every slow query — the plan, not the guess, tells you what is happening.
• The slow query log enabled and reviewed. Most applications have three or four queries responsible for the majority of the pain.
• N+1 patterns eliminated. This remains the most common performance bug in ORM-based applications by a wide margin.
• SELECT * replaced with the columns actually needed, particularly where wide text columns are involved.
• Keyset pagination instead of large OFFSET values, which get progressively slower the deeper a user pages.
• Connection pooling sized correctly — too small throttles the app, too large exhausts the database.

Caching

Caching is easy to add and hard to invalidate. We design invalidation at the same time as the cache: clear TTLs, event-based invalidation on writes, cache keys that include everything the result depends on, and protection against stampedes when a popular key expires. Cache hit rate is monitored, because a cache nobody measures is a cache nobody knows is failing.

Availability and recovery

• Replication for read scaling and failover, with the application aware of replication lag rather than assuming a read-after-write is safe.
• Automated backups with point-in-time recovery, stored off-site and encrypted.
• Restores tested on a schedule. An untested backup is not a backup. We have seen backup jobs that had been silently failing for months.
• A documented recovery procedure with a measured recovery time, so the plan is not being written during the incident.
• Monitoring on connections, replication lag, slow queries, lock waits and disk headroom, with alerts before thresholds become outages.

Security

Least-privilege database users — the application account has no business holding DROP rights. Encryption in transit and at rest, no database port exposed to the internet, sensitive columns encrypted at the application layer where regulation requires it, and production data anonymised before it is copied into a development environment.

What you get

• A schema designed for your access patterns, documented with its reasoning.
• Indexes verified against real query plans, with before-and-after timings.
• A caching layer with a deliberate invalidation strategy.
• Backups that have been restored in a test, with a measured recovery time.
• Monitoring and alerting on the metrics that predict outages.

A backup you have never restored is a hypothesis. The night you need it is a poor time to test it.