Hello!
I would like to use "pagination" in my intranet client app.
My aims:
- allow user to open and scroll large lists from query (without narrowing the query)
- at the same time minimize time until the showing of large lists to the user
- and at the same time minimize backend-frontend traffic as possible (not loading all at once)
User opens potentially large list of some documents and
visually scrolls through the list opening some another related lists (master-detail queries).
All those lists stays opened and should be scrollable until user closes them.
Number of opened lists per each session is about <= 20.
Basically there is two possibilities:
1. Client app explicitly open cursor and fetch records on demand.
Cursors should remain open until the user closes corresponding list of documents -
from minutes to hours.
2. Client app use PQsendQuery with PQsetSingleRowMode and PQgetResult insteed of PQExec.
This variant forces to use one connection to backend per each opened list (each PQsendQuery).
So insteed of one connection it would be dozens of.
And questions are:
1. How much and what resources (cpu/mem/processes) "eats" each cursor/connection on backend?
2. From what resource usage of cursor/connection depends?
3. What would be more efficient in terms of resource usage and maybe speed?