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

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, a 1,000-feature page was 12.4 MB and the server managed 2.3 requests per second; the full 4,596-feature dataset was 54 MB. Ten clients asking at once is a memory problem.
  • Pagination. Four hundred round trips, offset drift on changing data, and page-boundary bugs.

Streaming is the third answer: one request, one response, and neither side holds more than a chunk. The server writes features as it produces them and the client parses them as they arrive, so peak memory is a property of the chunk size rather than 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))})

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.

Two panels comparing buffered and streamed response memory profiles.
Database cursors and Arrow record batches are what make it real.

Step-by-step solution

1. Choose the streaming format

Three that work, with different trade-offs:

Format Media type Client support
Newline-delimited GeoJSON application/geo+json-seq trivial to parse anywhere
A single streamed FeatureCollection application/geo+json works with existing GeoJSON clients
Arrow IPC stream application/vnd.apache.arrow.stream fastest, needs an Arrow client

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.

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 "]}"

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 a real feature payload, 857,061 bytes became 299,123 — a factor of 2.87 — and streaming does not change that ratio.

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

Grid of three streaming formats with their media types and client costs.
A JSON parser needs the whole value, which is why the sequence format exists.

Code examples

Example 1 — a streaming endpoint over a database cursor 

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

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


def features_from_query(sql, params, batch_rows=1000):
    """Lazy all the way down: record batches, not a DataFrame."""
    result = con.execute(sql, params)
    while True:
        batch = result.fetch_arrow_table(batch_rows) if hasattr(
            result, "fetch_arrow_table") else None
        rows = batch.to_pylist() if batch is not None and batch.num_rows else []
        if not rows:
            break
        for row in rows:
            yield {"type": "Feature",
                   "id": row["id"],
                   "geometry": json.loads(row["geometry"]),
                   "properties": {k: v for k, v in row.items()
                                  if k not in ("geometry",)}}


@app.get("/features/stream")
async def stream(request: Request, bbox: str | None = None,
                 limit: int | None = Query(None, ge=1)):
    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 ""

    matched = con.execute(f"select count(*) from features {where}",
                          params).fetchone()[0]
    sql = (f"select id, name, st_asgeojson(geom) as geometry from features "
           f"{where}" + (f" limit {limit}" if limit else ""))

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

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

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
>>> consume_stream("http://localhost:8000/features/stream",
...                params={"bbox": "-10,49,2,61"})
  1,000 / 4596
  2,000 / 4596
  ...
4596

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 

import io
import pyarrow as pa
import pyarrow.ipc as ipc
from fastapi.responses import StreamingResponse


@app.get("/features/arrow")
def stream_arrow(bbox: str | None = None, batch_rows: int = 10_000):
    """Binary, typed, and far smaller than JSON for the same features."""
    sql, params = build_query(bbox)
    reader = con.execute(sql, params).fetch_record_batch(batch_rows)

    def generate():
        buffer = io.BytesIO()
        writer = None
        for batch in reader:
            if writer is None:
                writer = ipc.new_stream(buffer, batch.schema)
            writer.write_batch(batch)
            yield buffer.getvalue()
            buffer.seek(0)
            buffer.truncate(0)
        if writer is not None:
            writer.close()
            yield buffer.getvalue()

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

Measured on a million coordinate pairs, the transfer sizes were 31.77 MB as JSON, 6.86 MB gzipped, and 11.53 MB as Arrow with zstd — and the Arrow client gets typed columns without parsing text.

Explanation

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.

Bar chart of transfer sizes for a million coordinate pairs in four encodings.
An Arrow client also gets typed columns without parsing text.

Edge cases or notes

  • 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.

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.