Teardown: how reactive and sync databases work under the hood

A "reactive" or "sync" database promises that the UI stays correct without you writing the code that keeps it correct. Behind that promise every system answers the same four questions. Where do changes come from? When a write commits, how does the system find the queries it affects? What does the client hold? And what is guaranteed when two writes race, or the network drops?

This post takes ten systems apart against those questions: Convex, Supabase Realtime, Cloud Firestore, Replicache and Zero, ElectricSQL, PowerSync, InstantDB, Triplit and LiveStore. Then it holds Loams Live to the same standard.

Method. Public sources only: each project's open-source code (read on GitHub, not cloned), its docs, its engineering posts and, for Firestore, its ICDE 2023 paper. Every mechanism links to where we read it. Convex and PowerSync are FSL-1.1 and Triplit is AGPL-3.0: we read them to understand them, and no code from any of them is in this post or in Loams. Everything was read on 2026-10-18 and changes often.

The four questionsCopy Anchor Link

The systems fall into four rough families, by where a change gets matched to the people who care about it:

1. Track reads, rerun the query. The server runs your query, records what it read, and reruns it when a write overlaps that record. Convex, InstantDB and Loams Live.
2. Filter the change stream. The server reads a log of row changes and decides, change by change, which subscribers get it. Supabase Realtime and Firestore.
3. Maintain views incrementally. The server keeps a dataflow pipeline per query and pushes row diffs through it. Zero, and Triplit since 1.0.
4. Replicate a partition. The server ships a subset of the data, or a log of events, to a local database on the client, and the client queries locally. ElectricSQL, PowerSync and LiveStore.

The family decides most of the rest: how a system scales, what "consistent" means for it, and whether it works offline.

Convex: read sets, one committer, one timestamp per clientCopy Anchor Link

Convex is the cleanest example of the first family, and the model Loams Live borrows from. Its backend is open source as get-convex/convex-backend under FSL-1.1-Apache-2.0.

Functions. Your queries and mutations are TypeScript, bundled with esbuild and run in V8 isolates. The runtime controls time, randomness and I/O scheduling so that a function's result depends only on its arguments and what it read (How Convex Works). That determinism carries the design: a mutation can be rerun after a conflict because it had no side effects, and a subscription is exact because nothing outside its reads could change its result.

Transactions. Each function runs at a begin timestamp, a Hybrid Logical Clock value in nanoseconds. Its reads are recorded as a read set, made of index ranges such as "user_item where userToken = x and itemId = y", and its writes are buffered in a write set. A single committer is the only writer to the transaction log. It assigns a commit timestamp, checks whether any write between the begin and commit timestamps overlaps the read set, and either appends the write set or fails with an OCC conflict, after which the function runner retries at a newer timestamp. The check lives in crates/database/src/committer.rs, against the recent commits held in write_log.rs. Because conflicts are checked on read ranges, not only on written keys, Convex mutations are serializable.

Subscriptions. A query's read set is the same structure, kept after the query returns. The SubscriptionManager holds every live read set in an interval map per index (and a separate structure for text-search subscriptions). advance_log walks the new log entries once, looks up each written index key in those maps, and notifies the subscribers whose ranges it hits. Their queries rerun, and the new read sets replace the old ones. The query cache uses the same validity check.

The sync protocol. A client keeps one WebSocket to a sync worker. It sends ModifyQuerySet, Mutation and Authenticate messages. The server answers with Transition messages that move the client from a startVersion to an endVersion, where a version is the triple of query-set version, identity version and timestamp (npm-packages/convex/src/browser/sync/protocol.ts; the server half is crates/sync/src/worker.rs). The sync worker keeps every query in a client's set at the same timestamp, so two components on one screen never show different moments. Optimistic updates run on the client against its local query results and are rolled back once the server's results include the mutation (optimistic updates).

