Performance: Add pagination and virtual scrolling for high-transaction wallets

[schjonhaug]

Problem

Wallets with 1,000+ transactions will hit significant performance bottlenecks across multiple layers. This was identified through a code-level analysis of the API, frontend, and backend sync paths, followed by concrete performance testing on regtest.

Empirical Testing

• 1,000 tx on M4 Pro MBP: Scrolls smoothly, no noticeable lag. Everything works fine.
• Conclusion: The system works well up to ~1,000 tx on fast hardware. The real question is where it breaks on slower devices and at higher tx counts.

Realistic User Profiles

User Type	Expected Transactions	Tier
New HODLer	10–100	Personal
Active user (2–3 years)	100–500	Personal
Bitcoin OG (10+ years)	1,000–10,000	Team
Uncle Jim (family/friends)	5 wallets × 100–1,000 each	Team
Power user / small business	5,000–50,000	Team
Exchange hot wallet	500,000–2,000,000+	Not a target user

Target: Support up to ~50,000 tx comfortably. This covers every non-exchange user.

Risk Levels by Transaction Count

Count	Risk	Impact
< 1,000	Low	Confirmed fine on fast hardware (M4 Pro)
1,000–5,000	Medium	May lag on mobile, needs testing
5,000–10,000	High	Slow page loads, large poll payloads
10,000+	Critical	Data silently truncated by 10K hard limit
50,000+	Broken	Sync hangs due to O(n²) algorithms

What Breaks First (in order)

1. Silent data truncation — 10K hard limit means users never see older transactions
2. Backend sync hangs — O(n²) CPFP/RBF detection blocks for minutes at 50K+ tx
3. API response time — N+1 notification queries: 10K separate SQLite queries per detail request
4. SQLite sort performance — No index on ORDER BY expression, full table sort on every query
5. Frontend rendering — 10K+ DOM nodes cause jank on mobile
6. Network bandwidth — 5+ MB every 60 seconds on cellular

Bottleneck Details

Backend Sync — O(n²) Algorithms (Highest Risk)

CPFP detection (sync.rs): Uses .find() inside a loop over all BDK transactions. For each unconfirmed tx, linearly searches all 2M transactions to find it, then checks its inputs against a map of unconfirmed outputs.

Outer loop: O(U) unconfirmed transactions
  Inner .find(): O(N) all transactions
Total: O(U × N)

• At 50K tx with 1% unconfirmed: 500 × 50K = 25M ops (~25ms) ✅
• At 2M tx with 1% unconfirmed: 20K × 2M = 40B ops (~40 seconds) ❌

RBF conflict detection (sync.rs): Same pattern — nested linear searches through all transactions for each conflicted txid, then iterating all canonical transactions to find shared inputs.

For each conflicted tx: O(C)
  Find in all_bdk_txs: O(N)
  For each canonical tx: O(N)
    Find in all_bdk_txs: O(N)
Total: O(C × N²)

Fix: Replace .find() loops with HashMap lookups → O(1) per lookup, total O(N).

SQLite Queries — N+1 Problem and Missing Index

N+1 notification queries (metadata/transaction.rs): The detail endpoint runs a separate SELECT on notification_logs for each of the 10,000 returned transactions. Even with indexes, 10K round-trips to SQLite adds up to 50–100 seconds.

Fix: Single JOIN query or batch IN clause.

Missing composite index: The main query sorts by ORDER BY COALESCE(confirmed_at, first_seen_at) DESC but there's no index covering this expression. SQLite must sort all matching rows in memory before applying LIMIT.

• At 10K rows: ~1ms (fine)
• At 50K rows: ~10–50ms (fine)
• At 2M rows: 200–800ms (noticeable)

Fix: Add expression index or denormalized sort column.

API Layer — No Pagination

• GET /api/wallets/{checksum}/detail returns up to 10,000 transactions with no pagination support
• At 10K tx: ~5.5 MB JSON response
• No ETags, no If-Modified-Since, no incremental updates
• Every 60-second poll re-downloads the full payload

Frontend — No Virtual Scrolling

• transactions.tsx: Direct .map() renders all transactions as DOM nodes
• No react-window or @tanstack/react-virtual
• Aggressive 1-second polling during sync downloads full history each time
• Empirically confirmed: 1,000 tx works fine on M4 Pro, need to test on mobile/low-end devices

Proposed Mitigations (prioritized)

Phase 1: Backend sync fixes (unblocks 50K+ tx)

1. Replace O(n²) with HashMap lookups in CPFP and RBF detection — the single most important fix
2. Fix N+1 notification query with a single JOIN
3. Add composite index for the ORDER BY sort expression

Phase 2: API pagination (unblocks 10K+ tx visibility)

4. Server-side cursor pagination on (confirmed_at, txid) with configurable page size
5. Incremental polling — since_timestamp parameter to fetch only new/changed transactions

Phase 3: Frontend performance (better mobile experience)

6. Virtual scrolling with @tanstack/react-virtual for DOM efficiency
7. Separate notification endpoint — fetch notification logs on-demand per transaction

Phase 4: Polish

8. Response compression (gzip/brotli)
9. ETags / conditional requests to skip unchanged responses

Key Code Paths

Path	Issue
backend/src/sync.rs — CPFP detection	O(N²) .find() loops, needs HashMap
backend/src/sync.rs — RBF conflict detection	O(C×N²) nested iteration, needs HashMap
backend/src/metadata/transaction.rs:104-183	10K limit, N+1 notification queries
backend/src/electrum.rs:59-100	O(N) scan per sync (acceptable)
backend/src/handlers/wallet.rs:709-884	No pagination parameter
frontend/src/hooks/useWalletDetail.ts	Full re-download every poll
frontend/src/hooks/useWalletsList.ts:75-79	1-second polling during sync
frontend/src/components/transactions.tsx:227-327	Direct .map() with no virtualization

Biggest Known Bitcoin Mainnet Wallets (for testing)

Address	Description	Transactions
bc1qm34lsc65zpw79lxes69zkqmk6ee3ewf0j77s3h	Binance cold wallet	~2,156,000
1LAnF8h3qMGx3TSwNUHVneBZUEpwE4gu3D	Huobi hot wallet	~536,000
1A1zP1eP5QGefi2DMPTfTL5SLmv7DivfNa	Satoshi's Genesis address	~60,000

These are single addresses, not full wallets — a descriptor wallet watching an exchange xpub would have significantly more transactions across all derived addresses.

Regtest Stress Testing

Use ./dev.sh create-stress-wallet <count> to create wallets with arbitrary transaction counts for testing:

./dev.sh create-stress-wallet 1000    # Quick test (~3 min)
./dev.sh create-stress-wallet 5000    # Medium test (~15 min)
./dev.sh create-stress-wallet 10000   # Stress test (~30 min)