Skip to content

Page deck and card lists by id with limit and after - #96

Merged
lesnik512 merged 1 commit into
mainfrom
perf/keyset-pagination
Oct 17, 2026
Merged

lesnik512 merged 1 commit into
mainfrom
perf/keyset-pagination

Conversation

@lesnik512

Copy link
Copy Markdown
Member

GET /api/decks/ and GET /api/decks/{id}/cards/ returned every row. A benchmark had the deck list going from 4 ms with 20 decks to 120 ms with 8,500.

Both endpoints now page by id (keyset):

  • Query params: limit (1 to 100, default 50) and after (the last id the client has seen, optional).
  • Response: {"items": [...], "next_after": <id> | null}. Pass next_after back as after to get the next page. It is null on the last page.
  • The repository runs WHERE id > :after ORDER BY id LIMIT :limit + 1. The extra row tells Page.from_models whether there is a next page, so no COUNT(*) is needed.

Why keyset over OFFSET: both lists were already ordered by id, and OFFSET makes Postgres read and discard every skipped row, so deep pages get slower as the table grows. Keyset keeps every page at index-scan cost on the primary key.

No new index. The deck list uses the primary key. The card list filters on deck_id and orders by id, which the (deck_id, front) unique index plus a sort covers well enough for per-deck card counts. A (deck_id, id) index can come later if decks grow large.

Breaking change: clients that relied on getting every row in one response now get the first 50 and must follow next_after. Write endpoints (POST/PUT /decks/{id}/cards/) still return the unpaged Cards collection. schemas.Decks is replaced by schemas.DecksPage.

Bad params (limit=0, limit=101, after=0, non-integer) are rejected by the framework's validation.

AGENTS.md documents the pattern next to the Collection[T] convention.

just lint and just test pass (100% coverage).

@lesnik512
lesnik512 merged commit c64450c into main Oct 17, 2026
3 checks passed
@lesnik512
lesnik512 deleted the perf/keyset-pagination branch October 17, 2026 10:44
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant