the threat model is exactly the same for those two scenarios, bear with it and focus on something else :)

Yes, it can be done. Not to the point of deleting your key (that makes no sense - you need the key), but ssh-agent is what you want. Add it to your shell config and it will only ask to be unlocked once per however often you define.

I have this function defined and called:

function ssh-agent-setup() {
    # SSH agent
    pid_file="$HOME/.ssh/ssh-agent.pid"
    SSH_AUTH_SOCK="$HOME/.ssh/ssh-agent.sock"
    if [ -z "$SSH_AGENT_PID" ]
    then
      # no PID exported, try to get it from pidfile
      SSH_AGENT_PID=$(cat "$pid_file")
    fi

    if ! kill -0 "$SSH_AGENT_PID" &> /dev/null
    then
      # the agent is not running, start it
      rm "$SSH_AUTH_SOCK" &> /dev/null
      >&2 echo "Starting SSH agent, since it's not running; this can take a moment"
      eval "$(ssh-agent -s -a "$SSH_AUTH_SOCK")"
      echo "$SSH_AGENT_PID" > "$pid_file"

      >&2 echo "Started ssh-agent with '$SSH_AUTH_SOCK'"
    fi
    export SSH_AGENT_PID
    export SSH_AUTH_SOCK
}

ssh-agent-setup

This way it stores the unlocked key in memory until the end of the session.

The basic concept is the same. The URL you provide is specifically for Windows.

Check the Arch wiki on SSH keys to achieve what you want. It applies to other Linux distros

https://wiki.archlinux.org/title/SSH_keys

What about a hardware key? Like nitrokey or yubikey?

I use keychain.

Your ssh private keys are safe, assuming nobody has physical access to your home directory. You can configure them to not require a password.

If someone has physical access to your computer then they could become compromised. If you are worried about that you could encrypt the whole drive.

I store my keys via KeepassXC. They got a Freedesktop.org secret service and key-agent integration