It sounds like you’re dealing with a significant performance bottleneck due to the volume of data being loaded in your relationship mapping. To address this efficiently, here are a few suggestions:

Use Lazy Loading: Ensure that your @ManyToMany relationship is set up with fetch = FetchType.LAZY. This way, the associated data will only be loaded when explicitly accessed. For example:

@ManyToMany(fetch = FetchType.LAZY)private Set<ArticoloPolicySconto> articoloPolicySconti;

This reduces the risk of fetching unnecessary data during application startup.

Custom Querying with JPQL/Criteria API: Instead of relying on automatic loading of relations, you can create custom repository methods to fetch only the data relevant to the logged-in user. For example:

@Query("SELECT aps FROM ArticoloPolicySconto aps JOIN aps.users u WHERE u.id = :userId")List<ArticoloPolicySconto> findPolicyByUserId(@Param("userId") Long userId);

This approach minimizes the data fetched and processed at any time.

Batching or Pagination: If fetching data still results in performance issues due to size, consider using batching or pagination with your queries to fetch smaller chunks of data:

PageRequest pageRequest = PageRequest.of(0, 100); // Example for pagination
List<ArticoloPolicySconto> policies = repository.findPolicyByUserId(userId, pageRequest);

Indexing in PostgreSQL: Ensure that your database tables have appropriate indexes on fields used in your queries (e.g., id_user, id_articolo, politica_sconto_id). This will significantly enhance query performance.

Additionally, if you need custom help to optimize your system, Elogic Commerce just happens to specialize in e-commerce solutions, including optimizing database relationships and performance in complex systems like yours. Feel free to contact us if you'd like to consider working together - we've helped companies solve similar challenges in the past)

Best.