Six Data Management Patterns

Most systems do not manage data with a single database table and a few queries. As traffic, reporting needs, and product complexity grow, teams end up combining several data patterns. The key is to know which copy of the data is authoritative, which copies are derived, and how stale each derived copy is allowed to become.

The base rule is simple: keep one primary write model that owns correctness, then add supporting patterns only when a clear access problem appears.

Cache Aside

Cache aside is the most common read optimisation. The application reads from a cache first and falls back to the primary store on a miss. After reading from the store, it writes the value back into the cache. This works well when reads greatly outnumber writes and stale data is acceptable for a short period.

The tradeoff is cache invalidation. If an update changes the primary store but the cache key is not expired or deleted correctly, users keep seeing old data. Hot keys can also cause stampedes when many requests miss at once and all hammer the database. Teams usually reduce that risk with short TTLs, request coalescing, or background refresh.

Materialised Views

A materialised view stores a precomputed result, such as daily revenue per customer or a denormalised search list. It exists because some queries are too expensive to rebuild on every request. This is common in reporting, dashboards, and operational back offices.

The cost is freshness. A materialised view is only as current as its refresh strategy. If it refreshes every hour, nobody should expect minute-level accuracy. Incremental refresh pipelines reduce lag, but they also make failure handling harder because partial updates can leave the view inconsistent with the source.

CQRS

Command Query Responsibility Segregation splits write models from read models. The write side focuses on validation and business rules. The read side is shaped for fast queries and may be denormalised heavily.

CQRS is useful when the same domain needs strict transaction handling for writes and very different access patterns for reads. The downside is operational complexity. A team now has multiple models, replication lag, and more failure modes. CQRS is rarely worth it for simple CRUD applications.

Event Sourcing

Event sourcing stores state changes as an append-only sequence of events instead of overwriting the latest row. That gives a strong audit trail and makes it possible to rebuild projections or replay history after a bug fix.

The tradeoff is that reads become indirect. Most applications still need projections, snapshots, or read models because rebuilding state from a long event stream on every request is too slow. Schema evolution is another hard problem. Once events are written, they must remain interpretable for years.

Index Tables

An index table is a purpose-built lookup structure for queries the primary schema handles poorly. For example, a user table may be keyed by user ID, while an index table maps email address to user ID for fast login and uniqueness checks.

This pattern is practical, but it introduces consistency work. If the primary record changes and the index table update fails, lookups return the wrong result. Good implementations treat the index as derived data that can be rebuilt, or update both structures in one transaction when the storage engine supports it.

Sharding

Sharding splits data across multiple physical partitions so no single node carries the whole load. It is usually introduced when a single database instance cannot handle storage size, write throughput, or tenant isolation requirements.

Sharding solves one scaling limit by creating several operational ones. Cross-shard joins become expensive, rebalancing is painful, and a poor shard key can create hotspots that defeat the whole design. It should be a last major step, not the first answer.

Good data management is mostly about restraint. Add the simplest pattern that solves the current bottleneck, and be explicit about freshness, consistency, and repair when derived data drifts.