Using the Seeking (or keyset pagination) method for data pagination means that you filter out the previous records using the WHERE clause and a reference record. Just as other pagination systems, an ordered list is required.
With the reference record, you can easily decide which records from the ordered list came before or after that reference element.
For the best performance, a compound index should be applied on the ORDER BY fields and WHERE fields.
Example index
# For normal queries # User_id, locale and app for frequent where clauses. # Order matter, if we don't use the :locale for example, we can still use the index for the user_id and app. # accessed_on and id for ordering by. create index(:sevenmind_history_records, [:user_id, :locale, :accessed_on, :id]) # For queries where we filter on completion # Same as the key before, except we use completed as an extra filter criteria create index(:sevenmind_history_records, [:user_id, :locale, :completed, :accessed_on, :id]) ## Recreate indices for seven_sleep create index(:sevensleep_history_records, [:user_id, :accessed_on, :id]) create index(:sevensleep_history_records, [:id])
Downsides
As other methods, this too has downsides.
Some downsides
You have no way to go to an arbitrary page without a reference record.
The reference record is everything you use to sort, which could become complicated