perf(documents): move density aggregation into SQL when documents > 50k #481

New Issue

marcel · 2026-05-08T11:39:30+02:00

marcel commented

2026-05-08 11:39:30 +02:00

Context

The /api/documents/density endpoint (issue #385) currently groups by month in JVM memory:

List<LocalDate> dates = documentRepository.findAll(spec).stream()
        .map(Document::getDocumentDate)
        .filter(Objects::nonNull)
        .toList();
// ... TreeMap merge in Java ...

This loads every matching Document row (with eager joins for sender/receivers/tags) and groups in Java. Acceptable today (~5k rows, <50ms). The trade-off and threshold are documented inline:

backend/src/main/java/org/raddatz/familienarchiv/document/DocumentService.java:144
TODO: revisit when documents > 50k — at that scale move the aggregation
into SQL (GROUP BY TO_CHAR(meta_date, 'YYYY-MM')) and accept that the
criteria/specification surface needs a parallel native-query path.

This issue exists so the threshold lives outside the code comment, where comments age into invisibility (per @Markus's PR #478 review and @Elicit OQ-385-1).

Acceptance criteria

Density aggregation issued as a single SQL GROUP BY TO_CHAR(meta_date, 'YYYY-MM') (or date_trunc) query.
Predicate composition still reuses buildSearchSpec filters (sender, receiver, tag, status, FTS, tagOp); the implementation may take a parallel native-query / EntityManager.createNativeQuery path that mirrors the Specification predicates.
Re-add the idx_documents_meta_date index that was dropped in commit 360db1ae once SQL aggregation lands — at that scale the index pays.
p95 latency for the endpoint stays under 200ms at 50k documents.
Existing test pyramid (service unit, controller slice, integration with Testcontainers Postgres) updated to cover the SQL path.

Triggering signal

Re-evaluate when SELECT COUNT(*) FROM documents > 50_000 or when p95 of GET /api/documents/density crosses 200ms in production telemetry, whichever comes first.

Out of scope

Caching layers in front of the endpoint (Tobi: defer until shared cache exists)
Extracting DocumentSearchCriteria (Markus round-1 #2: defer until a third aggregation endpoint lands)

References

PR #478 — feat(documents): timeline date-range filter with density bars (#385)
@Markus round-1 review concern #1, round-3 suggestion #1
@Elicit OQ-385-1

## Context The `/api/documents/density` endpoint (issue #385) currently groups by month in JVM memory: ```java List<LocalDate> dates = documentRepository.findAll(spec).stream() .map(Document::getDocumentDate) .filter(Objects::nonNull) .toList(); // ... TreeMap merge in Java ... ``` This loads every matching `Document` row (with eager joins for sender/receivers/tags) and groups in Java. Acceptable today (~5k rows, <50ms). The trade-off and threshold are documented inline: ``` backend/src/main/java/org/raddatz/familienarchiv/document/DocumentService.java:144 TODO: revisit when documents > 50k — at that scale move the aggregation into SQL (GROUP BY TO_CHAR(meta_date, 'YYYY-MM')) and accept that the criteria/specification surface needs a parallel native-query path. ``` This issue exists so the threshold lives outside the code comment, where comments age into invisibility (per @Markus's PR #478 review and @Elicit OQ-385-1). ## Acceptance criteria - Density aggregation issued as a single SQL `GROUP BY TO_CHAR(meta_date, 'YYYY-MM')` (or `date_trunc`) query. - Predicate composition still reuses `buildSearchSpec` filters (sender, receiver, tag, status, FTS, tagOp); the implementation may take a parallel native-query / `EntityManager.createNativeQuery` path that mirrors the Specification predicates. - Re-add the `idx_documents_meta_date` index that was dropped in commit `360db1ae` once SQL aggregation lands — at that scale the index pays. - p95 latency for the endpoint stays under 200ms at 50k documents. - Existing test pyramid (service unit, controller slice, integration with Testcontainers Postgres) updated to cover the SQL path. ## Triggering signal Re-evaluate when `SELECT COUNT(*) FROM documents > 50_000` or when p95 of `GET /api/documents/density` crosses 200ms in production telemetry, whichever comes first. ## Out of scope - Caching layers in front of the endpoint (Tobi: defer until shared cache exists) - Extracting `DocumentSearchCriteria` (Markus round-1 #2: defer until a third aggregation endpoint lands) ## References - PR #478 — feat(documents): timeline date-range filter with density bars (#385) - @Markus round-1 review concern #1, round-3 suggestion #1 - @Elicit OQ-385-1

marcel added the P3-later refactor labels 2026-05-08 11:39:35 +02:00

marcel referenced this issue from a commit

2026-05-08 11:55:19 +02:00

docs(documents): link density TODO to follow-up #481 (#385)

marcel referenced this issue

2026-05-08 11:55:48 +02:00

feat(documents): timeline date-range filter with density bars (#385) #478

marcel referenced this issue

2026-05-08 12:22:47 +02:00

feat(documents): timeline date-range filter with density bars (#385) #478

marcel referenced this issue

2026-05-08 12:23:24 +02:00