handling large datasets

mko

Hello,
I've noticed problems when handling large amounts of data. Loading takes an extremely long time or sometimes crashes. This happens both in the relation preview and in lists on forms.

The SQL statements with LIMIT/OFFSET looks very good and runs individually within milliseconds. However, the browser stops at "const mountChildren = " in vue.js.

From testing, I would say that for form lists children.length corresponds to the dataset-size (rowcount) divided by 10, and for relations > data preview its divided by 50. An empirical guess, actually.

I wonder if I've set an unfavorable configuration, only or if others can confirm this behavior and there's a general problem.

Kind regards
Martin

gabriel

mko
To start troubleshooting, I need some more context please:

What amount of data are we talking about? Millions of records or more?
What type of "crash" do you encounter? Does your browser hang or does the r3 server crash completely?
What limit do you use on the list field? The default goes up to 1000 (setting "Results per page"), but you can overwrite the default limit to something much larger.
Are you using complex filters? If so, are you testing the SQL with those filters enabled?

mko

Hello Gabriel,
thanks for your reply indicating there should be no general problem. Here's the setup (extra large for testing purpose):

amount of data: 5.000.000
type of crash: browser hangs and offers to stop the script, server is fine
limit: 50
no filters, no sorting (actually, the relation preview tab)

The reason is the generated offset-selector in inputOffset.js. So no error, generally. But much too much entries for the pull-down menu. Haven't worked with vue yet, but are you interested in some kind of patch making the the pull-down selector changing into an input-selector if there are more than # entries? Would try my best.

Kind regards
Martin

gabriel

mko
The offset selector is causing this? Sounds reasonable - with the vue component trying to create 100.000 option elements to select from, the browser definitely has a lot of work to do.

If you want, you can try your hand at a patch. Otherwise we´ll fix it with the next patch, once I´ve prepared an instance to replicate the issue. Just let me know if you want to take a stab at it first :)

mko

I'll prepare something.

gabriel

I just merged your pull. I like your solution, scales well but still allows navigation to all pages. Thank you for your effort :)

There was a small bug where the page display label on the last selection would show values larger than the total due to the changes to pageCount. I´ve fixed the issue and updated the names so that things become more clear - pageCount became pages as its now an array instead of a number.

mko

Thank you!

mko

I see further optimization potential when using large tables. The count(*) over() function for determining the number of returned rows significantly slows down rei3. As far as I know, it's common practice to separate queries in such cases. I've made a draft-like adjustment [1], but I'm not satisfied due to the increased complexity. It might even be a step backward for normal queries, haven't done any benchmarking yet.

Do you know of a better solution?

[1] https://github.com/r3-team/r3/commit/ba701c5baf5fcdebf3eff01ed991781fbbd37841

gabriel

Funny enough, we´ve had separated queries for data + count for the longest time. We´ve "just" (June 2025) got rid of them:
https://github.com/r3-team/r3/commit/6c8d1f44e491639786aa8ad0c9814f8940d2c6b9

Our benchmarking consistently showed that running separate queries results in much worse performance, especially if heavy filtering was involved. Reason being that Postgres had to evaluate the filters (incl. relation policies) again when running a separate count query, as totals take filters into account.

If I remember correctly, we mostly tested with common scenarios, and never much more than 100.000 records - so maybe this is different with much larger data sets.

If you want to spend the time to benchmark this, I would be interested in the results - though we´d need to re-evaluate our old tests before we´d consider going back to the old way.

Do you currently have "bad" performance in r3? (bad as in significantly worse than querying the DB directly outside of r3)