How We Built Real-Time Analytics at Scale
When we started building AItocha Surveys, the analytics dashboard was an afterthought — a simple page that queried the database and displayed counts. That worked fine with a few hundred responses. It stopped working when a single survey started receiving 10,000 responses in an hour during a product launch campaign. This post describes how we rebuilt our analytics pipeline to handle real-time data at scale.
The Problem
Our original architecture was straightforward: a PostgreSQL database storing survey responses, and API endpoints that ran aggregate queries on every page load. SELECT COUNT(*), AVG(rating), etc.
This created three problems:
1. Query latency climbed linearly with data volume. A survey with 100K responses took 2–3 seconds to compute analytics. Users were staring at loading spinners.
2. Database load from analytics queries competed with write operations. During high-traffic periods, inserting new responses slowed down because the database was busy computing analytics for the dashboard.
3. No real-time updates. Users had to refresh the page to see new responses. In 2026, that feels archaic.
Architecture Overview
We settled on an event-driven architecture with pre-computed aggregates:
Write path: When a new response comes in, we write it to PostgreSQL (source of truth) and simultaneously publish an event to our message queue. A worker process picks up the event and updates pre-computed aggregate tables.
Read path: The analytics dashboard reads from the pre-computed tables, which are always up-to-date. Query time is constant regardless of total response count — we're reading a single row per metric, not scanning millions of rows.
Real-time path: We use WebSockets to push updates to connected dashboard clients. When a new response arrives, the worker not only updates the aggregates but also broadcasts the update to any open dashboard sessions.
Pre-computed Aggregates
The core insight is that most analytics queries are variations of the same few operations: count, average, distribution, and time-series grouping. We can maintain running counters instead of recomputing from scratch.
For each survey, we maintain:
Updating these on each new response is O(1) — a constant-time operation regardless of how many total responses exist. Reading them is also O(1). This replaced queries that were O(n) where n is the total response count.
Handling Race Conditions
With concurrent writes, naive increment operations can lose updates. If two responses arrive simultaneously and both read count=100, increment to 101, and write, we've lost a response.
We use PostgreSQL's atomic UPDATE with arithmetic expressions:
UPDATE survey_aggregates SET total_count = total_count + 1,avg_rating = avg_rating + (NEW.rating - avg_rating) / (total_count + 1)
WHERE survey_id = NEW.survey_id;This is executed as a single atomic operation within the database, eliminating race conditions without requiring application-level locks.
WebSocket Real-Time Updates
For the real-time dashboard, we use a WebSocket connection per active dashboard session. When the analytics worker updates aggregates, it publishes the delta to a pub/sub channel. A WebSocket server subscribes to these channels and forwards updates to connected clients.
On the frontend, we merge these deltas into the existing state without a full page refresh. The result is that you can watch your dashboard update in real-time as responses come in — counters tick up, charts extend, and new feedback appears in the feed.
To handle reconnection gracefully, each update includes a sequence number. If a client reconnects and its last-seen sequence is behind, it fetches the current state from the REST API and resumes real-time updates from there.
Performance Results
After the migration:
The pre-computed aggregate tables add about 1KB of storage per survey — negligible compared to the response data itself.
Lessons Learned
Start with the simplest thing that works. Our original query-on-read approach was correct for the first year. Pre-optimizing would have been wasted effort. We migrated when the data told us we needed to.
Pre-computed aggregates solve most "analytics at scale" problems. You rarely need a full data warehouse or stream processing framework. Running counters and incremental computation handle the vast majority of dashboard use cases.
WebSockets add complexity. Connection management, reconnection, state synchronization — it's more code than you expect. But for a real-time analytics dashboard, the UX improvement is worth it.
If you're building something similar and want to discuss architecture, reach out — we're happy to share what we've learned.
