Implementing Atomic Updates for Complex Relational Graphs with Snapshotting
This article introduces a snapshot-oriented persistence model to enable atomic updates for complex relational data graphs. It addresses the challenge of ensuring readers never observe a partially updated state when multiple related tables need to be updated concurrently. The solution involves building a complete new version of the data alongside the current one and exposing it only when fully ready, using a "strate" identifier for versioning.
Read original on Dev.to #architectureWhen dealing with relational data spread across multiple tables, ensuring data consistency during updates can be challenging. A common problem arises when an update to a logical entity requires changes across several related tables. If these changes are applied directly, readers might observe an inconsistent, partially updated state during the transition. While database transactions can help, very large and long-running transactions for complex graphs might not be desirable due to potential locking and performance implications.
The Snapshot-Oriented Persistence Model
The core idea presented is to implement a snapshot-oriented persistence model. Instead of modifying existing data in place, a completely new version of the data graph is built. This new version, or "strate," is constructed in isolation and becomes visible to readers only after it is fully prepared and released. This approach guarantees that readers always interact with a consistent, complete version of the data.
- Build Strate: Each complete version of a dataset is assigned a unique identifier (UUID v7), called a "build strate." Every row belonging to that version stores this `buildStrate`.
- Released Strates Table: A central `released_strates` table maps a `(scope, scope_id)` pair to the currently active `buildStrate`. This table acts as a pointer to the visible version of the data for a given domain instance.
- Atomic Release: Publishing a new version is an atomic operation. It involves a single `UPDATE` query on the `released_strates` table, switching the pointer from the old strate to the new one. This means the publication time is constant, regardless of the size of the data graph.
Architectural Concepts
- Scope: Defines the business domain being versioned (e.g., `catalog`, `configuration`, `pricing`).
- Scope ID: Identifies a specific instance within a scope (e.g., `shop-42` for a `catalog` scope). This allows for multi-tenant or independently managed datasets.
- Build Strate: The unique identifier for a complete candidate version of the data within a specific `(scope, scope_id)` context.
Example Workflow
1. Create New Strate: A new, empty `buildStrate` is created; it is initially invisible to readers. 2. Persist Graph: All new or modified entities and their relations are persisted with this new `buildStrate`. The old version remains active and visible to readers. 3. Release New Version: Once the entire graph is built, the `released_strates` table is updated to point to the new `buildStrate`. At this exact moment, all readers immediately switch to seeing the complete new version. 4. Garbage Collection: Older, superseded strates can be garbage collected later.