Course contentsShow
← System Design
Lesson 295 of 1,9198. Database Performance OptimizationFree lesson

Maintaining Data Consistency

Handle the challenge of keeping denormalized copies in sync using application logic or triggers.

Maintaining Data Consistency

What you'll learn: How to keep denormalized copies of data in sync when you've duplicated information for performance.


When you denormalize data (like storing a user's name in multiple places), you face a critical challenge: what happens when the original data changes? If a user updates their profile name, you need to update every copy, or your system shows inconsistent information.

The Synchronization Challenge

Imagine you run a bookstore where the same book title appears on multiple shelves organized by genre. When the title changes, you must update every shelf—otherwise customers see different titles in different places. That's the denormalization consistency problem.

Two Main Approaches

1. Application Logic

Your application code handles updates explicitly. When data changes, you write code to update all related copies:

  • Update the primary record
  • Find all denormalized copies
  • Update each one in sequence or parallel
  • Handle failures gracefully

This gives you full control but increases code complexity.

2. Database Triggers

The database automatically updates copies when the source changes. A trigger fires on UPDATE operations and propagates changes to denormalized tables:

  • Changes happen automatically
  • Less application code
  • But harder to debug and monitor
  • Can create performance bottlenecks

Trade-offs

Both approaches have risks. Application logic can fail partway through updates. Triggers can slow down writes and create hidden dependencies. You must also decide: is temporary inconsistency acceptable? Many systems use eventual consistency—denormalized data updates asynchronously, accepting brief mismatches for better performance.

Key Takeaway: Denormalization trades consistency complexity for read performance; you must actively maintain sync using application code or database triggers, accepting that brief inconsistencies may occur.