[HN Gopher] Keyset cursors, not offsets, for Postgres pagination
       ___________________________________________________________________
        
       Keyset cursors, not offsets, for Postgres pagination
        
       Author : todsacerdoti
       Score  : 17 points
       Date   : 2024-12-04 20:18 UTC (1 days ago)
        
 (HTM) web link (blog.sequinstream.com)
 (TXT) w3m dump (blog.sequinstream.com)
        
       | AlexErrant wrote:
       | > This user experience is mostly good, as users typically care
       | about domain-level filtering (commits between these time ranges)
       | versus the arbitrary traversal of pages
       | 
       | If you're using SQLite, you can use temp tables as a hack to get
       | arbitrary traversal:
       | 
       | Imagine an MS Excel-style spreadsheet that may have 10k+ rows
       | that a user may search/sort on; I virtualize it and query for
       | data in blocks of 100 rows. Being Excel-style, you can
       | arbitrarily scroll any distance down the spreadsheet. OFFSET has
       | terrible perf when skipping 15k+ rows, and users might want to
       | jump to the bottom of the spreadsheet (or anywhere, really). I
       | handle this in two steps:                   1. Return the first
       | 100 results of a query ASAP.         2. In a nonblocking manner,
       | add all the relevant ids for that query to a temp table.
       | 
       | When a user tires of the first 100 results and starts randomly
       | scrolling, I can use cursor pagination with the temp table's
       | rowid (WHERE rowid > 15000 ORDER BY rowid LIMIT 100) by taking
       | advantage of the fact that the spreadsheet's row number is now
       | the same as the temp table's rowid. Building this cache is
       | relatively expensive; it takes ~10 seconds on my devbox with ~15k
       | rows. However jumping to the bottom of that result set takes
       | ~200ms, so I'm happy with it. (Note I'm on a fork of wa-sqlite,
       | i.e. it's running in the browser - "real" sqlite is much, much
       | faster.)
        
       ___________________________________________________________________
       (page generated 2024-12-05 23:00 UTC)