/accounts/{a} first call still ~18 ms: get_account_ops_count opens all of hafd.account_operations' 830 segment files on each new backend; keep a per-account op count
Problem
After #160 (closed), /accounts/{a}, the block explorer's busiest data endpoint (about 1,090 requests/h behind the API cache), still costs about 18 ms on the first call of each new DB session. Calls 2–5 now take 1.2–1.5 ms, and steady state 0.8 ms. #160 (closed) measured where call 1 goes on a full-scale shape:
get_account_ops_count: 7.0 ms;- balance_tracker
get_account_balances: 5.0 ms (another repository; see the related balance_tracker issue); get_account_proxied_vsf_votes: 1.4 ms;- hafah
get_account_id: 1.2 ms; - the rest: under 1 ms each.
get_account_ops_count is not slow because of planning. It reads the newest row of hive.account_operations_view for the account (account_op_seq_no + 1). The first plan of any query against hafd.account_operations in a backend opens every 1 GB segment file of the table and of both its indexes (508 + 266 + 59 GB): the backend's open file descriptors go from 10 to 889. That is 4.9 ms on the first plan and 0.04 ms afterwards, and a generic plan pays it too. PostgREST reaches PostgreSQL through pgbouncer, so every new server connection pays it again.
Change
Stop touching hafd.account_operations in /accounts/{a}:
- Keep the account's operation count in the block explorer's own state. For example, add an
ops_countcolumn to an existing per-account table that block processing already updates (hafbe_app.account_parametersor similar), or a new(account_id PK, ops_count)table, maintained from the block'saccount_operationsrows during sync. - Measure the write cost on the full-scale node or a shape copy: rows touched per block, sync throughput.
- If an existing per-account table already carries an equivalent value, use it and add no table.
- Fill it at the end of massive sync and in the upgrade path, and register it with the context so fork rollback reverts it.
get_account_ops_countreads it with a primary-key lookup. The result must equalaccount_op_seq_no + 1of the newest account operation, and 0 or NULL as today for accounts without operations.- Measuring safely: use aidev's estimate commands or the slot's clone of the shared HAF. Never run
CREATE DATABASE … TEMPLATE shape_*(aidev#16266).
Acceptance
/accounts/{a}output is byte-identical to before on the tavern accounts. A regression check comparesops_countwithaccount_op_seq_no + 1for a sample of accounts after sync, after live blocks and after a fork rollback.- On a fresh DB session,
/accounts/{a}no longer openshafd.account_operations. Check by comparing open files orpg_stat_iobefore and after the first call. Its first-call DB time drops by about the 7 ms above. - The commit states the sync-time write cost measured.
- Quick, full and baseline slots pass.
Related-To: #160 (closed)