/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_count column to an existing per-account table that block processing already updates (hafbe_app.account_parameters or similar), or a new (account_id PK, ops_count) table, maintained from the block's account_operations rows 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_count reads it with a primary-key lookup. The result must equal account_op_seq_no + 1 of 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 compares ops_count with account_op_seq_no + 1 for a sample of accounts after sync, after live blocks and after a fork rollback.
  • On a fresh DB session, /accounts/{a} no longer opens hafd.account_operations. Check by comparing open files or pg_stat_io before 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)