Engineering notes · Postgres

The guardrail that saves you 364 days a year breaks the export on the 365th

2026-08-19 · 3 min · field note

Every query our REST API runs is capped. The role it connects as carries a statement timeout, set once at role level rather than trusted to every call site:

ALTER ROLE borme_api SET statement_timeout = '10s';

That line has earned its keep. An unbounded ILIKE over 9.5M gazette events is a sequential scan that would otherwise sit there holding a pool slot until someone noticed. With the cap, it dies in ten seconds and the caller gets an error instead of a hung request.

Then we shipped bulk export — the full companies snapshot as one gzipped CSV stream — and the guardrail turned on us. A COPY over the whole table legitimately runs for minutes. Ten seconds in, Postgres killed it. The symptom was not a timeout message either: the client saw a truncated gzip stream, because the HTTP response had already started with a 200 before the query died.

Why the fix is one line, and why it is safe

conn.execute("SET statement_timeout = '600s'")

ALTER ROLE ... SET does not enforce a ceiling. It sets a session default, applied when a connection is opened, and a plain SET in that session overrides it. So this is not a hole punched in the guardrail — every other connection this process opens still starts at ten seconds. Only the one session doing the export gets the long leash, and only for as long as it lives.

That last part matters more than it looks, which is why the export does not borrow a connection from the pool.

Three things the timeout was hiding

A pool slot must not be pinned for minutes. The export opens its own connection. Under the pool, one download would have held a slot for the whole transfer while ordinary requests queued behind it — the timeout had been quietly preventing that scenario from ever lasting long enough to hurt.

A client disconnect mid-COPY leaves the connection unsyncable. If the reader hangs up halfway, there is no clean way to drain a half-consumed COPY and hand the connection back. The honest exit is to close the socket, in a finally so it happens on every path:

conn = psycopg.connect(os.environ["BORME_DSN"])
try:
    conn.execute("SET statement_timeout = '600s'")
    with conn.cursor() as cur, cur.copy(_DUMP_SQL) as copy:
        for chunk in copy:
            gz.write(bytes(chunk))
            if buf.tell() > 256 * 1024:
                yield buf.getvalue()
                buf.seek(0); buf.truncate()
    gz.close()
    yield buf.getvalue()
finally:
    conn.close()

Compress and flush as you go. The rows go through a GzipFile into a 256 KB buffer that is yielded and truncated each time it fills. Peak memory is the buffer, not the export — which is the whole reason this can run inside the API process at all.

The numbers

Smoke run against the live snapshot: 3,278,470 rows, 95 MB gzipped, 24.6 seconds. Comfortably inside the new ceiling — and two and a half times the old one, on a good day. The margin on a slow day is exactly why the override is 600s and not 30.

What we did not build

The obvious shape for a bulk endpoint is a nightly job that materialises a file onto a volume and an endpoint that serves it. That is a cron, a PVC, a regeneration window, and a file that is stale by up to a day.

COPY ... TO STDOUT streaming straight into the HTTP response removes all four. There is no artifact to keep, nothing to garbage-collect, and the bytes are as fresh as the moment the request arrived. The cost is that a heavy query now runs on demand, so the endpoint carries its own throttle: pro tier and above, one download per key per day, counted off the request log we already keep.

API docs Pricing & tiers