Recently I have been reworking some code to instead of accessing the database directly, to use an API through a ‘server’ application instead. The main idea behind this was to have a single source of truth. Getting rid of any syncing problems in case the database would change as well as preventing duplicated code.
However, the amount of data you want to transfer from server to client is a lot less compared to having an application that can access the database directly. Instead you want to have a pagination concept where you request a certain amount of rows and request subsequent results if there are. As soon as you think about something like that the Cursor concept comes to mind. Needless to say, with the power of hindsight, using that was not a good idea.
While the concept of cursors offers everything you need, such as keeping track of where you currently are located it comes with a couple of problems as well. There are problems when you extend it to a client-server application. First of all it requires transactions (although you can mark it to survive beyond the span of the transaction), leading to long-living transactions that can block other things you may want to do.
Another big issue is all of the housekeeping the server has to do. It needs to keep track of all hanging cursors and binds them to a certain client. There also needs to be some way a client can indicate it is no longer interested in any more results such that the server can close the cursor. But even then if a client crashes, the server needs some sort of clean-up procedure to close ‘hanging’ cursors.
Another big downside of this approach is scale-ability, since you are binding your client to the server, there is no way you can extend capacity by adding a load-balancer in front of the servers. Unfortunately it was only after I had done all the work, and faced all of the problems and worked around them that I discovered a blog post that discussed this issue.
So how would I do it in the future? I would simply create simple queries that can be executed on-demand. Postgresql offers the OFFSET option to start from a specific point in the results. Don’t get me wrong, I still see the value of using Cursors, but just not in a client-server application. But if you have a dedicated application that accesses the database directly there are advantages to be gained. The gain lies in the parallel fetching from the database and processing results compared to first fetching all the results and processing it afterwards. By doing this you can have a more responsive application without any of the problems with the server-client situation as you still have full control.