Contrasting workload priorities — extended reference

This reference preserves the earlier detailed chapter and its citations. Start with the guided explanation, then use these sections for additional mechanisms and qualifications. References to the original lesson sequence are historical.

Part 8: Architectural case studies and synthesis

Prerequisites: Lesson 46 and the preceding foundations.

Learning outcomes

Explain how SpacetimeDB and Convex reduce application-managed synchronization through different programming and execution models.
Contrast realtime transitions and subscriptions with ClickHouse’s analytical scan priorities.
Evaluate games, collaborative applications, and InsForge-style backends using correctness, delivery, freshness, and operational responsibilities.

Three applications ask for different kinds of speed

A shared world processes movement and ownership changes. A collaborative project tool keeps several people’s task lists synchronized. An analytics service groups a billion historical events by country and hour. All three want low latency, but they do not ask the engine to perform the same work.

The shared world emphasizes short authoritative actions and selective delivery of changing state. The project tool emphasizes transactional edits and reactive query results. The analytics service emphasizes scanning, filtering, and aggregating large datasets efficiently. Optimizing one path can make another less convenient. A design comparison should therefore begin with the shape of useful work, not a list of product features.

This lesson relates SpacetimeDB, Convex, and ClickHouse to the mechanisms already studied. It also clarifies the role of a conventional backend such as InsForge. These products overlap in capabilities but package different responsibilities. None eliminates the need to define authorization, durability, retry behavior, and operational limits.

The shared-world contract

Consider a hypothetical game where players gather materials and craft items. One craft operation must verify ownership, consume materials, create the result, and update visible state. Two clients racing to consume the same final material must not both succeed. A player’s client may propose an action, but the authoritative server validates the rules.

The client also needs nearby positions and public equipment, while private inventory remains restricted. Delivering the whole world to every player would be expensive and unsafe. The engine must combine transactional state changes with authorized interest selection. The user experience depends on both: correct actions that take seconds to display still feel broken, and fast displays of invalid actions do not preserve the game.

A conventional architecture separates the game service, database, broker, and client state manager. Each can be well engineered, but the boundaries require protocols. The game service commits changes, another mechanism communicates them, and clients apply updates. If publication fails after commit, the world can be correct in storage while temporarily stale on clients.

An integrated design can move those boundaries inside one system. The optimization is partly computational and partly a reduction in the number of consistency protocols the application team must implement.

SpacetimeDB integrates logic, tables, and subscriptions

SpacetimeDB’s architecture defines application modules containing tables and reducers, with reducers providing server-side transactional actions. Its client subscriptions maintain selected row data locally. This makes an authoritative action and synchronized application state central parts of one programming model. Key architecture, Subscriptions

The architectural consequence is that application logic can inspect and change state without repeatedly crossing a separate application-to-database connection. Local client reads can also avoid new requests when the necessary state is already subscribed. These are different optimizations: the first shortens authoritative computation; the second shortens subsequent display access.

A cached read is not a fresh authoritative action. The client can display its latest known position immediately while that value remains behind the server. The application should distinguish “available locally,” “server accepted,” and “durable under the chosen acknowledgment contract.” Conflating them produces misleading latency claims and confusing UI states.

The integrated model can reduce boilerplate for shared applications, but it also makes the database execution model part of application architecture. Long business actions, large maintained results, or broad subscriptions consume resources inside the same overall system.

Current positioning extends beyond games: the repository and homepage describe reactive applications such as collaborative tools, dashboards, and chat, with BitCraft as a prominent game example. Games supply a demanding motivating workload rather than an exclusive audience. My architectural interpretation is that persistent worlds particularly benefit when authoritative rules, durable ownership, and selective delivery concern the same state transitions. Other collaborative products can obtain similar integration benefits with different update rates and latency budgets. This describes workload fit, not a claim that the platform removes all operations work. Repository, Homepage

action -> authoritative transaction -> maintained authorized result
                                      -> fan-out -> local client state
analytics event history -> selected columns -> vectorized reduction

Figure: The realtime path maintains changing visible state; the analytical path reduces historical data. Their dominant work and freshness boundaries differ.

Sequential execution as a contention strategy

SpacetimeDB’s September 2026 scaling explanation describes single-threaded execution within a database and reports that this outperformed its earlier parallel execution approach. It distinguishes computation, storage, and networking scaling, and lists some future capabilities separately. These are vendor explanations and roadmap statements, not independently established universal performance results. Scaling architecture

