So I've never downloaded any add on content for a game. I'm wondering how it works?

Do you apply a patch to any save game of your choice?

or

Once downloaded is it automatically part of every save game?

Can you only apply it to a single save game?

It's more like a patch, it's a plugin that's active on all saves once you install it.

It's under add-ons

The content is added to the main game, so every save file, character or new game will always have the DLC content.

edit: At least with Bethesda games. Some games add a menu option with DLC as they are considered stand alone. Dragon Age: Origins as an example had DLc that allowed you to play as a Darkspawn or one of the companions in a new series of standalone quests.

Whenever it's downloaded into your current game, start playing and you'll likely get a pop-up telling you it's live and a new quest added to your menu directing you to how and where the new stuff starts.