Redash: Better support for large query results

[arikfr]

If a query has large result (~50K rows) it will make the UI to get stuck. We need to detect large results sets and handle them differently (server side pagination?).

[natict]

No pagination + Indicator + CSV download
should be a good start :)

[arikfr]

yep, another optimization is to mark big data sets when we store the query result object.

[arikfr]

Relevant discussion: https://groups.google.com/forum/#!topic/redash-users/UbwvXewsJrQ

[ChrisLocus]

Anybody heard anything about any work on this front? Running into this now.

[eshubhamgarg]

@arikfr Do you have any updates on this one ? Like when can be expect a feature release

[arikfr]

It's very low priority compared to other stuff, as usually you don't need large result sets in Redash. So far on work was done on this one.

[adfel70]

Hi,
Can you explain what the bottleneck is?
Thanks

[bboe]

@arikfr how open would you be to a pull request in this area? I have little knowledge of Redash internals, however, we would like to solve the issue and may be able to throw some resources at it if we can work to get any changes incorporated into the project.

Do you have a ballpark estimate on the amount of effort it would require to detect a large result set, and offer a download?

[arikfr]

@bboe how open? very much :) this is low priority for me, but I definitely want to better handle this.

It's hard to give an estimate without looking into this in more detail & understanding what kind of solution you want to achieve. Shoot me an email and let's talk further (arik at redash io).

[jesse-osiecki]

@arikfr @bboe was a pull request ever made regarding this?

[bboe]

Not from my end. Development time for value ended up not being worth it.

[antwan]

Value is to use redash to export/browse large sets of data. Currently this is only suitable for statistics generation.

A quick workaround would be to add an option truncate data on the backend (after 1000 entries) so users can still hit the button "export" without laggy UI due to massive JSON being parsed.

[jezdez]

This has been merged! ✨

[arikfr]

@jezdez this issue is about large query results and not a long list of queries :)

[jezdez]

Ugh, being able to read would clearly be an advantage 😬

[changchichung]

is this issue solved ? I gave same situation when return rows > 50K .

[arikfr]

@changchichung unfortunately not yet. Although if you don't have much more than 50K, maybe just giving more memory to Redash will resolve your issue.

[koooge]

+1
Version 5.0.1+b4851 on EC2 t2.small
redash server cannot resnponse during its processing.

[ismailsimsek]

Version 5.0.1+b4851 on EC2 t2.small EC2 m3.large
getting "redash Worker exited prematurely: signal 9 (SIGKILL)."
our main requirement is ability to download large datasets

[arikfr]

@ismailsimsek try using a larger instance (depends on the dataset size you're trying to download).

[ismailsimsek]

@arikfr what do you think about adding pagination to _query_runner_? using server side cursor where database is handling the large result set. then client application can process the result in batches.
This probably requires an message in the UI when the full result-set is not passed to UI.

Thanks for the great software btw.

[arikfr]

@ismailsimsek pagination/server side cursors won't help without changing how we store the data, because we can't stream the data into Postgres (where we currently store results cache). Also it won't help with serving the results to the browser, because we serve the results to the browser from the cache.

It will help once we change how we store the results and will significantly reduce the memory footprint of the workers.

[Puhapig]

@arikfr I've been following this issue and I'm keen to contribute back if possible. We've had to deal with bad queries locking up our whole Redash service and would like a way to limit the maximum response sizes that are returned, (either response size in memory or row count).

Could a minimum solution to this simply be adding a configuration option to set a maximum query size, and fail safely if it is exceeded. Some use-cases have been mentioned that include paging the query results into the database and I'm interested to hear how these might be made available for download e.g. as csv.

[diwu1989]

Can we set up a config for enforcing limit clause automatically?
Many SQL IDEs do this by default to provide better user experience and prevent users from shooting themselves in the foot.

Default limit to 10k is a reasonable threshold. Nobody actually pages through 10k of results line by line anyways and their UI would stutter.

[arikfr]

Can we set up a config for enforcing limit clause automatically?

Yes.. just need to find a way to do it in a "scalable" way for all the data sources (not all of them have to support it though).