Building a Real-Time Analytics Dashboard with Next.js and PostgreSQL

The Architecture Decision

Analytics dashboards have two distinct requirements: historical queries (show me last 30 days of revenue) and live updates (show me what is happening right now). Most teams build one system that tries to do both and ends up doing neither well. The right approach is to separate them: PostgreSQL with proper indexing for historical data, and a lightweight pub/sub mechanism for live updates.

Time-Series Data in PostgreSQL

PostgreSQL handles time-series data well with the right schema. Use a timestamptz column for all timestamps, create a BRIN index on it (much smaller than B-tree for sequential timestamp data), and use table partitioning by month for datasets over 10 million rows. The date_trunc function makes aggregations clean:

-- Revenue by day for the last 30 days
SELECT
  date_trunc('day', created_at) AS day,
  SUM(amount) AS revenue,
  COUNT(*) AS transactions
FROM payments
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Caching Aggregations

Never run raw aggregation queries on every dashboard load. Pre-compute hourly and daily aggregates with a background job and cache them in Redis with a 5-minute TTL. For the current hour, run the live query. For everything before that, serve from cache. This pattern handles millions of rows without dashboard latency.

Live Updates with Server-Sent Events

For real-time metrics (active users, live revenue), use Server-Sent Events from a Next.js route handler. Every time a relevant event occurs, publish it to a Redis channel. The SSE handler subscribes to that channel and streams updates to the browser. This is lighter than WebSockets for one-directional data flow and works perfectly on Vercel.

// app/api/live-metrics/route.ts
export async function GET() {
  const stream = new ReadableStream({
    start(controller) {
      const subscriber = redis.subscribe('metrics', (message) => {
        controller.enqueue(`data: ${message}\n\n`);
      });
      return () => subscriber.unsubscribe();
    },
  });
  return new Response(stream, {
    headers: { 'Content-Type': 'text/event-stream', 'Cache-Control': 'no-cache' },
  });
}

The UI Layer

Use Recharts or Tremor for the chart components — both are React-native and TypeScript-friendly. Tremor in particular is designed for dashboards and has sensible defaults for time-series data. Pair with TanStack Query for data fetching: it handles caching, background refetching, and stale-while-revalidate out of the box.

I have built analytics dashboards for SaaS products, healthcare platforms, and e-commerce operations. See the case studies or get in touch to discuss your dashboard requirements.