How to Stream Large Query Results from a Spatial API

Problem statement

A client wants everything matching a filter. The two usual answers both have a ceiling:

• One large response. The server builds the whole body in memory, then sends it. Measured on Natural Earth's 4,596 state and province polygons, the whole collection was 51.40 MB and the server process peaked at 732 MB building it (Example 4). Ten clients asking at once is a memory problem.
• Pagination. Hundreds of round trips for a large result, offset drift on changing data, and page-boundary bugs.

Streaming is the third answer: one request, one response, and neither side has to hold the whole result. The server writes features as it produces them and the client parses them as they arrive. Measured on the same request, the streaming server peaked at 386 MB instead of 732 — what remains is the Python process, DuckDB and one batch of large polygons, and it no longer grows with the size of the result.

Quick answer

Newline-delimited GeoJSON, generated lazily:

import json
from fastapi import FastAPI
from fastapi.responses import StreamingResponse

app = FastAPI()


@app.get("/features/stream")
def stream(bbox: str | None = None, chunk: int = 500):
    subset = LAYER
    if bbox:
        x0, y0, x1, y1 = (float(v) for v in bbox.split(","))
        subset = subset.cx[x0:x1, y0:y1]

    def generate():
        for start in range(0, len(subset), chunk):
            page = json.loads(subset.iloc[start:start + chunk].to_json())
            for feature in page["features"]:
                yield json.dumps(feature, separators=(",", ":")) + "\n"

    return StreamingResponse(generate(),
                             media_type="application/geo+json-seq",
                             headers={"X-Total-Count": str(len(subset))})

from fastapi.testclient import TestClient

with TestClient(app).stream("GET", "/features/stream", params={"bbox": "-10,49,2,61"}) as response:
    lines = [line for line in response.iter_lines() if line]
print(response.headers["content-type"], "| X-Total-Count", response.headers["x-total-count"],
      "|", len(lines), "lines; first:", json.loads(lines[0])["properties"]["name"])

application/geo+json-seq | X-Total-Count 278 | 278 lines; first: Donegal

application/geo+json-seq — GeoJSON Text Sequences — is the standard media type for exactly this. Each line is a complete Feature, so a client can parse and discard as it goes.

Pick a streaming format, then stay lazy to the source

1. Choose the streaming format

Three that work, with different trade-offs:

The first is the right default: one line, one feature, no framing to get wrong, and a client that stops early simply closes the connection.

2. Generate lazily, all the way down

A generator that builds the entire result before yielding its first chunk has streamed nothing. The laziness has to reach the data source:

def generate():
    for batch in con.execute(sql).fetch_record_batch(rows_per_batch=1000):
        for row in batch.to_pylist():
            yield json.dumps(to_feature(row)) + "\n"

Database cursors, DuckDB record batches and file readers all support this. .df() and .fetchall() do not — they materialise. Nor does DuckDB's fetch_arrow_table(n), despite the argument: n is the internal batch size, and the call returns the whole result as one table.

3. Stream a FeatureCollection when the client needs one

Some clients only understand a complete FeatureCollection. It can still be streamed, by writing the envelope by hand:

def generate_collection(features, chunk=500):
    yield '{"type":"FeatureCollection","features":['
    first = True
    for feature in features:
        yield ("" if first else ",") + json.dumps(feature, separators=(",", ":"))
        first = False
    yield "]}"

collection = "".join(generate_collection(json.loads(line) for line in lines))
print(len(collection), "characters;", len(json.loads(collection)["features"]), "features parse as one document")

1238197 characters; 278 features parse as one document

The client still has to buffer the whole thing to parse it, so this saves the server's memory and not the client's. That is often the constraint that matters.

4. Do not set Content-Length you do not know

A streamed response uses chunked transfer encoding precisely because the length is unknown in advance. Computing it would require generating the body first, which defeats the purpose.

Send the row count in a custom header instead, which is cheap and lets the client show progress:

headers={"X-Total-Count": str(matched)}

5. Handle client disconnects

A client that stops reading — closed a tab, hit its own timeout — leaves the generator suspended. Without a check, the server keeps querying and serialising for nobody.

from starlette.requests import Request


async def generate(request: Request):
    for feature in feature_source():
        if await request.is_disconnected():
            break
        yield json.dumps(feature) + "\n"

