Streaming GeoJSON Responses Without Buffering

Problem Statement

An export endpoint returns every parcel in a district as GeoJSON — about four hundred thousand features and 1.6 gigabytes of text. Built the ordinary way it holds all of that in memory before sending a byte, and three concurrent requests exhaust the process. This page assembles the same response as a stream, extending the type coercion and serialization patterns to the endpoint layer.

Why the Naive Approach Fails

python
@app.get("/export/{district_id}")
def export(district_id: int, session: Session = Depends(get_session)):
    rows = session.scalars(
        select(Parcel).where(Parcel.district_id == district_id)
    ).all()                                   # ← every row, in memory
    return {
        "type": "FeatureCollection",
        "features": [
            {"type": "Feature",
             "geometry": mapping(to_shape(p.geom)),   # ← parse and re-encode
             "properties": {"id": p.id, "ref": p.parcel_ref}}
            for p in rows
        ],
    }

Three separate copies of the data exist at peak: the ORM objects, the shapely geometries, and the dict that the framework then serialises to a fourth. Time to first byte is the time to build all of them.

Buffered against streamed, on the same export The buffered endpoint peaks at 4.1 gigabytes and sends its first byte after 96 seconds. The streamed endpoint holds 42 megabytes throughout and sends its first byte in under a second, finishing at about the same total time. 400,000 features, two implementations buffered 96 s building the response — nothing sent then 22 s sending peak memory 4.1 GB · three concurrent requests exhaust the process streamed first byte at 0.6 s, then sending continuously for 104 s peak memory 42 MB · concurrency limited by the pool, not by RAM

Production-Ready Implementation

Build the feature JSON in the database, so each row arrives ready to write:

sql
SELECT json_build_object(
    'type', 'Feature',
    'geometry', ST_AsGeoJSON(geom)::json,
    'properties', json_build_object(
        'id',  id,
        'ref', parcel_ref,
        'area_m2', round(ST_Area(geom::geography))
    )
)::text AS feature
FROM parcels
WHERE district_id = %(district_id)s
ORDER BY id

Then stream it. The envelope is written by hand around the rows, which is what makes the document valid without knowing its length in advance:

python
from typing import Iterator

import psycopg
from fastapi import APIRouter
from fastapi.responses import StreamingResponse

router = APIRouter()

FEATURE_SQL = """
    SELECT json_build_object(
        'type', 'Feature',
        'geometry', ST_AsGeoJSON(geom)::json,
        'properties', json_build_object(
            'id', id, 'ref', parcel_ref,
            'area_m2', round(ST_Area(geom::geography))
        )
    )::text
    FROM parcels
    WHERE district_id = %(district_id)s
    ORDER BY id
"""


def geojson_stream(dsn: str, district_id: int, chunk: int = 500) -> Iterator[str]:
    """Yield a complete FeatureCollection without ever holding it."""
    yield '{"type":"FeatureCollection","features":['
    first = True
    with psycopg.connect(dsn) as conn:
        # a named cursor keeps the result set on the server
        with conn.cursor(name="geojson_export") as cur:
            cur.itersize = chunk
            cur.execute(FEATURE_SQL, {"district_id": district_id})
            for (feature,) in cur:
                yield feature if first else "," + feature
                first = False
    yield "]}"


@router.get("/export/{district_id}")
def export(district_id: int) -> StreamingResponse:
    return StreamingResponse(
        geojson_stream(DSN, district_id),
        media_type="application/geo+json",
        headers={"Content-Disposition":
                 f'attachment; filename="district-{district_id}.geojson"'},
    )

The named cursor is what makes this genuinely constant-memory: without it, psycopg fetches the entire result before the loop starts, and the generator becomes a very elaborate way to buffer.

Every link must stay lazy Four stages from the database to the client, each with the mistake that turns it back into buffering: fetchall instead of a named cursor, a list comprehension instead of a generator, a framework that materialises the response body, and a proxy that buffers the whole response before forwarding. One eager link and the whole chain buffers database named cursor breaks on: fetchall() generator yield per feature breaks on: a list() framework StreamingResponse breaks on: a dict return proxy must not buffer check its config proxy_buffering off; # nginx — otherwise it collects the whole body first The proxy is the link people forget, because the application looks correct in local testing and buffers in production, where there is a reverse proxy that nobody thought of as part of the response path. Response size by assembly strategy Bytes for a 400,000-feature export: 1.6 gigabytes assembled in Python with default float precision, 1.1 gigabytes with ST_AsGeoJSON at six decimal places, and 180 megabytes once the proxy gzips the stream. Bytes on the wire for the same 400,000 features Python json.dumps 1.6 GB · 17 significant digits per coordinate ST_AsGeoJSON(geom, 6) 1.1 GB · centimetre precision is plenty … plus gzip 180 MB · the proxy compresses each chunk Coordinate precision is the cheapest win here: six decimal places is about 11 cm, and nobody needs more.

