Dataset page `findDeep` join-fetches all per-file role assignments: multiplicative row explosion OOMs the JVM on grant-heavy datasets

[bikramj]

What happened

Our production installation (Borealis, ~40K datasets) crashed repeatedly over one week with sudden heap exhaustion: heap going from ~20% to 99% in under two minutes, followed by JVM death (-XX:+ExitOnOutOfMemoryError) or a GC death spiral. We traced every event to anonymous page views of two specific datasets.

Root cause: DatasetPage.init() calls DatasetServiceBean.findDeep(), which fetches the dataset and ~17 file-related collections with eclipselink.left-join-fetch hints in a single SQL statement — including o.files.roleAssignments and o.files.fileAccessRequests. Join-fetching multiple independent to-many collections in one statement returns their Cartesian product per file, not their sum. On a dataset with many files, several versions, and many per-file access grants, the result set explodes:

1,084 files × ~10 versions (fileMetadatas) × ~137 roleAssignments/file
  ≈ 1.5M rows × ~250 selected columns  ≈ 15+ GB heap for ONE page view

The PostgreSQL JDBC driver buffers the entire result set before the first row is consumed (default fetch size, autocommit), so this lands in heap all at once. ~4 concurrent page views of such a dataset filled a 67.5 GB heap in ~2 minutes and killed the JVM. The visitors don't need to be logged in — findDeep loads all grants for all files unconditionally; it does not filter by the requesting user. Crawler traffic triggered several of our outages.

The numbers (production)

	Dataset A	Dataset B
Files	1,084	2,872
Dataset versions	10	1
Users granted file access	161	23
Per-file roleassignment rows	148,651	60,323

These rows exist because approving a file-access request writes one fileDownloader assignment per user per file — so grant rows grow as users × files, and every one of them is joined and shipped on every page view.

Captured evidence (available on request: full SQL, PostgreSQL logs, jcmd class histograms):

