perf: stream table dumps via Postgres COPY — snapshot-consistent, resumable, constant-memory

[rorybyrne]

Summary

Table dumps (/data/{schema}/records.csv[.gz], /data/{schema}/{feature}.csv[.gz]) currently stream row-by-row through SQLAlchemy over a server-side cursor: each row is fetched, materialized as Python objects, serialized to CSV in app code, and written out. Postgres can do all of this natively and much faster with COPY.

Design

Replace the FullStream execution path (#212 workstream B) with PG-native COPY:

BEGIN ISOLATION LEVEL REPEATABLE READ;
COPY (SELECT ... ORDER BY <pk>) TO STDOUT (FORMAT csv, HEADER);

piped through a streaming gzip encoder into the chunked HTTP response (asyncpg exposes copy_from_query).

What this buys:

• Speed / constant memory. Postgres serializes CSV itself and streams raw bytes; no per-row ORM work. Time-to-first-byte stays sub-second once the plan starts emitting (with perf: /data table reads do O(table) work per request — per-request COUNTs, unbounded SQL, unindexable sort #212 C, no pre-sort).
• Snapshot consistency. REPEATABLE READ pins one snapshot for the whole dump — today a long export can contain half of an ingest batch that commits mid-stream. With batch-level visibility as the consistency unit, a dump should reflect one instant.
• Resumability. Accept ?after_id=<last received id> and continue as a keyset range scan inside the COPY subquery. A dropped connection at row 3M no longer restarts from zero — and the same parameter is the incremental-pull primitive a mirror node needs.

Details:

• Records dumps project JSONB metadata to declared columns inside the COPY subquery — still fully streaming.
• Dump ordering becomes PK order (srn / id), not the default page sort — dumps need a stable resumable order, not a meaningful one. Document this.
• Cap concurrent exports (small semaphore) so a 10^7-row scan cannot starve page reads of I/O.
• Note in ops docs: a long REPEATABLE READ snapshot delays vacuum; acceptable at current write volume.
• Client disconnect must cancel the COPY (asyncpg cancellation), not leave it running server-side.

Relation to #212

Workstream B introduces the BoundedPage | FullStream plan union; this issue changes only the FullStream executor. Land after #212 B/C (the index work is what makes TTFB instant). The gzip-while-streaming machinery from #137 is reused.

Acceptance

• Dump of a 5M-row feature table: TTFB < 1s, constant memory, output identical (modulo row order) to current CSV
• Dump taken while an ingest run commits batches contains no partial batch
• Interrupted dump resumed via after_id yields byte-identical concatenated content
• Concurrent-export cap enforced; page-read p50 unaffected during a running export