实时协作编辑器数据库存储机制及支持Operational Transform/CRDT的Schema设计咨询

Hey there! Let me break down these two key questions based on my experience building real-time collaborative editors and digging into industry best practices.

There are three primary storage patterns used in real-time editors, each with tradeoffs for performance, complexity, and use case:

• Full Snapshot + Operation Logs
  This is the most common approach for OT-based systems. You store a full, serialized snapshot of the document (e.g., as JSON, Protobuf, or plain text) for quick initial loading, plus a detailed log of every edit operation. The snapshot lets new clients load the document instantly, while the operation log is used to replay edits, resolve conflicts via OT, and sync with offline clients.

• Lazy Snapshotting (Operation Logs First)
  For large documents with frequent edits, storing only operation logs initially can save space. You generate snapshots periodically (e.g., every 100 operations or hourly) to avoid long initial load times. This balances storage efficiency with usability—clients load the latest snapshot, then apply any subsequent operations to catch up.

• CRDT-Based Direct Storage
  If you’re using CRDTs, you can directly store the serialized CRDT state (e.g., Yjs’s YDoc or Automerge’s document) in the database. CRDTs are designed for eventual consistency, so you don’t need to track strict operation sequences. You can either store the full state each time or incremental deltas (partial changes) to reduce bandwidth and storage overhead.

The schema depends heavily on whether you’re using OT or CRDTs—here’s what each looks like in practice:

For Operational Transform (OT) #

OT relies on strict version control and ordered operation sequences, so your schema needs to enforce this:

Document Metadata Table #

Tracks core document info and current state to avoid replaying all operations on every load:

CREATE TABLE documents (
    id UUID PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    current_version INT NOT NULL DEFAULT 0,
    latest_snapshot JSONB, -- Use JSON for non-PostgreSQL databases
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Operations Log Table #

Stores every edit operation, tied to a specific document version to enable OT conflict resolution:

CREATE TABLE ot_operations (
    id UUID PRIMARY KEY,
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,
    operation_type VARCHAR(50) NOT NULL, -- e.g., 'insert', 'delete', 'format'
    operation_data JSONB NOT NULL, -- e.g., {"position": 10, "text": "hello", "length": 5}
    base_version INT NOT NULL, -- The document version this operation was intended for
    applied_version INT NOT NULL, -- The version after applying this operation
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(document_id, base_version) -- Prevent duplicate operations for the same version
);

Key note: The base_version and applied_version fields are critical for OT—when a client submits an operation, the server checks if the base version matches the current document version. If not, the server transforms the operation to fit the current state before applying it.

For Conflict-free Replicated Data Types (CRDT) #

CRDTs don’t require strict ordering, so the schema is simpler and more flexible:

Basic CRDT Document Table #

Stores the serialized CRDT state directly—most CRDT libraries provide built-in serialization for this:

CREATE TABLE crdt_documents (
    id UUID PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    crdt_state BYTEA, -- Use binary for compact storage (e.g., Yjs's Uint8Array)
    -- Or JSONB for JSON-based CRDTs: crdt_state JSONB,
    version BIGINT NOT NULL DEFAULT 0, -- Optional, for tracking update count
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Optional CRDT Delta History Table #

If you need to track edit history or support incremental sync, store partial changes (deltas) instead of full state updates:

CREATE TABLE crdt_deltas (
    id UUID PRIMARY KEY,
    document_id UUID REFERENCES crdt_documents(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,
    delta BYTEA NOT NULL, -- Incremental CRDT change
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sequence_number BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY -- Preserves delta order
);

Key note: For CRDTs, you can choose between full state storage (easier to implement) or delta storage (better for bandwidth and large documents). Many libraries handle delta merging automatically, so you don’t need to manage conflict resolution in the database.

• Database Selection: OT works best with relational databases (PostgreSQL, MySQL) that support transactions and strong consistency. CRDTs are flexible—you can use relational databases, document databases (MongoDB), or even distributed databases (CockroachDB) depending on your scaling needs.
• Performance: For OT, periodically merge old operations into a new snapshot to keep log sizes manageable. For CRDTs, use binary serialization instead of JSON to save space and speed up serialization/deserialization.

内容的提问来源于stack exchange，提问作者Ajeet