The general reasoning is sound within the appropriate workload: if short transactions repeatedly contend on shared objects, avoiding parallel conflict coordination can reduce overhead. It does not follow that sequential execution always wins. Independent long-running transactions can benefit from parallel resources, while one serialized execution domain has a finite service rate.

For an illustrative 25 microseconds of exclusive execution per action, one resource has an idealized service ceiling of 40,000 actions/s before other overhead. At 20,000 actions/s it is 50% occupied by that execution component. If one action occasionally spends 20 ms computing a path, it occupies as much exclusive time as 800 ordinary actions. Network concurrency does not remove that execution occupancy.

This suggests an application rule: keep authoritative state transitions short, move independent expensive work outside their critical boundary, and validate any resulting proposal before applying it. Moving work outside a transaction creates a stale-input problem, so the optimization must preserve a validation step.

A craft-action trace

The following pseudocode is a language-neutral teaching model rather than a direct API example:

transaction craft(player, recipe, request_id):
    if completed_request(player, request_id) exists:
        return stored_result
    require authenticated_caller owns player
    require player is allowed to use crafting station
    required = recipe.requirements
    require inventory contains required materials
    remove required materials
    create crafted item with unique identifier
    record request_id and result
    commit all changes together

The transaction boundary protects the inventory invariant. Request identity protects the logical operation against a lost reply and retry, subject to retention policy. Authentication alone does not validate distance to a station or ownership. A subscription can then convey the resulting authorized state, rather than relying on the client to infer success from an optimistic animation.

If the client predicts crafting success, that prediction is presentation state. It must be reconciled with the authoritative outcome. A failed request should not leave a predicted item in an authoritative inventory view. Keeping predicted and confirmed state distinguishable makes failures recoverable.

The same trace applies to a collaborative task edit with different business rules. Games make the consequences visible, but the underlying issue is coordinated state transition.

Views: rerunning work versus maintaining deltas

SpacetimeDB documents procedural views and query-expression views. Procedural computations can rerun when dependencies change, while query expressions can use incremental query evaluation; sharing also depends on whether results are caller-independent. The important boundary is the kind of computation and its dependency model. Views documentation

To understand the difference, consider a region with 10,000 entities. A count of entities can update by adding or subtracting one when an entity enters or leaves. A general function that sorts every entity and then executes arbitrary code may be harder to update from a small delta. A reactive API does not guarantee constant work per underlying change.

Result maintenance also has a sharing dimension. If 1,000 clients subscribe to the same public region result, computation can potentially be shared before delivery. If each client requests a different radius and private visibility rule, sharing opportunities may shrink. The precise engine implementation determines the available reuse, but the conceptual distinction is stable.

Even perfectly shared computation still leaves output bytes. One changed row delivered to 1,000 clients is 1,000 deliveries. Reducing evaluation work and reducing recipient count solve different bottlenecks.

Convex makes queries reactive around transactions

Convex documents transactional mutations with optimistic concurrency control and automatically reactive query functions. It tracks query dependencies and updates subscribed results when relevant data changes. Its documentation emphasizes deterministic transactional computation so retry behavior can remain safe within the database model. Concurrency control, Realtime model

The comparison with SpacetimeDB is useful because both reduce application-managed synchronization, while their execution and developer interfaces differ. A useful evaluation asks how a given mutation reads and writes data, which concurrent changes can conflict, how query dependencies are established, and what work reruns after a change.

Consider a project board with tasks partitioned by project. A mutation changes one task’s status. A subscribed query returns visible tasks ordered by priority. The system must ensure the changed result reflects a coherent database state and the caller’s permissions. The developer avoids manually broadcasting “task changed” and writing a separate reconciliation protocol for the query result.

A poor access pattern can still create unnecessary work. If every small edit also updates one shared project summary that all mutations read, that record can become a conflict point. If every query reads all projects before filtering in application code, dependency tracking may encompass far more state than intended. Platform automation does not remove the value of narrow indexed access paths.

Reactive invalidation has a dependency footprint

A query’s cost includes its immediate execution and the future consequences of its dependencies. Reading one row creates a narrower footprint than scanning a broad range. A write affecting that footprint may invalidate the result and require work even when the final displayed value barely changes.

Suppose a hypothetical board has 5,000 tasks and 200 subscribers. Each subscriber requests the same visible task list, and one task changes 20 times/s. If the architecture shares one result computation, it can avoid 200 independent full executions. If results are personalized, the number of distinct computations may be much larger. Neither number can be inferred from subscriber count alone.

An illustrative full recomputation reading 5,000 tasks at 20 changes/s examines 100,000 task instances/s for one computation. A delta-based update may touch far fewer rows, but can require an index, retained intermediate state, and more complicated maintenance. The tradeoff is recurring compute versus maintained representation and implementation complexity.

