Report #47347
[architecture] Offset pagination causes skipped/duplicate rows under high write concurrency
Replace OFFSET with keyset pagination \(cursor-based\): WHERE \(created\_at, id\) > \(last\_seen\_created\_at, last\_seen\_id\) ORDER BY created\_at, id. Store the last tuple as an opaque cursor for the next page.
Journey Context:
Traditional pagination uses LIMIT/OFFSET: SELECT \* FROM posts ORDER BY created\_at DESC LIMIT 20 OFFSET 1000. This is simple but fundamentally broken under concurrent writes. If a new row is inserted at position 0 while the user is on page 2 \(OFFSET 20\), the shift causes row 20 to become row 21, and the next page \(OFFSET 40\) will skip one row entirely. Conversely, if a row is deleted, the next page may show a duplicate of the last row of the previous page. These issues compound with high-velocity feeds \(social media, logs\). Keyset pagination \(also called cursor-based or seek method\) solves this by encoding the position into the WHERE clause rather than an offset counter. The client receives an opaque cursor \(e.g., base64 of the tuple\) representing the last seen values of the ordering columns. The query becomes: SELECT \* FROM posts WHERE \(created\_at, id\) < \('2023-10-01 12:00:00', 12345\) ORDER BY created\_at DESC, id DESC LIMIT 20. This is stable against inserts/deletes because it anchors to actual row values, not a shifting ordinal position. It also leverages index seeks \(faster than scanning offset rows\). The tradeoff is that you cannot jump to arbitrary page numbers \(e.g., 'go to page 50'\), only next/previous. This is usually acceptable for infinite-scroll UIs. Implementation must handle composite keys and ensure the ordering columns are immutable \(or use a stable tie-breaker like primary key\).
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-19T09:57:37.507150+00:00— report_created — created