The Report Works. Is All That Database Work Necessary?

00:00:00 — Alex
Welcome to Development Radio. I'm Alex, here with Maya.

00:00:03 — Maya
I've got a report fix I want to keep, and a slowdown I don't want to accept. For this example, the customer needs a month's orders in one file. The old version can't finish because the application is trying to hold the whole result in memory. So the patch lets it read a small page, use it, release it, then get the next one. The report finishes. But when we try that patch on a small group of workers, other reports start slowing down.

00:00:31 — Alex
On the patched workers, or are the old ones getting slow too?

00:00:35 — Maya
Both. They're still sharing the same database. Once the large report finishes, the pressure falls. We've held the rollout. But I don't buy ‘that's just the cost of finishing.’ We changed how the application gets its data. I want to understand which work the report needs, and which work comes from that choice.

00:00:53 — Alex
I also don't want us to throw away the part that worked. Holding a page at a time gets the report through. Can we keep that and be more suspicious of what happens between pages?

00:01:03 — Maya
The patch uses offset pages. A later page skips the rows from earlier pages. Making the pages bigger would mean fewer requests, but it also starts spending the memory we just got back. I don't especially want a batch-size compromise to be the answer.

00:01:19 — Alex
There may be a different change. GitLab has a published offset example where they locate the IDs first, then fetch the full records for just the requested page. They still skip entries. They reduce the full-record fetching while finding the page.

00:01:34 — Maya
But that's one page, isn't it? Our report eventually needs every record. I wouldn't carry their speedup over to the whole job.

00:01:41 — Alex
Neither would I. What interests me is which work their query stops repeating. Our report needs those earlier rows for the output. It doesn't need their contents fetched again just to reach the next page. Whether our query does that is the part we haven't seen.

00:01:57 — Maya
And the index walk is still there. I'd be thinking about keyset paging for our report: keep the last row's full ordering key and ask for the rows after it. We're going forward through the whole result. We don't need to jump to some arbitrary page number.

00:02:12 — Alex
That fits what you're trying to preserve. The worker can still consume a small page and release it. It would give the database a different way to find the next page, rather than telling it how many rows to skip. I'd want to see whether the actual query can use an index to get there efficiently.

00:02:28 — Maya
With the report's existing sort order, though. I don't want a wonderfully fast export that quietly rearranges the report. We've kept the input unchanged for this example, and the ordering is stable. That gives us something specific to compare: the same next rows, reached a different way.

00:02:46 — Alex
Then we can look at how the database reaches those rows. Does it get close to where the previous page ended, or still do a lot of work before returning the next batch? The page coming back small doesn't answer that.

00:02:58 — Maya
I don't want to lose the original comparison, though. We started with a broken version and a working version. Now we're talking about two ways to fetch a page in the working one. Have we quietly stopped asking what the fix made worse?

00:03:11 — Alex
I think we're getting a better comparison for that part. The old code shows us that offset paging was introduced. But its failed run can't tell us what a complete, efficient report should cost. Comparing two ways to deliver the same required rows lets us question the added work without treating failure as the cheap version of success.

00:03:32 — Maya
Now I want to know where the old run failed. Was it still fetching, or had it got the data and then failed building the report? Those are quite different pictures of how much database work was already happening.

00:03:44 — Alex
They are. If the data was already fetched, finishing the file doesn't mean all those rows are new database work. The memory failure alone doesn't place that boundary. We know the patch introduced offset paging; we haven't established how much of the database cost was already there.

00:04:00 — Maya
Then I'd start with a later page in an isolated comparison, away from customer traffic. Keep its input and required rows the same, and compare the current query with one that continues from the previous key. I want to see which work disappears while the memory fix stays intact.