Limits and failure modes. Each query or mutation may read 16 MiB and scan 32,000 documents, write 16 MiB and 16,000 documents, read 4,096 index ranges, and run user code for 1 s. Documents are at most 1 MiB (limits). The classic failure is the hot document: many mutations updating one counter conflict, retry, and eventually throw. A mutation that reads a whole table conflicts with every insert into it (OCC errors). There is one committer per deployment. Self-hosted Convex stores state in SQLite by default, or in Postgres or MySQL (self-hosting), and the docs we read describe no offline mode: the client holds query results, not a replica.

Supabase Realtime: the WAL, wal2json and a check per subscriberCopy Anchor Link

supabase/realtime (Apache-2.0) is an Elixir and Phoenix cluster. It offers three features: Broadcast (pub/sub between clients over Phoenix channels), Presence (an in-memory, CRDT-backed key-value store per channel) and Postgres Changes, the database-backed one (architecture).

How a subscription is registered. When a client joins a channel with a postgres_changes filter such as UPDATE on public.todos where user_id=eq.42, Realtime writes a row into the tenant's realtime.subscription table. The row holds the table, the parsed filters, the action and the client's verified JWT claims (ARCHITECTURE.md).

How a change finds subscribers. A ReplicationPoller per tenant holds a temporary logical replication slot. It polls by calling the SQL function realtime.list_changes, which drains the slot with pg_logical_slot_get_changes in wal2json format 2, with transaction markers off. It backs off when idle (replication_poller.ex). For each change, realtime.apply_rls groups the subscriptions by role. It switches to each role, sets request.jwt.claims to each subscriber's claims, and executes a prepared statement that asks whether that subscriber may select that row. Only visible rows go out, with the columns the role cannot read stripped. The table's own RLS policies decide, not a copy of them, which is careful. It also means each change costs a query per subscriber.

What it promises. Supabase's own docs state the trade-off. Postgres Changes authorizes every event against each subscriber, so throughput scales with subscribers, not with write rate. Changes are processed on a single thread to keep their order, and larger database instances do not meaningfully raise throughput. Above roughly 3,000 subscribers on the same changes, the docs recommend Broadcast instead. Deletes cannot be authorized, because the row is gone, so subscribers get only the primary key (Postgres Changes). The client receives row events, not query results. Keeping a list correct (ordering, limits, joins) is the application's job, and the slot is temporary, so the docs describe no way to replay what a disconnected client missed. Broadcast from the database is the scalable path: a trigger calls realtime.broadcast_changes or realtime.send, which inserts into realtime.messages, a table partitioned by day and kept for about three days. Realtime reads it through its own publication, and private channels can replay up to 25 messages (Broadcast).

Firestore: the changelog sits in the commit pathCopy Anchor Link

Firestore is closed source, but its ICDE 2023 paper (Kesavan et al., Firestore: The NoSQL Serverless Database for the Application Developer) describes the real-time path in more detail than most open-source projects document theirs.

The write side. A Firestore write is a Spanner read-write transaction. Before committing, the backend picks a maximum commit timestamp M and sends Prepare RPCs to the Real-time Cache's in-memory Changelog tasks, each of which returns a minimum timestamp. Spanner then commits within those bounds, and the backend sends Accept RPCs carrying the outcome and the full changed documents. So every committed change reaches the Changelog in timestamp order, and the change feed is part of the commit protocol, not a tail read afterwards. The price is availability: if Prepare fails because the Real-time Cache is unavailable, the write fails.

The read side. A listener first runs as a normal query and records the Spanner timestamp of its result, the max-commit-version. The frontend then sends Subscribe RPCs to the Query Matcher tasks that own the document-name ranges the query covers (ownership is sharded by Slicer). Matchers run the query "in reverse" against every document update after that version. Because a query's updates come from several matchers, the frontend waits until all of them have reached a timestamp (idle ranges send heartbeats every few milliseconds) before it sends a consistent incremental snapshot. All queries on one connection advance to a timestamp t only once every one of them has reached t. Firestore's real-time queries at scale page gives the same flow and adds that security rules are evaluated again for each forwarded update.

