The term comes from orchestra seating:
Figurative meaning: A subordinate role, supporting someone else's lead. Can be negative ("stuck playing second fiddle") or positive ("happy to play second fiddle to a great leader").
The metaphor extends to any hierarchy where one person takes the primary part and another provides essential but less visible support.