A partial index that saved 40 GB
97% of rows were "settled". We only ever queried the other 3%.
Our status index covered 1.2 billion rows. Replacing it with a partial index on pending rows shrank it by 40 GB and made the hot query 9x faster.
CREATE INDEX CONCURRENTLY idx_txn_pending
ON transactions (created_at)
WHERE status = 'pending';