This is a back to basics questions for anyone who understands backend in depth.
The problem is simple i have http api
Eg: v1/foo?filter=F1&offset=100&limit=10 with following query parameters
- limit
- offset
- filter
This api calls back into PG database on a standalone table with following columns
- PK (Serial int)
- Desc(text, not null)
- Flags (text, not null)
I assume most optimised query for the above api is(with a index on flags col as well)
select * from table
where Flags=${Filter}
offset ${offset}
limit ${limit}
order by PK
But if you look from DB Engine side its the worst query ever, DB Engine has to run the where clause across the entire table/index(assume rows above 10Million) then order it by PK and then limit and offset it.. which is the most inefficient thing… it kills the reason for having pagination on the API don’t you think?
I mean it has to scan entire table/index filter the results for everything and just return a part of it and repeat this for all pages(while we feel we have implemented pagination for lesser load and strain on resources)
So then what does the correct answer looks like for pagination?
First we ask PG how many blocks/OS File is this table spread across, we then take one block per api request scan it with where filter condition and then return the results?
It has some side effects like null page response (where the block returned zero results for filtered criteria even though there might be more blocks to scan through which may have results)Its more like an iterator interface where server decides what the page size and offset should be before yielding.
This seems to be the best way to manage resources and really be true to definition of pagination (loose analogy of scanning through a book of sorts),
But here is the problem i see no database every supports such thing why?
Limit and Offset always felt like after thought where result is entirely compiled then they are applied…
Or there are other operators/ways to implement this simple requirement?
Or am i missing anything here?
1