In this lesson, you will learn how to design schemas that gracefully handle data updates originating from multiple sources simultaneously, even when the system is not perfectly synchronized at every millisecond.
The Challenge of Multi-Writer Systems
In a multi-writer system, two or more clients might try to update the same record at the same time. If your schema forces strict, immediate consistency (where everyone sees the exact same data instantly), you often run into performance bottlenecks or locking issues. Instead, we design for eventual consistency, where we accept that the local state (what one process sees) and the global state (what the master database records) might diverge temporarily.
Designing for Divergence
To manage this, we shift our schema design from "overwrite" models to "additive" or "versioned" models. Instead of a single status column that gets updated, we move toward a ledger-style approach or use timestamps to resolve conflicts.
Consider a system tracking inventory updates from multiple warehouses:
-- Instead of a simple current_stock column:
CREATE TABLE inventory_updates (
update_id UUID PRIMARY KEY,
warehouse_id INT,
product_id INT,
change_amount INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
source_node VARCHAR(50)
);
By storing the intent of the change (the change_amount) rather than the result (the absolute stock level), we allow multiple writers to submit data without locking the entire table. We can then calculate the actual state by aggregating these entries. Because we have the created_at timestamp and a source_node identifier, the system can reconcile the global state by processing these updates in order or by merging them based on predefined business rules.
Resolving Local vs. Global State
When a local node performs an action, it can update its local "read-only" view immediately to ensure a responsive UI, while the global master database receives the update asynchronously. If the global state reveals a conflict—such as two nodes selling the last item—the schema’s audit trail allows us to trigger a compensation action or alert. By designing schemas that favor append-only structures, we avoid the "lost update" problem common in systems that rely on simple UPDATE statements.
Key Takeaway: Design schemas that treat updates as individual events rather than overwritable state, allowing your system to reconcile local divergence into a unified global truth.