Failure modes. If an Accept times out or reports an unknown outcome, the Changelog cannot order that name range. It marks the range out of sync, and every frontend with a matching query throws its state away and reruns the query from the start. Hot spots come from data shape: a sequentially increasing indexed field, such as a creation timestamp, writes consecutive index rows that are hard to split, and the best practices ask for a gradual ramp of new traffic (the "500/50/5" rule).

Offline. This is where Firestore is far ahead of the first two families. The mobile and web SDKs keep a local cache with local indexes, acknowledge a client's write as soon as the cache is updated, and flush it later. Persistence is on by default on Android and Apple platforms, off on the web, with a 100 MB default cache (offline data). Client SDK transactions are optimistic, emulated with version preconditions, while server libraries in Standard edition lock pessimistically (transaction concurrency).

Replicache and Zero: server reconciliation, then IVMCopy Anchor Link

Rocicorp's code is in rocicorp/mono (Apache-2.0).

Replicache introduced the write model that Zero keeps. The client holds a key-value client view. Mutators are named functions that run immediately on the client and are queued as pending mutations with sequential ids. Push sends them to your server, which runs the same mutators authoritatively, in order. Pull returns a patch, a new opaque cookie and each client's last processed mutation id. The client then rebases: it rewinds to the last server state, applies the patch, replays the still-pending mutations on top, and reveals the result in one step. An optional poke tells clients to pull now (how Replicache works). Conflicts are resolved in code: the server replay sees the newest state and decides.

Zero adds queries. zero-cache replicates Postgres through logical replication into a SQLite replica. One replication-manager owns the slot and backs the replica up with Litestream, and any number of view-syncers serve clients (self-hosting). Queries are named functions that your server resolves to ZQL, which is also where permissions are applied. A query runs first on the client against the local store, then on a view-syncer, which hydrates it once and then pushes only row diffs through an incremental view maintenance pipeline. The operators (filter, join, take, fan-out and fan-in) are in packages/zql/src/ivm. Client view records track what each client group has. Results are typed unknown, cached or complete, and Zero syncs "a consistent partial replica" (queries).

Limits. The pipelines are per client group, on Node's single thread, so one busy group can starve others. A circuit breaker resets pipelines when advancing looks slower than rehydrating, and sticky sessions avoid cold rehydration. Zero recommends datasets under about 100 GB on local NVMe, supports offline reads but not offline writes, and says plainly that it "is not local-first" (when to use).

ElectricSQL: shapes as cacheable HTTP logsCopy Anchor Link

Electric (Apache-2.0) is the read-path half of a sync engine, deliberately. A shape is a table, an optional where clause (subqueries allowed) and optional columns. Shapes are immutable and single-table (shapes). The Elixir sync service reads Postgres logical replication and, for each change, asks Electric.Shapes.Filter which shapes it affects. The filter keeps equality and inclusion indexes over where conditions in ETS tables, so matching does not scan every shape. Each shape's changes are appended to a shape log.

The protocol is plain HTTP (HTTP API). offset=-1 fetches the log from the start, in pages. live=true with the shape's handle and the last offset long-polls (or uses SSE) for what comes next. An up-to-date control message says the client has everything the server knew when it answered, and must-refetch (or a 409) tells it to drop the shape and start again. Because responses are ordinary cacheable HTTP with ETags, a CDN can serve initial syncs and collapse concurrent long-polls into one request to the origin. That is Electric's scaling story, and a good one. Writes are out of scope: they go through your own API, and the docs ask you to proxy shape requests through your backend for authorization.

PowerSync: buckets and checkpointsCopy Anchor Link

The PowerSync service (FSL-1.1-ALv2) replicates Postgres, MongoDB or MySQL into buckets: append-only histories of row operations, defined by sync streams (or the older sync rules) and shared between users who need the same data. The service streams bucket operations to clients and never queries the source database during streaming (service architecture). The client is a real SQLite database.

