Convert hot-path entity queries to ID+findMultiple pattern to leverage 2LC

[stalep]

Problem

Several hot-path queries load ValueEntity and NodeEntity instances via native SQL (createNativeQuery(..., EntityClass.class)) or JPQL (createQuery(...)) which bypass the Hibernate second-level cache (2LC) entirely. Only em.find() and Session.findMultiple() check the 2LC before hitting the database.

The 2LC is configured and working:

• ValueEntity: @Cacheable @Cache(READ_ONLY), 10,000 entries
• NodeEntity: @Cacheable @Cache(READ_WRITE), 500 entries

But during bulk imports, the most frequently called queries bypass it, causing unnecessary DB round-trips for entities that are already cached.

The pattern

The ID+findMultiple pattern already exists in two places in ValueService:

// Query IDs only (skips BYTEA data column transfer)
List<Number> ids = em.createNativeQuery("""
    WITH RECURSIVE sourceRecursive (v_id) AS (...)
    SELECT distinct v.id FROM value v JOIN sourceRecursive sr ON v.id = sr.v_id
    WHERE v.node_id = :nodeId
""").setParameter(...).getResultList();

// Load entities via 2LC (cache hit for recently accessed values)
List<Long> longIds = ids.stream().map(Number::longValue).toList();
return em.unwrap(Session.class).findMultiple(ValueEntity.class, longIds);

This pattern:

1. Queries only IDs (no data column transfer over the wire)
2. Loads entities via findMultiple() which checks 2LC first
3. Issues a single batched SQL query for any cache misses

Hot-path queries to convert

Priority	Method	File:Line	Frequency	Current	Proposed
1	findMatchingFingerprint()	ValueService.java:380	N×M per upload (fingerprints × detectors)	SELECT * FROM value v ... via native SQL	Query IDs in CTE, findMultiple(), preserve order
2	getAncestor()	ValueService.java:413	Per detection calculation	SELECT v.* FROM value v ... via native SQL	Query ID, em.find()
3	JPQL re-fetch with sources	ValueService.java:564	Per activeNode × sourceValue	SELECT DISTINCT v FROM value v LEFT JOIN FETCH v.sources	Consider @BatchSize on sources or Hibernate.initialize()
4	getDependentNodes()	NodeService.java:253	Per cascade Work creation	SELECT DISTINCT n FROM node n LEFT JOIN FETCH n.sources	Query IDs, findMultiple(), batch-init sources
5	getDescendantValues(root)	ValueService.java:129	Per purge/delete	SELECT * FROM value v ... via native SQL	Use existing ID+findMultiple pattern (sibling method already does this)
6	ValueEntity.list(...) dependent values	ValueService.java:72	Per cascading delete	Panache list() generates JPQL	Query IDs, findMultiple()
7	ValueEntity.find("node.id",...).list()	ValueService.java:627	Per recalculation	Panache find().list()	Query IDs by node_id, findMultiple()

Additional findings

• Query cache is NOT enabled — no hibernate.cache.use_query_cache setting, no @QueryHint or setCacheable(true) anywhere in the codebase. Enabling the query cache could help for repeated identical queries (e.g., same fingerprint lookup), but would add invalidation complexity.

• 8 cold-path queries also bypass the 2LC (REST display, CLI, admin operations) but are low priority since they're not in the import hot path.

• ~20 scalar/DML queries (DELETE, UPDATE, COUNT, aggregation) correctly don't use the 2LC.

Implementation notes

Ordering: findMatchingFingerprint() returns results ordered by sortable (domain value). After converting to ID+findMultiple, the ordering from the ID query must be preserved. Approach: maintain a Map<Long, Integer> of ID→position from the ID query, then sort the findMultiple() results by position.

JOIN FETCH for sources: getDependentNodes() and the getDescendantValueByPath() re-fetch both use LEFT JOIN FETCH to eagerly load the sources collection. With findMultiple(), this association won't be initialized. Options:

• Use @BatchSize on the sources mapping (already present on ValueEntity via @BatchSize(size=100)) so Hibernate batch-loads sources on first access
• Call Hibernate.initialize(entity.sources) explicitly after findMultiple()
• Keep the JOIN FETCH query but use it only for the sources initialization pass (not entity loading)

Impact estimate

The highest-impact conversion is findMatchingFingerprint() (Priority 1). During a boot-time-verbose import, this query is called hundreds of times. Each call currently:

1. Executes a complex recursive CTE against PostgreSQL
2. Transfers full entity rows including the data column (BYTEA) over the wire
3. Hibernate constructs new entity instances, ignoring the 2LC

After conversion, step 2 transfers only IDs (tiny), and step 3 serves entities from the 2LC for any values that were recently processed. For change detection nodes that examine sliding windows of historical values, the same values are loaded repeatedly across consecutive uploads — the 2LC would serve these from cache instead of re-fetching from the DB.