FastAPI Backend Development Bootcamp · Ders

Büyük Ölçekte İmleç ve Ofset Sayfalandırması

Büyük ve sık değişen veri kümelerinde kararlı, yüksek performanslı listeleme için anahtar kümesi/İmleç sayfalandırması uygulayın.

Büyük Ölçekte İmleç ve Ofset Sayfalandırması, CoddyKit'te ücretsiz bir FastAPI Backend Development Bootcamp dersidir. Bu, 4 dersinin 2. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, FastAPI Backend Development Bootcamp öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. FastAPI Backend Development Bootcamp kursu toplamda 4 dersten oluşur.

Bu dersin bazı bölümleri henüz çevrilmemiş olup İngilizce olarak gösterilmektedir.

Why Pagination Strategy Matters

When a list endpoint returns thousands or millions of rows, you must page the results. The two dominant strategies are offset pagination (LIMIT/OFFSET or ?page=3) and cursor/keyset pagination (?after=<token>).

Offset is simple and supports jumping to arbitrary pages.
Cursor is stable and fast on large, frequently changing datasets.

This lesson shows why offset breaks down at scale and how to implement keyset pagination correctly in a FastAPI service.

How Offset Pagination Works

Offset pagination tells the database to skip N rows and return the next page-sized chunk. A request for page 3 with page size 20 becomes OFFSET 40 LIMIT 20.

The endpoint is trivial to write and lets clients jump directly to any page. Here is a minimal in-memory simulation of how offset slicing behaves.

def offset_page(rows, page, size):
    start = (page - 1) * size
    end = start + size
    return rows[start:end]

items = [f"item-{i}" for i in range(1, 101)]
print("page 1:", offset_page(items, 1, 5))
print("page 3:", offset_page(items, 3, 5))
print("page 20:", offset_page(items, 20, 5))

The Hidden Cost of OFFSET

Databases do not magically jump to row 1,000,000. To satisfy OFFSET 1000000 LIMIT 20, the engine must scan and discard the first one million rows before returning 20. The deeper the page, the slower the query.

Page 1 is instant; page 50,000 can take seconds.
Work grows linearly with the offset (O(offset)).
Indexes help ordering but cannot skip the discarded rows for free.

This makes deep offset pagination a common cause of slow list endpoints and database load spikes.

The Drift Problem

Offset pages are computed against a moving target. If rows are inserted or deleted between requests, the offsets shift underneath the client.

A new row is inserted at the top while the user reads page 1.
On page 2, the last item of page 1 reappears (duplicate).
Or a deletion causes an item to be skipped entirely.

On busy feeds and dashboards this produces missing and repeated items — unacceptable for infinite scroll.

rows = list(range(1, 11))  # ids 1..10, newest last
size = 3

page1 = rows[0:3]            # [1, 2, 3]
rows.insert(0, 0)           # a new row 0 arrives at the top
page2 = rows[3:6]           # shifted by the insert
print("page1:", page1)
print("page2:", page2)
print("id 3 repeated?", 3 in page2)

Enter Keyset (Cursor) Pagination

Keyset pagination does not count rows to skip. Instead it remembers the last seen key and asks for rows strictly after it: WHERE id < :last_id ORDER BY id DESC LIMIT :size.

The query uses an index seek, not a scan — constant time regardless of depth.
Inserts and deletes before the cursor do not shift the window, so no duplicates or skips.

The trade-off: you can only move next/previous relative to a cursor — you cannot jump to "page 4,217".

Keyset Logic in Plain Python

Before touching SQL, it helps to see the keyset rule in isolation. Given a sorted list and the last id from the previous page, return the next chunk of items whose id is below that cursor.

Notice the work depends only on the page size, not on how deep we are.

def keyset_page(rows, after_id, size):
    # rows sorted by id DESC; return items strictly after the cursor
    result = [r for r in rows if r["id"] < after_id]
    return result[:size]

rows = [{"id": i, "name": f"u{i}"} for i in range(10, 0, -1)]
first = rows[:3]
print("first page:", [r["id"] for r in first])
cursor = first[-1]["id"]
next_page = keyset_page(rows, cursor, 3)
print("next page:", [r["id"] for r in next_page])

Designing a Stable Sort Key

Keyset pagination needs a total ordering — the sort key must be unique. Paginating by created_at alone is unsafe because many rows can share the same timestamp; rows on the boundary may be lost or repeated.

Use a strictly increasing key like the primary key id, or
Use a composite key such as (created_at, id) so ties are broken by the unique id.

Always include the unique column as the final tiebreaker, and make sure a matching index exists on the same column order.

Encoding the Cursor as an Opaque Token

Never expose raw internal keys directly. Wrap them in an opaque, URL-safe token (typically base64). Clients treat it as a black box and simply echo it back, which lets you change the underlying key format later without breaking the contract.

For composite keys, encode all parts together inside the token.

import base64, json

