Background Jobs for Large CSV Exports That Do Not Time Out

An export that takes four minutes cannot live inside a web request. The proxy gives up, the user stares at a spinner, a database connection is held for the duration and one impatient click starts a second copy. The standard answer is to turn the export into a job: the click records an intent, a worker does the work out of band and the user is told when the file is ready. Doing that well is mostly a matter of five details: how the job reads the table, how it writes the file, how it reports progress, how the user collects the result and when the result goes away. This article goes through them, with real timings for the read strategy. It follows from the streaming approach in streaming a CSV export from a web app, for exports too large to stream, and it belongs to the larger picture in CSV import and export features in applications.

The shape of the feature

The request that starts an export does three things and returns: it validates the user's permission and the filters, creates an export record (user, filters, status queued, no file yet) and puts a job on a queue with the record's ID. It then answers "We are preparing your export; you will get an email when it is ready", or better, shows the export in a list where the status updates. It does no work.

The worker picks up the job, sets the status to running, reads the data, writes the file and sets the status to done with the file's location, size and row count, or to failed with the reason. A notification goes to the user. The user clicks a link, the application checks that this user owns the export and that it has not expired, and serves the file or redirects to a signed URL. A scheduled cleanup deletes expired files and marks the records.

Each of those steps has a way to go wrong, and the record is what makes them manageable: a user who asks "where is my export" can be answered from it.

Read in chunks, by key

A job reading ten million rows should not issue one query that returns them all, since that loads the result set into memory (or holds a cursor open for a long time) and a single failure means starting over. Read in chunks of 5,000 to 50,000 rows. There are two ways to chunk, and the choice matters.

LIMIT n OFFSET m asks for "the n rows after the first m". The database has to produce and discard the first m rows each time, so each chunk costs more than the last. Keyset pagination asks for "the n rows with an id greater than the last one I saw", which uses the index to jump to the right place:

SELECT id, customer, total FROM orders WHERE id > :last ORDER BY id LIMIT 10000;

I measured both on a one-million-row SQLite table in chunks of 10,000:

offset: total 0.75s, first chunk 3.9 ms, last chunk 11.8 ms
keyset: total 0.28s, first chunk 3.2 ms, last chunk 2.7 ms

Offset took 2.7 times as long in total and its last chunk cost three times its first, and the gap widens with table size and with the complexity of the sort. SQLite is a gentle case because it walks an index fast, and on PostgreSQL or MySQL with a join or an unindexed sort, an OFFSET deep into a big table can be dramatically slower. Keyset stays flat, and it has a second advantage: rows inserted while the job runs do not shift the pages, so no row is read twice or missed. The cost is that it needs a unique, ordered key. For an export sorted by something other than the primary key, use a composite key, (created_at, id), and compare with a row value: WHERE (created_at, id) > (:last_date, :last_id).

Save the last key in the export record every few chunks. A job that dies at row 7 million can resume from the saved key instead of starting over, if the file it was writing is still there.

Write the file safely

The worker writes to a temporary name, appends each chunk and, when it finishes, renames the file into its final place. Anything else risks someone, or some process, picking up a half-written file. In object storage, which has no append, write the file in parts (a multipart upload) and complete it at the end, or write locally and upload when done. Record the byte size and the row count in the export record at the end, and compare the row count with a COUNT(*) run when the job began, adjusting for rows added since: a file that is 2 percent short is a bug to know about, not one for the customer to find (testing that a CSV export was not truncated).

The usual details apply. Write UTF-8, with a byte order mark if the export is for Excel users and a way to choose. Neutralize text that a spreadsheet would run as a formula (escaping formula characters in a CSV export). Compress the file if it is large (.csv.gz is a tenth of the size and every tool reads it, though Excel does not open it directly, so offer a zip for those users). And put a header with the export date and filters into the file name, orders-2026-10-02-paid.csv, so that three exports in a downloads folder can be told apart.

Progress and failure

Update a progress count in the export record after each chunk: rows written and, if you counted first, the total. The page that lists exports polls it and shows "412,000 of 1,000,000 rows", which turns a long wait into a visible one. Let users cancel: the worker checks a flag between chunks and stops, deleting the partial file.

Failures need a plan. Distinguish transient errors (a lost database connection, a storage timeout), which the queue should retry with a backoff, from permanent ones (a permission change, a bad filter), which should fail the export with a clear message. Retries must be safe: the job should be able to start from the saved key and append to a fresh temporary file, or restart from the top, and in neither case produce a duplicate or leave the old partial file lying around. Give the job a time limit and a memory limit, so that a runaway export is stopped before it starves the queue, and run exports on a queue of their own so that a burst of them does not delay password reset emails.

Deliver and expire

When the file is ready, tell the user: an email with a link, a notification in the application, or both. The link should be short-lived and tied to the user: a signed URL with an expiry (a day is common), or a route that checks ownership and then streams the file. Do not email the file itself; an attachment is a copy you cannot revoke (sending a CSV by email safely, and better options). Show the expiry in the message ("this link works for 24 hours") so that users who download late are not surprised.

Then delete. An export is a full copy of data that the user could already read, kept somewhere less carefully guarded than the database. A scheduled job removes files after their retention period, a few days at most, and updates the record to expired. If an export holds personal data, the retention rule belongs in your data-protection record (handling CSV files full of customer data under GDPR). Storage lifecycle rules (an S3 lifecycle expiration, for instance) do the deleting without code, and they act as a backstop if the cleanup job breaks.

Permissions and limits

The job must run with the user's permissions, not the system's. Capture the user and the filters at request time and apply the same row-level rules as the on-screen list when the worker queries, otherwise the export becomes a way around access control. Re-check the permission when the file is downloaded, since a user who has left the organization in the meantime should not be able to collect it.

Limit the load: one running export per user, a daily cap and a maximum size are cheap and prevent the accident of someone queuing fifty full-table exports. Count export requests in your logs, because a sudden pile of them is either a bug in a client or a person copying your data out.

When a synchronous download is fine

For results that appear in a couple of seconds, up to a few tens of thousands of rows in most systems, a streamed download is simpler, has no moving parts and gives the user the file immediately. The job machinery is worth building once exports regularly exceed what a request can survive: measure the largest real export, and if it takes more than a third of your shortest timeout, move it to a job. A hybrid works well: stream if the count is below a threshold, queue if it is above, and tell the user which happened.

If the data does not need to be a CSV at all, consider whether the user wants an API, a connector or a scheduled delivery to storage instead of repeated manual exports (scheduling CSV reports from SQL with cron). The queue and storage features differ by framework, and the pattern does not. The CSV format is RFC 4180, and keyset pagination is explained well in the PostgreSQL documentation's notes on LIMIT and OFFSET.