• The exact generated SQL, captured via statement_timeout cancellation logging, with bind parameters identifying the datasets (WHERE t2.ID = $1 AND t2.DTYPE = 'Dataset'; the statement joins DVOBJECT/DATAFILE to INGESTREQUEST, DATATABLE, AUXILIARYFILE, INGESTREPORT, DATAFILETAG, FILEMETADATA (+ categories, vargroups), EMBARGO, RETENTION, fileaccessrequests, AUTHENTICATEDUSER ×2, ALTERNATIVEPERSISTENTIDENTIFIER, ROLEASSIGNMENT).
• One logged execution: duration: 118649 ms for a single findDeep of dataset B, which completed successfully and OOM'd the JVM ~2.5 minutes later.
• Heap histograms at failure: 9.4–12.3M org.postgresql.core.Tuple, matching counts of byte[][] row buffers, 78–89M java.sql.Timestamp (DVOBJECT's seven timestamp columns are selected repeatedly per row), millions of EclipseLink ArrayRecord/DatabaseRecord/*ValueHolder.

A second, compounding defect: grants survive unrestriction

On dataset A, 99.2% of the 148,651 grant rows (147,399) are on files that are no longer restricted — the files were unrestricted in a later version, but the per-file fileDownloader assignments were never removed. Role assignments are never garbage-collected when a file is unrestricted, so the join input for findDeep only ever grows over a dataset's life. A dataset that was once restricted and popular becomes a permanent landmine.

Why this is hard to mitigate operationally

• No amount of heap helps — the ceiling only sets the countdown. We run 67.5 GB heaps.
• Rate limiting doesn't help — ~4 concurrent ordinary page views is the detonation threshold; ours were triggered by 4–5 distinct visitors, one request each.
• The data shape is regenerated by the normal access-approval workflow, so cleaning it up (collapsing per-file grants to dataset-level grants, which we are doing) is temporary relief, not a fix.

Suggested fixes

1. Stop join-fetching independent to-many collections in one statement. EclipseLink eclipselink.batch hints (one follow-up query per collection) make the cost additive instead of multiplicative — same data, sum(collection sizes) narrow rows instead of product(collection sizes) wide rows. (Hibernate refuses this pattern outright with MultipleBagFetchException; EclipseLink silently allows it.)
2. Remove roleAssignments / fileAccessRequests from the page's entity graph entirely. Deciding one viewer's access by materializing everyone's grants is inverted — permission checks should be predicate queries (EXISTS … WHERE assigneeidentifier IN (:user, :groups)). This also fixes the fact that anonymous visitors currently pay for the full grant table.
3. Paginate the page's file hydration — the page renders 10–25 files but findDeep hydrates the full graph of all of them; the paginated file APIs added around Performance: Slow response for the versions API call with large number of files or versions #9763 show the pattern.
4. Garbage-collect (or compact) per-file role assignments when a file is unrestricted, and/or have bulk access approval grant at the dataset level instead of per-file.

Workaround guidance for other installations (until fixed)

• Set statement_timeout on the application's DB role (we use 300s) — it converts silent heap bombs into logged, parameterized SQL and frees connections.
• Identify at-risk datasets: rank datasets by per-file grant rows; anything above ~20K rows was dangerous at our scale:

  SELECT df.owner_id, count(*) AS file_role_rows
  FROM roleassignment ra JOIN dvobject df ON ra.definitionpoint_id = df.id
  WHERE df.dtype = 'DataFile' GROUP BY df.owner_id ORDER BY 2 DESC LIMIT 15;

• Collapse uniform per-file grants to dataset-level fileDownloader assignments.

Environment

• Dataverse 6.8 (custom fork, findDeep unmodified from upstream), Payara 6.2025.3, PostgreSQL 16, OpenJDK 17, Ubuntu 24.04; 3 app servers, 67.5 GB heap each.

Related issues

• Performance: Slow response for the versions API call with large number of files or versions #9763 (versions API slow with many files/versions — same multiplicative-fetch family)
• Dataset with large number of files #8928 (dataset with 75K files → 500s)
• Check the dataverse and dataset pages for memory use and/or leaks #7803, Optimize/speed up the dataverse page #7804 (page memory/efficiency)

Happy to provide the full SQL captures, PostgreSQL logs, and heap histograms, and to test candidate fixes against our data shape.

[pdurbin]

@bikramj thanks for the detailed writeup! I added this issue to our tech hours agenda.

[landreev]

Hi @bikramj,
Thank you for the report.
I had the same/similar experiences with findDeep() here; or rather, I saw enough anecdotal evidence of it being the source of crashes. I admittedly did not research the issue as deeply as you did. But I did conclude that findDeep() was causing more trouble than its worth on datasets with way-too-many files, even as it offered the advantage of issuing 1 query instead of O(N) on the number of files for reasonably-sized datasets.

So I just cooked up a quick draft pr (#12148) disabling findDeep() in the application, and I have been deploying custom builds of Dataverse releases with these changes patched in...
Having read your report, I feel a little bit like a jerk for not cleaning up the fixes and releasing them to the community. I just assumed (incorrectly) that nobody else except us had monstrous datasets like we do here, for this to be a real problem

[landreev]

I like your workaround suggestions ("until fixed"). On my part, I can build patched war files of a few recent versions with findDeep() disabled and offer that as another temporary workaround to instances that may be affected. As a slightly longer-term workaround, we could make the use of findDeep() controlled by a feature flag. And let individual instances decide if they want to use it or not.

[landreev]

I agree that a proper, clean fix is to refactor DatasetPage and make it stop instantiating the entire list of filemetadatas/datafiles and do proper pagination instead (item 3. on your list). The code for retrieving files in manageable page chunks already exists and is used by the API calls that the new/experimental React UI uses. What happened is that a questionable decision was made some time ago to stop doing any work on the JSF pages, in favor of spending all the resources on the new UI. Years later, we are nowhere near the point where most instances will be able to stop using the JSF UI; but all the known issues and inefficiencies in the old pages are still there. I sincerely hope to be able to start working on the Dataset page specifically sometime soon - but as of now I'm spending most of my time putting out fires on our prod. servers, in large part because JSF pages like this one are server-killing hogs.

[landreev]

(we do not have that many per-file role assignments here, thank god. so this aspect of the problem was not on my radar at all. I remember that a very early iteration of findDeep attempted to load GuestBookResponses too... which of course made it into a bomb on popular datasets - but that was intercepted and fixed early on)

[qqmyers]

FWIW - findDeep was tested and found to be faster in general (which is why it was merged) but those tests probably didn't look at datasets where assignments grew (as @landreev noted, guestbook growth was caught). At QDR, we removed one of the hints: https://github.com/QualitativeDataRepository/dataverse/blob/1c2929750a94963cc515b87c8c045e3879a6485b/src/main/java/edu/harvard/iq/dataverse/DatasetServiceBean.java#L145 (not in the community version). I wouldn't be surprised if using that and commenting out the roleassignments as well might provide some relief in your case (and be a reasonable compromise overall?).

I think there are probably some other relatively lightweight things that can be done, e.g. while working on #12617, I was reminded that some page widgets can be marked as 'dynamic' which means they aren't triggered unless/until the relevant pane/widget is shown. (So if entries aren't loaded by findDeep, they would not be lazy loaded when the page loads, but only if/when the roleassignment page is shown, etc.) That probably isn't done everywhere it could be.

[landreev]

I honestly do not believe findDeep was tested on truly monstrous datasets when it was merged. It is probably true that it is faster in general, by virtue of reducing the per-query overhead... But with an added qualifier of "... as long as it does not kill the application with an OOM" maybe?
This is something I should probably re-measure carefully, and then come back and be able to speak about it more scientifically... But I had an impression that findDeep could be a bomb on a dataset with 10Ks of files, without anything else oversized attached.

@qqmyers I actually assumed that you had also concluded that there was something problematic with findDeep, since you got rid of its use in the indexing code in one of your PRs last summer (I thought you did - ?).

[qqmyers]

I'm not sure it's been proved, but I think, if you are going to iterate through the files, findDeep is better, even for a 10K file dataset (you end up with the same stuff in memory). What I did in indexing is actually avoid creating 10K DataFile objects at all (and their FileMetadata objects where the name/label is) by doing a direct query to just get the fields needed for indexing.

[bikramj]

Thank you @landreev @qqmyers for looking into the issue.
@landreev we do run custom code for Borealis and I will ask our devs to look at your PR to disable findDeep. I think the main issue at the moment for us is the number of individual permissions on lot of files for those large datasets and more people keep on requesting permissions. Even the curator is not able to accept or reject the permissions as that page take forever to load. Our server crashes are so frequent now and I am asking team to test Dataset level permissions rather than individual files and will clean up all the file permissions from DB after.

[landreev]

@bikramj If you are building a custom patch, please consider @qqmyers' suggestion to only disable a couple of select hints that may be causing trouble, rather than disabling findDeep completely like I did.

[landreev]

@qqmyers Don't get me wrong, I was an enthusiastic adopter of findDeep initially. And I did think that it had improved the performance of indexAll. Which would include the "monster datasets". I later assumed there was a problem with my testing then.
I acknowledged already that I wasn't sufficiently scientific or thorough about this. What I know for the fact is that the multi-story query with all the joints was showing up in the "slow query log" coinciding with the crashes. And that the situation was improved by disabling findDeep.
There is a possibility that we had extra complicating factors - like the per-file assignments - as well, besides the sheer number of files in some datasets.

[landreev]

you end up with the same stuff in memory

As the end result, once all the entities are instantiated, yes... Would there be some initial memory premium on the jdbc driver side with findDeep though?