This is why a product’s “automatic realtime” feature should be interpreted as a programming guarantee, not a promise that every query shape is equally cheap. Explain dependencies and execution paths before projecting capacity.

ClickHouse optimizes a different unit of work

ClickHouse’s architecture documentation describes column-oriented storage and vectorized processing of blocks of values. Its emphasis is efficient analytical execution over large datasets. These mechanisms reduce unnecessary column reads and per-value dispatch costs while supporting parallel processing. Architecture overview

Consider a billion events with 100 equal-width columns, where one query needs only timestamp and revenue. In an idealized uncompressed representation, reading two columns instead of all 100 reads 2% of the column bytes. If each value occupies 8 bytes, all columns total 800 GB, while the two needed columns total 16 GB. Compression, indexes, row selection, and encoding alter actual reads, but the order-of-magnitude motivation is clear.

This workload benefits from large sequential access and batched arithmetic. It does not need one client to receive every row change immediately. A transactional action checking a handful of ownership records has a different cost profile. Choosing between architectures requires identifying whether useful work is a small protected transition or a large reduction over data.

Column storage does not make all queries cheap. A query reading nearly every column, returning enormous results, or joining skewed inputs can still consume substantial resources. A narrow aggregation can be fast because it avoids work, while a poorly selective query can move the bottleneck to memory bandwidth, storage, or shuffle.

Batch size and user-visible delay

Batching amortizes overhead, but its delay must be counted. An analytics ingestion pipeline can accept a short collection interval to form efficient batches. A player waiting for a movement confirmation may not accept that same interval. Both systems can use batching, but their acceptable batch boundaries differ.

Suppose an illustrative pipeline collects events for 100 ms, processes the batch in 30 ms, and exposes results after another 20 ms. An event arriving just after a batch closes can wait nearly 100 ms before processing, giving roughly 150 ms end-to-end freshness before network effects. An event arriving just before closure waits less. Reporting only the 30 ms execution time omits the user-visible ingestion delay.

Conversely, forcing every analytical event through an individual request can increase protocol, allocation, and storage overhead enough to reduce sustained throughput. A low per-event batching delay may be achieved with adaptive policies, but they need evidence under both low and high traffic.

Latency is therefore always relative to a defined start and finish: input accepted, transaction committed, result queryable, subscription applied, or frame rendered. Different systems often emphasize different endpoints.

Where a conventional backend fits

InsForge exposes a Postgres-backed application platform with channel-oriented realtime mechanisms, including trigger-published database events and broadcasts. That supplies useful infrastructure while leaving the application responsible for choosing event semantics and maintaining its client state model. InsForge realtime overview

A conventional stack can implement exactly the same business invariants using transactions, constraints, server functions, and reliable publication patterns. The comparison is not correctness versus incorrectness. It concerns how much architecture the product supplies, how much the team assembles, and how closely the supplied model matches the application.

For a content application with occasional notifications, channels may be simpler than maintaining many reactive relational views. For a shared simulation, automatically maintained row state may remove substantial custom synchronization code. For analytics, publishing every raw change to every user is usually less useful than maintaining aggregated queryable history.

A complete cost comparison includes application code, operational components, schema evolution, client reconciliation, and failure handling. Comparing only database execution time misses the engineering work that these products aim to reduce.

A fair scenario matrix

The following matrix describes workload pressures rather than a product ranking:

Scenario	Dominant concern	Mechanism to investigate
Contested item pickup	Short exact state transition	Transaction boundaries, contention behavior, retry identity
Collaborative task list	Coherent reactive result	Dependency footprint, result sharing, authorization
Historical revenue report	Efficient reduction over many rows	Column pruning, vectorization, partition pruning, aggregation
Live world positions	Selective frequent delivery	Interest management, fan-out, batching, backpressure
Administrative export	Large bounded data transfer	Snapshot semantics, streaming, resource isolation

A single application can need all five. It may use one engine within acceptable limits or maintain separate transactional and analytical representations. Separation adds pipeline lag and recovery obligations, so it should solve an actual workload problem.

A worked shared-world capacity envelope

Assume 1,000 players each generate 20 authoritative updates/s. That is 20,000 updates/s. If each update requires 25 microseconds of exclusive state execution, execution demand is 20,000 × 0.000025 = 0.5 execution-seconds/second. This is an idealized 50% occupancy of one execution resource before subscription work and maintenance.

