/total_wallet_addresses scans all 2.6M account_parameters rows on every call: keep a daily aggregate table
Problem
/total_wallet_addresses runs COUNT(*) … GROUP BY date_trunc(created) over all 2.6M hafbe_app.account_parameters rows on every call, plus a second count for the baseline. On a full-scale node it takes 228–235 ms at p50 and up to 959 ms at p95, for yearly, daily or any granularity. The default range is the full history, so an index on created doesn't help: the whole table is still read.
Change
- Add a daily aggregate table, e.g.
hafbe_app.wallet_stats_by_day(day DATE PRIMARY KEY, created INT NOT NULL). It has about 3,900 rows, the same pattern astransaction_stats_by_day. - Maintain it in block processing: one upsert per block that creates accounts, which is a few per block. Register it with the context so fork rollback reverts it.
- Backfill it at the end of massive sync and in the upgrade path from
account_parameters. get_total_wallet_address_aggregationreads the table, rolling up to month or year astransaction-statisticsdoes, and gives the same output as today.
Acceptance
- Responses are byte-identical to today's for yearly, monthly and daily, both directions, and a bounded range.
- A regression check shows that the table equals the aggregate over
account_parametersafter sync, after live blocks and after a fork rollback. - Expected: about 230 ms down to 1–3 ms, like
/transaction-statisticsfor the same output shape. Write cost: a few upserts per block and a few hundred KB of disk. - Install, reinstall, upgrade and uninstall paths pass.