safe-global / safe-transaction-service / 19764860243

91%

master: 100%

push

github

Add denormalized safe_address to InternalTxDecoded for faster pending tx queries (#2719)

## Problem

The `process_decoded_internal_txs_for_safe_task` was causing database performance issues in production, with queries reaching 276ms average latency at 40.8 calls/sec, causing RDS to hit 100% CPU.

The problematic query was:
```sql
SELECT ... FROM history_internaltxdecoded 
INNER JOIN history_internaltx ON (...)
INNER JOIN history_ethereumtx ON (...)
WHERE NOT processed AND _from = '...'
ORDER BY CASE WHEN function_name = 'setup' THEN 0 ELSE 1 END, ...
```

**Root cause**: Filtering by `internal_tx._from` (Safe address) required a JOIN with `history_internaltx`, and the existing partial index `WHERE processed=False` couldn't help with the address filtering.

## Solution

Added a denormalized `safe_address` field to `InternalTxDecoded` that copies `internal_tx._from`, along with a partial index for efficient filtering:

```python
safe_address = EthereumAddressBinaryField()
```

# Partial index for unprocessed records only
```python
Index(name="history_decoded_pending_idx", fields=["safe_address"], condition=Q(processed=False))
```

### Changes
- **Model**: Added `safe_address` field with partial index
- **QuerySet methods**: `for_safe()`, `pending_for_safe()`, `safes_pending_to_be_processed()` now filter directly on `safe_address`
- **Indexers**: All places creating `InternalTxDecoded` now set `safe_address=internal_tx._from`
- **Migrations**: Split into two for minimal downtime:
  - `0096`: Adds field + backfills NOT processed records + creates index (fast, ~30s)
  - `0097`: Backfills processed records (slow, ~5-10min on 4M rows, but non-blocking)
- **Admin**: Added `safe_address` to readonly fields and search fields

### Query Improvement

**Before**: 3-table JOIN, filtering on joined table
WHERE internal_tx._from = '...' AND NOT processed**After**: Direct filter on indexed column
WHERE safe_address = '...' AND NOT processed
-- Uses partial index hist... (continued)

8 of 8 new or added lines in 4 files covered. (100.0%)

8489 of 9362 relevant lines covered (90.68%)

0.91 hits per line