Decathlon / Decathlon/internal-developer-platform
[Perf][P1] Cut ORM/REST serialization overhead (10–30x the actual SQL cost; root cause of concurrency ceiling)
- Dominant language
- Java
- Stars
- 9
- Forks
- 0
- Avg merge
- 3d 1h
- Merged PRs (30d)
- 5
Description
## Context
From `idp-v2-vs-app-referential-analysis.md`: at the current data volume (~4,000 entities, ~6,500 relationships), IDP v2's REST latency is **10–30x its own raw-SQL cost**. This is the dominant, most actionable bottleneck today — bigger than the EAV/schema-shape question (see companion P3 issue).
## Evidence
| Test | REST API | Raw SQL | Ratio |
|---|---|---|---|
| Get entity by ID + 145 relations | ~17–20 ms | ~0.17 ms | ~100x (though most of this maps to serialization, not SQL) |
| Filtered list, low selectivity (18/953 match) | ~28–30 ms | ~3.1 ms | ~10x |
| Filtered list, high selectivity (1,042/3,972 match) | ~77–90 ms | ~4.0 ms | ~20x |
| Graph traversal depth 1–3 | ~27–37 ms, roughly flat | ~2.9 ms (raw CTE) | ~10–13x |
Concurrent load testing (k6, VU=1/10/25/50) confirms this overhead is also the **direct cause of a throughput ceiling** under load:
- IDP v2 throughput plateaus around 10–25 concurrent users then flatlines/declines while p50 latency keeps climbing linearly (e.g. get-by-id: 68→198→180→183 req/s while latency grows 14→47→127→258 ms).
- app-referential (denormalised model) scales throughput ~linearly with concurrency over the same range (109→365→519→604 req/s) on the same hardware, same HikariCP pool size (10 connections both sides).
- Root cause is not pool size (confirmed identical config) — IDP v2 holds each connection open longer per request (more Hibernate hydration + JSON serialization work), so the same pool saturates sooner.
At VU=50, IDP v2 is 3–10x slower and 3–9x lower throughput than app-referential across scenarios; worst case p95 hits ~981 ms vs app-referential's 437 ms for the same high-selectivity filtered list.
## Recommended fix
1. Profile and fix Hibernate fetch plans in `PostgresEntityGraphAdapter` / `PostgresEntityAdapter` — tune batch sizes and fetch-join hints on relation/property collections instead of relying purely on the current N+1-safe-but-chatty separate-query strategy.
2. Replace full JPA entity graph + Jackson mapping with projection DTOs (JPQL constructor expressions or native-query-to-record mapping) for read paths — skips an entire entity→DTO copy step.
3. Offer a lighter serialization path for list/search endpoints that don't need full property bags.
## Priority
P1 — highest-leverage fix independent of any schema change; also the direct explanation for the concurrency/throughput ceiling found under load testing, so it should be treated as a scalability fix, not just a latency polish item, before IDP v2 is exposed to multi-user or high-QPS (e.g. LLM-driven) production traffic.
## Related
Companion to P0 (#130, relation opt-in — already reduces per-item serialization cost for list endpoints, but the underlying ORM/mapping overhead documented here is broader and applies to get-by-id and graph endpoints too).
Contributor guide
Research direction
Start with PostgresEntityGraphAdapter and PostgresEntityAdapter, then profile the ORM hydration and JSON serialization paths described in the issue. Run the existing k6 concurrency scenarios and compare the REST and raw-SQL timings. Done means the dominant overhead is identified and the selected read-path improvements are validated against latency and throughput without changing endpoint behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, postgresql
- Domain
- api, backend, databases, performance
- Issue type
- Refactor
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Active
- Clarity
- Needs clarification
- Newbie friendliness
- 28/100