Part of the design consideration here should be limiting SPOFs like this and creating bounded isolated components which interact with their own persistence laye

Part of the design consideration here should be limiting SPOFs like this and creating bounded isolated components which interact with their own persistence layer. If you're in a traditional three tier architecture you might run into your database being overloaded by the autoscaling behavior but this is fundamentally a provisioning problem. These problems are better served in models that are scalable. Your suggestion to by a (necessarily overprovisioned) on prem database server is cost inefficient, degrades in value over time, and is effectively a lock in. Embracing eventual consistency is not that scary!