Index larger than table

On-call incidentHard40 min

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