How We Built Real-time Sync Without Melting Our Database

When we decided to make DayPilot fully real-time, we knew the engineering challenge was significant. This post walks through the decisions we made, the mistakes we survived, and the architecture we landed on.

Why real-time is hard

Real-time collaboration sounds simple: when Alice moves a task, Bob sees it move. But the moment you have more than two users, you hit fundamental distributed systems problems — network partitions, ordering guarantees, and conflict resolution.

Our starting point: polling

Our first implementation was embarrassingly simple: poll the database every 3 seconds. It worked for our first 500 users, but as we scaled, the load became unsustainable. At 5,000 concurrent users, our Supabase instance was getting 100,000 SELECT queries per minute just from polling.

// The naive approach — don't do this
useEffect(() => {
  const interval = setInterval(async () => {
    const data = await fetchAllTasks(boardId);
    setTasks(data);
  }, 3000);
  return () => clearInterval(interval);
}, [boardId]);

Switching to Supabase Realtime

Supabase's Realtime engine uses PostgreSQL's logical replication to stream changes to connected clients. Instead of polling, clients subscribe to a channel and receive row-level change events.

const channel = supabase
  .channel(`board:${boardId}`)
  .on('postgres_changes', {
    event: '*',
    schema: 'public',
    table: 'tasks',
    filter: `board_id=eq.${boardId}`,
  }, (payload) => {
    handleRealtimeEvent(payload);
  })
  .subscribe();

Optimistic updates and conflict resolution

The hardest part wasn't subscribing to changes — it was handling the 200ms window between a user's action and the server confirmation. We needed updates to feel instant while also being correct.

• Apply change optimistically to local state immediately
• Send mutation to the server
• On confirmation: no-op (state already correct)
• On error: revert with a toast explaining what happened
• On concurrent conflict: last-write-wins with version vectors

The rule we live by: the UI must never lie to the user for more than 200ms.