Index larger than table
Your task
Postgres. A frequently-updated sessions table (every request bumps last_seen) has a btree index on last_seen. Lookups on that index have slowly degraded over months from 1 ms to 60 ms, and the index is now larger than the table itself. pg_stat_user_indexes shows heavy usage; autovacuum runs. Table row count is stable. CPU/IO fine. The column updated each request is the indexed one. Triage why the index keeps bloating and give the durable fix.
It covers
- Index bloat
- Hot update
- Heap only tuples