Switch from Offset to Cursor Pagination
Use a cursor based on the last item's sort key so page 2 does not skip or duplicate items when new data arrives between requests.
Task
Implement SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; so the service degrades gracefully under failure instead of cascading timeouts to callers.
When to use this
A downstream service slows down and without SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; the upstream exhausts its thread pool in 12 seconds.
Prerequisites
- A service with inter-service calls or external dependencies
- A retry or circuit breaker library available
Steps
1. Configure SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; in the project configuration file with the recommended defaults.
SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20;
Expected output: The configuration is accepted and SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; runs without errors on the first invocation.
2. Run the check locally to verify SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; produces the expected result before pushing.
pnpm test
Expected output: Local run matches CI behavior; no surprises after push.
3. Add the gate to the CI pipeline so SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; blocks merge on failure.
- name: Run add pagination cursor offset
run: pnpm test
Expected output: Pull requests with failing SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; are blocked from merging.
Troubleshooting
Problem
The check passes locally but fails in CI
Solution
Pin dependency versions and the runtime to match CI exactly; reproduce with the same Docker image or act.
Problem
Configuration is rejected with a schema error
Solution
Check the tool version matches the config schema; older versions may not support newer fields.
Limitations
- The gate only runs on events that trigger the workflow; locally pushed commits bypass it.
- Configuration drift is possible if someone edits settings outside version control.
Isolate failure
Start by measuring the problem. Use a cursor based on the last item’s sort key so page 2 does not skip or duplicate items when new data arrives between requests. The most common mistake is adding the gate without first confirming it catches the issue you care about. Run SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; against the current codebase and inspect the output before wiring it into CI.
SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20;Verify graceful degradation
Once the gate is in place, make failures actionable. A failing SELECT * FROM items WHERE (created_at, id) < ($cursor_ts, $cursor_id) ORDER BY created_at DESC, id DESC LIMIT 20; should point to the exact file and line, not a generic error. Pin the tool version in the lockfile so a upstream release does not silently change behavior. Review the gate output in every pull request just like a test result.