Consistency is per checkpoint: a point in time on the server that holds only committed transactions. The server announces a checkpoint with a checksum per bucket, streams the operations, then sends "checkpoint complete". The client applies the whole checkpoint at once and verifies the checksums. On a mismatch it deletes the bucket and resyncs (protocol). Local writes go to an upload queue, a blocking FIFO that your backend connector drains into your own API. While the queue holds writes, the client does not advance to a new checkpoint. Once the queue is empty it requests a write checkpoint and waits until replication has carried its writes back (consistency). So the client never merges and never shows server data that omits its own writes, at the cost that one rejected mutation blocks the queue until the app handles it.

InstantDB: triples, topics and the WALCopy Anchor Link

Instant (Apache-2.0) stores everything as entity-attribute-value triples. Its query language, InstaQL, compiles to Datalog (A Graph-Based Firebase). Its invalidator sits between the first two families. The server reads the Postgres WAL through a temporary logical replication slot (jdbc/wal.clj). For each transaction, reactive/topics.clj turns the changed triples into topics, patterns over index, entity, attribute and value. Then reactive/invalidator.clj marks every subscribed query whose topics match as stale and refreshes those sockets. Topics play the role of Convex's read-set ranges, but they are derived from a WAL that any Postgres client could have written. Behind a flag the invalidator can drop work under backpressure.

The client (Reactor.js) persists its query subscriptions and pending mutations in IndexedDB, so queries answer from cache offline and writes survive a reload. Conflicts are last-writer-wins per attribute. One status note: Instant's site says its hosted service is sunsetting, with services continuing until August 31, 2027. The open-source code remains.

Triplit: the same database on both endsCopy Anchor Link

Triplit (AGPL-3.0) runs one TypeScript database, TriplitDB, in the browser and on the server. Writes are stamped with a hybrid logical clock (hybrid-clock.ts) and merged attribute by attribute as a CRDT, so two clients editing different fields of one record both win. Clients subscribe to queries, and the server sends only the attribute-level changes a client has not seen. Offline writes queue in an outbox. The offline docs are candid that the default storage is memory, so a page refresh loses the cache and the outbox unless you choose IndexedDB. Triplit 1.0 added an ivm module to the database (packages/db/src), which puts it in the third family too. Status: Triplit's co-founder joined Supabase in October 2025 (Supabase blog), and the repository was last pushed in January 2026.

LiveStore: event sourcing on the clientCopy Anchor Link

LiveStore (Apache-2.0) moves the database to the client completely. Every change is an event in an ordered event log. Materializers project the log into SQLite on the client, in memory for queries and persisted for durability, and the event and its materialization commit together. Sync works like git: a client pulls upstream events, rebases its unpushed events on top, then pushes. A sync backend only stores events, enforces their total order and notifies clients (how LiveStore works). Reactive queryDb queries re-run when the tables they read change (reactivity). The limits follow from the design. All of an app's data must fit in an in-memory SQLite database (up to about 1 GB is fine on most devices), and one event log suits tens to low hundreds of concurrent users. For a server-owned source of truth its docs point to Zero or Electric (when to use).

Side by sideCopy Anchor Link

Two patterns stand out. Systems that own their commit log (Convex, Firestore) can give a client one consistent moment across all its queries; systems that read Postgres's WAL give you rows or replicas and leave that question to the client or your API. And offline writes appear only where the client holds a replica or a durable queue.

Loams Live against the same questionsCopy Anchor Link

Loams Live is in the first family on purpose. Its design document says the goal is "Convex's developer model on infrastructure Loams can run", borrowing concepts, not Convex's wire protocol, API names or code (§20). Everything below comes from the Loams repository.

Where changes come from. Live owns its commit log, like Convex, but the log is not a separate service. Every mutation is one optimistic transaction on TiKV (on an embedded MVCC store on redb in loams dev and Loams Desktop), and the same transaction writes a journal entry. The entry names each document written and its removed and added index keys, at a dense sequence number in one of 64 journal shards (config.rs). We chose it over TiKV's change feed, which in our spike needed about a second before events could be put in commit order (§20 §5.3).

