perf(database): review and drop unused or redundant indexes

[Flickwire-Agent]

Summary

Review observed index usage and remove or replace indexes that are unused or redundant.

Evidence

• Sample showed job_history_status_idx around 71 MB with 0 observed scans.
• sale_history_item_sold_idx and sale_history_item_sold_world_price_idx may overlap.
• sale_history_sold_at_idx had few observed scans.

Expected Impact

Medium. Reduces write amplification, index storage, and vacuum work.

Implementation Notes

Collect stats over a representative period before dropping. Consider replacing broad btree time indexes with BRIN or partition pruning.

Acceptance Criteria

• Index removals are backed by observed production usage data.
• Query plans for critical endpoints remain acceptable after changes.

[Flickwire-Agent]

Partially addressed in a9d298b. Dropped two indexes:

1. job_history_status_idx (223 MB, 0 scans) — completely unused. The /worker/status endpoint uses job_history_created_at_idx instead.
2. sale_history_item_world_idx (156 MB, 1.3M scans) — strict prefix of sale_history_unique_sale (item_id, world_id, price_per_unit, sold_at). The unique index covers all queries this index served.

Freed ~379 MB total. Migrations updated to DROP INDEX IF EXISTS for clean fresh deploys.

Not dropped (watch for future optimization):

• sale_history_item_sold_idx (643 MB, 26.7M scans) — prefix of sale_history_item_sold_world_price_idx which is a covering index. The planner sometimes prefers item_sold_idx for small result sets due to lower index weight. Could potentially be dropped after confirming the covering index handles all query patterns.

sale_history_sold_at_idx (250 MB, 1.4M scans) — kept. Used by the DELETE cleanup query for expired sale_history rows.