There are generally two classes for FDWs: Postgres<->Postgres and Postgres->Everything else.
The first one is generally suitable for production and is very useful for sharded Postgres when you want to communicate across shards without having to go back out through the application.
The second one's mileage really varies. Some implementations might or might not be prod ready or mig target only specific version combinations. Can be very useful for data engineering or analytics use cases for quick ETL into a staging database. Or for data migrations between database vendors.
At my previous workplaces: no it has always been an unmitigated disaster. And in one case I think moving from the original monolith to microservice based architecture killed the business.
At my current workplace: yes! We have about a dozen separate "microservices" and it works pretty well actually. The main difference I can see is that the boundary layers are extremely well defined and completely asynchronous. There are no synchronous REST or RPC calls between services at all. And it actually works!
I think you're missing the key part here. Most tech workers do have some ownership in the companies which muddies the calculus a little bit. But if you don't have a stake in the company you are literally trading time for money. Work more time for the same money and you're just under-pricing your labor by an equivalent amount.
Also, if you need to work overtime to get good recommendations and promotions that is not a company you want to spend your time at.
Also also, if you need to work overtime to care about the work you're doing then you should re-examine your value system.
I'd say the real value of the 'application-like' capabilities of Postgres is for analytics and offline tasks where there may not even be an application to 'eat'. This article is a great example, anyone can get this working on their laptop easily and just feed new data into the structure they have created and trust that it will just work.
Now, if this is powering some actual business logic then that's can cause problems since now your application is tightly coupled to a specific implementation in a specific database technology that may or may not be testable, versioned, or observable. But if you're a data hacker trying to answer questions, produce a report, or tease out features for a ML model having a such a robust easy to manage tool in your toolbelt is super useful.
Application Postgres and Analytics Postgres are really different animals, it's pretty cool that one piece of software can do both so well and be so many different things to different people, but yeah I agree you can into real trouble if you use the wrong part of the tool for the job.
The first one is generally suitable for production and is very useful for sharded Postgres when you want to communicate across shards without having to go back out through the application.
The second one's mileage really varies. Some implementations might or might not be prod ready or mig target only specific version combinations. Can be very useful for data engineering or analytics use cases for quick ETL into a staging database. Or for data migrations between database vendors.