Limits, caps and unsupported SQL

Every cap in one place, with the reason for it and the way around it. Most of them stop mattering once the WHERE clause is something the cluster can apply.

Engine limits#

The engine source states the fetch rule plainly:

Otherwise the index is scanned up to the fetch cap and the response carries a warning.

"Otherwise" means: the WHERE was not pushed in full, or the statement has a DISTINCT, window function, QUALIFY or an aggregate the cluster cannot run. The result then shows a note such as "orders has 1,284,201 matching documents; only the first 50,000 were scanned", and the Show as DSL tab shows the request in mode scroll (up to 50,000 rows). The pushdown rules on the pushdown page tell you how to get back to a bounded search or an aggregation.

Two consequences worth knowing:

• An index pattern counts as one index. logs-* across a month of daily indices shares one 50,000-row cap.
• A self-join fetches the same index twice, once per alias, each with its own cap.

Aggregation limits#

A pushed GROUP BY stops after 50,000 groups and adds a note. Statements with a high-cardinality key (a user id, an order id) hit this; aggregate at a coarser level or filter first. COUNT(DISTINCT x) is computed by the cluster's cardinality aggregation and is approximate, about 1 percent off at large counts; the result says so. APPROX_COUNT_DISTINCT is the honest name for the same thing. MEDIAN, PERCENTILE_CONT, APPROX_PERCENTILE, APPROX_TOP_K, LISTAGG and ARRAY_AGG are computed in the engine over a scan, so the 50,000-row cap applies to them.

Export limits#

Downloads stream to disk as they are produced, in CSV, TSV, JSON, NDJSON, Excel or Parquet, up to 5,000,000 rows. A plain projection over one index with the whole WHERE pushed (SELECT a, b FROM orders WHERE status = 'paid' ORDER BY placed_at) streams straight from the cluster with no 50,000-row cap. Anything that needs the engine, such as a join, a window function or a GROUP BY the cluster cannot run, runs through the engine first and inherits the per-index cap. The export page has the formats and the CSV export walkthrough shows a 5,000,000-row run.

Unsupported SQL#

Statements that are not read-only are rejected with "Only read-only SELECT statements run here". Writes typed as Elasticsearch requests are still subject to the connection's read-only flag, wildcard-delete protection and destructive-operation confirmations.

Cluster SQL limits#

In Cluster SQL mode the statement is executed by x-pack SQL or the OpenSearch SQL plugin, and their rules apply: no joins or subqueries, aggregations limited to GROUP BY on keyword or numeric fields with COUNT, SUM, AVG, MIN, MAX, no sorting or grouping on text fields without the keyword sub-field, and the indices:data/read/sql privilege on the user. Results are not paginated beyond the LIMIT you write. When the cluster rejects a statement for a syntax or feature reason, FlatQL runs it in Full SQL and says so in the result notes.

Plan limits#

The Free plan runs the full engine on one cluster; the caps in this page are the same on both plans. Prices and what the licence covers are on pricing and pricing and licensing. For the limits of SQL on Elasticsearch in general, rather than of FlatQL, see Elasticsearch SQL limitations.