How a change finds its subscribers. Each app's subscription manager ticks on a timestamp from the timestamp oracle. It wakes right after a local commit and otherwise polls every 20 to 200 ms. It reads the journal shards up to that timestamp and looks up every written key in a read-set index: a hash map for documents read by id, and an augmented AVL interval tree for index ranges (readset.rs, subs.rs). Matching subscriptions rerun at the tick, 16 at a time. Bursts are coalesced to one rerun per 50 ms, and intermediate results are skipped, never reordered. Subscriptions with the same deployment, function, argument digest and identity share one rerun. As a safety net every subscription is rerun and compared every five minutes, and a difference is counted (live_missed_invalidation_total) and repaired.

What the client holds. Query results, as with Convex. The sync API is Connect, not a WebSocket: a server-streamed Watch plus unary ModifyQuerySet, Authenticate, Query and Mutate, so one contract works over HTTP/1.1, HTTP/2, gRPC and gRPC-Web (live.proto). A session's version is the same (query set, identity, timestamp) triple, and every query in a session is evaluated at one tick. A mutation returns its commit timestamp, and the client keeps an optimistic update until its session passes it. A reconnecting client resumes by rerunning its whole query set, with no diffs.

Consistency. Here Live is weaker than Convex by default, and we would rather say so. TiKV gives snapshot isolation. Live promotes documents read by id into the lock set, so read-modify-write of a document is safe. Index-range reads in a mutation are plain snapshot reads, though, so write skew is possible. An option, serializable_ranges, closes the gap by locking every journal shard head, which makes a range-reading mutation conflict with every concurrent writing mutation in the app. It is correct, it costs concurrency, and it is off by default (txn.rs). Mutations retry up to 16 times on conflict, and an idempotency key makes a lost acknowledgement safe to retry.

What Live already does that the others do differently. It runs on infrastructure you operate. The target topology is three PD nodes, at least three TiKV nodes across zones and at least two stateless Live nodes, which forward session calls to the node holding the stream (§45); the preview already serves one store from several nodes. Many apps share one engine, each in its own keyspace, with per-app and per-table authorization and end users from any OIDC provider. Functions run in QuickJS, either in process or in a sandboxed worker per app. Deployments are versioned, with rollback, validators and online index changes, and a deploy reaches every node at the same tick through a catalog journal entry (preview release notes).

What the others do better, today:

• Offline and local-first. Firestore, PowerSync, Instant, Triplit and LiveStore keep working without a network. Live does not, and offline-first sync is an explicit non-goal in §20: clients hold query results and optimistic updates, not a replica.
• Client libraries. Convex ships a polished reactive client and React hooks. Live's TypeScript SDK has the generated stubs and a watch stream in @loams/client. The hand-written reactive client with optimistic updates, @loams/live-react and the deploy CLI are planned tasks in the LV1 plan (Tasks 34 to 37), not shipped code.
• Serializability. Convex validates read ranges at commit, so its mutations are serializable by default. Live is serializable only for point reads, unless you pay for serializable_ranges.
• Fan-out at large audiences. Live shares reruns only between subscribers with the same identity, so a thousand users watching one room cost a thousand reruns. Electric's CDN-cacheable shape logs and Supabase's Broadcast are built for that audience. Live has no equivalent yet.
• Operations. The LV1 plan's nemesis tests, metrics, quotas, per-app backup and point-in-time restore, and performance gates (Tasks 25 to 33) are not yet built. §45 ties the word "production" to that checklist.
• Incremental results. Zero and Triplit push row diffs through view pipelines. Live reruns the whole query, which is simpler to prove correct and costs more for large results that change a little.

Why we still think the Convex shape is right for Loams. A journal written inside the transaction, ranges instead of row lists so phantoms cannot slip through, and one timestamp per session give a guarantee that is easy to state and to check: every pushed result equals a fresh snapshot read at its timestamp, and the reactive checker tests exactly that. Live's bet is that an application database beside the rest of Loams is worth more as a server-authoritative system with exact invalidation than as a replica on every device. Offline is the gap that bet leaves.

Corrections are welcome. If we have misread any of these systems, open an issue and we will fix the post.