#🔒 Django Rest Framework — How can I efficiently paginate while avoiding repeating data retrieval?

45 messages · Page 1 of 1 (latest)

fathom compass
#

I understand this may not be possible. Let's say I have a function get_data(). This function takes 10-20 seconds to run and it retrieves a bunch of data from a database and processes it. It processes through a database with hundreds of thousands of rows and returns a few thousand rows in response. This few thousand rows is quite large—probably a few megabytes of JSON, more than I'd rather load in an API response. So, I would like to be able to paginate my response. However, the issue I'm running into is that every time you go to the next page, it reruns the query, which means that every single response takes a long time. Here is the code I'm using

class Query(APIView):
    def get(self, request, format=None):
        paginator = PageNumberPagination()
        paginator.page_size = 10
        data = get_data()
        page = paginator.paginate_queryset(data, request, view=self)
        return paginator.get_paginated_response(page)

I almost wonder if the best way to deal with this is to open a websocket between client and API and pass the data as it's requested? Or perhaps have a system of caching where each time the client makes a request, they generate some unique token (probably would be made by caching all query parameters as well as the exact timestamp at which the request was made) which attaches to their request, and then any time they ask for the next page of data, that unique token is attached to the request and the cached response is taken. Not sure if that would be better than a websocket. Any help is greatly appreciated.

gleaming troutBOT
#

@fathom compass

Python help channel opened

Remember to:

  • Ask your Python question, not if you can ask or if there's an expert who can help.
  • Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
  • Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.
late remnant
#

which database are you using? some of them have cursor systems which keep a query open for some time associated with a unique ID

fathom compass
#

I'm using sqlite currently. Honestly I'd rather be using postgres but the get_data function comes from another project I created which is distributed as a python package, so it pretty much has to use sqlite

#

But in theory if I did that as long as I give that unique ID back in the API's paginate call, that would work, right? I could just look for an open query if the unique ID is passed in the request parameters.

late remnant
#

actually maybe I'm not clear on some of the details around the process that generates the rows as output

#

streaming the data to the requestor might make more sense in this situation actually

#

assuming the process has to be completed from scratch every time someone requests it, and it isn't an indexing or query related issue that makes it take so long

fathom compass
#

Yeah I tried different indexing methods and none of them were able to consistently enough make the request faster

#

I found that other than in a few niche cases, it actually made the query slower

#

Presumably because making the file bigger is bad

fathom compass
late remnant
fathom compass
#

i assume I can't use django rest framework to do this

late remnant
#

but also I'm curious if it might make sense to just compress the json blob and send it back as a zip or something lol

#

depending on how big it is

#

it's like if you're going to invest the time in processing all that data you might as well just dump the result on a drive somewhere in a place where it gets cleaned up occasionally

#

just spitballing

fathom compass
#

I'm just not convinced it makes full sense to send all the data at once to the client

#

Like couldn't that cause performance issues to have a bunch of data unnecessarily loaded into memory?

#

conceivably with a relatively simple query to this database, a user could generate a JSON response which is 50 MB large

late remnant
#

hm i see

#

I used to do api design and we would argue about stuff like this for hours lol

#

my gut instinct on this is that if you're designing the api you get to set expectations with respect to what happens when someone calls an endpoint

#

and my concern is that trying to manage the fallout of running this query on your end too much is going to make your api more fragile and complicated

#

but if you go to your consumers and tell them hey if you call this you're going to wait for a bit then get a very large JSON file in response, they can build around that

#

but then that only makes sense if they ultimately want all that data at once

#

if this is something like paginated search results where they'll only see the first page or two, it doesn't make sense to send the whole json blob back

fathom compass
#

Well the only customer to this (most likely) is me. Because I'm just making this API as a backend to create a frontend website.

I do wonder if I will have to do something like a websocket solution either way. Because if a query can take 20+ seconds, that's dangerously close to the timeout limit, and if the server is under stress, it could take longer.

fathom compass
late remnant
#

ahh I see

#

huh maybe this is still just a query optimization problem, although for the purposes of addressing your question I'm assuming there isn't anything that can be done about it

#

do you have redis or anything you could toss the results in?

fathom compass
#

Oh redis could be good

#

Hmm I think that maybe something like this would be good:

Save the parameters used for the query as well as the response to the query. Any time the database is updated (at least once per day, at most once every few minutes—I haven't decided yet) I can do something smart to figure out which queries that have been cached in the redis would be affected (it should be relatively easy—just anything that would need to pull from the most recent data). Then I can delete those responses from the cache

#

Also fwiw this is my query. It has some Python template fstrings but it's not really all that significant. The query is literally just a SELECT statement, a few aggregations, a group by, and a "WHERE" clause (which is dynamically generated based on options the user selects). The LEFT JOIN might cause slight issues, but even removing it fully doesn't make it super significantly faster.

        SELECT
            {query_select}
            min(events.year) as start_year,
            max(events.year) as end_year,
            COUNT(DISTINCT events.GAME_ID) AS G, 
            SUM(events.PA) AS PA,
            SUM(events.AB) AS AB,
            SUM(events.H) AS H,
            SUM(events."1B") AS "1B",
            SUM(events."2B") AS "2B",
            SUM(events."3B") AS "3B",
            SUM(events.HR) AS HR,
            SUM(events.UBB) AS UBB,
            SUM(events.IBB) AS IBB,
            SUM(events.HBP) AS HBP,
            SUM(events.SF) AS SF,
            SUM(events.SH) AS SH,
            SUM(events.K) AS K,
            SUM(events.DP) AS DP,
            SUM(events.TP) AS TP,
            {"" if self.find == "player" else "SUM(SB) AS SB,"}
            {"" if self.find == "player" else "SUM(CS) AS CS,"}
            SUM(events.ROE) AS ROE,
            SUM(events.FC) AS FC,
            SUM(events.R) AS R,
            SUM(events.RBI) AS RBI,
            SUM(events.GB) AS GB,
            SUM(events.LD) AS LD,
            SUM(events.FB) AS FB,
            SUM(events.PU) AS PU
        FROM events
        LEFT JOIN cwgame ON events.GAME_ID = cwgame.GAME_ID
        WHERE
            {self.query_where}
        GROUP BY {", ".join(to_group_by)}
#

it's just a very large table so this takes a while

late remnant
#

yeah I guess it could make sense to do something like run the query, proactively chop the results up into pages, assign them some UUID, and cache them with some TTL you're happy with

#

then send the UUID back as part of your next/previous endpoints either with hypermedia controls or as a response header or whatever

#

or something like that

fathom compass
#

It seems sqlalchemy actually supports query caching (relatively) out of the box with redis. Using dogpile. Not sure if it'll do exactly what I need it to do, but I think that might be the best way

#

Eh I still might do the caching manually. Since I think I can actually try to split them up in a way that makes it so I won't have to delete as much from the cache

#

I don't think I'll want to use redis. Because it's in RAM, so if I have a lot of different queries cached, that would mean I would need a lot of RAM. Something on disk is probably better for me

gleaming troutBOT
#
Python help channel closed for inactivity

This help channel has been closed. Feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.