So there are two ways that I know of
- a snapshot in a database (which I’ve never used with prostgres, but used it with sqlite in an ios app, https://www.sqlite.org/c3ref/snapshot.html, but I’m pretty sure postgres has something similar https://www.postgresql.org/docs/current/static/sql-set-transaction.html, maybe someone more knowledgeable would be able to answer this) for which you’d need to create a read committed snapshot for the duration of your paginated queries for a particular user probably via ecto transaction + stream (https://hexdocs.pm/ecto/Ecto.Repo.html#c:stream/2) and source your results from there (maybe in a genserver which would have the stream as its state … I don’t know, never done that)
# this probably won't work
# in the genserver
def init(your_paginated_query) do
{:ok, Repo.stream(your_paginated_query)}
end
def handle_call({:page_after, cursor}, _from, stream) do
Repo.transaction fn ->
# do something with the `stream`
end
end
Or maybe just create a snapshot with Repo.query at the beginning of the user session and then release it in the end, note that creating too many transactions on a database might significantly hurt its performance due to locks. Not sure if it applies here, though. Or you might run out of the checked out ecto(postgrex) connections/processes to the database if you use long running streams …
Also, I’ve found Using Ecto to run a long-running multi-process transaction, might be somewhat relevant, although I think in this case what’s needed is a long-running single process transaction.
- a “snapshot” in your application (I used this one for a paginated chatbot’s search feature) which would load some arbitrary number of posts into memory (can be genserver per http session) and serve “pagination” requests from there. For the chatbot I loaded about 10 pages (look at your users’ access patterns, you might need to load more) of content into the genserver which had a data structure like
@type page :: [SearchResult.t]
@type paged_snapshot :: %{
current_page: page,
pages_before: [page],
pages_after: [page]
}
@spec next(paged_snapshot) :: paged_snapshot
defp next(%{pages_after: []} = snapshot), do: snapshot # or request a new snapshot
defp next(%{pages_after: [new_current_page | pages_after], pages_before: pages_before, current_page: old_current_page} = snapshot) do
%{snapshot | pages_after: pages_after, pages_before: [old_current_page | pages_before], current_page: new_current_page}
end
# similar for prev(snapshot)
as its state and was alive only for the duration of the user’s interaction with it (each interaction prolonged its “life” up to a certain limit). You can also create these snapshots only around the time when the “total_post_views” is about to change.
Another way would be to create materialized views for the posts sorted by total_post_views. And keep two of these views in the database when total_post_views is being updated.
