Exporting 5 million rows from Elasticsearch without OOM

Two ways to run out of memory#

The first failure is on the cluster. A search request with from and size has to collect from + size hits on every shard and merge them on the coordinating node, so Elasticsearch refuses to page past index.max_result_window, 10,000 hits by default. You can raise the setting, but each deeper page costs more than the last, and a 5-million-row walk with from would ask the shards to sort millions of documents hundreds of times.

The second failure is on whatever sits between the cluster and the file. The obvious export is: run the query, collect the rows into a list, serialise the list, send the file. Each row becomes a Python dict or a JavaScript object with its own overhead, and the serialised text is held once more before it is written. A few million documents of a few hundred bytes each turns into several gigabytes of process memory, and the browser tab that was supposed to receive the download goes first.

Avoiding both failures means never holding the result anywhere: not as a search response, not as a list, not as a string.

Scroll and point in time#

Elasticsearch offers two mechanisms for reading a large result in order. A scroll keeps a search context open on the cluster and hands back a cursor; each _search/scroll call returns the next page and renews the context for the keep-alive you asked for. A point in time (PIT) plus search_after is the newer alternative: it pins a view of the index and lets you page by sort values without a server-side cursor per request.

FlatQL's SQL export uses scroll. The first request goes to /<index>/_search?scroll=3m with "sort": ["_doc"] when no sort was pushed, which is the cheapest order the cluster can produce, and every following page comes from /_search/scroll with the same 3-minute keep-alive. When the generator finishes, or the download is cancelled, the scroll is deleted so the cluster does not hold the context until it expires. The Browse page's document export takes the search_after route with a _doc tiebreaker, and is capped at 200,000 documents.

Sliced scrolls in the engine#

When a statement needs the engine, a join, a window function, a GROUP BY the cluster cannot run, FlatQL has to pull rows for each index before DuckDB can start. That scan is where sliced scrolls come in. The scan opens SCROLL_SLICES scrolls, four of them, in parallel, each with "slice": {"id": i, "max": 4} so that the cluster hands every slice a disjoint quarter of the matching documents. Each slice pages at SCROLL_PAGE, 5,000 hits per request, until it has its share of the fetch cap: 50,000 rows divided by four, plus one. The slices run in a thread pool and their rows are concatenated, cut at 50,000, and loaded into DuckDB.

That cap is the engine's working set per index per statement. It exists because DuckDB needs the whole row set in hand to join or window over it, and the limits page explains the warning you get when an index has more matching documents than that. For an export, the question is whether your statement needs the engine at all.

Two export paths#

The export planner looks at the statement and picks one of two routes.

Direct from the cluster. If the statement is a plain projection over one index, every WHERE conjunct was pushed, there is no aggregation, DISTINCT, window, QUALIFY, JOIN or OFFSET, and any ORDER BY is on fields the cluster can sort, the export never touches DuckDB. One scroll is opened with the pushed filters, the pushed sort or _doc, and _source restricted to the selected columns. Each 5,000-row page is turned into output rows and yielded immediately. Nothing accumulates, and there is no cap other than the row count you asked for.

Through the engine. Anything else runs as a normal statement: indices are scanned (with the sliced scrolls and the 50,000-row cap above) or aggregated, DuckDB executes the SQL, and the result is read from the cursor 5,000 rows at a time with fetchmany. The output still streams; the input was bounded by the cap.

Show as DSL tells you which path you are on before you export: a single entry with mode scroll or search and a fully pushed WHERE means the direct route. The pushdown rules decide the rest.

Streaming to disk, not to memory#

On the backend, the export is a Python generator wrapped in a Flask response. For CSV, CSV for Excel and TSV, a csv.writer writes the header, yields it, then for every page of rows writes the page into a small buffer, yields the text and truncates the buffer. For JSON the generator yields [, then one serialised object per row with the separating commas, then ]; the file is a valid JSON array that was never assembled as one. The response is sent with X-Accel-Buffering: no so a proxy in front of FlatQL passes chunks through instead of collecting them.

In the browser, the download reads the response body as a stream. In Chrome and Edge, FlatQL asks for a save location first with the File System Access API and writes each chunk to the file as it arrives, so the file grows on disk while the cluster is still scrolling. In browsers without that API the chunks are collected into a Blob and saved at the end; the dialog says so. Progress counts rows by newline-terminated lines, quote-aware so a line break inside a quoted CSV field is not mistaken for a row.

Formats#

Excel is the exception. The workbook is built in the browser from the rows in memory, so it is capped at 100,000 rows and cells longer than 32,000 characters are truncated. When a result may hold more, the export dialog says to pick CSV.

The 5,000,000-row cap#

The export endpoint clamps the requested row count to SQL_EXPORT_MAX_ROWS, 5,000,000, and the export dialog offers presets from 1,000 up to 1,000,000 with a custom field that stops at the same cap. The number is a server setting rather than a technical ceiling: the direct path would keep yielding pages for as long as the scroll had hits.

It exists for the cluster's sake and for yours. A scroll context is work the cluster keeps alive for the duration of the download; 5,000,000 rows at 5,000 per page is 1,000 round trips, each renewing the context. Beyond that size a one-off download stops being the right tool, and a reindex, a snapshot or a scheduled pipeline is. The cap keeps a mistyped export from becoming a cluster event. The value is a server-side setting, not a limit of the format or the browser.

Practical advice#

Filter first, with predicates the cluster can push. status = 'paid', placed_at >= '2026-01-01', service IN ('api', 'web') all become filters in the scroll request, so only matching documents leave the cluster. A predicate the planner cannot translate, such as a function on a column, forces the engine route and the 50,000-row cap.

Select columns. SELECT * fetches the whole _source and expands the column list from the first page plus the mapping. Naming the columns you need shrinks every page on the wire and the file on disk.

Sort only when it pushes. ORDER BY placed_at DESC on a date field is sent to the cluster and the scroll returns rows in that order. ORDER BY LOWER(name) cannot be pushed, which turns a direct export into an engine export. If you do not need the order, leave it out and let _doc order do the work.

Know when you have left the direct path. A JOIN, a GROUP BY, a window function or DISTINCT means DuckDB runs the statement, and each index contributes at most 50,000 rows. That is fine for a report; it is not how you export 5 million order lines. Export the raw rows and aggregate elsewhere, or push the GROUP BY as an aggregation so the cluster does the counting.

Prefer CSV or TSV over Excel for anything large. The streamed formats have no browser-side ceiling; the workbook does.

A worked example of the whole flow is on the export to CSV page, and the export reference lists the dialog options. Streaming exports are part of the Pro licence; the Free plan runs the same statements without the download. Create an account to connect a cluster and see which path your statements take.