All case studies

PostgreSQL Dashboard Optimization: 15s → 2s

Moving repeated reporting work out of the request path with materialized views, pre-aggregation and revised queries.

Independent client work · Reporting dashboard · 2022–2023

Node.jsPostgreSQLPrismaGraphQL

I analyzed the data and query structure, introduced the materialized views and pre-aggregation, and revised the Node.js/Prisma/GraphQL dashboard queries.

THE OUTCOME15 seconds → 2 seconds

The problem

An outbound-sales reporting dashboard took about 15 seconds to load, repeating reporting calculations on every request.

The dashboard used Node.js, Prisma and GraphQL over PostgreSQL. The optimization needed to preserve existing software integrations and support reporting across active and historical campaigns.

What I did

I analyzed reporting queries across campaign, lead, manager and industry metrics. The investigation identified repeated aggregation work that could be moved out of the dashboard request path.

I introduced materialized views and precomputed campaign and lead metrics, then revised the dashboard queries to use those results. Active and historical campaigns received filtered aggregation paths.

Technical details

CampaignMetricCombined and ActiveClient materialized views supported the new reporting structure. Manager and industry aggregation moved into prepared data, allowing the API to retrieve results with less repeated computation.

Workflow overview

Before · work on every request

  1. Dashboard API
  2. Joins and aggregation
  3. Raw reporting tables
  4. About 15s dashboard load

After · prepare once, read efficiently

  1. Dashboard API
  2. Revised queries
  3. Materialized / pre-aggregated data
  4. About 2s dashboard load

Pre-aggregation speeds up reads but requires data refreshes. Refresh frequency determines how current the dashboard can be.

The result

Reported dashboard load time improved from approximately 15 seconds to 2 seconds, an 87% reduction, after introducing materialized views, pre-aggregation and revised queries.

More case studies.

All case studies

Facing a similar engineering challenge?

I help companies solve difficult backend, data and AI integration problems.

Book a technical call
Hiring a senior backend, applied AI or forward-deployed engineer?Explore my experienceDownload résumé