Fixing a Slow PostgreSQL Dashboard

While working on a B2B operations platform, I built a dashboard that got slower as the amount of business data increased. Nothing was completely broken. The dashboard was just slow enough to be irritating every time someone opened it, which happened throughout the day. The database was clearly involved, so increasing its resources looked like a reasonable solution. More CPU or memory probably would have improved the response temporarily. But it wouldn’t have fixed what the application was doing.

The Dashboard Queries

The dashboard combined totals, status summaries, recent activity, and data grouped across different parts of the operation. From the user’s perspective, it was one page. From the database’s perspective, it was a sequence of queries with joins, filters, and aggregations over data that had grown significantly since the first version. Some filters didn’t align with the available indexes. One aggregation recalculated information that didn’t need to be rebuilt for every request. The API also fetched details that the dashboard never displayed. No single query looked disastrous when tested with a small dataset. Under production-like data and repeated use, the combined cost was obvious, so we needed to understand the exact work the application was giving the database.

The first difficulty was reproducing the delay. On my local database, the same dashboard opened quickly because there wasn’t enough data for the inefficient parts to matter. I prepared a dataset closer to the volume we had in the real environment and measured the complete request, including the time spent converting the result in the Java application. That showed that part of the waiting was in PostgreSQL and another part came from loading and mapping records that never appeared on the screen.

I used the query plans to compare what I expected PostgreSQL to do with what it was actually doing. A few indexes looked relevant from the table definition but didn’t match the combination of filters used by the dashboard. One query also joined a larger set of records before applying a condition that could have reduced the work much earlier. These weren’t unusual mistakes, but they had been almost invisible while the dataset was small.

The Real Access Pattern

I started by looking at the queries generated by the critical requests and how the database executed them. We weren’t trying to make every query perfect, only to find the few operations responsible for most of the waiting time. We changed the queries that mattered, added indexes based on the real filters, removed data the dashboard didn’t use and stopped recalculating stable information on every page load. The response dropped from several seconds to under one second in the workflows we worked on. The client didn’t care which index we added or what changed in the query plan. They cared that a dashboard they opened throughout the day was fast again. We never needed to replace the database. The application just needed to ask less from it on every page load.

We released the changes separately and measured the dashboard again after each one. This helped because not every query improvement changed the complete page in a noticeable way. Some operations ran in parallel and others were only used with specific filters, so a large percentage improvement in an isolated query could still mean very little for the normal user. The change that removed unnecessary details from the API response was less interesting from a database perspective, but it improved both query time and the work performed by the application.

After the Change

The dashboard stayed under one second for the workflows and data volume we tested. I don’t treat that number as permanent because the platform continued receiving more data and the way people used the filters also changed. We kept the slow requests visible so the team could see if the same access pattern became expensive again instead of waiting for users to report that the page felt slow.

I liked this problem because the final code changes were relatively small compared with the effect they had on the product. We considered increasing the database resources and that might have bought us some time, but it would have made the same set of queries more expensive on a larger machine. Understanding the dashboard first gave us a faster page without changing the database or adding another component to the system.