Why the big difference in the timing/duration on upgrade of Amazon Aurora PostgreSQL

The discrepancy in upgrade times between your smaller and larger Amazon Aurora PostgreSQL databases is indeed puzzling, as typically larger databases are expected to take longer to upgrade. However, several factors can influence the upgrade duration, which might explain this unexpected result:

1. Database complexity: The upgrade process is not solely dependent on database size. The number and complexity of objects within the database, such as tables, indexes, stored procedures, and triggers, can significantly impact upgrade time. A smaller database with more complex structures might take longer to upgrade than a larger one with simpler structures.

2. Data distribution: The way data is distributed across tables can affect upgrade performance. If the smaller database has more tables with unevenly distributed data or more fragmented indexes, it might require more time for the upgrade process to reorganize and optimize these structures.

3. Caching and memory utilization: The upgrade process involves various operations that benefit from caching. If the larger database's working set fits better within the available memory, it might experience fewer I/O operations, leading to faster upgrade times.

4. Background processes: After an upgrade, Aurora PostgreSQL performs various background tasks to ensure database consistency and apply necessary changes. The extent of these processes can vary between databases and may contribute to perceived differences in upgrade duration.

5. Query optimizer statistics: The upgrade process may involve rebuilding query optimizer statistics. If the smaller database requires more extensive statistics updates, it could lead to a longer overall upgrade time.

6. Resource utilization: While both clusters use the same instance class, the actual resource utilization during the upgrade might differ. The larger database might be utilizing the available CPU and memory more efficiently, resulting in a faster upgrade.

7. Number of database objects: The upgrade duration can vary with the number of database objects such as tables, columns, routines, and constraints. A database with a large number of objects can take longer to upgrade, regardless of its overall size.

To better understand the cause of this discrepancy, you may want to:

1. Review the upgrade logs for both databases to identify any specific steps that took longer in the smaller database.
2. Compare the number and complexity of database objects between the two databases.
3. Monitor resource utilization during the upgrade process for both databases.
4. Consider running ANALYZE and REINDEX operations on both databases after the upgrade to ensure optimal performance.

Remember that upgrade times can vary, and a single comparison may not be representative of typical behavior. It's advisable to perform multiple test upgrades to get a more accurate picture of expected upgrade durations.
Sources
Aurora RDS slow performance | AWS re:Post
Major version upgrade prechecks for Aurora MySQL - Amazon Aurora

The largest factor in Postgres to perform major version upgrades is related to #7 in the above answer, "number of database objects". For example, a system a database with a 100,000 tables versus one with 1.000 tables. Raw size (1 Tb vs 100 Gb) normally doesn't play a role unless a certain Postgres version alters its page formats and has to re-write everything. That hasn't happened for probably around 20+ years...