perf: /v1/fiat and /v1/asset hit the DB on every call for a 24-row table that is already cached elsewhere

[TaprootFreak]

Summary

FiatController.getAllFiat and AssetController.getAllAsset each issue one uncached query per request against transaction_specification — a 24-row table whose contents change rarely. In the same controller, the two neighbouring lookups are already cached. The uncached one accounts for essentially the entire response time.

Evidence

Production traces (Tempo), GET /v1/fiat, four slowest of the sample:

total     DB time   span
 364 ms    361.5 ms  pg.query  SELECT "TransactionSpecification"…
2180 ms   2178.1 ms  pg.query  SELECT "TransactionSpecification"…
1491 ms   1489.3 ms  pg.query  SELECT "TransactionSpecification"…
1318 ms   1277.8 ms  pg-pool.connect        ← waiting for a free pool connection

Every trace contains exactly one DB span. The other two lookups never appear — because they are cached:

call in FiatController.getAllFiat	cached?
fiatService.getAllFiat() → fiatRepo.findCached('all')	yes
countryService.getAllCountry() → countryRepo.findCached('all')	yes
repoFactory.transactionSpecification.find()	no

AssetController.getAllAsset (asset.controller.ts:38-39) uses the same uncached specRepo.find().

Row counts in production:

transaction_specification    24 rows
fiat                         24 rows   (2 buyable)
country                     250 rows   (53 dfxEnable)

Why the query is slow

It is not. Selecting 24 rows takes well under a millisecond. The time is queueing, not work — the API runs as a single Node process whose event loop measured 85–93 % of one core on dfxprd, so every round trip waits for the loop. The same effect is visible elsewhere: an identical DB connect takes 6 ms from a fresh process and 2850 ms from inside the running API.

That is precisely why removing the round trip helps: the fix is not "make the query faster", it is "stop making 21 unnecessary round trips per minute across a saturated event loop".

Volume

Measured over one hour of production access logs (7'982 requests total, complete window — not a sample):

endpoint	calls/hour	calls/min	mean	share of total processing time
GET /v1/fiat	744	12.4	502 ms	13.8 %
GET /v1/asset	521	8.7	361 ms	6.9 %

Together 20.7 % of all processing time, for data that is nearly static.

Suggested fix

TransactionSpecificationRepository extends BaseRepository. Extending CachedRepository instead (src/shared/repositories/cached.repository.ts, default CacheItemResetPeriod.EVERY_5_MINUTES) makes findCached available, matching what FiatRepository and CountryRepository already do in the same request path.

Both call sites then become specRepo.findCached('all'). Anything that writes specifications must call invalidateCache(), as FiatService.updatePrice already does.

Worth deciding explicitly: whether a 5-minute staleness window is acceptable for transaction specifications. If not, CacheItemResetPeriod offers shorter windows — even 30 seconds would remove ~97 % of these round trips.

Ruled out

Fiat.ibanCountryConfigObject (fiat.entity.ts:53) runs JSON.parse inside a getter, and isIbanCountryAllowed calls it once per country per fiat — a plausible-looking hot path. Measured and dismissed: only one fiat row has a non-null ibanCountryConfig, and it is 21 characters long. That is ~53 parses of a tiny string per request, i.e. microseconds. Not the cause — noted here so nobody spends time on it again.

Not verified

Whether the 5-minute default is safe for this table has not been checked against how specifications are edited in practice. The expected improvement follows from removing the round trip; it was not measured against a patched build.