RDS on-demand server upgrade does not stay in the same Availability Zone

Question

Dear AWS Architect and Support:

We are runnning postgres 14.12 on an on-demand db.m5.2xlarge server with multi-az.  Server is at us-west-2a and secondary node is also at us-west-2a.  Let's call the Availability Zones 2a and 2b.   Our data occupied about 1.2T of space on the storage.
There is a mission to update the database including
create new indexes
Vacuum/Analyze some large tables
Create relationships
change some primary key fields on tables from characters to BIGINT

where all above statements took about 19 hours on db.m2.2xlarge and we wish to cut that amount of time as much as we can.  Since pgdump and import took too much time, we are going through another route presented below.

The idea is to 
* upgrade our DB from db.m5.2xlarge multi-zone to db.m5.16xlarge non multi-zone. I think non multi-az will run faster without dual commits.
* execute DB statements/operations on this much larger non multi-az instance and downgrade it back to db.m5.2xlarge multi-zone

So we are going through some experimental steps to prove the above idea is applicable

1. Create a snapshopt from the real DB which is db.m5.2xlarge.   The snapshot is at 2a
2. Create a db.m5.2xlarge multi-az from the snapshot as a duplicated DB as our playground
3. upgrade #2 to db.m5.16xlarge non-multi-az, run all DB statements and hopefully to cut down the time dramatically.
4. downgrade #3 single non-multi-az back to db.m2.2xlarge multi-az.

Our issue is from 1->2, there is no guarantee the db instance created off snapshot will be on 2a.  We tried a couple of times and it seems like if we chose the same security group between the original DB and #2 duplciated DB will make the instance on 2a.  Is that(using same SG) a correct course of action to create a duplicated instance on the very same AZ?

Also, from 2 -> 3, we found upgrading db.m5.2xlarge to db.m5.16xlarge has no guarantee to create the larger DB in 2a.  In fact, sometimes we could see the DB was being created at 2a in the beginning but during space optimization step, it switch to 2b.  Duing instance size upgrade, aws web console has not choice of which AZ to be the target.  How could we ensure the larger instance is created on 2a?

Again, #4, we are worrying when we downgrade a 16xlarge non multi-az to 2xlarge multi-az if everything will be on 2a.  If there is a way, how could we secure the downgrade from x16large non multi-az to x2large multi-az will still be on 2a?

Does AWS data migration service transfer data much faster than using either pgdum or snapshot?

we have many EC2s, lambdas and Fargate accessing the DB so we don't want to increase the cost by seeing the final result is a DB on 2b instead of 2a.

re:Post Agent · Answer

Thank you for providing such a detailed explanation of your situation and concerns. I'll address your questions and provide some insights based on the information available.

Regarding your approach to upgrade, perform operations, and then downgrade:

1. Creating a DB instance from a snapshot in the same Availability Zone (AZ):
There is no guaranteed way to ensure that a new DB instance created from a snapshot will be in the same AZ as the original. Using the same security group does not guarantee AZ placement. Amazon RDS automatically chooses the AZ when creating a new instance, and this choice is not directly controllable by users.

2. Upgrading from db.m5.2xlarge to db.m5.16xlarge and AZ placement:
When modifying an instance size, Amazon RDS may need to move the instance to a different AZ due to resource availability. There's no way to ensure the larger instance stays in the same AZ (2a in your case) during the upgrade process.

3. Downgrading from 16xlarge non-multi-AZ to 2xlarge multi-AZ:
When converting back to a multi-AZ deployment, you cannot control which AZ will host the primary instance. Amazon RDS manages this automatically.

Given these limitations, your proposed approach may not guarantee the AZ consistency you're looking for. However, there are some alternatives you could consider:

1. Instead of changing instance sizes, you could use Amazon RDS Performance Insights to identify and optimize the slow queries in your current setup.

2. For the specific operations you mentioned (creating indexes, vacuum/analyze, etc.), consider using maintenance windows and optimizing your queries rather than changing instance sizes.

3. If you need to perform these operations on a larger instance, you might want to create a new larger instance in your preferred AZ (if possible), perform the operations there, and then use a snapshot or logical replication to bring the changes back to your original instance.

Regarding your question about AWS Database Migration Service (DMS):
DMS can be faster than pg_dump or snapshots in some scenarios, especially for large databases or when you need to minimize downtime. However, its performance depends on various factors such as network bandwidth, instance sizes, and data characteristics. For your specific use case, it might not provide significant advantages over your current approach.

Lastly, it's important to note that while keeping your resources in the same AZ can potentially reduce latency, it also increases the risk of all your resources being affected if there's an AZ-level issue. Amazon RDS is designed to work efficiently across AZs, so the performance impact of being in different AZs is usually minimal.

If AZ consistency is crucial for your application architecture, you might want to consider redesigning your application to be more AZ-agnostic, or explore using Amazon Aurora, which offers different multi-AZ deployment options that might better suit your needs.  
**Sources**  
[Multi-AZ DB instance deployments for Amazon RDS - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZSingleStandby.html)  
[Upgrading the engine version of a Multi-AZ DB cluster for Amazon RDS - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/multi-az-db-clusters-upgrading.html)

Gary Mclean · Answer

**The AI Answer is Wrong I am afraid.**

When you create a DB(restore) a snapshot under the VPC settings, you have the ability to Choose which AZ you want to create the Database Instance in. By default its set to no prefence. You can leave it or set an AZ as you wish.

![Enter image description here](/media/postImages/original/IMkmokpYYlQZ2H-J-2HVHAxQ)

On restoring the snapshot, it will be Single AZ also.

Giulio Iannazzo · Answer

Hello,

you can also use the CLI, even for the upgrade/downgrade part, with the *rds modify-db-instance* command, which allows to specify the destination AZ. You just need to disable the Multi-AZ deployment option first.

RDS on-demand server upgrade does not stay in the same Availability Zone

Add your answer

Relevant content