Implement pagination which memorizes where we left off?

Question

Let's pick a relation database table (say an audit table) with high write rate. We can display 100 records at a time. But as a user don't want to see some the records on page 1 on page 2 when a couple more new records are added.

I thought about caching, but we can only keep so much on a cache server like memcache or redis and invalidating caching is really difficult (imagine user opening a new tab now we have to deal with two sessions): what is the standard to implement pagination that can resume where we left off? Hold on to the cursor would eventually used up all connections in the pool.

A navie LIMIT or range won't work because of high write rate.

Answer 1

You're going to have to cache something somewhere, whether it is using the db to do it with a cursor, or doing it external to the db.

However, if you have full control of the db and its schema, you could do some caching of rows and ages of rows within the tables themselves.

You could incorporate a timestamp or version number column into your table that would help you get a reasonable snapshot of the rows without a long lived transaction (i.e. cursor) in the face of constant updates.

So, you could use separate queries for each page=N, each supplying the same timestamp or version number for the system to use in conjunction in the where clause, such that only information valid from that one point time is returned regardless of what page number is being requested and of inserted new rows.

If rows are also being deleted then you'd have to leave them in place to keep the snapshot, and merely mark them as deleted as of when/what version; a deletion comparison would then also be used in conjunction in the where clause. (You could create a view to be used for all other purposes that omits deleted rows.)

With deletions only being marked and not actually performed, you'd have a source of "memory leak", but this is could well be better than running out of cursors. (You may want some form of "GC" to regularly clean out old deletions, assuming you don't want to support going back in time.)

If rows are also being modified, you're getting in pretty deep, but it could be handled by deleting (i.e. marking deleted) the original and adding a new row as replacement. (Your primary key will become an issue that needs to be addressed somehow as you'll effectively have duplicates).

Some of the issues might be addressed by having an alternate or duplicate table that has the timestamp/version# column, and relaxing some constraints on that table. The long-lived queries would use that alternate table; the transactional updates would use the original table, and there'd be a mechanism to feed one from the other.

Basically, this would be a manual / poorman's version of snapshot isolation. You might be better off using cursors... and just terminating the too long-lived ones.