What you’re describing here is a serverless DB like Fauna, which you can use with Redwood if you set up your own connection and queries. (Note: currently this has only been done as a proof of concept.)
The Tutorial uses Heruko for 2 reasons: the free option and ease of setup. But I can’t say I know of anyone using a Heroku DB for production. One alternative that’s been low cost and performant is Digital Ocean. See a thread here.