In terms of PAM account credential rotation, you link a second privileged account to do the rotation on the target account. Meaning that you have the rotating account, and another that does the rotation. The access process is different based on account type.
For example, a Postgres rotation would consist of the privileged account automatically accessing the rotating account through your gateway and running a SQL command to change the password.
The "long lived" part in this setup would only be the credential of the privileged rotating account. But you can solve this by having this privileged account rotate itself, or just have another account rotate it. This means there would be zero long-lived credentials.
Every account credential is encrypted in the database, so a database leak would not automatically give an attacker the secrets unless they also have your environment (with the encryption key).