7

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.

6
  • Does your database support OFFSET?
    – Ixrec
    Apr 18, 2016 at 20:59
  • @lxrec yes, in my choice I use postgres. But i think most popular RDBMS should. I guess what I really don't know sure yet is given query SELECT * FROM aduit_table where [SOME_CONDITION] will return recent data, so wouldn't offset be off on the next query?
    – CppLearner
    Apr 18, 2016 at 21:01
  • Won't that be a problem in any case, unless you plan on paginating old, outdated data? How many pages are we talking about? Apr 18, 2016 at 21:12
  • @RobertHarvey well I am certainly not writing Google but would it be to greedy :-) to look at that scale? By paging old data, well, if we keep adding more data, the next page will just repeat what we have seen on the previous page.
    – CppLearner
    Apr 19, 2016 at 0:55
  • Are rows regarded immutable once added (i.e. append-only)?
    – rwong
    Oct 16, 2016 at 6:41

1 Answer 1

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.

3
  • I think timestamp is probably the way to go, so like you said yeah maybe we still need to somehow cache a version/snapshot such that we don't start paging 10,000 records. In what situation would I see this high write rate, well, I guess in reality really none because if there is high write rate I'd not want to actually read the data. Perhaps you are right, I may have to build a snapshot and perhaps two users differ by 10 minutes would be looking at two different snapshots.
    – CppLearner
    Apr 19, 2016 at 0:58
  • So I am building a web app which inventory infrastructure. We don't create instances every minute, maybe at most a few a day. But at this scale, it would be really bad if the user click on next page and saw the last M results repeated even for an application whose data rarely change. It seems minor but bugs me because I truly believe this is not an unique problem I have.
    – CppLearner
    Apr 19, 2016 at 1:00
  • @CppLearner If you are concerned at all about scaling I wouldn't try to keep track of this on the database or server side. Since you are building a web app you could keep track of the timestamp of the newest record originally returned and use it in subsequent requests to only get records older than that record.
    – Adrian
    Dec 15, 2016 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.