def encode_cursor(created_at, last_id):
    raw = json.dumps({"ts": created_at, "id": last_id}).encode()
    return base64.urlsafe_b64encode(raw).decode()

def decode_cursor(token):
    raw = base64.urlsafe_b64decode(token.encode())
    return json.loads(raw)

token = encode_cursor("2026-06-10T12:00:00Z", 8842)
print("cursor:", token)
print("decoded:", decode_cursor(token))

A FastAPI Cursor Endpoint

Now wire it into FastAPI. The endpoint accepts an optional cursor and a limit, decodes the cursor to a last_id, and queries one page. A common trick: fetch limit + 1 rows to detect whether a next page exists.

This is framework code that depends on a database session, so it is illustrative rather than self-contained.

from fastapi import FastAPI, Query, Depends
from sqlalchemy import select

app = FastAPI()

@app.get("/users")
async def list_users(
    cursor: str | None = Query(default=None),
    limit: int = Query(default=20, le=100),
    db=Depends(get_session),
):
    last_id = decode_cursor(cursor)["id"] if cursor else None
    stmt = select(User).order_by(User.id.desc()).limit(limit + 1)
    if last_id is not None:
        stmt = stmt.where(User.id < last_id)
    rows = (await db.execute(stmt)).scalars().all()
    has_more = len(rows) > limit
    rows = rows[:limit]
    next_cursor = encode_cursor(rows[-1].id) if has_more else None
    return {"items": rows, "next_cursor": next_cursor}

Shaping the Response Contract

A clean cursor API returns the page plus navigation metadata — never a total page count (computing it would reintroduce a full scan). A typical envelope:

items: the current page of records.
next_cursor: token for the following page, or null when the list is exhausted.
optionally has_more: a boolean convenience flag.

Clients loop by passing the returned next_cursor back as ?cursor= until it is null.

def build_page(rows, limit, get_id):
    has_more = len(rows) > limit
    page = rows[:limit]
    next_cursor = get_id(page[-1]) if has_more and page else None
    return {"items": page, "next_cursor": next_cursor, "has_more": has_more}

fetched = list(range(20, 9, -1))  # asked for 10, got 11
result = build_page(fetched, limit=10, get_id=lambda x: x)
print("items:", result["items"])
print("next_cursor:", result["next_cursor"])
print("has_more:", result["has_more"])

Composite Keyset and Indexing in SQL

For sorting by recency, compare the tuple (created_at, id) so ties never break the contract. In Postgres you can use row-value comparison:

WHERE (created_at, id) < (:ts, :id) ORDER BY created_at DESC, id DESC LIMIT :n
Back it with CREATE INDEX ON items (created_at DESC, id DESC).

The index lets the planner seek directly to the cursor position, giving stable performance whether the user is on page 1 or page 10,000.

Quick Check

Test your understanding of when to choose each strategy.

Recap: Cursor vs Offset at Scale

You learned to choose and build the right pagination strategy:

Offset is simple and supports page jumping, but is O(offset) slow on deep pages and drifts (duplicates/skips) when data changes.
Keyset/cursor seeks by the last-seen key, giving constant-time deep pages and stable results on busy datasets.
Use a unique total ordering — a primary key or composite (created_at, id) — and back it with a matching index.
Expose an opaque cursor token and return { items, next_cursor } instead of page numbers and totals.

Rule of thumb: pick offset for small, admin-style tables that need page jumping; pick cursor for large, frequently changing, infinitely scrolled lists.

Sıkça Sorulan Sorular

“Büyük Ölçekte İmleç ve Ofset Sayfalandırması” dersi ücretsiz mi?

Evet — “Büyük Ölçekte İmleç ve Ofset Sayfalandırması” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve FastAPI Backend Development Bootcamp kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. FastAPI Backend Development Bootcamp kursu toplamda 4 dersten oluşur.

“Büyük Ölçekte İmleç ve Ofset Sayfalandırması” dersinde ne öğreneceğim?

Büyük ve sık değişen veri kümelerinde kararlı, yüksek performanslı listeleme için anahtar kümesi/İmleç sayfalandırması uygulayın. FastAPI Backend Development Bootcamp ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.

FastAPI Backend Development Bootcamp öğrenmeye başlamak için deneyim gerekli mi?

Önceden deneyim gerekmez. CoddyKit'te FastAPI Backend Development Bootcamp, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 4 dersinin 2. dersidir.

“Büyük Ölçekte İmleç ve Ofset Sayfalandırması” dersi ne kadar sürer?

Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.

Bu FastAPI Backend Development Bootcamp dersinde kod yazıp çalıştırabilir miyim?

Evet. Her FastAPI Backend Development Bootcamp dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.

Bu kursun tüm dersleri

URL, Üst Bilgi ve Ortam Türü Sürümleme
Büyük Ölçekte İmleç ve Ofset Sayfalandırması
Dinamik Filtreleme ve Sıralama Parametreleri
Kararlı Yanıt Zarfları Tasarlama

← FastAPI Backend Development Bootcamp Sayfasına Dön