You bring up a valid point about the limitations of cursor-based pagination
with sorting. Cursor pagination works efficiently when sorting by a unique or
sequential field (like `id`, `created_at`, or `updated_at`), but it breaks down
when we try to sort by non-unique columns (e.g., `name`, `status`).
To address this, there are a couple of potential solutions:
1. **Combined Sorting**: We could continue using cursor-based pagination but
with a fallback to a secondary unique column like `id`. This way, sorting by
`name` or `status` would still function but would maintain stable pagination by
also ordering by `id` to ensure uniqueness:
```sql
ORDER BY name ASC, id ASC
```
This approach would retain the benefits of cursor-based pagination while
allowing some flexibility with sorting.
2. **Offset-based Pagination**: If we want to fully support sorting by
arbitrary columns, we could switch to offset-based pagination when sorting by
non-unique columns. This is less efficient on larger datasets but avoids the
problem of ambiguous cursor positions.
3. **Hybrid Approach**: Implement cursor-based pagination for sortable fields
that are unique (like `id` or timestamps), and fallback to offset-based
pagination for others.
Before proceeding, I’d like to get some thoughts on which approach fits best
with our performance requirements and use cases. If we'd prefer sticking with
cursor-based pagination, I can explore implementing the combined sorting
method, as it strikes a balance between efficiency and flexibility.
--
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/5259#issuecomment-2411376926
You are receiving this because you are subscribed to this thread.
Message ID:
<openstreetmap/openstreetmap-website/issues/5259/2411376...@github.com>
_______________________________________________
rails-dev mailing list
rails-dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/rails-dev
