⚙️ How-To APIs & tile services Performance & scale Serving Spatial Data · step 4 of 20 Updated

How to Paginate a Large Feature API in Python

Problem statement

Pagination is added to bound the response, and it frequently does not, because the row count is not the payload. Measured on a real dataset of administrative polygons:

page size    payload      gzipped     throughput
       10     0.22 MB     0.07 MB
       50     0.86 MB     0.30 MB      32.5 req/s
      200     3.37 MB     1.17 MB
    1,000    12.43 MB     4.31 MB       2.3 req/s

A "page" of a thousand features is a 12 MB response, and the server managed 2.3 of them per second. The pagination is working exactly as designed and the API is unusable.

There is a second problem that only appears in production: offset paging over changing data skips and repeats features, silently, and the client has no way to notice.

Quick answer

Small pages, a hard maximum, and links rather than arithmetic:

from fastapi import FastAPI, Query, Request, Response
import geopandas as gpd
import json

app = FastAPI()
LAYER = gpd.read_file("provinces.gpkg")
MAX_LIMIT, DEFAULT_LIMIT = 200, 50


@app.get("/features")
def features(request: Request, limit: int = Query(DEFAULT_LIMIT, ge=1, le=MAX_LIMIT),
             offset: int = Query(0, ge=0), bbox: str | None = None):
    subset = LAYER
    if bbox:
        x0, y0, x1, y1 = (float(v) for v in bbox.split(","))
        subset = subset.cx[x0:x1, y0:y1]

    matched = len(subset)
    page = subset.iloc[offset:offset + limit]
    body = json.loads(page.to_json())
    body["numberMatched"] = matched
    body["numberReturned"] = len(page)

    base = str(request.url.remove_query_params(["offset"]))
    joiner = "&" if "?" in base else "?"
    body["links"] = [{"href": str(request.url), "rel": "self"}]
    if offset + limit < matched:
        body["links"].append(
            {"href": f"{base}{joiner}offset={offset + limit}", "rel": "next"})

    return Response(json.dumps(body), media_type="application/geo+json")

numberMatched is what lets a client decide to narrow the filter instead of paging four hundred times.

Bar chart of payload size for six page sizes from 10 to 1,000 features.
For points the same target allows thousands. Measure your own geometry.

Step-by-step solution

1. Set the page size from the payload, not from a round number

Fifty is not a magic number; it is the size at which a page of these features is under a megabyte. Measure your own:

for n in (10, 25, 50, 100, 200):
    page = layer.iloc[:n].to_json().encode()
    print(f"{n:5}: {len(page) / 1e6:5.2f} MB")

A target of about 0.5–1 MB per page keeps responses fast and browsers responsive. For large polygons that is tens of features; for points it can be thousands.

2. Enforce a maximum, and clamp rather than error 

limit: int = Query(50, ge=1, le=200)

FastAPI will return a 422 for limit=10000, which is honest. Some services prefer to clamp silently to the maximum; either is defensible, and having no maximum is not.

3. Return numberMatched and numberReturned

The client needs to know how much it selected, before deciding to page:

{"type": "FeatureCollection", "features": [...],
 "numberMatched": 4596, "numberReturned": 50}

Counting the matched features costs a len() on a filtered frame, or a count(*) in a database — cheap relative to serialising the page. On a very large table it can be worth an estimate instead, clearly labelled.

4. Page with links, not with client arithmetic

A rel="next" link means the client never constructs a URL, so the service can change from offset paging to cursor paging without breaking anybody. That is the same reasoning as in OGC API Features, and it costs three lines.

5. Understand what offset paging does to changing data

Offset paging asks for "rows 100 to 150 of the current result". If a feature is inserted before row 100 between two requests, one feature shifts across the page boundary and is never returned. If one is deleted, a feature is returned twice.

Neither is visible to the client. On a read-only snapshot it does not matter; on live data it silently corrupts any client that concatenates pages.

6. Use keyset pagination when the data changes

Instead of "skip 100 rows", say "everything after this key":

select * from features
where id > :last_id
order by id
limit :limit;

The client passes back the last id it saw. Inserts and deletes elsewhere in the table cannot shift the window, and the query does not get slower as the offset grows — a database still has to scan and discard offset rows, so deep offset paging degrades while keyset paging does not.

The cost is that clients cannot jump to page 400, which spatial clients almost never want to do.

Two panels showing a feature skipped when a row is inserted between paged requests.
Keyset pagination asks for rows after a value, which inserts elsewhere cannot move.

Code examples

Example 1 — keyset pagination with an opaque cursor 

import base64
import json
from fastapi import HTTPException


def encode_cursor(payload: dict) -> str:
    return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode()


def decode_cursor(cursor: str) -> dict:
    try:
        return json.loads(base64.urlsafe_b64decode(cursor.encode()))
    except Exception:
        raise HTTPException(400, "invalid cursor")


def page_by_key(con, limit=50, cursor=None, bbox=None):
    """Stable under inserts and deletes, and does not slow down as you page."""
    clauses, params = [], []
    if bbox:
        clauses.append("st_intersects(geom, st_makeenvelope(?, ?, ?, ?))")
        params += list(bbox)
    if cursor:
        clauses.append("id > ?")
        params.append(decode_cursor(cursor)["last_id"])

    where = ("where " + " and ".join(clauses)) if clauses else ""
    rows = con.execute(
        f"select id, name, st_asgeojson(geom) as geometry from features "
        f"{where} order by id limit ?", params + [limit]).fetchall()

    next_cursor = encode_cursor({"last_id": rows[-1][0]}) if len(rows) == limit else None
    return rows, next_cursor

