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.

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