Configuration and Tuning Knobs

itersize on the named cursor controls how many rows the driver fetches per round trip. Five hundred is a reasonable default for feature-sized text; larger values reduce round trips at the cost of a larger transient buffer.

statement_timeout must accommodate the whole stream, because the cursor stays open for its duration. That is a real trade: a streaming export holds a transaction open for minutes, which holds back the xmin horizon and blocks vacuum on the tables involved. For very long exports, consider a snapshot-consistent chunked approach with a keyset instead.

Compression matters more than any of this for GeoJSON, which compresses roughly ten to one. Ensure the response is gzipped by the proxy, and note that this is compatible with streaming — the proxy compresses each chunk as it passes.

Content-Disposition turns the response into a download in a browser, which is usually what an export endpoint wants and is easy to forget.

Verification Steps

bash
# time to first byte should be small and independent of result size
curl -o /dev/null -s -w 'ttfb=%{time_starttransfer}s total=%{time_total}s\n' \
     https://api.example.com/export/42

# and the process should stay flat while it runs
while true; do ps -o rss= -p "$(pgrep -f 'uvicorn')"; sleep 2; done
python
# a parse check on the streamed output — truncation shows up as a JSON error
import json, urllib.request

with urllib.request.urlopen("https://api.example.com/export/42") as r:
    doc = json.load(r)
assert doc["type"] == "FeatureCollection"
print(len(doc["features"]), "features")

The parse check is the honest end-to-end test. A stream that was cut short produces invalid JSON, which is exactly the signal a consumer needs — and it is the reason not to wrap the generator in a try/except that swallows errors mid-stream.

Gotchas Checklist

  • A named cursor requires a transaction and does not survive a connection returned to a pool. Keep the connection for the life of the generator, as above.
  • ST_AsGeoJSON(geom)::json avoids double-encoding. Without the cast, the geometry is embedded as a JSON string containing JSON, and every client has to parse twice.
  • Streaming responses cannot set an accurate Content-Length. Clients that require one — some older download managers — will need a different endpoint.
  • An exception mid-stream cannot change the status code. Validate cheaply first: check the district exists and the row count is plausible before yielding the opening brace.
  • Long-open cursors block vacuum. An export that takes twenty minutes holds the horizon for twenty minutes; on a high-churn table that is a real cost, and a chunked keyset export avoids it.

Backpressure, Timeouts, and the Proxy in the Middle

A streaming endpoint introduces a failure mode that a buffered one does not have: the response is open for the whole duration of the query, and several components along the path have opinions about how long that is allowed to be.

The database transaction is the first. A server-side cursor holds a transaction open until the last row is read, which means a slow client keeps a snapshot alive, blocks vacuum on every table the query touched, and counts against idle_in_transaction_session_timeout if that is set. On a large export consumed by a client on a poor connection, this is how a streaming endpoint quietly causes bloat elsewhere in the database. Setting statement_timeout for the connection bounds the query itself; bounding the total read requires the application to close the cursor when the client disappears.

Client disconnection is the second, and it is the case most often left unhandled. When the consumer aborts, the framework raises on the next write, and unless that exception closes the cursor and returns the connection, the pool leaks a connection per abandoned download. A single misbehaving client can exhaust a pool this way in minutes.

The proxy is the third. Nginx buffers proxied responses by default, which means a carefully streamed response is accumulated in full before any of it reaches the client — reintroducing exactly the memory problem the streaming was meant to solve, one layer further out. proxy_buffering off for the streaming route, along with a proxy_read_timeout long enough for the slowest legitimate export, is what makes the streaming visible end to end.

Verify the whole path rather than the handler. Requesting the endpoint with curl -N and watching bytes arrive continuously — rather than in one burst at the end — confirms every layer is cooperating, which is a thirty-second check that nothing else provides.