Having to just manage a database is far easier infra wise than a complex system for small rails operations.
Scaling up there will always be strong needs for complexity, but doesn't mean you can't get really far without it.
If you really do outgrow it, only then do you have to pay the cost to move parts of your system to something more specialized. Hopefully by then you’ve achieved enough success & traction to justify doing so.
Should be the default mindset for any new project if you don’t have very demanding performance & throughput needs, IMO.
My rule of thumb is: PG for transactional data consistency, Clickhouse for OLAP. Maybe Elasticsearch if a full-text search is really needed.
Keep postgres as the source of truth.
My point isn't that this will scale. It's that you can get really really far without complexity and then tack on as needed. This is just another bit of complexity removal for early tech. I'd use this in a heart beat.
That said, putting the queue, cache, etc in the database is actually relatively new in the Rails world.
As a result, the process of moving an existing app with a presumably stable infrastructure to a db-only one (often a separate db from your primary app) actually adds complexity/uncertainty, at least until you restabilize things.
This has been my experience too, unfortunately. Before Que hit v1.0 things were definitely dicey trying to find an ACID compliant queue library for Rails–everyone was so into Redis and Sidekiq, but Que is still the only ACID queue lib around for longer than four years if that is meaningful to you.