If every update goes to 100 recipients with 80 payload bytes each, delivery demand is 20,000 × 100 × 80 = 160,000,000 B/s, or 160 MB/s. Framing, copies, encryption, and acknowledgments add more. The application can be below its execution ceiling and above its network capacity simultaneously.

Reduce average recipients to 20 through interest management and payload becomes 32 MB/s. That change saves a factor of five in this component without making transaction execution faster. Alternatively, shared serialization can reduce CPU copying while leaving the same 160 MB/s of payload. Name the resource saved to understand the optimization.

Finally, a client receiving 20% of those updates over a poor connection can become a slow consumer. The service needs bounded backlog and resynchronization behavior. Keeping a connection open indefinitely is not itself a successful realtime experience.

Games add simulation semantics above state delivery

A synchronized position is a sample of authoritative state. Drawing smooth movement requires interpolation or prediction. Correcting predicted movement requires reconciliation. Evaluating a shot against earlier observed positions can require lag compensation. These are simulation and presentation protocols, not automatic consequences of low database latency.

A database can make the authoritative transition reliable and fast while a game still feels uneven because updates arrive irregularly. Conversely, prediction can make a game feel responsive while authoritative confirmation remains delayed. The two layers should be measured separately so a smooth client does not hide server overload.

Persistent worlds make integrated state particularly attractive because the same actions affect durable ownership, current simulation, and client visibility. A short-lived competitive match may prioritize different networking techniques and persist only selected outcomes. Games are a demanding family of workloads, not one uniform workload.

A comparison trace with equal guarantees

Suppose an engineer compares two implementations of crafting. Implementation A replies after local in-memory mutation and later persists it. Implementation B waits for a replicated durable decision. A is likely to show a shorter acknowledgment path in this setup, but the comparison has changed durability. The appropriate result is “these configurations provide different contracts,” not “A implements the same operation faster.”

The same mistake can occur on the read side. A local subscribed value is immediately accessible but may reflect an earlier server position. A request for a fresh authoritative value pays a communication path. Comparing their read times without naming freshness creates an apparent advantage partly produced by different semantics.

A fair protocol records four points: client intent, server acceptance, durable outcome, and client application of the resulting state. It reports delays between the relevant points and identifies any stages that overlap. It also records the client location and the number of recipients. One writer with one subscriber and one writer with a thousand subscribers are different workloads even when reducer logic is identical.

For analytics, equal guarantees require an ingestion boundary. If one system reports events queryable immediately after acknowledgment and another makes them queryable after a background batch, query latency alone omits freshness. A five-millisecond query over one-minute-old data can be appropriate for a dashboard, but it is not equivalent to a five-millisecond query over the latest committed data.

Avoiding an accidental universal engine

A hypothetical team initially stores task edits and activity history in one operational database. At modest scale, one system simplifies transactions and recovery. Later, history grows to billions of events, and broad reports repeatedly disturb interactive edits. The first response should identify the actual interference: scan CPU, buffer eviction, storage bandwidth, or lock/version retention.

If a secondary analytical representation is justified, its interface should be explicit. Committed changes flow through a recoverable position, ingestion records progress, duplicates are handled according to stable event identities, and schema versions remain interpretable. Reports display a freshness watermark. The analytical service must not become the authority for permission changes or exact inventory simply because it contains a copy of the data.

This split is an example of architectural specialization, with a real operational bill. The team now owns replay, retention, monitoring, and reconciliation between representations. It can rebuild analytics from an authoritative history only if that history is complete and retained long enough. A pipeline that misses old deleted records may be unable to reconstruct equivalent results.

An integrated realtime engine may delay the need for custom synchronization, while an analytical engine may delay the need for custom scan optimization. Neither removes the responsibility to define authoritative facts and derived representations. The productive question is which recurring work the application can entrust to a mechanism whose contract it understands.

This perspective also makes product evolution easier to assess. A new feature should be placed on the mechanism map: does it improve execution, storage capacity, query maintenance, delivery, or operational recovery? A roadmap statement about one dimension should not be generalized into solved scalability in all dimensions.

Synthesis

SpacetimeDB, Convex, and ClickHouse illustrate three complementary priorities: integrated authoritative state with subscriptions, reactive transactional application queries, and efficient analytical execution. The useful comparison follows computation boundaries, data layout, concurrency, result maintenance, and delivery cost.

A strong engineer can explain why a design is favorable for a particular operation and unfavorable for another without treating either result as a contradiction. The final lesson uses that skill to evolve a concrete reference architecture, carrying correctness and failure assumptions through each added optimization.

Sources and further reading

Course index · Lesson plan