Junior-ish DevOps with some blue/green experience.

It’s a very thorny problem, and I think your willingness to put up with the trade offs really would drive what patttern of architectecture.

Most of our blue/green deployment types use a unitary database behind the backend infra. There’s a lot ways to implement changes to the database (mostly done through scripting in the pipeline, we don’t typically use hibernate or other functionality that wants to control the schema more directly), and it avoids the pain of trying to manage consistency with multiple db instances. It helps that most of our databases are document types, so a lot of db changes can be implemented via flag. But I’ve seen some SQL implementations for table changes that lend themselves to blue/green - you just have to be very mindful to not Bork the current live app with what you’re doing in the background. It requires some planning - not just “shove the script into source control and fire the pipeline.”

If we were using SQL with a tightly integrated schema and/or we couldn’t feature flag, I think we’d have to monkey around with blue/greening the database as well. But consistency is non trivial, especially depending on what kind of app it is. And at least one time when a colleague set up a database stream between AWS accounts, he managed a circular dependency, which….well it wasn’t prod so it wasn’t a big deal, but it easily could’ve been. The data transfer fees are really what kills you. We managed to triple our Dev AWS bill prototyping some database streams at one point. Some of it undoubtedly was inefficient code, but point stands. With most blue/green infra, your actual costs are a lot less than 2x what a ‘unitary’ infra would cost, because most infra is pay for use and isn’t necessary except when you go to deploy new code anyway. But database consistency, at least when we tried it, was way MORE expensive than just 2x the cost of a unitary db, because of the compute and transfer fees.