6. Compress the stream

Gzip works on a stream, and text features compress heavily. Measured on the full province stream, 51.40 MB became 17.79 MB — a factor of 2.9 — and streaming does not change that ratio (Example 4).

FastAPI's GZipMiddleware compresses streamed responses; the client sees a compressed chunked stream and decompresses as it goes.

Streaming from a database cursor to a client

The data used on this page

The features are Natural Earth's 1:10m admin-1 states and provinces — 4,596 polygons with detailed coastlines, which makes them heavy as GeoJSON — loaded into a DuckDB file as a features table with an id, the province and country names, and the geometry. The quick answer serves the same layer from a GeoDataFrame.

import io
import os
import zipfile

import duckdb
import geopandas as gpd
import requests

if not os.path.exists("provinces.shp"):
    archive = requests.get("https://naciscdn.org/naturalearth/10m/cultural/"
                           "ne_10m_admin_1_states_provinces.zip", timeout=300)
    with zipfile.ZipFile(io.BytesIO(archive.content)) as z:
        for ext in (".shp", ".shx", ".dbf", ".prj", ".cpg"):
            open(f"provinces{ext}", "wb").write(z.read(f"ne_10m_admin_1_states_provinces{ext}"))
if not os.path.exists("data.duckdb"):
    build = duckdb.connect("data.duckdb")
    build.execute("install spatial; load spatial;")
    build.execute("""create table features as
        select row_number() over () as id, name, admin, geom from st_read('provinces.shp')""")
    build.close()
LAYER = gpd.read_file("provinces.shp")[["name", "admin", "geometry"]]
print(len(LAYER), "provinces;", duckdb.connect("data.duckdb", read_only=True)
      .execute("select count(*) from features").fetchone()[0], "rows in data.duckdb")

4596 provinces; 4596 rows in data.duckdb

Example 1 — a streaming endpoint over a database cursor

The service is a file, so it can run under uvicorn in its own process and be measured there. It has the buffered endpoint for comparison.

%%writefile stream_app.py
import json

import duckdb
from fastapi import FastAPI, Query, Request
from fastapi.responses import JSONResponse, StreamingResponse

app = FastAPI()
con = duckdb.connect("data.duckdb", read_only=True)
con.execute("install spatial; load spatial;")


def build_query(bbox=None, geometry="st_asgeojson(geom)", limit=None):
    clauses, params = [], []
    if bbox:
        x0, y0, x1, y1 = (float(v) for v in bbox.split(","))
        clauses.append("st_intersects(geom, st_makeenvelope(?, ?, ?, ?))")
        params += [x0, y0, x1, y1]
    where = ("where " + " and ".join(clauses)) if clauses else ""
    sql = (f"select id, name, admin, {geometry} as geometry from features {where}"
           + (f" limit {int(limit)}" if limit else ""))
    return sql, params, where


def features_from_query(sql, params, batch_rows=1000):
    """Lazy all the way down: a record-batch reader, never the whole result."""
    reader = con.cursor().execute(sql, params).fetch_record_batch(batch_rows)
    for batch in reader:
        for row in batch.to_pylist():
            yield {"type": "Feature", "id": row["id"],
                   "geometry": json.loads(row["geometry"]),
                   "properties": {"name": row["name"], "admin": row["admin"]}}


@app.get("/features")
def buffered(bbox: str | None = None, limit: int | None = Query(None, ge=1)):
    """The one-large-response answer: everything in memory, then sent."""
    sql, params, _ = build_query(bbox, limit=limit)
    return JSONResponse({"type": "FeatureCollection",
                         "features": list(features_from_query(sql, params))})


@app.get("/features/stream")
async def stream(request: Request, bbox: str | None = None,
                 limit: int | None = Query(None, ge=1)):
    sql, params, where = build_query(bbox, limit=limit)
    matched = con.cursor().execute(f"select count(*) from features {where}", params).fetchone()[0]

    async def generate():
        for feature in features_from_query(sql, params):
            if await request.is_disconnected():
                break
            yield json.dumps(feature, separators=(",", ":")) + "\n"

    return StreamingResponse(generate(), media_type="application/geo+json-seq",
                             headers={"X-Total-Count": str(matched)})

import socket
import subprocess
import sys
import time

