If the pepper is stored as an environment variable, adding it in addition to the salt can be a minor increase in the password security. The thing is it often isn't that hard to get if someone has access to your server.
If it's just the db that is exposed though, it could be a small added layer of protection.
And yes, I would not ever do it instead of a salt.
Would in this scenario be running each service (database/backend/frontend) in separated clouds/envirorments be benefitial to mitigating risks?
Let me explain. for example, I might have my NextJS frontend in Vercel using it's secret management /env tools.
The backend as a vanilla node-apollo-server-express could probably be on a cheap VPS, being monitored/restarted/load-balanced by PM2.
The database would be cloud, either PostgreSQL as a Service, or Fauna or something.
Would this scenario be better than just cramming everything into a VPS and trying to get that as secure/closed down as possible and be done with it (do monthly updates and whatnot)...
I've faced recently this conundrum at job. Small new app will not have more than a few hundred concurrent users optimistically...
As @wongarsu said, I think running the DB separately makes a lot of sense. Also, that (in theory) protects any ENV variables on the main host server from exposure if your DB is compromised. And the DB is likely better protected and up-to-date if you use a DB-as-a-service. But I wouldn't go extreme either. There are a lot of hidden costs associated with utilizing additional machines, whether they be via lamda functions, VPSs, etc.
One other good thing about keeping things separate is updates in theory should be easier, as besides OS stuff, you only have a handful of applications on each machine. But again, for small apps, it's generally not worth the extra complexity. 3 platforms is likely as far as I go (client-side server, API, DB) in the majority of cases with small apps, and most of the time I'd probably just go with 2 assuming Node can serve both the client and API.
Theoretically, more separation means more security. On the other hand, securing many things is harder than securing few things.
PostgreSQL as a service (or whatever DB you prefer) is worth it, that means somebody else can get backups, security relevant settings, patches, version upgrades etc right. Everything else is probably fine on one server, but it doesn't sound like that would be much besides the backend in your case anyways.
If it's just the db that is exposed though, it could be a small added layer of protection.
And yes, I would not ever do it instead of a salt.