SurrealDB Schema Migrations and Multi-Master Replication: Consistency During Hot Upgrade

1. Read‑After‑Write Guarantees During a Blocking ALTER TABLE

SurrealDB’s documentation states that a transaction is atomic and isolated at the node that executes it. When a ALTER TABLE that rewrites the table is issued, the node that receives the command pauses new writes until the rewrite finishes. Reads from that node are immediately consistent, but reads from other replicas are eventual – they may return stale data or a temporary error until the updated schema is propagated via the multi‑master replication layer.

2. Conflict Resolution for Concurrent Writes

During a rewrite, concurrent writes that target the same record are resolved by SurrealDB’s last‑writer‑wins rule, which uses a logical timestamp assigned to each write. If a write arrives after the schema has been altered and the record shape no longer matches the new schema, the node that receives the write will reject it with a schema‑violation error. The rejecting node will then propagate the error to the other replicas through the replication channel.

3. Validating Migration Impact Before Promoting a Replica

SurrealDB does not ship a dry‑run feature for large schema changes. The most reliable pattern is:

1. Apply the ALTER TABLE on a secondary replica that is not serving production traffic.
2. Run a representative write workload against that replica while the rewrite is in progress.
3. Collect:
• Replication lag via /replication/lag endpoint (aim for < 100 ms).
• Write latency and error rate from the workload.
• Post‑migration read‑after‑write visibility by reading the same record from both the node that performed the write and a different replica.
4. If metrics stay within acceptable thresholds, promote the replica to primary.

Practical Verification Steps

# On the staging replica
curl -sS http://staging:8000/replication/lag
# Run a load test (e.g., wrk or custom script) while the ALTER TABLE is running
# After migration
curl -sS http://staging:8000/execute --data '{"request": "SELECT * FROM users WHERE id = 1"}'

Assumptions & Caveats

• These behaviors are based on SurrealDB 1.x documentation; newer releases may alter locking or conflict‑resolution logic.
• The rewrite may lock the table for the entire duration, stalling writes that arrive during the lock.
• SurrealDB’s conflict resolution currently uses a last‑writer‑wins rule; future versions might adopt vector clocks.

Missing Diagnostic Detail

To fine‑tune the recommendation, could you confirm which SurrealDB version you are running? The exact version can influence the replication lag thresholds and the behavior of blocking ALTER TABLE operations.