import httpx


def start_server(module="stream_app"):
    with socket.socket() as probe:                 # a free port
        probe.bind(("127.0.0.1", 0))
        port = probe.getsockname()[1]
    proc = subprocess.Popen([sys.executable, "-m", "uvicorn", f"{module}:app", "--port", str(port),
                             "--log-level", "warning"], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    base = f"http://127.0.0.1:{port}"
    for _ in range(100):
        try:
            httpx.get(base + "/docs", timeout=1)
            return proc, base
        except httpx.TransportError:
            time.sleep(0.1)
    raise RuntimeError("server did not start")


server, BASE = start_server()
print("serving on a free local port:", httpx.get(BASE + "/features/stream", params={"limit": 1}).headers["content-type"])

serving on a free local port: application/geo+json-seq

features_from_query holds one record batch at a time. The first version of this example called fetch_arrow_table(batch_rows) in a loop, which looks incremental and is not — DuckDB returns the whole result on the first call.

Example 2 — the client side

import httpx
import json


def consume_stream(url, params=None, on_feature=None):
    """Constant memory on the client too: one line at a time."""
    count = 0
    with httpx.stream("GET", url, params=params, timeout=None) as response:
        response.raise_for_status()
        total = response.headers.get("x-total-count")
        for line in response.iter_lines():
            if not line.strip():
                continue
            feature = json.loads(line)
            count += 1
            if on_feature:
                on_feature(feature)
            if total and count % 1000 == 0:
                print(f"  {count:,} / {total}")
    return count

print(consume_stream(BASE + "/features/stream", params={"bbox": "-10,49,2,61"}), "features in Britain and Ireland")
print(consume_stream(BASE + "/features/stream"), "features in all")

278 features in Britain and Ireland
  1,000 / 4596
  2,000 / 4596
  3,000 / 4596
  4,000 / 4596
4596 features in all

The client can stop at any point by breaking out of the loop, and httpx closes the connection — which the server's disconnect check notices.

Example 3 — streaming Arrow for an analytical client

Appended to the same service:

%%writefile -a stream_app.py

import io

import pyarrow.ipc as ipc


@app.get("/features/arrow")
def stream_arrow(bbox: str | None = None, batch_rows: int = 1000):
    """Binary and typed: WKB geometry in record batches."""
    sql, params, _ = build_query(bbox, geometry="st_aswkb(geom)")
    reader = con.cursor().execute(sql, params).fetch_record_batch(batch_rows)

    def generate():
        buffer = io.BytesIO()
        writer = ipc.new_stream(buffer, reader.schema,
                                options=ipc.IpcWriteOptions(compression="zstd"))
        for batch in reader:
            writer.write_batch(batch)
            yield buffer.getvalue()
            buffer.seek(0)
            buffer.truncate(0)
        writer.close()
        yield buffer.getvalue()

    return StreamingResponse(generate(), media_type="application/vnd.apache.arrow.stream")

import pyarrow as pa

server.terminate(); server.wait()
server, BASE = start_server()                     # restart so the new endpoint is loaded
table = pa.ipc.open_stream(httpx.get(BASE + "/features/arrow", timeout=None).content).read_all()
print(table.num_rows, "rows;", table.schema)

4596 rows; id: int64
name: string
admin: string
geometry: binary

Example 4 — measuring the three answers

Each measurement starts a fresh server, makes one request for all 4,596 provinces, and reads the server process's peak resident memory from /proc.

import gzip


def measure(path):
    proc, base = start_server()
    idle = int(next(l for l in open(f"/proc/{proc.pid}/status") if l.startswith("VmHWM")).split()[1]) / 1024
    started = time.perf_counter()
    body = httpx.get(base + path, timeout=None).content
    seconds = time.perf_counter() - started
    peak = int(next(l for l in open(f"/proc/{proc.pid}/status") if l.startswith("VmHWM")).split()[1]) / 1024
    proc.terminate(); proc.wait()
    return body, seconds, idle, peak


print(f"{'endpoint':18} {'MB':>7} {'gzipped':>8} {'seconds':>8} {'server MB idle':>15} {'peak':>6}")
for path in ("/features", "/features/stream", "/features/arrow"):
    body, seconds, idle, peak = measure(path)
    print(f"{path:18} {len(body) / 1e6:7.2f} {len(gzip.compress(body)) / 1e6:8.2f} {seconds:8.2f} "
          f"{idle:15.0f} {peak:6.0f}")
server.terminate()

endpoint                MB  gzipped  seconds  server MB idle   peak
/features            51.40    17.79     1.66             155    732
/features/stream     51.40    17.79     1.45             155    386
/features/arrow      15.00    14.70     0.11             155    250

Why streaming beats paging for everything

Why streaming beats pagination for "give me everything"

Pagination exists so that neither side holds the whole result, and it achieves that by making four hundred requests, each with a round trip, an offset that can drift under concurrent writes, and a page boundary at which bugs live.

A stream achieves the same memory property with one request and no boundaries. The client can still stop early — it simply stops reading — which is the only genuine advantage pagination had.

Pagination remains right when the client wants a page: a UI showing fifty results with a next button. It is the wrong shape for a bulk transfer.

Why the laziness has to reach the data source

StreamingResponse with a generator looks like streaming and is not, if the generator's first line materialises everything:

def generate():
    rows = con.execute(sql).df()          # the whole result, right here
    for row in rows.itertuples():
        yield ...

The peak memory is unchanged; only the transfer is incremental. Database cursors, Arrow record batches and chunked file readers are what make it real, and the test is simple: watch the process's memory while a large query runs.

Why newline-delimited beats a FeatureCollection

A FeatureCollection is a single JSON document. A client cannot parse the first feature until the closing bracket arrives, because a JSON parser needs the whole value — so streaming one saves the server's memory and not the client's.

Newline-delimited GeoJSON is a sequence of independent documents. The client parses each line as it arrives, holds one feature at a time, and can stop whenever it likes. That is why application/geo+json-seq exists as a registered media type.

Why the disconnect check matters more than it looks

Without it, a client that closes a tab leaves the server generating and serialising features into a socket nobody is reading, until the write buffer fills and the connection errors — which for a slow query can be minutes of wasted work per abandoned request.

On a public endpoint that is a trivially exploitable resource sink. await request.is_disconnected() in the loop is one line and it bounds the damage.

Media types, unknown lengths and mid-stream errors

• application/geo+json-seq is the registered media type for newline-delimited GeoJSON.
• Do not send Content-Length on a streamed response; use X-Total-Count for progress.
• .df() and .fetchall() materialise. Use cursors or record batches.
• Errors mid-stream cannot change the status code — the 200 has already been sent. Emit an error object as the last line.
• Gzip works on streams and compounds with everything else.
• Check for disconnects, or abandoned requests keep working.
• Proxies may buffer streams. Check nginx's proxy_buffering before blaming the application.
• Arrow is far better for analytical clients and needs an Arrow-aware consumer.

Internal links

• How to paginate a large feature API in Python — the alternative, and when it is right
• Serving spatial data explained: files, features and tiles — where bulk transfer fits
• GeoJSON, vector tiles or Parquet: choosing an API response format — the format decision
• Fixing an API worker that runs out of memory — the failure streaming prevents
• How to read a large PostGIS table without exhausting memory — the same idea server-side
• How to process a large GeoPackage in chunks — chunked reading
• Fixing a spatial API that is slow under load — the wider performance picture
• How to build a GeoJSON API with FastAPI — the base service

FAQ

When should an API stream instead of paginating?

When the client wants everything matching a filter. Streaming is one request with constant memory on both sides; pagination is hundreds of round trips with offset drift on changing data.

What format should a stream use?

Newline-delimited GeoJSON, application/geo+json-seq. Each line is a complete Feature, so the client parses one at a time and can stop whenever it likes.

Why is my streaming endpoint still using all the memory?

Because the generator materialises the result before yielding — .df() or .fetchall() in the first line. Use a cursor or Arrow record batches instead.

Can I stream a FeatureCollection?

Yes, by writing the envelope by hand. It saves the server's memory but not the client's, because a JSON parser needs the whole document.

How does the client know how many features to expect?

A custom header such as X-Total-Count. A streamed response cannot carry Content-Length, because the length is not known when the headers are sent.

What happens if the client disconnects?

Without a check, the server keeps generating into a socket nobody reads. await request.is_disconnected() inside the loop stops it.