Exporting large CSVs from Phoenix without exhausting memory or your database pool

Almost every business application eventually gets a "Download as CSV" button. It works fine with a few hundred rows. Then the table grows to millions of rows, someone starts an export over a slow connection, and the whole app gets slower for everyone.

I've just released BeamExport 0.1.0, an Elixir library for exactly this problem. This post walks through why the common approaches fail, the idea BeamExport is built on, and the failure cases that took most of the work.

Approach 1: load everything

The simplest version loads every row, builds the CSV in memory and sends it:

# Loads the entire dataset into the process heap
Repo.all(query) |> Enum.map(&format_row/1) |> CSV.encode() |> send_download(...)

Memory grows with the size of the export. A few hundred thousand rows can mean hundreds of megabytes for a single request, plus garbage-collection pressure for everything else running on the node. A few concurrent exports can take the node down.

Approach 2: stream straight to the client

The usual fix is to stream. In Ecto, Repo.stream/2 reads rows through a database cursor, and a cursor must live inside a transaction:

# Holds a pool connection for as long as the slowest client takes
Repo.transaction(fn ->
  query |> Repo.stream() |> NimbleCSV.dump_to_stream() |> Enum.into(conn)
end, timeout: :infinity)

Memory is now bounded, but there's a new problem: the database connection is held for the entire download. The server can only send data as fast as the client reads it, so a client on a slow mobile connection keeps a connection from your pool checked out for minutes. Pools are small (often 10 connections), so a handful of slow downloads can starve the rest of the application. On PostgreSQL, long-running transactions also hold back vacuum.

The idea: stage the export on disk

The database is fast, and the network to the client is the slow part. So separate them:

STAGE 1  database cursor ──> CSV chunks ──> private temp file      (holds 1 DB connection)
                                                │
         ── transaction commits, connection returns to the pool ──
                                                │
STAGE 2  HTTP client  <────── send_file ─────── temp file          (no DB connection)
                                                │
         ── temp file deleted when the request process exits ──

Stage 1 reads the rows at database speed and writes them to a temporary file in bounded chunks. The transaction commits and the connection goes back to the pool. Stage 2 serves the file with sendfile, however long the client takes, without touching the database.

The trade-off is that the response only starts once the file is written. For most exports that takes seconds. For very large ones, generate the file in a background job instead.

Using BeamExport

Add the dependency:

def deps do
  [
    {:beam_export, "~> 0.1.0"}
    # plus :ecto / :plug if you export from Ecto or serve downloads (Phoenix already has both)
  ]
end

A staged download from a Phoenix controller:

def export(conn, _params) do
  query = from(u in User, order_by: u.id)

  BeamExport.Plug.send_staged_export(conn, "users.csv", {Repo, query},
    headers: ["ID", "Name", "Email"],
    mapper: fn user -> [user.id, user.name, user.email] end
  )
end

Writing a file from a background job:

{:ok, %{rows: rows, bytes: bytes}} =
  BeamExport.to_file({Repo, query}, "/var/app/exports/users.csv",
    headers: ["ID", "Name", "Email"],
    mapper: fn user -> [user.id, user.name, user.email] end
  )

A path ending in .gz gives you gzip compression on the fly. There's also to_stream/2 (a lazy stream of CSV chunks) and to_iodata/2 (for small exports, such as email attachments).

The hard part: failing loudly

Streaming rows into a file is the easy part. Most of the work went into the cases where things go wrong, because the worst outcome for an export isn't an error. It's an incomplete file that looks complete. Someone opens it, sees 40,000 rows instead of 50,000, and makes a decision based on it.

BeamExport treats that as the one thing it must never do:

• Stalled consumers and databases. If whatever consumes a stream stops asking for data, or the database stops answering, the export fails with a BeamExport.TimeoutError (its message says the export is incomplete) instead of quietly ending early.
• Failed commits. Rows are only reported as done after the transaction actually commits.
• Disk full, reported late. Buffered file writes often report errors only when the file is closed, not when you write. Ignoring the return value of close is a classic way to produce truncated files. BeamExport checks it, and syncs the data to disk before an atomic rename, so a reader never sees a partial file.
• Cut-off downloads. If a chunked download fails mid-way, the response ends without its final chunk, so the HTTP client knows the transfer is incomplete rather than saving a short file.
• Cleanup that never destroys data. A failed export removes its own temporary file, and never touches a file it didn't create.

Safe by default

A few defaults exist because exports usually leave your system and land somewhere else:

• CSV injection protection is on. Spreadsheet applications treat a cell starting with =, +, - or @ as a formula. A user who names themselves =HYPERLINK("http://evil.example", "click") can turn your export into a phishing link in someone's Excel. Following the OWASP recommendation, BeamExport prefixes such text with '. Numbers are never changed, and you can turn this off for machine-to-machine exports.
• No row data in errors or telemetry. Exceptions end up in logs and error trackers. A failing mapper reports the row number and something like key :email not found, not the customer record. You can opt in to full records while debugging.
• Private files. Exports are created readable only by their owner (0600), and each staged download gets its own private directory.

Getting the CSV right

Some details that are easy to get wrong:

• Floats are written in their shortest exact form. A rounding formatter can silently turn 1.0e-12 into 0.0.
• Decimals never come out in scientific notation (1E+3), which spreadsheets and importers often misread.
• Lists, maps and tuples raise a clear error instead of being mangled. Converting ["a", "b"] to a string naively gives "ab".
• Header mismatches raise. If your headers don't match a struct's fields, you get an error, not a file full of empty columns.
• Typos in options raise with a "did you mean" hint, so sanitize_formula: false can't silently do nothing.

Performance

Measured on Linux (OTP 26, Elixir 1.17) writing a 5-column synthetic dataset with to_file/3, single runs:

Rows	Time	Rows/s	Peak process memory
10,000	66 ms	~150k	0.4 MB
100,000	477 ms	~210k	0.4 MB
300,000	1.6 s	~190k	0.3 MB

Memory stays flat as the export grows, because it depends on the chunk size, not the number of rows. Your numbers will depend on your database, row width and mapper.

Limitations

It's an early 0.x release, so I'd rather be clear about what it doesn't do yet:

• It writes CSV only.
• Its test suite runs against SQLite. I haven't run it against PostgreSQL or MySQL in CI yet.
• Every running export holds one database connection while it reads rows, so you should still cap concurrent exports below your pool size.
• A staged download only starts after the file is written, so very large exports belong in a background job.

Try it

• Hex: https://hex.pm/packages/beam_export
• Docs: https://hexdocs.pm/beam_export
• GitHub: https://github.com/HataluliRandima/beam_export

Next on my list are streaming exports straight to S3-compatible storage, NDJSON and Parquet output, and a ready-made Oban worker for background exports. If you try BeamExport, especially on a large PostgreSQL or MySQL table, I'd love to hear how it goes. Issues and feedback on GitHub are very welcome.