using postgres server-side cursor for caching

Question

To speed page generation for pages based on large postgres collections, we cache query results in memcache. However, for immutable collections that are very large, or that are rarely accessed, I'm wondering if saving server side cursors in postgres would be a viable alternate caching strategy.

The idea is that after having served a page in the middle of a collection "next" and "prev" links are much more likely to be used than a random query somewhere else in the collection. Could I have a cursor "WITH HOLD" in the neighborhood to avoid the (seemingly unavoidable) large startup costs of the query?

I wonder about resource consumption on the server. If the collection is immutable, saving the cursor shouldn't need very many resources, but I wonder how optimized postgres is in this respect. Any links to further documentation would be appreciated.

Answer 1

You're going to run into a lot of issues.

• You 'd have to ensure the same user gets the same sql connection
• You have to create a cleanup strategy
• The cursors will be holding up vacuum operations.
• You have to convince the connection pool to not clear the cursors
• Probably other issues I have not mentioned.

In short: don't do it. How about precalculating the next/previous page in background, and storing it in memcached?

Answer 2

A good answer to this has previously been made Best way to fetch the continuous list with PostgreSQL in web

The questions are similar, essentially you store a list of PKs on the server with a pagination-token and an expiration.