Hi Team,
I have a question for Metabase Row limitation.
For Metabase, the download limitation is one million rows. For the query result showing limitation, what is for that? Is it 10,000 rows?
Can I change the configuration file and limit the download limitation, is that possible to limit the download limitation to 5000 rows? Thank you.
Best,
Janet
Hi @yhu
Download limit is 1 million. Display is 2,000 without aggregation, 10,000 with aggregation.
The limits are hardcoded, so you would have to build your own version with the limits changed or change your database configuration (most databases support setting a “max select limit”).
There’s a request for making the hardcoded limits configurable - go and upvote by clicking 
on the first post:
https://github.com/metabase/metabase/issues/4564
2 Likes
Hi @flamber - just to be clear, displays should still display results for all rows, not just the 2,000 or 10,000 subset, correct? As someone who frequently uses datasets that have 100,000+ rows, it would be a real bummer if charts were displaying only a subset of the data.
Could you clarify what is meant by the diff between display and the underlying query results?
@tablecloth
No, it will display 2,000 or 10,000 aggregated.
You’ll see a triangle in top-right corner of the chart, if you’re exceeding the limit.
So to clarify it only displays results for a sample set? If so, how is that sample set determined?
@tablecloth I don’t understand your question, but the limit is done by adding the SQL LIMIT x (or similar depending on database).
So the way I understand the display limits issue is that if my query results in say 100K rows metabase does not display even the aggregated results of that complete set.
I.e. I can’t work with data that has the potential of returning > 10K rows and expect accurate charting.
Am I misunderstanding that?
In retrospect, I think I am misunderstanding this. The display limit is in place in case there are say 2,000 unique (10,000 aggregate) results to display in a given chart. For example, a bar chart with 2000 bars, not 2000/10000 rows of data it made the chart from. In other words, as long as my charts don’t require that kind of detail, I should be able to crunch queries on datasets with many hundreds of thousands rows. Is that right?
@tablecloth
Correct. If you have more rows than the limit, then the result is limited.
If you’re trying to display 2000 bars, then you should consider binning the results, since it’s difficult to overview that many bars in a meaningful way. So if the bars are every minute, then you should bin them to every hour, which means you have 60 times fewer bars and can show a greater period.
Hey @flamber,
I might be missing something here, but when you say limit for aggregated data is 10000, what exactly does that mean?
I used a query containing aggregations like
select count(table.uniqueid) as count_ from table group by table.timestamp order by count_ desc
and i am still getting only 2000 rows.
Am i missing something very basic here?
@subro There’s an issue open about Native queries:
https://github.com/metabase/metabase/issues/12729 - upvote by clicking on the first post
It is 2023 and I was able to change the query limit by doing the following. This require me to change the source code and build my own version. I am building it on docker.
- Create a new record in "setting" table in the "Metabase application database" (referring to the database which metabase use to store the internal settings).
key: max-results-bare-rows
value: 10000
-
In the src/metabse/query_processor/middleware/constraints.clj file, change "default-max-results-bare-rows" and "max-results" to 10000 or more.
-
Rebuild the application and redeploy.
We were facing this issue too. Our long-term solution was ELT. I think it's perhaps the most elegant way of dealing with bigger tables that need to be partitioned and versioned. We went from Fivetran to Acho and are pretty happy with the result.