Study: DB vs binary snapshots for world-state persistence

marco commented

2026-07-17 13:24:32 +02:00

Owner

Question

We currently persist state (player positions + inventory) via EF Core (SQLite dev / Postgres prod). For world state especially, is a relational DB the right tool, or would our own binary snapshot files be lighter/simpler/faster?

Why ask

The tick loop must never touch the DB; persistence is periodic/async + on disconnect + shutdown. A DB brings migrations, a query engine, and per-row overhead for what is often "dump the whole world every N seconds".
ModernUO deliberately uses custom binary serialization + periodic world saves, not a SQL DB, for exactly this (we noted this in docs/architecture.md and chose EF for simplicity at our scale).

To decide (critically, later)

Split the concern: accounts/characters (Auth) are naturally relational + low-churn → probably stay EF. World/gameplay state (positions, items, later mobiles/containers/stats) is high-churn, whole-snapshot-friendly → candidate for binary snapshots (we already have a binary-container muscle: IsoMmo.Assets / .isoa).
Options: keep EF everywhere (status quo); binary snapshot for world + EF for accounts (hybrid); full custom serialization (ModernUO-style).
Criteria: save/load latency at target scale, crash-consistency, schema evolution, operational simplicity, testability.

Definition of Done

A short written comparison (status quo vs hybrid vs full-binary) with a recommendation and the migration cost, recorded in docs/architecture.md.
No code change required by this issue — it's a decision/research spike; implementation (if any) becomes its own issue.

Raised during the "playable among friends" work; deferred by decision to reason about it critically later.

## Question We currently persist state (player positions + inventory) via **EF Core (SQLite dev / Postgres prod)**. For **world state** especially, is a relational DB the right tool, or would our own **binary snapshot files** be lighter/simpler/faster? ## Why ask - The tick loop must never touch the DB; persistence is periodic/async + on disconnect + shutdown. A DB brings migrations, a query engine, and per-row overhead for what is often "dump the whole world every N seconds". - ModernUO deliberately uses **custom binary serialization + periodic world saves**, not a SQL DB, for exactly this (we noted this in `docs/architecture.md` and chose EF for simplicity at our scale). ## To decide (critically, later) - Split the concern: **accounts/characters** (Auth) are naturally relational + low-churn → probably stay EF. **World/gameplay state** (positions, items, later mobiles/containers/stats) is high-churn, whole-snapshot-friendly → candidate for binary snapshots (we already have a binary-container muscle: `IsoMmo.Assets` / `.isoa`). - Options: keep EF everywhere (status quo); binary snapshot for world + EF for accounts (hybrid); full custom serialization (ModernUO-style). - Criteria: save/load latency at target scale, crash-consistency, schema evolution, operational simplicity, testability. ## Definition of Done - A short written comparison (status quo vs hybrid vs full-binary) with a recommendation and the migration cost, recorded in `docs/architecture.md`. - No code change required by this issue — it's a decision/research spike; implementation (if any) becomes its own issue. _Raised during the "playable among friends" work; deferred by decision to reason about it critically later._

marco referenced this issue

2026-07-17 13:25:25 +02:00

feat: playable among friends (client/server config + chat throttle) #24

marco referenced this issue from a commit

2026-07-17 13:28:11 +02:00

feat: playable among friends (client/server config + chat throttle) (#24)

marco commented

2026-07-17 16:57:12 +02:00

Author

Owner

Broaden the study: also evaluate removing the SQL/EF layer entirely and persisting everything to binary saves like ModernUO (periodic full/incremental world snapshots + append log), not just world-state. Cover:

What each store holds today (GameServer: player position + inventory + now stats/skills; Auth: ASP.NET Identity accounts + characters) and which of these could/should move to binary vs stay relational (Auth accounts are the awkward one — Identity is SQL-shaped).
Pros: no DB dependency/ops, faster in-memory-first writes, ModernUO-proven, simpler local dev. Cons: no ad-hoc queries/reporting, custom serialization + versioning/migration burden, backup/restore tooling, concurrent-access story, losing EF migrations.
Migration path if we switch (all persistence moves together, so #8's skills table is not extra lock-in).

Deliver a recommendation (keep SQL / go binary / hybrid) with the reasoning.

Broaden the study: also evaluate **removing the SQL/EF layer entirely** and persisting everything to **binary saves like ModernUO** (periodic full/incremental world snapshots + append log), not just world-state. Cover: - What each store holds today (GameServer: player position + inventory + now stats/skills; Auth: ASP.NET Identity accounts + characters) and which of these could/should move to binary vs stay relational (Auth accounts are the awkward one — Identity is SQL-shaped). - Pros: no DB dependency/ops, faster in-memory-first writes, ModernUO-proven, simpler local dev. Cons: no ad-hoc queries/reporting, custom serialization + versioning/migration burden, backup/restore tooling, concurrent-access story, losing EF migrations. - Migration path if we switch (all persistence moves together, so #8's skills table is not extra lock-in). Deliver a recommendation (keep SQL / go binary / hybrid) with the reasoning.

marco referenced this issue from a commit

2026-07-18 04:44:10 +02:00

docs: persistence study — DB vs binary snapshots for world state (#23)

~~marco referenced this issue 2026-07-18 04:44:37 +02:00~~

feat(gameserver): binary world save instead of SQL (#23) #40

marco commented

2026-07-18 04:44:37 +02:00

Author

Owner

Study written: docs/persistence-study.md (PR above). Summary: the World is already in-memory-first and we never query the game DB, so game state is a poor fit for normalized SQL (and pays a dual-provider migration on every change), while Auth stays genuinely relational. Options A (normalized SQL) / B (ModernUO binary) / C (blob-in-SQL). Recommendation: C — one row = id + versioned blob — kills the migration tax while keeping the DB's atomicity/backup/Postgres; B if a DB-less game state is a hard goal. Awaiting the decision to implement.

Study written: `docs/persistence-study.md` (PR above). Summary: the World is already in-memory-first and we never query the game DB, so game state is a poor fit for normalized SQL (and pays a dual-provider migration on every change), while Auth stays genuinely relational. Options A (normalized SQL) / B (ModernUO binary) / C (blob-in-SQL). **Recommendation: C** — one row = id + versioned blob — kills the migration tax while keeping the DB's atomicity/backup/Postgres; **B** if a DB-less game state is a hard goal. Awaiting the decision to implement.

marco referenced this issue from a pull request that will close it,

2026-07-18 05:05:12 +02:00

feat(gameserver): binary world save instead of SQL (#23) #40

marco closed this issue

2026-07-18 16:47:15 +02:00

marco referenced this issue from a commit

2026-07-18 16:47:16 +02:00

feat(gameserver): binary world save instead of SQL (#23) (#40)

marco referenced this issue

2026-07-22 22:58:29 +02:00

refactor(world): read-only World.Mobiles / World.Items views (drop scalar count props) #160

marco referenced this issue from a commit