How to Paginate a Large Feature API in Python
On this page
Problem statement
Pagination is added to bound the response, and it frequently does not, because the row count is not the payload. Measured on Natural Earth's 4,596 administrative polygons, served by the app below on one machine (Example 4):
page size payload gzipped throughput
10 0.25 MB 0.08 MB 28.5 req/s
50 1.01 MB 0.34 MB 13.8 req/s
200 4.00 MB 1.32 MB 4.4 req/s
1,000 15.59 MB 5.00 MB 2.2 req/s
A "page" of a thousand features is a 16 MB response, and the server managed 2.2 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.
What you get — from 1. Set the page size from the payload, not from a round number
10: 0.25 MB
25: 0.61 MB
50: 1.01 MB
100: 2.06 MB
200: 4.00 MB
Size pages by bytes, clamp, count, then page by key
1. Set the page size from the payload, not from a round number
Fifty is not a magic number; for these features it is the size at which a page reaches a megabyte (1.01 MB), and 25 is the largest size under 0.75 MB. 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") 10: 0.25 MB
25: 0.61 MB
50: 1.01 MB
100: 2.06 MB
200: 4.00 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.
Keyset cursors and streaming as an alternative
The data used on this page
provinces.gpkg is Natural Earth's 1:10m admin-1 states and provinces: 4,596 polygons with their attributes, the same layer the other API guides use. layer is it, loaded once.
import os
import geopandas as gpd
if not os.path.exists("provinces.gpkg"):
gpd.read_file("https://naciscdn.org/naturalearth/10m/cultural/ne_10m_admin_1_states_provinces.zip") \
.to_file("provinces.gpkg")
layer = gpd.read_file("provinces.gpkg")
print(len(layer), "polygons,", len(layer.columns), "columns")4596 polygons, 122 columns
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
import duckdb
con = duckdb.connect()
con.execute("install spatial; load spatial")
con.execute("create table features as select row_number() over () as id, name, geom "
"from st_read('provinces.gpkg')")
rows, cursor = page_by_key(con, limit=50)
print(f"page 1: ids {rows[0][0]}-{rows[-1][0]}, next cursor {cursor}")
con.execute("delete from features where id = 10") # a deletion between requests
rows, cursor = page_by_key(con, limit=50, cursor=cursor)
print(f"page 2: ids {rows[0][0]}-{rows[-1][0]} — unaffected by the deletion")page 1: ids 1-50, next cursor eyJsYXN0X2lkIjogNTB9
page 2: ids 51-100 — unaffected by the deletion
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
best = choose_page_size(layer) page raw MB gzip MB
10 0.25 0.08
25 0.61 0.20
50 1.01 0.34 <- over target
100 2.06 0.71 <- over target
200 4.00 1.32 <- over target
500 8.29 2.68 <- 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.
Example 4 — serving it, and measuring the pages
import asyncio
import gzip
import threading
import time
import httpx
import uvicorn
@app.get("/features_unbounded") # the same handler without the 200 cap
def features_unbounded(request: Request, limit: int = Query(50, ge=1, le=5000), offset: int = 0):
return features(request, limit=min(limit, 5000), offset=offset, bbox=None) if limit <= MAX_LIMIT else \
Response(LAYER.iloc[offset:offset + limit].to_json(), media_type="application/geo+json")
app.add_api_route("/stream", lambda: feature_stream(LAYER), methods=["GET"])
server = uvicorn.Server(uvicorn.Config(app, port=8771, log_level="warning"))
threading.Thread(target=server.run, daemon=True).start()
while not server.started:
time.sleep(0.1)
async def throughput(url, n=40, concurrency=10):
async with httpx.AsyncClient(timeout=120) as client:
sem = asyncio.Semaphore(concurrency)
async def one():
async with sem:
return await client.get(url)
started = time.perf_counter()
responses = await asyncio.gather(*[one() for _ in range(n)])
return n / (time.perf_counter() - started), len(responses[0].content)
print(f"load average {os.getloadavg()[0]:.1f}")
print("page size payload gzipped throughput")
for n in (10, 50, 200, 1000):
rps, size = asyncio.run(throughput(f"http://127.0.0.1:8771/features_unbounded?limit={n}",
n=40 if n <= 200 else 10))
body = LAYER.iloc[:n].to_json().encode()
print(f"{n:9,} {size / 1e6:6.2f} MB {len(gzip.compress(body, 6)) / 1e6:6.2f} MB {rps:8.1f} req/s")
import shapely
vertices = shapely.get_num_coordinates(LAYER.geometry.values)
pages = [len(LAYER.iloc[i:i + 50].to_json()) for i in range(0, len(LAYER), 50)]
print(f"\nvertices per polygon: {vertices.min():,} to {vertices.max():,} ({LAYER.name.iloc[vertices.argmax()]}); "
f"50-feature pages: {min(pages) / 1e6:.2f} to {max(pages) / 1e6:.2f} MB")
too_big = httpx.get("http://127.0.0.1:8771/features?limit=10000")
print(f"\n/features?limit=10000 -> HTTP {too_big.status_code}")
lines = httpx.get("http://127.0.0.1:8771/stream", timeout=120).text.splitlines()
print(f"/stream: {len(lines):,} newline-delimited features in one response")load average 2.2
page size payload gzipped throughput
10 0.25 MB 0.08 MB 28.5 req/s
50 1.01 MB 0.34 MB 13.8 req/s
200 4.00 MB 1.32 MB 4.4 req/s
1,000 15.59 MB 5.00 MB 2.2 req/s
vertices per polygon: 4 to 38,527 (Nunavut); 50-feature pages: 0.17 to 2.79 MB
/features?limit=10000 -> HTTP 422
/stream: 4,596 newline-delimited features in one response
Bytes matter, rows do not
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. In this layer one polygon, Nunavut, has 38,527 vertices and the smallest has 4, so pages of the same 50 features range from 0.17 MB to 2.79 MB. Measured, a page of 1,000 of these polygons was 15.6 MB and dropped the server from 13.8 requests per second at 50 features to 2.2.
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.
Deep offsets, stable sorts and expensive counts
- Measure the page size on your own geometry. Fifty polygons and fifty points differ by orders of magnitude.
limitneeds 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.
numberMatchedcan 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 50-feature page from 1.01 MB to 0.34 MB on the wire.
Internal links
- Serving spatial data explained: files, features and tiles — the shape of the service
- OGC API Features explained —
numberMatchedand the paging links - How to add bounding box and attribute filters to a spatial API — reducing what needs paging
- How to stream large query results from a spatial API — the alternative to paging
- Fixing an API that returns empty or truncated GeoJSON — the client-side failure
- How to build a GeoJSON API with FastAPI — the base service
- Fixing a spatial API that is slow under load — when pages are too large
- How to read a large PostGIS table without exhausting memory — the same problem, server side
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 15.6 MB and dropped the server to 2.2 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.