Nov 20, 20264 min read

Stream a Large CSV Export from a Postgres-Backed API

Exporting a million rows as CSV should not load them into memory. How to stream rows from Postgres with keyset pagination and a cursor, write CSV as you go, handle timeouts and proxies, and when to build the file in the background instead, in Go with Encore.

Illustration for Stream a Large CSV Export from a Postgres-Backed API

The first version of a CSV export is almost always a query, a slice of rows and a loop that writes the file. It works for a thousand rows and falls over at a million, with the process out of memory or the request killed by a timeout.

Streaming fixes the memory half and a background job fixes the timeout half. This guide covers both, the query patterns that stay fast on large tables and a Go endpoint that streams from Postgres.

How do you stream rows from Postgres?

Run one query and read the result incrementally: drivers such as pgx read rows from the connection as you call Next, so only a small buffer is in memory at a time. Write each row to a CSV encoder that writes to the HTTP response and flush periodically so bytes reach the client as they are produced. Memory use then depends on the row size, not on the number of rows.

One consequence is that the status code and headers are sent before the data. If the query fails halfway, the response is already 200 and truncated, so write an end marker or row count somewhere the client can check, or switch to a background job for exports where completeness matters.

Why not use OFFSET?

Paging with LIMIT 1000 OFFSET 500000 makes Postgres produce and discard half a million rows before returning the page, so each page costs more than the one before. As the Postgres documentation on LIMIT and OFFSET notes, rows skipped by OFFSET still have to be computed. For exports use a single streaming query, or keyset pagination, WHERE id > $1 ORDER BY id LIMIT 5000, which uses the primary key index and reads each row once.

A single long query holds a transaction snapshot open, which can delay vacuum on a busy table. On a read replica or for exports of a few minutes that is acceptable, and keyset batches avoid it.

How should you format the CSV?

Follow RFC 4180: comma separators, fields containing commas, quotes or newlines wrapped in double quotes, and quotes doubled. A library handles this, so never join strings by hand. Write a header row, use ISO 8601 for timestamps, and prefix text cells that start with =, +, - or @ with a single quote if the file will be opened in a spreadsheet, since those cells can be interpreted as formulas.

When should the export run in the background?

Proxies and load balancers close idle or long requests, commonly after 30 to 60 seconds, and a user's laptop can sleep mid-download. When an export takes longer than that, or the user should be able to leave, generate the file in a worker, store it in a bucket and send a signed link, following the asynchronous request-reply pattern. Keep the streaming endpoint for exports that finish in seconds.

Streaming CSV with Encore

Encore

Encore is a backend framework with built-in infrastructure, and a Go service declares its Postgres database in code. A raw endpoint gives direct access to the response writer, which is what streaming needs, while the database runs locally under encore run and on RDS or Cloud SQL in your own cloud account.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

// orders/export.go package orders import ( "encoding/csv" "net/http" "strconv" "time" "encore.dev/rlog" "encore.dev/storage/sqldb" ) var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{Migrations: "./migrations"}) //encore:api auth raw method=GET path=/orders/export.csv func ExportCSV(w http.ResponseWriter, req *http.Request) { ctx := req.Context() w.Header().Set("Content-Type", "text/csv; charset=utf-8") w.Header().Set("Content-Disposition", `attachment; filename="orders.csv"`) rows, err := db.Query(ctx, `SELECT id, customer_id, total_cents, created_at FROM orders ORDER BY id`) if err != nil { http.Error(w, "export failed", http.StatusInternalServerError) return } defer rows.Close() out := csv.NewWriter(w) _ = out.Write([]string{"id", "customer_id", "total_cents", "created_at"}) flusher, _ := w.(http.Flusher) n := 0 for rows.Next() { var id, customer, total int64 var created time.Time if err := rows.Scan(&id, &customer, &total, &created); err != nil { rlog.Error("export scan failed", "err", err, "rows", n) return } _ = out.Write([]string{ strconv.FormatInt(id, 10), strconv.FormatInt(customer, 10), strconv.FormatInt(total, 10), created.UTC().Format(time.RFC3339), }) if n++; n%5000 == 0 { out.Flush() if flusher != nil { flusher.Flush() } } } if err := rows.Err(); err != nil { rlog.Error("export failed mid-stream", "err", err, "rows", n) return } out.Flush() }

Memory stays flat because only the current row and the CSV buffer are held, and the loop flushes every 5,000 rows so the download starts immediately. The endpoint is declared auth, so it needs an auth handler that identifies the caller, and a real export should also filter to the rows that caller may see. For exports that run longer than your proxy timeout, move the same loop into a Pub/Sub subscriber that writes to a bucket.

A coding agent can add a column or a filter to the export in the same change as the migration that creates it, and check the output locally with curl against encore run.

Best fit: Go teams with admin or customer data exports who want constant memory use and a straight path from Postgres to the response in their own cloud account.

Frequently asked questions

How do you export a large table as CSV without running out of memory?

Iterate over the query result row by row, write each row to the CSV encoder and flush periodically to the HTTP response, so memory use stays constant regardless of the number of rows. Never collect all rows into a slice before writing.

Why should you avoid OFFSET for large exports?

OFFSET makes Postgres scan and discard all skipped rows on every page, so each page is slower than the last. A single streaming query, or keyset pagination with WHERE id > last_id ORDER BY id LIMIT n, reads each row once.

When should an export run in the background instead?

When it takes longer than a load balancer or client timeout, typically 30 to 60 seconds, or when users should be able to close the page. Generate the file in a worker, store it in a bucket and send a signed link when it is ready.

How do I stream a CSV export with Encore?

Use a raw endpoint in a Go service, which gives direct access to the http.ResponseWriter. Query the Encore database, wrap the writer in encoding/csv, write each row and flush every few thousand rows. The database is provisioned in your AWS or GCP account, and the endpoint runs locally under encore run.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders