Sydnee nightly — performance audit — 2026-04-25 P0 findings: 0 P1 findings: 2 Risks: 2 - Area: Performance (Saturday theme) - Bugs found (P0 / P1): 0 / 2 - Risks noted: 2 --- Full report (dev branch): https://github.com/kanex1/sydnee.signals/blob/dev/docs/audit_2026-04-25_performance.md Reply FROM [email protected] to [email protected] to request fixes, e.g.: "code_task on sydnee-signals-dev: apply fix for the P0 about RVOL threshold in bot.py" Sydnee Agent will propose + you APPROVE (or plain 'approve') + auto-push to dev. --- Full audit below (first 12 KB) --- # Nightly Audit 2026-04-25 — PERFORMANCE ## Summary - Area: Performance (Saturday theme) - Bugs found (P0 / P1): 0 / 2 - Risks noted: 2 --- ## Findings ### BUG [P1]: `market_indices` grows without pruning; `DISTINCT ON` query degrades **File:** `core/database.py:826-836` (write), `core/database.py:842-846` (read) **Evidence:** ```python # database.py:832 — plain INSERT, no ON CONFLICT, no DELETE: INSERT INTO market_indices (symbol, price, change, change_pct, trends_json) VALUES %s # database.py:843 — read path, no composite index: SELECT DISTINCT ON (symbol) symbol, price, change, change_pct FROM market_indices ORDER BY symbol, timestamp DESC ``` `snapshot_indices()` fires every 300 ticks (~5 min) from the main loop (`bot.py:14166-14179`). With 5 index symbols, that is **300 new rows/day × 5 trading days = 1,500 rows/week**. No DELETE, no TRUNCATE, no cleanup function exists anywhere in `database.py`. `get_latest_indices()` uses `DISTINCT ON (symbol) … ORDER BY symbol, timestamp DESC`. The schema (`core/schema.sql:138-139`) has only two separate indexes: `idx_market_indices_timestamp(timestamp)` and `idx_market_indices_symbol(symbol)`. There is **no composite index on `(symbol, timestamp DESC)`**, so PostgreSQL cannot satisfy the `DISTINCT ON` sort with an index scan; it falls back to seq-scan + sort as the table grows. Same pattern applies to `positions`: `snapshot_positions()` (database.py:786-794) does plain INSERT every 60 ticks (~60 s) per open position, also with no cleanup. With 5 open positions × 390 RTH minutes × 5 days = **9,750 new rows/week**, growing indefinitely. **Impact:** As `market_indices` grows, `get_latest_indices()` — called on every `/api/nav` request when live index data is unavailable (`bot.py:10342`) — performs a progressively slower full-table sort. `positions` is currently write-only (no read path found) so its main impact is disk bloat, but both tables will cause operational headaches approaching real-money go-live. **Fix:** ```sql -- Add missing composite index for DISTINCT ON query: CREATE INDEX IF NOT EXISTS idx_market_indices_sym_ts ON market_indices(symbol, timestamp DESC); -- Add periodic prune to keep rolling 24 h of each table: DELETE FROM market_indices WHERE timestamp < NOW() - INTERVAL '24 hours'; DELETE FROM positions WHERE timestamp < NOW() - INTERVAL '24 hours'; ``` Call the DELETE from `_tick_counter % 3600 == 0` (hourly) in `bot.py`, or add a `prune_telemetry()` method to `Database` and call it there. --- ### BUG [P1]: `_polygon_bar_feed_loop` hammers Polygon with per-symbol uncached 1D bar fetches **File:** `bot.py:2517-2556`, `polygon_client.py:77-155` **Evidence:** ```python # bot.py:2517-2556 while not self._poly_bars_stop.is_set(): for sym in list(self.sym_states.keys()): # N symbols df = pc.fetch_bars(sym, 1, "1 D") # 1 HTTP request per symbol ... self._poly_bars_stop.wait(5.0) # then sleep 5 s ``` `fetch_bars()` in `polygon_client.py:91-149` has **no caching** — every call makes a fresh `requests.get` to Polygon's aggregates API. With 20 symbols on the watchlist, the bar-feed thread makes **4 requests/second** during the inner loop. The concurrent `_polygon_price_loop` (bot.py:2430) adds another **3.3 batch requests/second** via `get_snapshots_batch`. Combined peak rate: **~7 req/s**, which exceeds Polygon Starter tier's 5 req/s limit. When the 429 rate-limit triggers, `fetch_bars` backs off up to 3s per retry (`polygon_client.py:125`) and the entire bar-feed thread stalls — no new bars are processed for any symbol until the retry loop clears. On a 20-symbol watchlist this means **signal evaluations can be delayed 30+ seconds** during peak market hours while Polygon recovers. CLAUDE.md confirms `USE_POLYGON_BARS=true` on **both dev and prod** as of 2026-04-19, so this affects both environments every trading day. **Impact:** Bar-feed stall under rate-limit pressure → `_evaluate_symbol` not called → open positions not evaluated for exits → potential for missed stop triggers during fast market moves. **Fix (minimal, no logic change):** Add a simple per-symbol response cache keyed on `(symbol, "1D", floor_to_minute)`: ```python # polygon_client.py — add module-level cache: _bar1d_cache: dict = {} # sym -> (df, fetched_at) _BAR1D_TTL = 60 # seconds — new 1m bars arrive at most every 60s def fetch_bars_1d_cached(symbol: str) -> Optional[pd.DataFrame]: now = time.time() if symbol in _bar1d_cache: df, ts = _bar1d_cache[symbol] if now - ts < _BAR1D_TTL: return df df = fetch_bars(symbol, 1, "1 D") _bar1d_cache[symbol] = (df, now) return df ``` Replace `pc.fetch_bars(sym, 1, "1 D")` at `bot.py:2523` with `pc.fetch_bars_1d_cached(sym)`. This reduces bar-feed requests from N/5s to ≤1 per symbol per 60 s — a 12× reduction, well within Polygon limits. --- ### RISK: `bot_activity` table grows without pruning **File:** `core/database.py:579-588` **Evidence:** ```python # database.py:584 — plain INSERT, no TTL: INSERT INTO bot_activity (event_type, symbol, message, details_json) VALUES (%s,%s,%s,%s) ``` 176 call sites for `_log_activity` in `bot.py` (grep count). Every gate evaluation, order fill, signal change, and diagnostic message is persisted. No DELETE, TRUNCATE, or prune function exists. `db.info()` at database.py:1892 tracks the count for display but takes no action. On a busy trading day with 20+ symbols × frequent gate logs (entry rejected, OBV check, RV detail, etc.), `bot_activity` can accumulate **10,000–50,000 rows/day**. Over the 2-week run-up to 2026-05-01 real-money go-live, this could reach 200k–500k rows. The `SELECT * FROM bot_activity … ORDER BY timestamp DESC LIMIT %s` query (database.py:638-640) uses the implied `idx_bot_activity_timestamp` for the ORDER BY, but the payload includes `details_json JSONB` which inflates row size and page count as the table grows. **Fix:** Add `DELETE FROM bot_activity WHERE timestamp < NOW() - INTERVAL '7 days'` to the same hourly cleanup hook proposed for P1 #1 above. --- ### RISK: `pd.concat(…).iloc[-500:]` per bar close allocates O(N×TF) DataFrames **File:** `bot.py:2448, 2469, 2584` **Evidence:** ```python # bot.py:2448 (Polygon bar path): ss.bars[1] = pd.concat([ss.bars[1], new_row_1m]).iloc[-500:] # bot.py:2584 (IBKR tick path): ss.bars[tf] = pd.concat([ss.bars[tf], new_row]).iloc[-500:] ``` `pd.concat([existing_df, single_row_df])` creates a full copy of `existing_df` (up to 500 rows × 5 float64 columns = ~20 KB). `.iloc[-500:]` then creates another view/copy. For 20 symbols × 8 timeframes = **160 full-DataFrame allocations per signal-bar minute**, sustained 24×5. Memory is bounded (500 × 40 bytes × 5 cols = 100 KB per DataFrame), but the allocation rate generates GC pressure. Under Python's GC, 160 × 100 KB = ~16 MB of short-lived objects allocated per minute, triggering minor collections that add millisecond jitter to the bar-event callback thread. Not a correctness risk; classify as RISK because the pattern could contribute to latency spikes observed on high-symbol-count backtests. The fix (pre-allocate a ring-buffer or use `pd.concat` only when at capacity) is non-trivial and should wait for post-go-live. --- ## OK (checked, working or recently fixed) - **`/api/status` caching** (bot.py:12970-13001): 5-second background precompute thread. API requests always return from cache; no blocking build per request. ✓ - **In-memory `_activity` list** (bot.py:663-664): auto-pruned at 200 entries. Not a memory leak. ✓ - **Polygon price snapshot batching** (polygon_client.py:162-204): `get_snapshots_batch` issues a single HTTP call for all symbols regardless of watchlist size. Efficient use of the API. ✓ - **RVOL gate caching** (polygon_client.py:411-414): 15-second TTL per symbol. Short enough for near-real-time entry gates, long enough to prevent per-candidate hammering. Cache hit check at `_process_entry_candidates:4365` is correct. ✓ - **EOD trim N+1 query** (bot.py:3037-3044): fetches DB only on first evaluation per trade, then caches in `t._eod_trim_count`. Not a hot path. ✓ - **`bar_data` insert** (database.py:805-809): uses `ON CONFLICT DO NOTHING` on the `UNIQUE(symbol, timeframe, timestamp)` constraint. No duplicate rows. ✓ - **Watchdog thread** (bot.py:2369-2395): 30s sleep is intentional for a monitoring-only thread. Stall threshold is configurable, not a hot path. ✓ - **Polygon bar feed 429 handling** (polygon_client.py:122-126): retries with exponential backoff up to `retries=2`. Not ideal under sustained rate-limit (see P1 #2), but failure path is safe (returns None → bar skipped, not crash). ✓