The cursor is opaque on purpose: clients cannot construct one, so the service can change what is inside it — a compound key, a timestamp, a sort position — without a breaking change.

Example 2 — streaming instead of paging 

from fastapi.responses import StreamingResponse
import json


def feature_stream(gdf, chunk=500):
    """One response, constant memory on both ends: newline-delimited GeoJSON."""
    def generate():
        for start in range(0, len(gdf), chunk):
            for feature in json.loads(gdf.iloc[start:start + chunk].to_json())["features"]:
                yield json.dumps(feature) + "\n"

    return StreamingResponse(generate(),
                             media_type="application/geo+json-seq")

For a client that wants everything, streaming is better than paging: one connection, no offset arithmetic, and the server holds one chunk at a time rather than the whole result. GeoJSON Text Sequences (application/geo+json-seq) is the standard media type for it.

Example 3 — measuring the page size that suits your data 

import gzip


def choose_page_size(gdf, target_mb=0.75, candidates=(10, 25, 50, 100, 200, 500)):
    """Pick the largest page that stays under the target payload."""
    print(f"{'page':>6} {'raw MB':>8} {'gzip MB':>9}")
    best = candidates[0]
    for n in candidates:
        body = gdf.iloc[:n].to_json().encode()
        raw, gz = len(body) / 1e6, len(gzip.compress(body, 6)) / 1e6
        marker = ""
        if raw <= target_mb:
            best = n
        else:
            marker = "  <- over target"
        print(f"{n:6} {raw:8.2f} {gz:9.2f}{marker}")
    print(f"\nsuggested page size: {best}")
    return best
  page   raw MB   gzip MB
    10     0.22      0.07
    25     0.53      0.18
    50     0.86      0.30  <- over target
   100     1.75      0.63  <- over target
   200     3.37      1.17  <- over target
   500     6.71      2.32  <- over target

suggested page size: 25

For these polygons, twenty-five features is a page. For a point layer the same target allows thousands — which is exactly why the number should be measured rather than copied.

Explanation

Why the payload matters and the row count does not

A client's experience is governed by bytes: transfer time, parse time, browser memory. The row count is a proxy for bytes that is accurate only when features are uniform.

Spatial features are famously not uniform. One country polygon can be larger than ten thousand points, so a fixed page size produces responses varying by three orders of magnitude. Measured, a page of 1,000 of these polygons was 12.4 MB and dropped the server from 32.5 to 2.3 requests per second.

Why offset paging drifts

OFFSET 100 LIMIT 50 is evaluated fresh on each request against the current data. Between two requests, an insert before the window shifts every subsequent row down by one, and the row that was at position 150 is now at 151 — so the client's next page starts after it and it is never returned.

The client sees a plausible sequence of pages that is missing a feature. Nothing raises, and the total count will not necessarily reveal it.

Why keyset pagination fixes it and what it costs

A keyset query asks for rows after a value, not after a position. Inserts and deletes elsewhere cannot move the boundary, because the boundary is a value in the data.

It also stays fast: a database serving OFFSET 100000 still reads and discards a hundred thousand rows, while a keyset query seeks straight to the key. The cost is that random page access is impossible — which for a spatial API that pages through a filtered result is not a real limitation.

Why streaming is frequently the better answer

Paging exists so that neither side has to hold the whole result. A stream achieves the same thing with one request, no offset arithmetic, no drift and no page-boundary bugs.

The reason to page instead is that clients want to stop early, or to show a progress bar, or to retry a failed chunk — all real requirements. But for "give me everything matching this filter", a newline-delimited GeoJSON stream is simpler and faster than four hundred paged requests.

Two panels contrasting pagination with streaming for large result sets.
The client can still stop early in a stream — it simply stops reading.

Edge cases or notes

  • Measure the page size on your own geometry. Fifty polygons and fifty points differ by orders of magnitude.
  • limit needs a documented maximum. Without one the maximum is the dataset.
  • Deep offsets get slower in a database; keyset pagination does not.
  • A stable sort is required for any paging to be correct — add a tiebreaker on the id.
  • numberMatched can be expensive on very large tables; an estimate, clearly labelled, is acceptable.
  • Opaque cursors let you change the paging strategy without breaking clients.
  • GeoJSON Text Sequences (application/geo+json-seq) is the standard streaming format.
  • Gzip everything — it took a measured page from 857 kB to 299 kB on the wire.

FAQ

What page size should a feature API use?

Whatever keeps the payload under about a megabyte. Measured on administrative polygons that was 25–50 features; for points it can be thousands.

Why is my paginated API still slow?

Because the page size bounds rows, not bytes. A page of 1,000 polygons was 12.4 MB and dropped the server to 2.3 requests per second.

What is wrong with offset pagination?

On changing data it silently skips and repeats features, because it asks for a position rather than a value. It also gets slower as the offset grows.

What is keyset pagination?

Paging by "everything after this key" rather than "skip this many rows". It is stable under inserts and deletes and does not degrade at deep offsets.

Should the cursor be readable?

No — make it opaque. Then the service can change what it encodes without a breaking change, and clients cannot construct invalid ones.

When should I stream instead of page?

When the client wants everything. One request, constant memory on both sides, no page-boundary bugs — and application/geo+json-seq is the standard media type for it.