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

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 #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

• #9763 (versions API slow with many files/versions — same multiplicative-fetch family)
• #8928 (dataset with 75K files → 500s)
• #7803, #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.

Start with DatasetPage.init() and DatasetServiceBean.findDeep(), then inspect the EclipseLink join-fetch hints for file-related collections, especially roleAssignments and fileAccessRequests. Compare the approach with the paginated file APIs from #9763 and reproduce the large-dataset query shape described in the report. Done means page views no longer create multiplicative result sets or load all per-file grants into heap, with regression coverage for grant-heavy datasets.