Live queries: screens that update in real time
Design a Reactive Database (Live Queries)
Replace polling with live queries: work out which writes change which results, keep every screen consistent with itself, stop two people breaking a rule at the same moment, and survive one query that a whole company is watching.
Advanced, about 45 minutes, 9 stages
The situation
A task-tracking app for teams: boards with columns, tasks that move between them, comments, and a count of unread notifications in the corner. When someone moves a task, everyone looking at that board should see it move.
Today the web app polls. Every open screen re-fetches its data every five seconds. At peak about 40,000 people have the app open, and a typical screen shows five pieces of data: the board, the open task, its comments, the unread count and the member list. Users make about 2,000 changes a second at peak.
Two complaints keep coming back. Changes take up to five seconds to appear, so people talk over each other in meetings ("it's in Done" / "no it isn't"). And the database spends most of its time answering polls whose answer has not changed. The team wants queries that stay live: the server sends new results when, and only when, they change.
What it has to do
Functional
- Clients subscribe to queries (a board, a task, a count) and receive new results when they change.
- Clients run mutations: create, move, edit and delete tasks.
- A column can have a work-in-progress limit: at most N tasks.
- Clients that reconnect catch up without reloading the page.
Non-functional
- A change appears on every affected screen within about 100 ms of being committed.
- Queries whose results did not change cost nothing after a write.
- One screen never shows results from two different moments (a count of 12 next to a board of 11).
- Rules such as the work-in-progress limit hold however many people act at once.
Constraints and assumptions
- About 40,000 people online at peak, with about five live queries each.
- About 2,000 mutations a second at peak.
- Queries and mutations are functions written by the app's developers, not fixed SQL.
- Most queries read one board or one task: tens to hundreds of rows through an index.
- A few queries are shared by everyone in a large company, such as an announcements board.
- Clients hold a WebSocket open while the app is in the foreground.
Interview questions it prepares you for
- “Design Firebase, or a real-time database.”
- “How would you make a dashboard update live without polling?”
- “Design the sync layer for a collaborative task tracker like Linear or Trello.”
- “Two users break a rule at the same moment. How do you prevent it?”
Read and practise next
How Convex built it · A database that pushes query results, in their engineers' own words
Concepts to know first: Persistent connections, Transactions.
Similar systems: Design a Collaborative Editor (Google Docs), Design a Distributed Cache (Memcache), Design a Notification System.