The problem
Accountants and store managers across TTMI’s 50 stores run reports over long date ranges: general ledger, stock movements, receivables and sales. These are wide reports over large transactional tables, and several of them were slow.
When we looked at the queries, the causes were mostly structural:
- The ORM-generated queries fetched far more columns and rows than the report showed.
- A report page needs both summary totals and one page of rows. Computing them separately meant running the expensive filtering query more than once per request, plus a count.
- For some query shapes, PostgreSQL’s planner badly misestimated how many rows a step would return and picked a slow join strategy.
I co-developed this work with a backend teammate. My part was the materialise-then-paginate refactor and several of the raw-SQL report endpoints.
What I did
Keep raw SQL in one layer
Raw SQL lives in a dedicated query layer and never in views or serializers. Views call that layer with parameterised inputs. This keeps the performance-sensitive code in one place where it can be tested and reviewed on its own.
The trade-off is losing some ORM convenience. A clear layering rule made that acceptable, and the team later adopted it as a convention.
Materialise once, then paginate
Inside one transaction, the endpoint runs the expensive filter and join once and writes the result into a temporary table. It refreshes that table’s statistics so the planner knows its real size, computes all summary totals in one pass, counts the rows, and returns the requested page. When the transaction ends, the temporary table goes away.
Each request now pays for an extra write. In exchange, the expensive part runs once, and totals, count and page all come from the same snapshot, so they always agree with each other.
Select only what the report shows
Every report query projects only the columns it displays. On wide tables this cuts the data the database reads and sends back to the API.
A scoped planner hint, used once
For one query shape, the planner’s row estimates stayed badly wrong. I applied a planner setting scoped to that single transaction so it cannot leak into other queries, and documented why it is there. Hints are brittle when data changes, which is why I kept this to one place and wrote the reason next to it.
Indexes built without blocking writes
Where query plans showed a real need, we added indexes, built concurrently so large tables stay writable during the build. We also wrote repair migrations for indexes whose definitions had drifted from what the code expected.
Result
Reports now compute totals, row count and the requested page from a single heavy pass instead of repeating it. Responses carry only the columns each report needs. Keeping raw SQL in a dedicated query layer became a team rule, which makes later performance work easier to review.
I do not have before-and-after latency figures I can publish for these endpoints, so I describe the outcome by what changed in the work each request does.
What I’d do differently
Most slow reports turned out to be doing the expensive work more than once, and missing indexes were a smaller part of the story. I would start every report investigation by reading the query plan and recording timings, so each gain has a number behind it. I would also add a regression check on query count per endpoint, to catch a report that slips back into running its heavy query twice.